I need a faster way

[kcwallace]

I am trying to transfer data from a SQLserver to an Access DB through code. The code below contains my method. It has to be dynamic enough to accept any SQL statement. I gets real slow filling the dataset. Is there a better way?

 

    
   Private Sub UpdateDB()
       Label7.Visible = True
       ResetLbl(Label7, "Looking up data in SQL Server Database")
       ProgressBar1.Visible = True

       Dim Cnn As SqlConnection
       Dim Cnnstr As String = "Data Source=" & TextBox1.Text & ";Initial Catalog=" & TextBox5.Text & ";Integrated Security=SSPI;"
       Dim SQL = TextBox2.Text & ""
       Cnn = SetCnn(Cnnstr)
       Dim Cmd As SqlCommand
       Cmd = New SqlCommand(SQL, Cnn)

       ResetLbl(Label7, "Conditioning Returned Data")

       Dim DS As DataSet
       Dim DA As SqlDataAdapter = GetDA(Cmd, DS)

       Dim ColCnt As Int64 = DS.Tables(0).Columns.Count
       Dim RowCnt As Int64 = DS.Tables(0).Rows.Count

       Dim X As Int16 = 0
       Dim Y As Int64 = 0
       Dim str As String = ""

       ResetLbl(Label7, "Opening Access Database")

       Dim Cnn2 As OleDbConnection
       Dim JetCnnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox3.Text & ";"
       Cnn2 = SetOLECnn(JetCnnStr)
       Dim SQL2 = "SELECT * FROM " & TextBox4.Text
       Dim Cmd2 As OleDbCommand
       Cmd2 = New OleDbCommand(SQL2, Cnn2)
       ResetLbl(Label7, "Preparing to Transfer Data")
       Dim SQL3 As String = "INSERT INTO [" & TextBox4.Text & "] ("
       For X = 0 To ColCnt - 1
           SQL3 = SQL3 & "[" & DS.Tables(0).Columns(X).ColumnName & "], "
       Next X
       ResetLbl(Label7, "Tranferring Data")
       SQL3 = Mid(SQL3, 1, Len(SQL3) - 2) & ") VALUES ("
       For Y = 0 To RowCnt - 1
           Dim ValStr As String = ""
           For X = 0 To ColCnt - 1
               Dim DbVal = DS.Tables(0).Rows(Y).Item(X)
               Dim DT = DbVal.GetType().ToString
               If DT = "System.String" Or DT = "System.DateTime" Then
                   ValStr = ValStr & "'" & RepSngQut(DbVal) & "', "
               ElseIf DbVal Is DBNull.Value Then
                   ValStr = ValStr & "NULL, "
               Else
                   ValStr = ValStr & DbVal & ", "
               End If
           Next X
           ValStr = SQL3 & Mid(ValStr, 1, Len(ValStr) - 2) & ")"
           ActionOLESQL(ValStr, Cnn2)
           SetProgBarVal(Y + 1, RowCnt, ProgressBar1)
       Next Y
       ResetLbl(Label7, "Finishing")
       Label7.Visible = False
       ProgressBar1.Visible = False
       AlreadyRan = Date.Today
   End Sub

   Private Sub SetProgBarVal(ByVal Comp As Int64, ByVal RecCnt As Int64, ByVal PBar As ProgressBar)
       Dim x As Int16
       x = Comp / RecCnt * 100
       If x > PBar.Value Then
           PBar.Value = Comp / RecCnt * 100
           PBar.Refresh()
       End If
   End Sub

   Public Function RepSngQut(ByVal IStr As String)
       Dim LenIStr As Integer
       Dim X As Integer
       Dim TmpTxt As String
       Dim Z As Integer

       TmpTxt = IStr
       Z = 1
StartOver:
       LenIStr = Len(TmpTxt)
       For X = Z To LenIStr
           Dim Y As String
           Y = Mid(TmpTxt, X, 1)
           If Y = "'" Then
               TmpTxt = Mid(TmpTxt, 1, X - 1) & "'&chr(39)&'" & Mid(TmpTxt, X + 1, LenIStr - X - 1)
               Z = X + 12
               GoTo StartOver
           End If
       Next
       RepSngQut = TmpTxt
   End Function

Private Sub ResetLbl(ByVal Lbl As Label, ByVal Str As String)
       Lbl.Text = Str
       Lbl.Refresh()
   End Sub

   Public Function SetCnn(ByVal CnnStr As String) As SqlConnection
       SetCnn = New SqlConnection(CnnStr)
       SetCnn.Open()
   End Function

   Public Function GetDR(ByVal SQL As String, ByVal Cnn As SqlConnection) As SqlDataReader
       Dim Cmd As SqlCommand = GetCmd(SQL, Cnn)
       GetDR = Cmd.ExecuteReader
   End Function

   Public Function GetDA(ByVal Cmd As SqlCommand, ByRef DS As DataSet) As SqlDataAdapter
       GetDA = New SqlDataAdapter
       GetDA.SelectCommand = Cmd
       DS = New DataSet("X")
       GetDA.Fill(DS)
   End Function

   Public Function GetCmd(ByVal SQL As String, ByVal Cnn As SqlConnection) As SqlCommand
       GetCmd = New SqlCommand(SQL, Cnn)
   End Function

   Public Sub ActionSQL(ByVal SQL As String, ByVal Cnn As SqlConnection)
       Dim Cmd As SqlCommand = GetCmd(SQL, Cnn)
       Cmd.CommandText = SQL
       Cmd.ExecuteNonQuery()
   End Sub

