Merging Tables in a DataSet 2

[acent]

Greetings,

This one is kind of hard to explain, though the concept is somewhat simple.

I have two databases running a .NET2.0 app. One is an Access2007, and the other is an proprietary ODBC DB. What I want to do is update a table in the Access Database with data from the ODBC database. I don't want to display the data, just basically run an update.

If I open the access database and link the ODBC, simply running a query like so would accomplish it:

UPDATE tblTable1 INNER JOIN ODBCTable2 ON tblTable1.Field1 = ODBCTable2.record_id SET field = field ...;

The problem with doing it this way, is that this is slower than molasses in winter. 13 seconds slow with minimal test data to be exact.

So I figued creating a dataset might be the solution. I have created my DataSet and added the tables, but now I'm stuck. Can someone help turn the dataset into a query?

Dim strSQL1, strSQL2 As String
Dim odbcconn as new OdbcConnection(ConfigurationManager.ConnectionStrings("odbcdb").ConnectionString)
Dim oleconn as new OleDbConnection(ConfigurationManager.ConnectionStrings("accdb").ConnectionString)
Dim ds As New DataSet
    
strSQL1 = "SELECT de_number,de_status,de_owing FROM debtor;"
strSQL2 = "SELECT ptp_rowid,fileno FROM tblPTP;"

Dim odbcDA as OdbcDataAdapter = New OdbcDataAdapter(strSQL1,oleconn)
odbcDA.Fill(ds,"odbcfiles")
Dim oleDA as OleDbDataAdapter = New OleDbDataAdapter(strSQL2,oleconn)
oleDA.Fill(ds,"olefiles")
    
ds.Relations.Add("olefiles_to_odbcfiles", _
  ds.Tables("odbcfiles").Columns("de_number"), _
  ds.Tables("olefiles").Columns("fileno"))
    
odbcconn.Close
oleconn.Close

If I'm on the wrong track and smoking the powerful stuff, please let me know and point me in a better direction.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[jmeckley]

this gets the data. now you need to loop through the table and update the values. then commit the values back to the access database.

something like

//loop through each record and update the value(s)
foreach(DataRow row in ds.Tables["..."])
{
   row[column 1] = the new value;
   row[column 2] = the new value;
}
//update the records in ms access
oleDA .Update(ds);

btw your code is very error prone. look into the using keyword to properly close/dispose your connections/adapters after you are finished using them.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

Trying to do this using datasets is only causing more over head and going to be slower. They way were doing it initally is the way it should be done. Also, using any type of ODBC connection is usually slower than a regular RDBMS connection.

 

[acent]

Thank you both for your comments. Unfortunately, higher forces have conspired against me to limit my time on this exact issue.

I will work with it later.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[acent]

I finally got some more time and have completed rewriting the sub. For any interested parties, the new sub is as follows:

  Private Sub update_operator
    Dim strSQL As String
    Dim fileList As String = "("
    Dim odbcconn as new OdbcConnection(ConfigurationManager.ConnectionStrings("collectdb").ConnectionString)
    Dim oleconn as new OleDbConnection(ConfigurationManager.ConnectionStrings("accdb").ConnectionString)
    Dim ds As New DataSet
    
    strSQL = "SELECT DISTINCT fileno FROM tblPTPs;"
    
    oleconn.Open
    odbcconn.Open
    Dim oleCMD As OleDbCommand = New OleDbCommand(strSQL, oleconn)
    Dim oleDR As OleDbDataReader = oleCMD.ExecuteReader
    While oleDR.Read
      fileList = fileList & oleDR.Item("fileno") & ","
    End While
    fileList = Mid(fileList,1,(Len(fileList)-1)) & ")"
    strSQL = "SELECT de_number,de_status,de_collector FROM debtor WHERE de_number IN " & fileList & ";"
    Dim odbcCMD As OdbcCommand = New OdbcCommand(strSQL, odbcconn)
    Dim odbcDR As OdbcDataReader = odbcCMD.ExecuteReader
    While odbcDR.Read
      strSQL = "UPDATE tblPTPs SET debtor_status='" & odbcDR.Item("de_status") & "',operator='" & odbcDR.Item("de_collector") & "' WHERE fileno = " & odbcDR.Item("de_number") & ";"
      Dim oleCMD2 As OleDbCommand = New OleDbCommand(strSQL, oleconn)
      oleCMD2.ExecuteNonQuery
    End While
    oleconn.Close
    odbcconn.Close
  End Sub

Doing it this way reduced the time from 10-15 seconds down to 2-3.

Jason, you mentioned my codee was error prone due to not properly closing connections. Can you please elaborate on this? All the tutorials, examples, etc. that I found close the connection this way. I was not able to find information on doing it another way.

Thanks,
Andrew

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[jmeckley]

look into the using keyword. basically it looks like this

using(var connection = new XConnection(...))
{
   using(var command = connection.CreateCommand())
   {
   }
}

the using keyword is a powerful feature that should be utilized when possible. there is alot happening with the use of Using.

1. using works with IDisposable implementation. when the block is closed Disposed() is called. With db connections they will be closed and then disposed.
2. using is sugar syntax for

XConnection connection;
try
{
    connection = new XConnection(...);
}
finally
{
    if(connection == null)
    {
       connection.Dispose();
    }
}

the finally block will execute before the error it thrown. You get benefits of the exception stack with resource cleanup.

as your code stands now if an error is thrown before the connection is closed you could have system resources "hangning" around before GC (garbage collection) will actually remove them. by disposing of the objects GC knows to pick them up. That's a rough overview anyway.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[acent]

IIII SEE NOW! My mind missed that "using" was the keyword you were referring to.

I will read up and see if I can rewrite my code to incorporate that.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws