Эффективный способ создания таблицы в базе данных или ее обновления с помощью C #

От: Из: Общие

У меня есть безголовое приложение, которое выполняет одно из двух действий.

  1. Если таблица не существует в базе данных, создайте ее и скопируйте все данные.

ИЛИ

  1. Если таблица существует, обновите ее

Сейчас я обычно использую уровни доступа, но это приложение немного старше, поэтому я не могу. Это то, что я написал.

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System;
using System.Text.RegularExpressions;
using System.Reflection;

namespace FPS_ClientTable_Mirroring
{
    class DoWorkController
    {
        private ApplicationSettings applicationSettings;

        internal int Start(ApplicationSettings applicationSettings)
        {

            string sourceCS = applicationSettings.MSWConnectionString;
            string destinationCS = applicationSettings.ClientTableConnectionString;
            int doesDbExist = 0;


            try
            {
                this.applicationSettings = applicationSettings;
                DateTime yesterday = applicationSettings.ProcessingDate.AddDays(-1);

                applicationSettings.Logger.Info("Checking for EODTable within ClientTable");
                SqlConnection connectionClientTable = new SqlConnection(destinationCS);
                SqlCommand cmd = new SqlCommand("SELECT CASE WHEN OBJECT_ID('dbo.EODTable', 'U') IS NOT NULL THEN 1 ELSE 0 END", connectionClientTable);
                connectionClientTable.Open();
                doesDbExist = Convert.ToInt32(cmd.ExecuteScalar());

                if (doesDbExist == 1)
                {
                    applicationSettings.Logger.Info("Found EODTable within ClientTable");
                    applicationSettings.Logger.Info("Updating EODTable within ClientTable");
                    connectionClientTable.Close();

                    using (SqlConnection mswConnection = new SqlConnection(sourceCS))
                    {
                        SqlCommand cmdCopyData = new SqlCommand("Select * from EODTable where TxnDT = @yesterday", mswConnection);
                        cmdCopyData.Parameters.Add(new SqlParameter("@yesterday", yesterday));
                        mswConnection.Open();
                        using (SqlDataReader rdr = cmdCopyData.ExecuteReader())
                        {
                            using (SqlConnection ClientTableConnection = new SqlConnection(destinationCS))
                            {
                                using (SqlBulkCopy bc = new SqlBulkCopy(ClientTableConnection))
                                {
                                    bc.DestinationTableName = "EODTable";
                                    ClientTableConnection.Open();
                                    bc.WriteToServer(rdr);




                                }
                            }
                        }
                    }
                    applicationSettings.Logger.Info("Finished updating EODTable within ClientTable");
                }
                else
                {
                    applicationSettings.Logger.Info("EODTable was not found within ClientTable");
                    applicationSettings.Logger.Info("Creating EODTable within ClientTable");



                    SqlCommand buildTableCommand = new SqlCommand("CREATE TABLE EODTable(ID bigint, RecID char(64), rectype char(1), mercid varchar(15), termid char(8), edcbatch char(3), batchprdt char(8), batchsrc char(13)");
                    buildTableCommand.Connection = connectionClientTable;
                    buildTableCommand.ExecuteNonQuery();
                    connectionClientTable.Close();

                    using (SqlConnection mswConnection = new SqlConnection(sourceCS))
                    {
                        SqlCommand cmdCopyData = new SqlCommand("Select * from EODTable", mswConnection);
                        mswConnection.Open();
                        using (SqlDataReader rdr = cmdCopyData.ExecuteReader())
                        {
                            using (SqlConnection ClientTableConnection = new SqlConnection(destinationCS))
                            {
                                using (SqlBulkCopy bc = new SqlBulkCopy(ClientTableConnection))
                                {
                                    bc.DestinationTableName = "EODTable";
                                    ClientTableConnection.Open();
                                    bc.WriteToServer(rdr);


                                }
                            }
                        }
                    }
                    applicationSettings.Logger.Info("Finished creating EODTable within ClientTable");
                }


                applicationSettings.Logger.Info("Checking for EODPayments within ClientTable");

                SqlCommand cmdCheckMrPayment = new SqlCommand("SELECT CASE WHEN OBJECT_ID('dbo.EODPayments', 'U') IS NOT NULL THEN 1 ELSE 0 END", connectionClientTable);
                connectionClientTable.Open();
                doesDbExist = Convert.ToInt32(cmdCheckMrPayment.ExecuteScalar());

                if (doesDbExist == 1)
                {
                    applicationSettings.Logger.Info("Found EODPayments within ClientTable");
                    applicationSettings.Logger.Info("Updating EODPayments within ClientTable");
                    connectionClientTable.Close();

                    using (SqlConnection mswConnection = new SqlConnection(sourceCS))
                    {
                        SqlCommand cmdCopyData = new SqlCommand("Select * from EODPayments where TransactionDate = @yesterday", mswConnection);
                        cmdCopyData.Parameters.Add(new SqlParameter("@yesterday", yesterday));
                        mswConnection.Open();
                        using (SqlDataReader rdr = cmdCopyData.ExecuteReader())
                        {
                            using (SqlConnection ClientTableConnection = new SqlConnection(destinationCS))
                            {
                                using (SqlBulkCopy bc = new SqlBulkCopy(ClientTableConnection))
                                {
                                    bc.DestinationTableName = "EODPayments";
                                    ClientTableConnection.Open();
                                    bc.WriteToServer(rdr);



                                }
                            }
                        }
                    }
                    applicationSettings.Logger.Info("Finished updating EODPayments within ClientTable");
                }
                else
                {
                    applicationSettings.Logger.Info("EODPayments was not found within ClientTable");
                    applicationSettings.Logger.Info("Creating EODPayments within ClientTable");



                    SqlCommand buildTableCommand = new SqlCommand("CREATE TABLE EODPayments(ID bigint, ProdID bigint, MercID nchar(15), Currency nchar(3), DbCr nchar(1), Amount float, " +
                        "Institution nchar(10), Transit nchar(10), AccountNumber nchar(16), EsiAppID nchar(10), TxnType int, ProdType nchar(1), TxnCode nchar(10), TxnDesc nchar(56), " +
                        "TransactionDate datetime, CoreBankingID bigint, Processed bit, ProcessedDate datetime, DataWarehouse bit, DataWarehouseDate datetime, GLInstitution nchar(10), " +
                        "GLTransit nchar(10), Converted bit, OriginalAmount float, OriginalCurrency nchar(3), Rate float, EntryID nvarchar(32), GLID nvarchar(32), NewCurrency char(3), SendRate bit) ");
                    buildTableCommand.Connection = connectionClientTable;
                    buildTableCommand.ExecuteNonQuery();
                    connectionClientTable.Close();

                    using (SqlConnection mswConnection = new SqlConnection(sourceCS))
                    {
                        SqlCommand cmdCopyData = new SqlCommand("Select * from EODPayments", mswConnection);
                        mswConnection.Open();
                        using (SqlDataReader rdr = cmdCopyData.ExecuteReader())
                        {
                            using (SqlConnection ClientTableConnection = new SqlConnection(destinationCS))
                            {
                                using (SqlBulkCopy bc = new SqlBulkCopy(ClientTableConnection))
                                {
                                    bc.DestinationTableName = "EODPayments";
                                    ClientTableConnection.Open();
                                    bc.WriteToServer(rdr);
                                }
                            }
                        }
                    }
                    applicationSettings.Logger.Info("Finished creating EODPayments within ClientTable");
                }

                applicationSettings.Logger.Info("Checking for EODRentals within ClientTable");

                SqlCommand cmdCheckRentalFees = new SqlCommand("SELECT CASE WHEN OBJECT_ID('dbo.EODRentals', 'U') IS NOT NULL THEN 1 ELSE 0 END", connectionClientTable);
                connectionClientTable.Open();
                doesDbExist = Convert.ToInt32(cmdCheckRentalFees.ExecuteScalar());

                if (doesDbExist == 1)
                {
                    applicationSettings.Logger.Info("Found EODRentals within ClientTable");
                    applicationSettings.Logger.Info("Updating EODRentals within ClientTable");
                    connectionClientTable.Close();

                    using (SqlConnection mswConnection = new SqlConnection(sourceCS))
                    {
                        SqlCommand cmdCopyData = new SqlCommand("Select * from EODRentals where DateProcessed = @yesterday", mswConnection);
                        cmdCopyData.Parameters.Add(new SqlParameter("@yesterday", yesterday));
                        mswConnection.Open();
                        using (SqlDataReader rdr = cmdCopyData.ExecuteReader())
                        {
                            using (SqlConnection ClientTableConnection = new SqlConnection(destinationCS))
                            {
                                using (SqlBulkCopy bc = new SqlBulkCopy(ClientTableConnection))
                                {
                                    bc.DestinationTableName = "EODRentals";
                                    ClientTableConnection.Open();
                                    bc.WriteToServer(rdr);



                                }
                            }
                        }
                    }
                    applicationSettings.Logger.Info("Finished updating EODRentals within ClientTable");
                }
                else
                {
                    applicationSettings.Logger.Info("EODRentals was not found within ClientTable");
                    applicationSettings.Logger.Info("Creating EODRentals within ClientTable");



                    SqlCommand buildTableCommand = new SqlCommand("CREATE TABLE EODRentals(ID bigint, Year nchar(4), Month nchar(12), DateProcessed datetime, CoreBankingID bigint, " +
                        "MerchantRecordID bigint, TerminalRecordID bigint, DeployedDate datetime, RecoveredDate datetime, MonthlyFee float, IsProRated bit, DaysActive int, TotalFee float, " +
                        "IsPinPad bit, Currency nchar(3)) ");
                    buildTableCommand.Connection = connectionClientTable;
                    buildTableCommand.ExecuteNonQuery();
                    connectionClientTable.Close();

                    using (SqlConnection mswConnection = new SqlConnection(sourceCS))
                    {
                        SqlCommand cmdCopyData = new SqlCommand("Select * from EODRentals", mswConnection);
                        mswConnection.Open();
                        using (SqlDataReader rdr = cmdCopyData.ExecuteReader())
                        {
                            using (SqlConnection ClientTableConnection = new SqlConnection(destinationCS))
                            {
                                using (SqlBulkCopy bc = new SqlBulkCopy(ClientTableConnection))
                                {
                                    bc.DestinationTableName = "EODRentals";
                                    ClientTableConnection.Open();
                                    bc.WriteToServer(rdr);
                                }
                            }
                        }
                    }
                    applicationSettings.Logger.Info("Finished creating EODRentals within ClientTable");
                }

                return 0;
            }
            catch (System.Exception ex)
            {
                applicationSettings.Logger.Fatal(ex.ToString());
                return applicationSettings.DefaultFailedExitCode;
            }
        }
    }
}

