多线程将多个excel导入到sql2000中
我正在做一个excel导出导入sql的应急程序.
我们数据库中的表都是每一天生成一个表,
例如
tt_20111220
tt_20111221
tt_20111222
tt_20111223
tt_20111224
导出
我用多线程,使用excel的querytable方法,每个表5-6k条,两个月的表25个左右大约4分钟,这个已经实现.
但是导入,不得不使用sql的sqlcommand,但是每次在执行时都会出现
"ExecuteReader 要求已打开且可用的连接。连接的当前状态为正在连接。"
或
"ExecuteReader 要求已打开且可用的连接。连接的当前状态为已经关闭。"
当然一个xls导入绝对不会出现问题,就是两个或以上.我都是用的托管代码,为什么还是说不行,难道说共用一个datareader连接?
上代码
For Each i As String In clbTable.CheckedItems '对checklistbox控件中的每一个xls表 If Not CheckIsExistsTable(i) Then '如果数据库不存在这个表 CreateNewTable(i) '则创建 Else '如果存在,则询问 Dim aReply As Integer = MsgBox("The table " & i & " has exists!" & vbLf & "Yes -- Drop Table " & i & vbLf & "No -- Insert Excel to " & i & " For Appending" & vbLf & "Cancel -- Cancel", MsgBoxStyle.YesNoCancel) If aReply = MsgBoxResult.Yes Then '删除重建 TruncateTable(i) CreateNewTable(i) ElseIf aReply = MsgBoxResult.No Then '不删除,插入 'Insert excel data Else '忽略此表,继续下一个 'Cancel Continue For 'Exit Sub End If End If newImportTread = New Thread(AddressOf ImportToDababase_Daily) '创建线程 newImportTread.Start(txtFolder.Text.Trim & "\" & i & ".xls") '执行excel导入sql的过程 Thread.Sleep(0) Next'Import To Database--Daily Public Function ImportToDababase_Daily(ByVal tablepath As String) As Boolean Dim sqlcon As New SqlClient.SqlConnection Dim sqlcom1 As System.Data.OleDb.OleDbCommand = Nothing Dim sqlcon1 As System.Data.OleDb.OleDbConnection = Nothing Dim dr As System.Data.OleDb.OleDbDataReader = Nothing Dim tablename As String = Mid(tablepath, InStrRev(tablepath, "\") + 1, InStrRev(tablepath, ".") - InStrRev(tablepath, "\") - 1) Dim tabledate As String = tablename.Substring(3, 4) & "-" & tablename.Substring(7, 2) & "-" & tablename.Substring(9) sqlcon = ConnectServer() '连接服务器的过程 'Try FormatSheet(tablepath) '这是格式化excel的过程,可以忽略 sqlcon1 = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" & tablepath & "; " & _ "Extended Properties='Excel 8.0;IMEX=1'") If sqlcon1.State = ConnectionState.Closed Then sqlcon1.Open() sqlcom1 = New System.Data.OleDb.OleDbCommand("select * from [Sheet1$]", sqlcon1) dr = sqlcom1.ExecuteReader '这是连接excel,进行查询 Dim newMycmd As SqlClient.SqlCommand While dr.Read Dim sql As String = "insert into " & tablename & " (SourceId,SourceName,SourceSubType,ResearchTeam,SourceURL,City,County,SourceState,UserName,Password,SourceNotes,NewPostings,DrillDowns,Register,Documents,SaveDocsLocally,DayVisit,OwnerID,OwnerName,ContactID,ResearcherComments,EventType,AmendmentVersion,Description,BidNumber,SubmittalDate,SubmittalTime,DocumentURL1,DocumentURL2,DocumentURL3,DocumentURL4,DocumentURL5,UserId,IBDDate,TimeId,TimeName,CompleteDate) " & _ "values (@SourceId,@SourceName,@SourceSubType,@ResearchTeam,@SourceURL,@City,@County,@SourceState,@UserName,@Password,@SourceNotes,@NewPostings,@DrillDowns,@Register,@Documents,@SaveDocsLocally,@DayVisit,@OwnerID,@OwnerName,@ContactID,@ResearcherComments,@EventType,@AmendmentVersion,@Description,@BidNumber,@SubmittalDate,@SubmittalTime,@DocumentURL1,@DocumentURL2,@DocumentURL3,@DocumentURL4,@DocumentURL5," & _ "null,'" & tabledate & "',null,'" & strPN & "','" & tabledate & " 12:00:00" & "')" newMycmd = New SqlClient.SqlCommand(sql, sqlcon) '这是sqlcommand,以下添加参数 newMycmd.Parameters.AddWithValue("@SourceId", CInt(dr.Item(0).ToString.Replace("src:", ""))) newMycmd.Parameters.AddWithValue("@SourceName", dr.Item(1).ToString) newMycmd.Parameters.AddWithValue("@SourceSubType", dr.Item(2).ToString) newMycmd.Parameters.AddWithValue("@ResearchTeam", dr.Item(3).ToString) newMycmd.Parameters.AddWithValue("@SourceURL", dr.Item(4).ToString) newMycmd.Parameters.AddWithValue("@City", dr.Item(5).ToString) newMycmd.Parameters.AddWithValue("@County", dr.Item(6).ToString) newMycmd.Parameters.AddWithValue("@SourceState", dr.Item(7).ToString) newMycmd.Parameters.AddWithValue("@UserName", dr.Item(8).ToString) newMycmd.Parameters.AddWithValue("@Password", dr.Item(9).ToString) newMycmd.Parameters.AddWithValue("@SourceNotes", dr.Item(10).ToString) newMycmd.Parameters.AddWithValue("@NewPostings", dr.Item(11).ToString) newMycmd.Parameters.AddWithValue("@DrillDowns", dr.Item(12).ToString) newMycmd.Parameters.AddWithValue("@Register", dr.Item(13).ToString) newMycmd.Parameters.AddWithValue("@Documents", dr.Item(14).ToString) newMycmd.Parameters.AddWithValue("@SaveDocsLocally", dr.Item(15).ToString) newMycmd.Parameters.AddWithValue("@DayVisit", dr.Item(16).ToString) newMycmd.Parameters.AddWithValue("@OwnerID", dr.Item(17).ToString) newMycmd.Parameters.AddWithValue("@OwnerName", dr.Item(18).ToString) newMycmd.Parameters.AddWithValue("@ContactID", dr.Item(19).ToString) newMycmd.Parameters.AddWithValue("@ResearcherComments", dr.Item(20).ToString) newMycmd.Parameters.AddWithValue("@EventType", dr.Item(21).ToString) newMycmd.Parameters.AddWithValue("@AmendmentVersion", dr.Item(22).ToString) newMycmd.Parameters.AddWithValue("@Description", dr.Item(23).ToString) newMycmd.Parameters.AddWithValue("@BidNumber", dr.Item(24).ToString) newMycmd.Parameters.AddWithValue("@SubmittalDate", dr.Item(25).ToString) newMycmd.Parameters.AddWithValue("@SubmittalTime", dr.Item(26).ToString) newMycmd.Parameters.AddWithValue("@DocumentURL1", dr.Item(27).ToString) newMycmd.Parameters.AddWithValue("@DocumentURL2", dr.Item(28).ToString) newMycmd.Parameters.AddWithValue("@DocumentURL3", dr.Item(29).ToString) newMycmd.Parameters.AddWithValue("@DocumentURL4", dr.Item(30).ToString) newMycmd.Parameters.AddWithValue("@DocumentURL5", dr.Item(31).ToString) newMycmd.ExecuteNonQuery() '''''这儿报错!!!! End While 'Catch ex As Exception 'MsgBox(ex.Message) 'Finally newMycmd.dispose() '每次都有释放 dr.Close() sqlcon1.Close() sqlcom1.Dispose() sqlcon.Close() sqlcon.Dispose() 'End Try End Function