开了一个新线程往SQLSERVER中导入海量数据,一次性导入数W条的时候正常,一次性导入数十W条的时候报如下错误:"CLR 无法从 COM 上下文 0x1b0de8 转换为 COM 上下文 ox1b1010,这种状态已持续 60 秒。拥有目标上下文/单元的线程很有可能执行的是非泵式等待或者在不发送 Windows 消息的情况下处理一个运行时间非常长的操作。这种情况通常会影响到性能,甚至可能导致应用程序不响应或者使用的内存随时间不断累积。要避免此问题,所有单线程单元(STA)线程都应使用泵式等待基元(如 CoWaitForMultipleHandles),并在运行时间很长的操作过程中定期发送消息。"
请高手帮忙解决:代码如下:
private void bnResultsInsertSQLDB_Click(object sender, EventArgs e)
{
lbProcessInfoIndex.Visible = true;
tdThread = new Thread(new ThreadStart(fnIndexInsertDB));
tdThread.TrySetApartmentState(ApartmentState.STA);
tdThread.Start();
}
private void fnIndexInsertDB()
{
dsDataSource = CindexSearch.fnReturnDataSource(CMainClass.szDefaultPickAllIndex);
DataTable dtDataSource = CindexSearch.fnAddNewDataColumn(dsDataSource.Tables["dtDataSource"], "FileExtension", 0, 1, 4,lbProcessInfoIndex);
CMainClass.fnIndexInsertSQLDB(dtDataSource, "filemaininfo", "所有文件索引信息已成功导入数据库!");
tdThread.Abort();
}
public DataSet fnReturnDataSource(string szKeyWords)
{
CqueryHelper.QuerySelectColumns = "FileName,Path,Size";
string szSqlQuery = CqueryHelper.GenerateSQLFromUserQuery(szKeyWords);
if(szKeyWords==CMainClass.szDefaultPickAllIndex)
szSqlQuery = szSqlQuery.Substring(0, szSqlQuery.IndexOf("WHERE") - 1);
//MessageBox.Show(szSqlQuery);
OleDbConnection odcConnection = new OleDbConnection(CqueryHelper.ConnectionString);
OleDbDataAdapter odaAdapter = new OleDbDataAdapter(szSqlQuery, odcConnection);
DataSet dsDataSource = new DataSet();
odaAdapter.Fill(dsDataSource, "dtDataSource");
return dsDataSource;
}
public DataTable fnAddNewDataColumn(DataTable dsOldDataTable,string szColumnName,int nTypeCode,int nFileNameLocation,int nFileExtensionLocation,Label lbLabelProcessShow)
{
//临时增加ID行,不可通用。
dsOldDataTable.Columns.Add(new DataColumn("ID", typeof(System.Int32)));
dsOldDataTable.Columns["ID"].SetOrdinal(0);
//*
dsOldDataTable.Columns["filename"].SetOrdinal(1);
dsOldDataTable.Columns["path"].SetOrdinal(2);
dsOldDataTable.Columns["size"].SetOrdinal(3);
//
if (nTypeCode == 0)
{
dsOldDataTable.Columns.Add(new DataColumn(szColumnName, typeof(System.String)));
}
else { //预留以增加其它DataType
}
for (int i = 0; i < dsOldDataTable.Rows.Count; i++)
{
//临时增加ID行,不可通用。
dsOldDataTable.Rows[i][0] = i + 1;
//*
if (dsOldDataTable.Rows[i][nFileNameLocation].ToString().LastIndexOf(".") > 0)
{
dsOldDataTable.Rows[i][nFileExtensionLocation] = dsOldDataTable.Rows[i][nFileNameLocation].ToString().Substring(dsOldDataTable.Rows[i][nFileNameLocation].ToString().LastIndexOf("."), dsOldDataTable.Rows[i][nFileNameLocation].ToString().Length - dsOldDataTable.Rows[i][nFileNameLocation].ToString().LastIndexOf("."));
}
CFileOperation.fnSetLabelText(lbLabelProcessShow, Convert.ToString(i + 1) + "/" + dsOldDataTable.Rows.Count.ToString() + "文件,正在处理:" + dsOldDataTable.Rows[i][nFileNameLocation].ToString());
}
return dsOldDataTable;
}
public void fnIndexInsertSQLDB(DataTable dtDataSource,string szSQLTableName,string szShowSuccessInfo)
{
scdCommand.CommandText = "delete from "+szSQLTableName;
try
{
scdCommand.ExecuteNonQuery();
}
catch (Exception exMessage)
{ //错误信息处理
fnInsertSQLDBException(Login.szUserName, exMessage.Message, DateTime.Now);
}
SqlBulkCopy sbcBulkCopy = new SqlBulkCopy(szSQLConnection, SqlBulkCopyOptions.UseInternalTransaction);
sbcBulkCopy.BulkCopyTimeout = 500000000;
sbcBulkCopy.NotifyAfter = dtDataSource.Rows.Count;
sbcBulkCopy.DestinationTableName = szSQLTableName;
try
{
sbcBulkCopy.WriteToServer(dtDataSource);
}
catch (Exception exMessage)
{ //错误信息处理
fnInsertSQLDBException(Login.szUserName, exMessage.Message, DateTime.Now);
}
MessageBox.Show(szShowSuccessInfo);
}
请高手帮忙解决:代码如下:
private void bnResultsInsertSQLDB_Click(object sender, EventArgs e)
{
lbProcessInfoIndex.Visible = true;
tdThread = new Thread(new ThreadStart(fnIndexInsertDB));
tdThread.TrySetApartmentState(ApartmentState.STA);
tdThread.Start();
}
private void fnIndexInsertDB()
{
dsDataSource = CindexSearch.fnReturnDataSource(CMainClass.szDefaultPickAllIndex);
DataTable dtDataSource = CindexSearch.fnAddNewDataColumn(dsDataSource.Tables["dtDataSource"], "FileExtension", 0, 1, 4,lbProcessInfoIndex);
CMainClass.fnIndexInsertSQLDB(dtDataSource, "filemaininfo", "所有文件索引信息已成功导入数据库!");
tdThread.Abort();
}
public DataSet fnReturnDataSource(string szKeyWords)
{
CqueryHelper.QuerySelectColumns = "FileName,Path,Size";
string szSqlQuery = CqueryHelper.GenerateSQLFromUserQuery(szKeyWords);
if(szKeyWords==CMainClass.szDefaultPickAllIndex)
szSqlQuery = szSqlQuery.Substring(0, szSqlQuery.IndexOf("WHERE") - 1);
//MessageBox.Show(szSqlQuery);
OleDbConnection odcConnection = new OleDbConnection(CqueryHelper.ConnectionString);
OleDbDataAdapter odaAdapter = new OleDbDataAdapter(szSqlQuery, odcConnection);
DataSet dsDataSource = new DataSet();
odaAdapter.Fill(dsDataSource, "dtDataSource");
return dsDataSource;
}
public DataTable fnAddNewDataColumn(DataTable dsOldDataTable,string szColumnName,int nTypeCode,int nFileNameLocation,int nFileExtensionLocation,Label lbLabelProcessShow)
{
//临时增加ID行,不可通用。
dsOldDataTable.Columns.Add(new DataColumn("ID", typeof(System.Int32)));
dsOldDataTable.Columns["ID"].SetOrdinal(0);
//*
dsOldDataTable.Columns["filename"].SetOrdinal(1);
dsOldDataTable.Columns["path"].SetOrdinal(2);
dsOldDataTable.Columns["size"].SetOrdinal(3);
//
if (nTypeCode == 0)
{
dsOldDataTable.Columns.Add(new DataColumn(szColumnName, typeof(System.String)));
}
else { //预留以增加其它DataType
}
for (int i = 0; i < dsOldDataTable.Rows.Count; i++)
{
//临时增加ID行,不可通用。
dsOldDataTable.Rows[i][0] = i + 1;
//*
if (dsOldDataTable.Rows[i][nFileNameLocation].ToString().LastIndexOf(".") > 0)
{
dsOldDataTable.Rows[i][nFileExtensionLocation] = dsOldDataTable.Rows[i][nFileNameLocation].ToString().Substring(dsOldDataTable.Rows[i][nFileNameLocation].ToString().LastIndexOf("."), dsOldDataTable.Rows[i][nFileNameLocation].ToString().Length - dsOldDataTable.Rows[i][nFileNameLocation].ToString().LastIndexOf("."));
}
CFileOperation.fnSetLabelText(lbLabelProcessShow, Convert.ToString(i + 1) + "/" + dsOldDataTable.Rows.Count.ToString() + "文件,正在处理:" + dsOldDataTable.Rows[i][nFileNameLocation].ToString());
}
return dsOldDataTable;
}
public void fnIndexInsertSQLDB(DataTable dtDataSource,string szSQLTableName,string szShowSuccessInfo)
{
scdCommand.CommandText = "delete from "+szSQLTableName;
try
{
scdCommand.ExecuteNonQuery();
}
catch (Exception exMessage)
{ //错误信息处理
fnInsertSQLDBException(Login.szUserName, exMessage.Message, DateTime.Now);
}
SqlBulkCopy sbcBulkCopy = new SqlBulkCopy(szSQLConnection, SqlBulkCopyOptions.UseInternalTransaction);
sbcBulkCopy.BulkCopyTimeout = 500000000;
sbcBulkCopy.NotifyAfter = dtDataSource.Rows.Count;
sbcBulkCopy.DestinationTableName = szSQLTableName;
try
{
sbcBulkCopy.WriteToServer(dtDataSource);
}
catch (Exception exMessage)
{ //错误信息处理
fnInsertSQLDBException(Login.szUserName, exMessage.Message, DateTime.Now);
}
MessageBox.Show(szShowSuccessInfo);
}
odaAdapter.Fill(dsDataSource, "dtDataSource");报错:行句柄无效,何解?
这句话,几万条的时候这个过程小于60S,所以正常,几十万条的时候大于60s,主线程得不到任何反馈信息所以抛出此异常。