asked on 5/22/2012

Data Adapter Crash

It seems that when I try to do an adapter.Fill(dt); it crashes if there is too much data. I'm trying to copy the contents from a remote database and storing it in a local database.

   string sql = "SELECT * FROM TABLE"  

   command = new OdbcCommand(sql, (OdbcConnection)remoteconn);

   command.CommandTimeout = 0;

   OdbcDataAdapter adapter = new OdbcDataAdapter((OdbcCommand)command);

   dtTemp = new DataTable();

   adapter.Fill(dtTemp);

Open in new window

This is just the fill code not counting that I have to loop through each one and do a setadded() so that it updates another data adapter.

Any clues on how I can do this differently or how much data can a datatable hold?

Algorithms Programming Languages-Other C#

Last Comment

jjdahl

5/23/2012

Bob Learned

5/23/2012

How many rows are you working with? The SqlDataAdapter has different Fill methods, and there is one that can retrieve rows in blocks.

DbDataAdapter.Fill Method (Int32, Int32, DataTable[])
http://msdn.microsoft.com/en-us/library/0z5wy74x.aspx

I prefer not to pull all the records into memory, if I am working with a lot of data.

jjdahl

5/23/2012

ASKER

Hello TheLearnedOne,

Thanks for answering to my question.

Yeah we are looking at 50K+ rows on one particular occasion. I'll look into the overloaded method and see if that works.

Would an ExecuteReader() and looping through it work better? At this point I'm looking at functionality over speed.

Regards,

RAFF

Bob Learned

5/23/2012

"Would an ExecuteReader() and looping through it work better? At this point I'm looking at functionality over speed."

I would think so, but you need to test that assumption to see. You have the perfect opportunity to test, since you have something that is broken. I would suggest creating a unit test for this condition.

50K rows should not choke the data adapter, so there could be something else going on there. What type of data are you getting?

jjdahl

5/23/2012

ASKER

Well the system is setup to do a huge preload where it has one transaction and loads all the data from an oracle or sql database tables and inserts them into a sql database.

we have a remote and local connection open through all of this using ODBC while we loop through all the select statements and inserting to the local database.

Most of it uses a datareader but in one case if it's a certain type of table we insert them into a temp table instead and that is where the adapter code was used.

Here is the code...

if (name == "TINLGENT" || name == "TININDIV" || name == "TINLGCOM" || name == "TINWSLEC")
{
   OdbcDataAdapter adapter = new OdbcDataAdapter((OdbcCommand)command);
   SqlDataAdapter adapterNew = new SqlDataAdapter(new SqlCommand("SELECT * FROM " + name + "_TEMP", localconn, localtran));
   SqlCommandBuilder builder = new SqlCommandBuilder(adapterNew);

   dtTemp = new DataTable();
   adapter.Fill(dtTemp);
                                
   foreach (DataRow drTemp in dtTemp.Rows)
       drTemp.SetAdded();
                       
    adapterNew.Update(dtTemp);
}

Open in new window

It was used as a shortcut to enter the data, but it fails on the adapter.FILL

Bob Learned

5/23/2012

Why are you using ODBC, rather than the native client? There are unknown limitations to ODBC. It is pretty simple to change to the SqlDataAdapter from OdbcDataAdapter.

jjdahl

5/23/2012

ASKER

This is for state clients and their database connection could be SQL or Oracle. Would a SQLDataAdapter work for that? What about Oracle?

ASKER CERTIFIED SOLUTION

Bob Learned

5/23/2012