Копируем данные из DBF в SQL Server

     Наконец то добрались до основной цели - загрузки данных из dbf на сервер. Это можно реализовать разными способами.

Например, можно читать данные из исходной таблицы построчно и вставлять командой INSERT. Решение в лоб, далеко не самое эффективное. Можно преобразовать их в XML и передать на сервер в таком виде целиком. Такое решение потребует, кроме всего прочего, написания хранимой процедуры.
    С другой стороны, такая задача весьма распространена и было бы странным, если бы уже не было готовых решений. Не будем изобретать велосипед и воспользуемся стандартным классом SqlBulkCopy. Он как раз и предназначен для массовой загрузки на SQL Server данных из различных источников.
    В качестве входящих данных он принимает DataRow, DataTable и IDataReader. Ранее мы как раз использовали для получения данных DataTable. Использовать SqlBulkCopy достаточно просто:


public string CopyToSQL()
        {
            string result;
            OleDbConnection conn = new OleDbConnection();
            SqlConnection connSQL = new SqlConnection();
            DataTable dt = new DataTable();
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\сервер\папка;Extended Properties=dBASE IV;User ID=;Password=;";
            connSQL.ConnectionString = @"Data Source=сервер;Initial Catalog=база;Integrated Security=True";



            try
            {
                    
                conn.Open();
                connSQL.Open();
                SqlBulkCopy bulk = new SqlBulkCopy(connSQL);
                bulk.DestinationTableName = "dbo.таблица";
                bulk.BatchSize = 100000;

                OleDbCommand comm = conn.CreateCommand();
                comm.CommandText = @"SELECT * FROM таблица";
                dt.Load(comm.ExecuteReader());
                bulk.WriteToServer(dt);


            }

            catch (Exception e)
            {
                result = "ошибка копирования" + e.ToString();

            }
            finally
            {
                conn.Close();
                connSQL.Close();
                result = "копирование завершено";

            }




            return result;
        }


В основном код этого метода уже был рассмотрен, потому остановимся на изменениях. Сначала создаются и открываются подключения к файловой базе и SQL Server.
 SqlBulkCopy bulk = new SqlBulkCopy(connSQL); - создаем обьект, передав ему подключение к серверу.
bulk.WriteToServer(dt); - копируем.

    Собственно и все. За исключением пары неприятных моментов.
1- SqlBulkCopy работает как транзакция, либо полностью выполняя копирование, либо, если возникла какая-то ошибка, полностью его не выполняя. При этом в случае возникновения ошибок он не генерирует никаких исключений. Т.е. разобраться в чем проблема может оказаться затруднительно.  Но можно. Основная масса ошибок (если не все) будет возникать при вставке в таблицу, в том случае если на данные в столбцах наложены ограничения. Например, требования уникальности ключа. Тут 2 варианта борьбы:
    - простейший - дать возможность писать в таблицу все подряд. А с корректностью разбираться после вставки данных
    - готовить данные перед подачей их на копирование. Тут сложнее, поскольку у самого SqlBulkCopy никаких возможностей проверки данных нет. Для реализации этого варианта придется либо перебирать данные, загруженные в DataRow и DataTable, либо писать собственный класс, наследующий IDataReader и в нем выполнять проверку и исправление ошибочных значений.


2 - Глюки при копировании больших объёмов. Я не смог за один раз загрузить таблицу длинной порядка трех миллионов записей. Относительно небольшие загружались без вопросов, большая - нет. Обойти это можно установив количество строк в пакете, который будет отсылаться для вставки на сервер:


bulk.BatchSize = 100000;

По умолчанию все данные из исходной таблицы передаются одним пакетом.  Задавая размер нужно помнить, что чем меньше пакеты, тем дольше копирование. Например, таблица с тремя миллионами записей при размере пакета в 100000 строк у меня загружалась около минуты, а при размере пакета в 10 записей - сильно больше часа. 

3 - Память. А хватит ли памяти, чтобы загрузить все в  DataTable? В моём случае для буферизации 300-т мегабайтной таблицы процесс забрал себе около 1,5 гига памяти, что весьма немало, и, что самое неприятное, я начал получать ошибки "out of memory". Чтобы этого не было, придется отказаться от буферизации. В этом случае используем ридер. Все предельно просто, изменения минимальны:

      OleDbDataReader read = comm.ExecuteReader();
      bulk.WriteToServer(read);

Похожие посты:

.