Массовая вставка SQL в C# без вставки значений
Я полностью новичок в C#, поэтому я уверен, что получу много комментариев о том, как отформатирован мой код - я приветствую их. Пожалуйста, не стесняйтесь давать любые советы или конструктивную критику, которые вы можете иметь по пути.
Я создаю очень простое приложение Windows Form, которое в конечном итоге должно брать данные из файла Excel различного размера, возможно, несколько раз в день, и вставлять их в таблицу в SQL Server 2005. После этого хранимая процедура в базе данных выполняет различные задачи обновления и вставки в зависимости от значений, вставленных в эту таблицу.
По этой причине я решил использовать метод массовой вставки SQL, так как не могу знать, вставит ли пользователь только 10 строк - или 10 000 - при любом данном выполнении.
Функция, которую я использую, выглядит следующим образом:
public void BulkImportFromExcel(string excelFilePath)
{
excelApp = new Excel.Application();
excelBook = excelApp.Workbooks.Open(excelFilePath);
excelSheet = excelBook.Worksheets.get_Item(sheetName);
excelRange = excelSheet.UsedRange;
excelBook.Close(0);
try
{
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString =
"Data Source=" + serverName + ";" +
"Initial Catalog=" + dbName + ";" +
"User id=" + dbUserName + ";" +
"Password=" + dbPassword + ";";
using (OleDbConnection excelConn = new OleDbConnection())
{
excelQuery = "SELECT InvLakNo FROM [" + sheetName + "$]";
excelConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=Yes'";
excelConn.Open();
using (OleDbCommand oleDBCmd = new OleDbCommand(excelQuery, excelConn))
{
OleDbDataReader dataReader = oleDBCmd.ExecuteReader();
using (SqlBulkCopy bulkImport = new SqlBulkCopy(sqlConn.ConnectionString))
{
bulkImport.DestinationTableName = sqlTable;
SqlBulkCopyColumnMapping InvLakNo = new SqlBulkCopyColumnMapping("InvLakNo", "InvLakNo");
bulkImport.ColumnMappings.Add(InvLakNo);
sqlQuery = "IF OBJECT_ID('ImportFromExcel') IS NOT NULL BEGIN SELECT * INTO [" + DateTime.Now.ToString().Replace(" ", "_") + "_ImportFromExcel] FROM ImportFromExcel; DROP TABLE ImportFromExcel; END CREATE TABLE ImportFromExcel (InvLakNo INT);";
using (SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn))
{
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
while (dataReader.Read())
{
bulkImport.WriteToServer(dataReader);
}
}
}
}
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
excelApp.Quit();
}
}
Функция работает без ошибок или предупреждений, и если я заменю WriteToServer с помощью ручных команд SQL строки вставляются; но bulkImport ничего не вставляю
ПРИМЕЧАНИЕ. В этом примере есть только одно поле, и в реальной функции, которую я сейчас запускаю для тестирования; но в конце будут вставлены десятки и десятки полей, и я буду делать ColumnMapping для всех них.
Кроме того, как уже говорилось, я знаю, что мой код, вероятно, ужасен - пожалуйста, не стесняйтесь указывать мне любые указатели, которые вы считаете полезными. Я готов и желаю учиться.
Спасибо!
3 ответа
Вот пример для чтения информации о схеме из Excel (здесь мы читаем имена таблиц - имена листов с таблицами в них):
private IEnumerable<string> GetTablesFromExcel(string dataSource)
{
IEnumerable<string> tables;
using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
string.Format("Data Source={0};", dataSource) +
"Extended Properties=\"Excel 12.0;HDR=Yes\""))
{
con.Open();
var schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
tables = schemaTable.AsEnumerable().Select(t => t.Field<string>("TABLE_NAME"));
con.Close();
}
return tables;
}
И вот пример, который превращает SBC из Excel в временную таблицу:
void Main()
{
string sqlConnectionString = @"server=.\SQLExpress;Trusted_Connection=yes;Database=Test";
string path = @"C:\Users\Cetin\Documents\ExcelFill.xlsx"; // sample excel sheet
string sheetName = "Sheet1$";
using (OleDbConnection cn = new OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+
";Extended Properties=\"Excel 8.0;HDR=Yes\""))
using (SqlConnection scn = new SqlConnection( sqlConnectionString ))
{
scn.Open();
// create temp SQL server table
new SqlCommand(@"create table #ExcelData
(
[Id] int,
[Barkod] varchar(20)
)", scn).ExecuteNonQuery();
// get data from Excel and write to server via SBC
OleDbCommand cmd = new OleDbCommand(String.Format("select * from [{0}]",sheetName), cn);
SqlBulkCopy sbc = new SqlBulkCopy(scn);
// Mapping sample using column ordinals
sbc.ColumnMappings.Add(0,"[Id]");
sbc.ColumnMappings.Add(1,"[Barkod]");
cn.Open();
OleDbDataReader rdr = cmd.ExecuteReader();
// SqlBulkCopy properties
sbc.DestinationTableName = "#ExcelData";
// write to server via reader
sbc.WriteToServer(rdr);
if (!rdr.IsClosed) { rdr.Close(); }
cn.Close();
// Excel data is now in SQL server temp table
// It might be used to do any internal insert/update
// i.e.: Select into myTable+DateTime.Now
new SqlCommand(string.Format(@"select * into [{0}]
from [#ExcelData]",
"ImportFromExcel_" +DateTime.Now.ToString("yyyyMMddHHmmss")),scn)
.ExecuteNonQuery();
scn.Close();
}
}
Хотя это будет работать, думая в долгосрочной перспективе, вам нужны имена столбцов, и, возможно, их типы различаются, но это может быть излишним, если делать это с помощью SBC, и вы можете вместо этого напрямую сделать это из OpenQuery сервера MS SQL:
SELECT * into ... from OpenQuery(...)
Я думаю, что было бы очень длинным и грязным ответом, если бы я прокомментировал ваш код, а также дал образцы кода указателя в том же сообщении, поэтому я решил разделить его на два сообщения. Сначала комментарии:
Вы используете автоматизацию, чтобы получить что? У вас уже есть имя листа, как я вижу, и хуже того, что вы делаете app.Quit() в конце. Полностью удалите этот код автоматизации. Если вам нужна информация из Excel (например, имена листов, имена столбцов), вы можете использовать метод GetOleDbSchemaTable OleDbConnecton. Вы можете сделать отображение в основном двумя способами:
- Порядковый номер столбца Excel для имени столбца таблицы SQL
- Имя столбца Excel в имя столбца таблицы SQL
оба сделают. В общем коде, если у вас одинаковые имена столбцов в обоих источниках, но их порядковый номер и количество могут отличаться, вы можете получить имена столбцов из таблицы схемы OleDbConnection и выполнить отображение в цикле.
Вы удаляете и создаете таблицу с именем "ImportFromExcel" для вставки временных данных, тогда почему бы просто не создать временную таблицу SQL-сервера, используя префикс # в имени таблицы? OTOH этот кусок кода немного странный, он будет выполнять импорт из "ImportFromExcel", если он там есть, затем удалит и создаст новый и попытается выполнить массовый импорт в этот новый. При первом запуске SqlBulkCopy (SBC) будет заполнять ImportFromExcel, а при следующем запуске он будет скопирован в таблицу с именем (DateTime.Now ...), а затем очищен через drop и снова создан. Кстати, наименование:
DateTime.Now.ToString().Replace(" ", "_") + "_ImportFromExcel"
не чувствует себя хорошо. Хотя это выглядит заманчиво, это не сортируется, вероятно, вы хотели бы что-то вроде этого:
DateTime.Now.ToString("yyyyMMddHHmmss") + "_ImportFromExcel"
Или еще лучше:
"ImportFromExcel_" +DateTime.Now.ToString("yyyyMMddHHmmss")
так что у вас есть что-то, что сортируется и выбирается для всех импортов в качестве символа подстановки или зацикливания по какой-то причине.
Затем вы пишете на сервер внутри цикла reader.Read(). Это не тот способ, которым работает WriteToServer. Вы не будете делать Reader.Read (), но просто:
sbc.WriteToServer(reader);
В моем следующем сообщении я приведу простое чтение схемы и простой пример SBC из excel во временную таблицу, а также предложу, как это сделать вместо этого.
WriteToServer(IDataReader) предназначен для внутреннего IDataReader.Read()операция.
using (SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn))
{
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
bulkImport.WriteToServer(dataReader);
}
Вы можете проверить документ MSDN по этой функции, есть рабочий пример: https://msdn.microsoft.com/en-us/library/434atets(v=vs.110).aspx