Я надеялся, что смогу понять, как сделать это немного более эффективным в том, что он делает. Я также не уверен, как лучше всего справиться с обработкой ошибок, например, между созданием таблицы соединение теряется.

Любая помощь будет принята с благодарностью!

2 ответа
2

У вас много избыточного кода. Так же Start метод очень длинный и трудный для чтения. По сути, он трижды выполняет одну и ту же процедуру, применяемую к разным таблицам. Идея состоит в том, чтобы создать параметризованный метод, который можно было бы вызывать трижды для трех таблиц. Это увеличивает ремонтопригодность кода. Его подпись

private int CreateOrUpdateTable(
    string tableName, string dateColumn, string createTableSql)

Мы можем сделать код более читабельным, создав несколько вспомогательных методов. Время вызова методов (несколько наносекунд) ничтожно по сравнению со временем, необходимым для выполнения операций с БД (от миллисекунд до секунд или даже минут). Даже для миллисекунд у нас есть коэффициент 1 миллион до наносекунд.

private void LogInfo(string message)
{
    applicationSettings.Logger.Info(message);
}

private static bool TableExists(SqlConnection connection, string tableName)
{
    using var cmd = new SqlCommand(
        $"SELECT CASE WHEN OBJECT_ID('dbo.{tableName}', 'U') IS NOT NULL THEN 1 ELSE 0 END",
        connection);
    return (int)cmd.ExecuteScalar() == 1;
}

