SQLBulkCopy为所有列插入一个具有NULL值的新行 [英] SQLBulkCopy inserts a new row with NULL values for all columns
本文介绍了SQLBulkCopy为所有列插入一个具有NULL值的新行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这段代码可以正常工作,并将excel数据加载到SQL表中.唯一的问题是,它还会为所有列插入一个带有NULL值的新行.
I have this code which works fine and loads the excel data into an SQL table. The only problem is that it also inserts a new row with NULL values for all columns.
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + Path.GetFileName(excelPath) + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.mySQLTable";
sqlBulkCopy.BulkCopyTimeout = 200;
sqlBulkCopy.ColumnMappings.Add("Employee", "Employee_Name");
sqlBulkCopy.ColumnMappings.Add("Sal/Hourly", "Sal/Hourly");
sqlBulkCopy.ColumnMappings.Add("Total", "Total");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
此2988行在excel中不存在,但是代码创建了它.感谢您的帮助.
This 2988 row is not present in excel, but the code creates it. Any help is appreciated.
推荐答案
您可以更改查询以在excel中跳过空行:
You could change your query to skip empty rows in excel:
"SELECT * FROM [" + Path.GetFileName(excelPath) + "] WHERE [Employee] IS NOT NULL"
这应避免将空行添加到 DataTable
.当然,您也可以稍后将其删除,但这会降低效率.例如:
This should avoid that empty rows are added to the DataTable
. Of course you could also remove them later but it would be less efficient. For example:
dtExcelData = dtExcelData.AsEnumerable()
.Where(r => !String.IsNullOrEmpty(r.Field<string>("Employee")))
.CopyToDataTable();
这篇关于SQLBulkCopy为所有列插入一个具有NULL值的新行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文