Solved

How to transfer data from datable to a database all at once.

Posted on 2011-03-14
8
581 Views
Last Modified: 2012-05-11
I have the following code from a VB.NET project that loads some data from a MySQL table into a datatable then inserts the data into another MySQL table on a different server. When loading the data into the datatable, it loads with a simple one-line command, dtTmp.Load(). Then, to insert into the other table, I have to cycle through each row and insert one record at a time. Does anyone know of a more efficient way to insert the data all at once rather then one record at a time. I experimented with table adapters but gave up after 4 unsuccessful hours.

               Dim cnM As New MySqlConnection(CS_MASTER)
               Dim cnA As New MySqlConnection(CS_ARCHIVE)
               Dim cmd As MySqlCommand
               Dim rst1 As MySqlDataReader
               Dim strSQL As String
               Dim dtTmp As New DataTable("temp")

		'loads the source data into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Inserts the source datatable to the new database
		'THIS IS WHERE I'D LIKE TO LOAD WITHOUT CYCLING THROUH EACH ROW.
                cnA.Open()
                For Each dtRow In dtTmp.Rows
                    strSQL = "INSERT INTO tbllkt_trans_archive (Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID)" & _
                             " SELECT " & dtRow(0) & " AS Expr0, '" & dtRow(1) & "' AS Expr1, '" & dtRow(2) & "' AS Expr2, " & dtRow(3) & " AS Expr3, '" & Format(dtRow(4), "yyyy-MM-dd hh:mm:ss") & "' AS Expr4, '" & dtRow(5) & "' AS Expr5, '" & dtRow(6) & "' AS Expr6"
                    cmd = New MySqlCommand(strSQL, cnA)
                    cmd.ExecuteNonQuery()
                Next
                cnA.Close()
                cmd = Nothing

Open in new window

0
Comment
Question by:pdi656
  • 5
  • 3
8 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35132906
Hi,

DataTableAdapter with CommandBuilder will help you and create all the Insert/Update/Delete statements for you, the only thing you need is to know the Select command that populates the Datatable the first time and that your Table have a Primary Key.

Here is an example of how to do it
Dim conConnection As OleConnection("YOURSTRINGCONNECTION")
        conConnection.Open()
        Dim strCommand as String

        'This is necesary for the CommandBuilder to Create the Insert/Update/Delete statements
        strCommand = "Select * From YourTable"

        Dim daAdapter As New OleDataAdapter(strComand, conConnection)
        Try
            Dim dcbCommand As New OleCommandBuilder(daAdapter)
            daAdapter.Update(dtData)
        Catch ex As Exception
            'Do something with the exception
        Finally
            daAdapter.Dispose()
        End Try

Open in new window

0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35132950
And here is how should be using your code example
Dim cnM As New MySqlConnection(CS_MASTER)
               Dim cnA As New MySqlConnection(CS_ARCHIVE)
               Dim cmd As MySqlCommand
               Dim rst1 As MySqlDataReader
               Dim strSQL As String
               Dim dtTmp As New DataTable("temp")

		'loads the source data into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()


                'UPDATE USING DATAADAPTER
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
			
                cnA.Open()
		Dim daAdapter as New MySqlDataAdapter(strSQL, cndA)

                Dim dcbCommand As New SqlCommandBuilder(daAdapter)
                daAdapter.Update(dtData)

                ''Inserts the source datatable to the new database
		''THIS IS WHERE I'D LIKE TO LOAD WITHOUT CYCLING THROUH EACH ROW.
                'For Each dtRow In dtTmp.Rows
                '    strSQL = "INSERT INTO tbllkt_trans_archive (Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID)" & _
                '             " SELECT " & dtRow(0) & " AS Expr0, '" & dtRow(1) & "' AS Expr1, '" & dtRow(2) & "' AS Expr2, " & dtRow(3) & " AS Expr3, '" & Format(dtRow(4), "yyyy-MM-dd hh:mm:ss") & "' AS Expr4, '" & dtRow(5) & "' AS Expr5, '" & dtRow(6) & "' AS Expr6"
                '    cmd = New MySqlCommand(strSQL, cnA)
                '    cmd.ExecuteNonQuery()
                'Next

                cnA.Close()
                'cmd = Nothing

