Avatar of jjdahl
jjdahl
Flag for United States of America asked on

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?
AlgorithmsProgramming Languages-OtherC#

Avatar of undefined
Last Comment
jjdahl

8/22/2022 - Mon
Bob Learned

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

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

"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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
jjdahl

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

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

ASKER
This is for state clients and their database connection could be SQL or Oracle.  Would a SQLDataAdapter work for that?  What about Oracle?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Bob Learned

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jjdahl

ASKER
I'll look into using the SQLDataAdapter instead of the odbc version and will check on SqlBulkCopy.  

=D