运行到sqlbulkcopy.WriteToServer(dt);时提示：来自数据源的 String 类型的给定值不能转换为指定目标列的类型 int。
//导入按钮
private void btnImport_Click(object sender, EventArgs e)
{
for (int i = 0; i < addDataFile.dataGridView.Rows.Count; i++)
{
string tableName = addDataFile.dataGridView.Rows[i].Cells["Column1"].FormattedValue.ToString().Substring(0,2);
string filepath = addDataFile.dataGridView.Rows[i].Cells["Column3"].FormattedValue.ToString();
if (tableName.Contains("CW"))
{
tableName = "TB_CW";
}
else if (tableName.Contains("SP"))
{
tableName = "TB_SP";
}
else if (tableName.Contains("PS"))
{
tableName = "TB_PS_TL";
}
else if (tableName.Contains("TN"))
{
tableName = "TB_TopN";
}
DataTable dtTable = new DataTable();
string[] list = getColumnName(tableName);//
if (list != null)
{
foreach (string columnName in list)
{
DataColumn dc1 = new DataColumn();
dc1.ColumnName = columnName;
dtTable.Columns.Add(dc1);
}
}
else
{
MessageDialog.ShowError("导入的数据为空！");
}
addRow(tableName, dtTable,filepath);
}
}
//获取导入文件的列名
private string[] getColumnName(string tableName)
{
//ArrayList list = new ArrayList();
string[] list = new string[addDataFile.dataGridView.Rows.Count];
if (tableName == "TB_CW")
{
list = new string[] {"model","coordinates","time","filter_bandwidth","central_frequent","fssi"};
}
if (tableName == "TB_PS_TL")
{
list = new string[] { "model", "coordinates", "time", "central_frequent", "PCI", "RSSI" };
}
if (tableName == "TB_SP")
{
list = new string[] { "model", "coordinates", "time", "frequent_start", "frequent_end", "filter_bandwidth", "central_frequent", "rssi" };
}
if (tableName == "TB_TopN")
{
list = new string[] { "model", "coordinates", "time", "top_n", "rssi" };
}
return list;
}
//添加数据到行
private void addRow(string tableName,DataTable dtTable,string logPath)
{
StreamReader sr = new StreamReader(logPath);
while (true)
{
string str = sr.ReadLine();
if (!string.IsNullOrEmpty(str))
{
string[] list = str.Split(' ');
if (str.Contains("CW") && str.Contains("-"))
{
DataRow dr = dtTable.NewRow();
dr["model"] = str.Substring(0,2);
dr["coordinates"] = str.Substring(3, 30);
dr["time"] = list[8];
dr["filter_bandwidth"] = list[9];
dr["central_frequent"] = list[10];
dr["fssi"] = str.Substring(54, str.Length - 54);
dtTable.Rows.Add(dr);
}
if (str.Contains("SP") && str.Contains("-"))
{
DataRow dr = dtTable.NewRow();
dr["model"] = list[0];
dr["coordinates"] = str.Substring(3, 30);
dr["time"] = list[8];
dr["frequent_start"] = list[13];
dr["frequent_end"] = list[15];
dr["filter_bandwidth"] = list[9];
dr["central_frequent"] = list[10];
dr["rssi"] = str.Substring(55, str.Length - 55);
dtTable.Rows.Add(dr);
}
if (str.Contains("0") && str.Contains("+"))
{
DataRow dr = dtTable.NewRow();
dr["model"] = list[0];
dr["coordinates"] = str.Substring(6, 31);
dr["time"] = list[8];
dr["central_frequent"] = list[9];
dr["PCI"] = list[10];
dr["RSSI"] = str.Substring(55,str.Length - 55);
dtTable.Rows.Add(dr);
}
#region
//if (str.Contains("TL") && str.Contains("+"))
//{
// DataRow dr = dtTable.NewRow();
// if (str.Contains("PS-TL"))
// {
// dr["model"] = list[0];
// dr["coordinates"] = str.Substring(6, 31);
// dr["time"] = list[8];
// dr["central_frequent"] = list[9];
// dr["PCI"] = list[10];
// dr["RSSI"] = str.Substring(55,str.Length - 55);
// dtTable.Rows.Add(dr);
// }
// else if (str.Contains("BQ-TL"))
// {
// dr["model"] = list[0];
// dr["coordinates"] = str.Substring(6, 31);
// dr["time"] = list[8];
// dr["central_frequent"] = list[9];
// dr["PCI"] = list[10];
// dr["RSSI"] = str.Substring(55, str.Length - 55);
// dtTable.Rows.Add(dr);
// }
// else if (str.Contains("BD-TL"))
// {
// dr["model"] = list[0];
// dr["coordinates"] = str.Substring(6, 31);
// dr["time"] = list[8];
// dr["central_frequent"] = list[9];
// dr["PCI"] = list[10];
// dr["RSSI"] = str.Substring(55, str.Length - 55);
// dtTable.Rows.Add(dr);
// }
// else if (str.Contains("RF-TL"))
// {
// dr["model"] = list[0];
// dr["coordinates"] = str.Substring(6, 31);
// dr["time"] = list[8];
// dr["central_frequent"] = list[9];
// dr["PCI"] = list[10];
// dr["RSSI"] = str.Substring(55, str.Length - 55);
// dtTable.Rows.Add(dr);
// }
//}
#endregion
}
else
break;
//sr.Close();
dataToDatabase(dtTable, tableName);
}
}
//导入数据到数据库 + table + table
private void dataToDatabase(DataTable dtTable, string currentOperatetable)
{
string sqlDelete = "truncate table " + currentOperatetable;
DBObject.ExecuteSql(Tool.DB_NAME, sqlDelete);
dataTableToDatabase(Tool.DB_CONNECT, currentOperatetable, dtTable);
}
//将读取的数据放入到dataBase中
private void dataTableToDatabase(string connectionStr, string tableName, DataTable dt)
{
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = tableName;//数据库表名
sqlbulkcopy.WriteToServer(dt);
}
解决方法：
数据库中rssi字段的数据类型定义错了，应该为varchar（）；