winform CSV文件入库数据缺失
public string TransferCSVData(string csvFile, string tablename, string connectionString)
{
try
{
DataTable dataTable = new DataTable();
string[] columnNames = File.ReadLines(csvFile, Encoding.GetEncoding("GBK")).First().Split(',');
foreach (string columnName in columnNames)
{
dataTable.Columns.Add(columnName, typeof(string));
}
foreach (string line in File.ReadLines(csvFile, Encoding.GetEncoding("GBK")).Skip(1))
{
string[] values = line.Split(',');
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < values.Length; i++)
{
dataRow[i] = values[i];
}
dataTable.Rows.Add(dataRow);
}
int rowsnum = dataTable.Rows.Count;
progressBar1.Maximum = rowsnum;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.BulkCopyTimeout = 666666666;
bulkCopy.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bulkCopy.BatchSize = 100;
bulkCopy.NotifyAfter = 100;
bulkCopy.DestinationTableName = tablename;
bulkCopy.WriteToServer(dataTable);
bulkCopy.Close();
richTextBox1.SelectionColor = Color.Blue;
richTextBox1.AppendText(System.IO.Path.GetFileName(csvFile) + "导入完成!!!!\n");
return "TAG";
}
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
richTextBox1.SelectionColor = Color.Red;
richTextBox1.AppendText(System.IO.Path.GetFileName(csvFile) + "导入失败!!!!" + "\n");
richTextBox2.SelectionColor = Color.Red;
richTextBox2.AppendText(DateTime.Now.ToString("HH:mm:ss ") + "导入异常" + "\n");
if (ex.Message == "与源或目标中的任意列均不匹配。")
{
richTextBox1.AppendText("详细失败原因如下:导入的EXCEL的单元格列数比设定的列数多,请检查EXCEL的数据是否异常\n");
}
else richTextBox1.AppendText("详细失败原因如下:" + ex.Message + "\n");
Application.DoEvents();
return "";
}
}
public string TransferCSVData0(string csvFile, string tablename, string connectionString)
{
try
{
DataTable dataTable = new DataTable();
string[] columnNames = File.ReadLines(csvFile).First().Split(',');
foreach (string columnName in columnNames)
{
dataTable.Columns.Add(columnName, typeof(string));
}
foreach (string line in File.ReadLines(csvFile).Skip(1))
{
string[] values = line.Split(',');
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < values.Length; i++)
{
dataRow[i] = values[i];
}
dataTable.Rows.Add(dataRow);
}
int rowsnum = dataTable.Rows.Count;
progressBar1.Maximum = rowsnum;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.BulkCopyTimeout = 666666666;
bulkCopy.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bulkCopy.BatchSize = 100;
bulkCopy.NotifyAfter = 100;
bulkCopy.DestinationTableName = tablename;
bulkCopy.WriteToServer(dataTable);
bulkCopy.Close();
richTextBox1.SelectionColor = Color.Blue;
richTextBox1.AppendText(System.IO.Path.GetFileName(csvFile) + "导入完成!!!!\n");
return "TAG";
}
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
richTextBox1.SelectionColor = Color.Red;
richTextBox1.AppendText(System.IO.Path.GetFileName(csvFile) + "导入失败!!!!" + "\n");
richTextBox2.SelectionColor = Color.Red;
richTextBox2.AppendText(DateTime.Now.ToString("HH:mm:ss ") + "导入异常" + "\n");
if (ex.Message == "与源或目标中的任意列均不匹配。")
{
richTextBox1.AppendText("详细失败原因如下:导入的EXCEL的单元格列数比设定的列数多,请检查EXCEL的数据是否异常\n");
}
else richTextBox1.AppendText("详细失败原因如下:" + ex.Message + "\n");
Application.DoEvents();
return "";
}
}