[Robby]

The first thing you need to do is remove all declarations from your For loop, next declare your Cmd as a page-level variable. the way you have it now, your declaring a new instance of these items for each iteration.

 

Can you post a sample of what the values would be for SQL, SQL2 and SQL3. (The values of each once your routine is complete)

 

Also, I noticed you have a "GoTo StartOver" in one of your loops, this is a big NO-NO. You can end up in an endless loop real easily, there are so many new string functions in .NET you should be using instead.

Edited April 29, 2005 by Robby

[kcwallace]

I took out the declaration in the loops, and it does run a little faster

 

Sample SQL statement after built;

 

INSERT INTO [badges] ([badgeUniqueID], [GroupCode], [RECORD_ID], [Acc], [Loc], [WearNum], [bADGE_TYPE], [bODYREGION], [bODY_PART], [HOLDERCODE], [WearDate], [bU], [Name], [FIRSTINIT], [MIDDLEINIT], [FILMREADER], [WearPeriod], [RECSEQNOF], [TRAY_ID], [TRAY_POS], [NL_SHALLOW], [NL_DEEP], [NL_EXTREM], [NL_NEUTRON], [sTART_DATE], [END_DATE], [sERIALNUM], [badgeID], [CR39-ID], [CR39_STAMP], [CONTROL_ID], [EXTSRCFACT], [NEUT_OPT], [NEUT_FACT], [bETA_FACT], [PHOTONFACT], [ALGO_TYPE], [CAL_FACIL], [DOSE_WEIGH], [FLIM_AVEF], [TLD_AVEF], [RTDATEFLAG], [XRAY_FLAG], [REPT_LATE], [REPT_UNUSL], [ANNEALDATE], [uNIT_MEAS], [ENVCNTL], [LABEDIT], [FILMTYPE], [FLMEVLFL], [DOSEREPN], [bKGS_FLAG], [MREADWIN], [sPARE3], [REPNEUT], [DOSETFLAG], [DAMAGE], [NEEDCONT], [Process], [NoteCode], [Emulsion], [sensitivity], [REC_DATE], [OD1S], [OD2S], [OD3S], [OD4S], [OD5S], [RAWS_READ6], [RAWS_READ7], [RAWS_READ8], [OD1I], [OD2I], [OD3I], [OD4I], [OD5I], [RAWI_READ6], [RAWI_READ7], [RAWI_READ8], [DEEP_DOSE], [EYE_DOSE], [sHALL_DOSE], [NEUT_DOSE], [sPARE4], [Valid1s], [Valid2s], [Valid3s], [Valid4s], [Valid5s], [Valid6s], [Range1s], [Range2s], [Range3s], [Range4s], [Range5s], [Range6s], [Valid1i], [Valid2i], [Valid3i], [Valid4i], [Valid5i], [Valid6i], [Range1i], [Range2i], [Range3i], [Range4i], [Range5i], [Range6i], [Pass1Dest], [Pass2Dest], [TimeLoaded], [HOD1S], [HOD2S], [HOD3S], [HOD4S], [HOD5S], [HOD1I], [HOD2I], [HOD3I], [HOD4I], [HOD5I], [CalIDUsed], [selected], [ReadTimeS], [ReadTimeI], [R1V1], [R1V2], [R1V3], [R1V4], [R1V5], [Volt1], [Volt2], [Volt3], [Volt4], [Volt5], [Filt4Ideal], [Filt5Ideal], [Filt6Ideal], [Filt6s], [Filt7s], [Filt8s], [Filt9s], [Filt6i], [Filt7i], [Filt8i], [Filt9i], [isAngle], [abs67], [abs89], [sum6789], [RobotS], [RobotI]) VALUES (9227, 'A', ' ', '67318 ', '00000DPT', ' 16', '01', 'WB ', ' ', 'FB ', '20021201', 'P', 'DARWIN ', 'T', ' ', ' ', 'M', '058000', ' ', ' ', '0000', '0000', '0000', '0000', ' ', ' ', 'F20557 ', 'F20557 ', ' ', ' ', '129', 'C', '1', 'M', 'A', 'A', 'N', 'B', ' ', 'A', 'L', 'N', '2', 'Y', 'Y', ' ', 'MO', 'N', ' ', '4', ' ', ' ', ' ', 'N', ' ', 'N', 'S', 'Y', 'N', '0059434', ' ', '115', 'S', '20030106', NULL, NULL, NULL, NULL, NULL, ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '8/27/2003 10:30:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)"