Open in new window

0
 
LVL 1

Author Comment

by:pdi656
ID: 35147113
Thank you for the examples - especially the modified version using my original code. That makes it real easy to understand. The good news is that it didn't error out on me. My original attempts using a data adapter kept kicking out errors. The bad news is that the data wasn't inserted into the MySQL table as I had anticipated. Any more ideas?

Here's my revised code:
                'loads the master data to be archived into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Inserts the master temp datatable to the archive database
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_log"
                cnA.Open()
                Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)
                Dim dcbCommand As New MySqlCommandBuilder(daAdapter)

                daAdapter.Update(dtTmp)
                daAdapter.Dispose()
                dcbCommand = Nothing
                cnA.Close()

Open in new window

0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35147282
Ok, according to your code, there is nothing to INSERT/UPDATE/DELETE, since the rows  do not change, and you are trying to update the SAME TABLE

I saw you original post, and based on that, the problem will be the destination TABLE, you should use tbllkt_trans_archive in the second Select Command

       'Inserts the master temp datatable to the archive database
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_archive"
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:pdi656
ID: 35147683
They are different tables on different database servers. The big picture is that I have multiple PCs (master dbs) that I want to ship their transactional data nightly to a common server (archive database).

I tried your latest suggestion (which makes complete sense to me as to why it should be changed), but that didn't seem to insert the records either. Thank you for sticking with this. I appreciate your help. I think this path is close, but not quite there yet.
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35148415
Ok, I understand now, the problem is that the UPDATE method will look for CHANGED rows, but since no rows has been changed it will not do any insert even if the target table does not have the rows because is in another server.

So, you can do something like this (but it will force you to CYCLING THROUH EACH ROW) at least to change  the rows  status


'loads the master data to be archived into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Inserts the master temp datatable to the archive database
		'SELECT NO RECORDS, JUST OBTAIN THE FIELDS			
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_archive WHERE 1=0"
                cnA.Open()

		'Use another Datable 
		Dim dtTarget As Datatable		
                Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)

		'Fill the datatable from ARCHIVE DB, this will gives an empty datatable
		daAdapter.Fill(dtTarget)

		'Add/Insert the Rows from MASTER DB to ARCHIVE DB
		For each drTmpRow in dtTmp.Rows
		     dtTarget.ImportRow(drTmpRow)
		Next

                Dim dcbCommand As New MySqlCommandBuilder(daAdapter)

		'Updte the ARCHIVE DB
                daAdapter.Update(dtTarget)
                daAdapter.Dispose()
                dcbCommand = Nothing
                cnA.Close()

Open in new window

0
 
LVL 1

Author Comment

by:pdi656
ID: 35148921
You're right...the row state is set to unchanged. I like where you headed me with that code - it got me thinking. I did a little research and found a way to alter the row state in my current datable without have to insert them into a second table. It cycled through 4k rows in a blink of an eye. The INSERT code then worked perfectly. The row state was the key. Here's my completed code, in case anyone else is curious in the future:

                'SELECT the master data to be archived into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Loop the rows of the datatable and set the row status to ADDED
                'NOTE This is required for the subsequent INSERT to work.
                For Each dr As DataRow In dtTmp.Rows
                    dr.SetAdded()
                Next

                'INSERT the master temp datatable to the archive database
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_archive"
                cnA.Open()
                Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)
                Dim dcbCommand As New MySqlCommandBuilder(daAdapter)

                daAdapter.Update(dtTmp)
                daAdapter.Dispose()
                dcbCommand = Nothing
                cnA.Close()

Open in new window


Thanks again for all of your help!
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35148939
Ok, great!!! glad to help
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now