Execl 파일의 특정 시트내용을 MS-Access 파일에 테이블로 추가하는 함수 의 속도 개선....

[케이치]

아래 함수는 Execl 파일의 특정 시트내용을 MS-Access 파일에 테이블로 추가하는 함수 입니다....

그런데 이 함수가 좀 많이 느린편이라서 빨리 할수 없을까 고민입니다...

아래 선언되지 않은 변수는 전역변수로 선언된 것들입니다...

Execl 시트의 라인이 6000개나 됩니다... 물론 더 많아 질수도 있고요...

그래서 아래 방식에서    Execl   DATATABLE to      Access  DATATABLE 로 복사하여

Access에 저장하는 방식이 있을 것 같은데 아무리 찾아봐도 그런 것은 보이지가 않습니다....

    Public Sub DB_Variable_Provider_Base(ByVal Table_Select_Name As String, ByVal OutputTextBoolean As Boolean)
        Open_DB_Provider = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
        Open_DB_Provider = Open_DB_Provider & CurrentDirectory & "\" & Yeardate & "년Masterdb.mdb;Persist Security Info=False"
        If OutputTextBoolean = False Then
            Structure_Read_SQL = ""
            Structure_Read_SQL = " SELECT *  FROM " & Table_Select_Name & " ORDER BY ShippingNumber,ProductName ASC"
        Else
            Structure_Read_SQL = ""
            Structure_Read_SQL = " SELECT *  FROM " & Table_Select_Name & " WHERE  (OutputText = '" & Filter_NotInstall & "') ORDER BY ShippingNumber,ProductName  ASC"
            'Open_SQL = " SELECT *  FROM " & Table_Select_Name & " WHERE  (ShippingNumber = '" & FormIpgoList.TextBoxOutputText.Text & "')"
        End If
        Open_SQL = " SELECT *  FROM " & Table_Select_Name & " ORDER BY ShippingNumber,ProductName ASC"

    End Sub

    Public Sub RunSqlTransaction(ByVal myCommand As OleDb.OleDbCommand)
      

        Try
            MY_Connection.Open()
            MY_OleDbTransaction = MY_Connection.BeginTransaction()
            myCommand.Transaction = MY_OleDbTransaction
            MY_OleDbTransaction.Commit()
            Console.WriteLine("Update successful.")
            MY_Connection.Close()
        Catch e As Exception
            Try
                MY_OleDbTransaction.Rollback()
            Catch ex As SqlException
                If Not MY_OleDbTransaction.Connection Is Nothing Then
                    Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                    " was encountered while attempting to roll back the transaction.")
                End If
            End Try

            Console.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered.")
            Console.WriteLine("Update failed.")
        End Try

    End Sub

    Public Sub DB_Open_Base(ByVal Table_Select_Name As String)
        MDIParentOA.Text = "OA관리"
        MDIParentOA.Text = MDIParentOA.Text & " - 읽기"
        My_DataSet.Clear()
        MY_DataTable.Clear()
        MY_Connection = New OleDbConnection(Open_DB_Provider)
        Try
            MY_Connection.Open()
            If (MY_Connection.State = ConnectionState.Open) Then
                MY_OleDbDataAdapter = New OleDbDataAdapter(Open_SQL, MY_Connection) '// m_DB연결)
                MY_DataTable.Locale = System.Globalization.CultureInfo.InstalledUICulture
                MY_OleDbDataAdapter.Fill(MY_DataTable)
                MY_OleDbDataAdapter.FillSchema(MY_DataTable, SchemaType.Mapped)
                MY_OleDbDataAdapter.Fill(My_DataSet, Table_Select_Name)

                ' If My_DataSet.Tables(0).Rows.Count = 0 Then
                ' MsgBox("입력된 데이터가 없습니다.", MsgBoxStyle.Exclamation, "확인")
                ' End If
            End If

        Catch ex As OleDbException
            MessageBox.Show(ex.Message, "데이터베이스 Open 오류")

        Finally

            MDIParentOA.Text = "OA관리"
        End Try
    End Sub

    Public Sub DB_Close_Base(ByVal Transaction_OleDbCommand As OleDb.OleDbCommand)
        MY_Connection.Close()
        Call RunSqlTransaction(Transaction_OleDbCommand)
    End Sub

    Public Sub Excel_To_Access_Close()
        Excel_OleDbConnection.Close()
    End Sub

    ' "Excel문서에서 DB로 추가하기"
    ' 엑셀문서에서 마스타 DB로 추가합니다...
    ' 추가방법은 입력된제조번호가 있는지 와 중복 검사후에 추가합니다.
    Public Sub Excel_To_Access_Insert(ByVal Excel_source_File As String, ByVal Table_Select_Name As String)

        Open_Excel_Provider = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & Excel_source_File & " ;Extended Properties=Excel 8.0;"
        Excel_OleDbConnection = New OleDbConnection(Open_Excel_Provider)

        MDIParentOA.Text = "OA관리"
        MDIParentOA.Text = MDIParentOA.Text & " - 읽기"
        Excel_DataTable.Clear()
        Excel_DataSet.Clear()
        Try

            Excel_OleDbConnection.Open()
            If (Excel_OleDbConnection.State = ConnectionState.Open) Then
                Excel_OleDbDataAdapter = New OleDbDataAdapter(Excel_Open_SQL, Excel_OleDbConnection)
                Excel_DataTable.Locale = System.Globalization.CultureInfo.InstalledUICulture
                Excel_OleDbDataAdapter.Fill(Excel_DataTable)
                Excel_OleDbDataAdapter.FillSchema(Excel_DataTable, SchemaType.Source)
                Excel_OleDbDataAdapter.Fill(Excel_DataSet)

                If Excel_DataTable.Rows.Count = 0 Then
                    MsgBox("Excel문서에 입력된 데이터가 없습니다.", MsgBoxStyle.Exclamation, "확인")
                    Call Excel_To_Access_Close()
                    Exit Sub
                Else

                    MDIParentOA.ToolStripProgressBar1.Maximum = Excel_DataTable.Rows.Count
                    sourceCommand = New OleDbCommand(Excel_Open_SQL, Excel_OleDbConnection)

                    sourceReader = sourceCommand.ExecuteReader()
                    Call DB_Variable_Provider_Base(Table_Select_Name, False) '테이블이름을 가지고 함수 실행합니다.
                    Call DB_Open_Base(Table_Select_Name) '테이블이름을 가지고 함수 실행합니다.
                    '=========================================================================================================

                    CheckTotal = 0
                    While sourceReader.Read()
                        If Not sourceReader.Item(15).ToString = "" Then
                            'Excel문서에 입력된 제조번호가 있는지 확인합니다
                            'DB에 추가할때 엑셀에 있는 제조번호가 DB에 있는지 검사한다.
                            If Filter_Serialnumber_Excel(Table_Select_Name, sourceReader.Item(15).ToString) = True Then
                                CheckProductserialnumber = False : ElseIf CheckProductserialnumber = True Then
                                '테이블에 필드값
                                Execl_TO_MSAccess_Insert_SQL = "INSERT INTO " & Table_Select_Name
                                Execl_TO_MSAccess_Insert_SQL = Execl_TO_MSAccess_Insert_SQL & MY_IpgoList_Data_Member_SQL & MY_IpgoList_Data_Values_SQL
                                'Command초기화
                                With Execl_TO_MSAccess_Insert_Command
                                    .Connection = MY_Connection
                                    .CommandText = Execl_TO_MSAccess_Insert_SQL
                                    .Parameters.Clear()
                                    .Parameters.Add("@Company", OleDbType.VarChar)
                                    .Parameters.Add("@OutputText", OleDbType.VarChar)
                                    .Parameters.Add("@PlacingAnDate", OleDbType.VarChar)
                                    .Parameters.Add("@InputDate", OleDbType.VarChar)
                                    .Parameters.Add("@OutputDate", OleDbType.VarChar)
                                    .Parameters.Add("@PlacingAnOrdernumber", OleDbType.VarChar)
                                    .Parameters.Add("@ShippingNumber", OleDbType.VarChar)
                                    .Parameters.Add("@itemNumber", OleDbType.VarChar)
                                    .Parameters.Add("@PlacingAnOrder", OleDbType.VarChar)
                                    .Parameters.Add("@PlacingAnUser", OleDbType.VarChar)
                                    .Parameters.Add("@PlacingAnOrderTel", OleDbType.VarChar)
                                    .Parameters.Add("@PlacingAnOrderBusu", OleDbType.VarChar)
                                    .Parameters.Add("@OutputCount", OleDbType.VarChar)
                                    .Parameters.Add("@Product", OleDbType.VarChar)
                                    .Parameters.Add("@ProductName", OleDbType.VarChar)
                                    .Parameters.Add("@Productserialnumber", OleDbType.VarChar)
                                    .Parameters("@Company").Value = Format(sourceReader.Item(0).ToString)              '사업장
                                    .Parameters("@OutputText").Value = Format(sourceReader.Item(1).ToString)           '구분
                                    .Parameters("@PlacingAnDate").Value = Format(sourceReader.Item(2).ToString)        '출하일자
                                    .Parameters("@InputDate").Value = Format(sourceReader.Item(3).ToString)            '입고일자
                                    .Parameters("@OutputDate").Value = Format(sourceReader.Item(4).ToString)           '출고날자
                                    .Parameters("@PlacingAnOrdernumber").Value = Format(sourceReader.Item(5).ToString) '발주번호
                                    .Parameters("@ShippingNumber").Value = Format(sourceReader.Item(6).ToString)       '출하번호
                                    .Parameters("@itemNumber").Value = Format(sourceReader.Item(7).ToString)           '품의번호
                                    .Parameters("@PlacingAnOrder").Value = Format(sourceReader.Item(8).ToString)       '발주자
                                    .Parameters("@PlacingAnUser").Value = Format(sourceReader.Item(9).ToString)        '사용자
                                    .Parameters("@PlacingAnOrderTel").Value = Format(sourceReader.Item(10).ToString)   '연락처
                                    .Parameters("@PlacingAnOrderBusu").Value = Format(sourceReader.Item(11).ToString)  '부서명
                                    .Parameters("@OutputCount").Value = Format(sourceReader.Item(12).ToString)         '수량
                                    .Parameters("@Product").Value = Format(sourceReader.Item(13).ToString)             '품명
                                    .Parameters("@ProductName").Value = Format(sourceReader.Item(14).ToString)         '제품명
                                    .Parameters("@Productserialnumber").Value = Format(sourceReader.Item(15).ToString) '제조번호
                                    MY_OleDbDataAdapter.InsertCommand = Execl_TO_MSAccess_Insert_Command
                                    .ExecuteNonQuery()
                                    .Parameters.Clear()
                                    CheckTotal = CheckTotal + 1
                                    MDIParentOA.ToolStripProgressBar1.Value = CheckTotal
                                End With

                            End If
                        End If
                    End While
                    sourceReader.Close()
                    '=========================================================================================================
                    If CheckTotal = 0 Then
                        MsgBox("Execl문서에서" & Excel_DataTable.Rows.Count & "개의 레코드를 읽어왔으나 추가할 레코드가 없습니다.", MsgBoxStyle.Exclamation, "확인")
                    Else
                        MsgBox("Execl문서에서" & Excel_DataTable.Rows.Count & "개의 레코드를 읽어와서 " & CheckTotal & "개의 레코드를 추가하였습니다.", MsgBoxStyle.Exclamation, "확인")
                    End If
                    Call DB_Close_Base(Execl_TO_MSAccess_Insert_Command)

                End If

            End If
           
            Call Excel_To_Access_Close()

        Catch ex As OleDbException
            MessageBox.Show(ex.Message, "엑셀파일을 MS-Access파일로 변환하는 과정에서 오류가 발생하였습니다.")
        Finally
            MDIParentOA.ToolStripProgressBar1.Value = 0
            MDIParentOA.Text = "OA관리"
        End Try
    End Sub

[스페셜원]

음..제가 vb를 잘몰라서.....잘못본것일수도 있는데... read하면서 각 row마다 insert작업을 하는데 그것보다.. insert문을 string으로 만들어서 반복문이 끝난후 일괄적으로 처리하는것이.. 더 효과적이지 않을가요? 그리고 "SELECT * FROM " 보다 필드명을 하나씩 적어주시는것이 속도가 더 빨른것으로 알고 있습니다. "SELECT Company, InputDate FROM" 이런식으로 말이죠....