您的位置: 首页  >  IT文章  >  VB Dotnet  >  多线程将多个excel导入到sql2000中,该如何处理

多线程将多个excel导入到sql2000中,该如何处理

分类: IT文章 • 2024-02-28 11:11:55
多线程将多个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连接?

上代码

VB.NET code
        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


VB.NET code
'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

相关推荐