Пакетная вставка в таблицу SQL Server из DataTable с использованием соединения ODBC

Меня попросили найти наиболее эффективный способ получения входных данных DataTable и записи их в таблицу SQL Server с использованием C#. Проблема в том, что решение должно использовать ODBC Connections повсюду, это исключает sqlBulkCopy. Решение также должно работать на всех версиях SQL Server до SQL Server 2008 R2.

Я думаю, что лучшим подходом было бы использовать пакетные вставки по 1000 строк за раз, используя следующий синтаксис SQL:

INSERT INTO dbo.Table1 (Field1, Field2) SELECT Value1, Value2 UNION SELECT Value1, Value2

Я уже написал код, проверяющий, существует ли на SQL Server таблица, соответствующая входным данным DataTable, и создаю ее, если ее нет.

Я также написал код для создания самого оператора INSERT. Я борюсь с тем, как динамически создавать операторы SELECT из строк в таблице данных. Как я могу получить доступ к значениям в строках, чтобы построить мой оператор SELECT? Я думаю, что мне также нужно будет проверить тип данных каждого столбца, чтобы определить, должны ли значения быть заключены в одинарные кавычки (') или нет.

Вот мой текущий код:

        public bool CopyDataTable(DataTable sourceTable, OdbcConnection targetConn, string targetTable)
    {
        OdbcTransaction tran = null;
        string[] selectStatement = new string[sourceTable.Rows.Count];

        // Check if targetTable exists, create it if it doesn't
        if (!TableExists(targetConn, targetTable))
        {
            bool created = CreateTableFromDataTable(targetConn, sourceTable);

            if (!created)
                return false;
        }

        try
        {
            // Prepare insert statement based on sourceTable
            string insertStatement = string.Format("INSERT INTO [dbo].[{0}] (", targetTable);

            foreach (DataColumn dataColumn in sourceTable.Columns)
            {
                insertStatement += dataColumn + ",";
            }

            insertStatement += insertStatement.TrimEnd(',') + ") ";

            // Open connection to target db
            using (targetConn)
            {
                if (targetConn.State != ConnectionState.Open)
                    targetConn.Open();

                tran = targetConn.BeginTransaction();

                for (int i = 0; i < sourceTable.Rows.Count; i++)
                {
                    DataRow row = sourceTable.Rows[i];

                    // Need to iterate through columns in row, getting values and data types and building a SELECT statement

                    selectStatement[i] = "SELECT ";
                }

                insertStatement += string.Join(" UNION ", selectStatement);

                using (OdbcCommand cmd = new OdbcCommand(insertStatement, targetConn, tran))
                {
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();
                return true;
            }
        }       
        catch 
        {
            tran.Rollback();
            return false;
        }
    }

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

Источник

2 ответа

Решение

Хорошо, так как мы не можем использовать хранимые процедуры или Bulk Copy; когда я моделировал различные подходы пару лет назад, ключевым фактором, определяющим производительность, было количество обращений к серверу. Таким образом, пакетирование набора операторов MERGE или INSERT в один вызов, разделенный точками с запятой, оказалось самым быстрым методом. Я закончил пакетировать свои операторы SQL. Я думаю, что максимальный размер оператора SQL был 32 КБ, поэтому я разделил пакет на единицы такого размера.

(Примечание - используйте StringBuilder вместо объединения строк вручную - это оказывает положительное влияние на производительность)

Psuedo-code
string sqlStatement = "INSERT INTO Tab1 VALUES {0},{1},{2}";
StringBuilder sqlBatch = new StringBuilder();
foreach(DataRow row in myDataTable)
{
    sqlBatch.AppendLine(string.Format(sqlStatement, row["Field1"], row["Field2"], row["Field3"]));
    sqlBatch.Append(";");
}
myOdbcConnection.ExecuteSql(sqlBatch.ToString());

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

Источник

Отмеченное решение PhillipH открыто для нескольких ошибок и SQL-инъекций.

Обычно вы должны построить DbCommand с параметрами и выполнить это вместо выполнения оператора SQL самостоятельной сборки.

CommandText должен быть "INSERT INTO Tab1 VALUES ?,?,?" для ODBC и OLEDB SqlClient нужны именованные параметры ("@").

Параметры должны быть добавлены с размерами лежащего в основе столбца.

Источник
Другие вопросы по тегам