В обоих случаях (таблица существует или нет) используется один и тот же код для выполнения массового копирования. Мы также извлекаем его в метод:

private static void ExecuteBulkCopy(
    string tableName, SqlConnection connectionClientTable, SqlCommand cmdCopyData)
{
    using SqlDataReader rdr = cmdCopyData.ExecuteReader();
    using var bc = new SqlBulkCopy(connectionClientTable);
    bc.DestinationTableName = tableName;
    bc.WriteToServer(rdr);
}

Мы можем использовать новые удобные объявления using (я думаю, начиная с C # 8.0). Они не требуют вложенных блоков кода, как это делают операторы using.

Вы создаете, открываете и закрываете одни и те же соединения в разных местах. Создавая и открывая оба соединения перед оператором if, мы можем еще больше упростить. Также не требуется явное закрытие соединений, поскольку объявления (или операторы) using делают это автоматически.

В итоге мы получаем такую ​​реализацию:

private int CreateOrUpdateTable(string tableName, string dateColumn, string createTableSql)
{
    string sourceCS = applicationSettings.MSWConnectionString;
    string destinationCS = applicationSettings.ClientTableConnectionString;

    try {
        LogInfo($"Checking for {tableName} within ClientTable");

        using SqlConnection connectionClientTable = new SqlConnection(destinationCS);
        connectionClientTable.Open();

        using SqlConnection mswConnection = new SqlConnection(sourceCS);
        mswConnection.Open();

        if (TableExists(connectionClientTable, tableName)) {
            LogInfo($"Found {tableName} within ClientTable");
            LogInfo($"Updating {tableName} within ClientTable");

            using var cmdCopyData = new SqlCommand($"Select * from {tableName} where {dateColumn} = @yesterday", mswConnection);
            DateTime yesterday = applicationSettings.ProcessingDate.AddDays(-1);
            cmdCopyData.Parameters.Add(new SqlParameter("@yesterday", yesterday));
            ExecuteBulkCopy(tableName, connectionClientTable, cmdCopyData);

            LogInfo($"Finished updating {tableName} within ClientTable");
        } else {
            LogInfo($"{tableName} was not found within ClientTable");
            LogInfo($"Creating {tableName} within ClientTable");

            var buildTableCommand = new SqlCommand(createTableSql, connectionClientTable);
            buildTableCommand.ExecuteNonQuery();

            var cmdCopyData = new SqlCommand($"Select * from {tableName}", mswConnection);
            ExecuteBulkCopy(tableName, connectionClientTable, cmdCopyData);

            LogInfo($"Finished creating {tableName} within ClientTable");
        }
        return 0;
    } catch (System.Exception ex) {
        applicationSettings.Logger.Fatal(ex.ToString());
        return applicationSettings.DefaultFailedExitCode;
    }
}

В Start метод становится

internal int Start(ApplicationSettings applicationSettings)
{
    this.applicationSettings = applicationSettings;

    int result = CreateOrUpdateTable("EODTable", "TxnDT", "CREATE TABLE EODTable ...");
    if (result != 0) return result;

    result = CreateOrUpdateTable("EODPayments", "TransactionDate", "CREATE TABLE EODPayments ...");
    if (result != 0) return result;

    return CreateOrUpdateTable("EODRentals", "DateProcessed", "CREATE TABLE EODRentals ...");
}

Длинные операторы создания таблицы могут быть созданы как строковые ресурсы проекта. Это позволяет писать их с разрывами строк и отступами и не загромождает код C #.

  • Мне очень понравилось, это работало как шарм!

    — Адил15

Я бы предложил создать абстрактный класс для построения минимальных Sql защищенные методы, такие как ExecuteNonQuery, ExecuteScalar, и ExecuteReader ..так далее. эти методы будут использоваться в унаследованных классах (репозиториях). Таким образом, каждый репозиторий будет работать только с одной таблицей, что даст вам определенную работу. Это придаст вашему проекту большую гибкость и ремонтопригодность.

Итак, вы можете начать с абстрактного класса:

public abstract class SqlRepoistoryBase
{
    private readonly string _connectionString; 
    
    protected SqlRepoistoryBase(string connectionString)
    {
        _connectionString = connectionString;
    }

    protected void ExecuteCommand(string query)
    {   
        try
        {
            if(string.IsNullOrWhiteSpace(query))
            {
                throw new ArgumentNullException(nameof(query));
            }
            
            using(var connection = new SqlConnection(_connectionString))
            using(var command = new SqlCommand(query, connection))
            {
                connection.Open(); 
                command.ExecuteCommand();
            }
        }
        catch (Exception ex)
        {
            applicationSettings.Logger.Fatal(ex.ToString());
        }
    }

    
    protected object ExecuteScalar(string query)
    {   
        try
        {
            if(string.IsNullOrWhiteSpace(query))
            {
                throw new ArgumentNullException(nameof(query));
            }
            
            using(var connection = new SqlConnection(_connectionString))
            using(var command = new SqlCommand(query, connection))
            {
                connection.Open(); 
                return command.ExecuteScalar();
            }
        }
        catch (Exception ex)
        {
            applicationSettings.Logger.Fatal(ex.ToString());
            return null;
        }
    }

    //...etc
}

затем вы проведете урок для EODTable только, который будет содержать его логику, что-то вроде:

public class SqlEoDRepoistory : SqlRepoistoryBase 
{
    public SqlEoDRepoistory(string connectionString) 
    : base(connectionString)    { }
    
    private bool IsTableExist()
    {
        const string query = $"SELECT CASE WHEN OBJECT_ID('dbo.EODTable', 'U') IS NOT NULL THEN 1 ELSE 0 END";
        
        var queryResult = ExecuteScalar(query);
        
        return int.TryParse(queryResult?.ToString(), out int queryResultInt) && queryResultInt == 1;
    }
    
    private void CreateIfNotExists()
    {
        if(!IIsTableExist())
        {
            const string query = "CREATE TABLE EODTable(ID bigint, RecID char(64), rectype char(1), mercid varchar(15), termid char(8), edcbatch char(3), batchprdt char(8), batchsrc char(13)";
            ExecuteCommand(query);
        }
    }
    
    
    public void CopyData()
    {
        if(!IIsTableExist())
        {
            const string query = "CREATE TABLE EODTable(ID bigint, RecID char(64), rectype char(1), mercid varchar(15), termid char(8), edcbatch char(3), batchprdt char(8), batchsrc char(13)";
            ExecuteCommand(query);
        }
        
        const string query = "Select * from EODTable where TxnDT = @yesterday";
        
        var parameters = new SqlParameter[]
        {
            new SqlParameter("@yesterday", yesterday)
        };
        
        // implement CopyFrom in the base, and reuse it in this class
        CopyFrom(query, parameters, destinationTableName: "EODTable");
        
    }
    
    //.... etc 
}

теперь в основном классе DoWorkController уменьшил бы код до чего-то вроде:

public class DoWorkController 
{
  private ApplicationSettings applicationSettings;
  private readonly SqlEoDRepoistory _eodTableRepoistory;
  
  internal int Start(ApplicationSettings settings)
  {
      
      try
      {
        _eodTableRepoistory = new SqlEoDRepoistory(settings.MSWConnectionString);
        _eodTableRepoistory.CopyData();
        
        // you do the same for the EODPayments
        
        return 0;
      }
    catch (System.Exception ex)
    {
        applicationSettings.Logger.Fatal(ex.ToString());
        return applicationSettings.DefaultFailedExitCode;
    }
  }
    
}

    Похожие записи:

    1. Класс пользователя C # с использованием базы данных SqlServer
    2. Многопоточный регистратор
    3. Программы C и C ++, позволяющие увеличить время доступа к файлам
    4. Цикл по результатам поиска в Selenium
    5. Как добавить в логгер место, откуда вызван код?

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *