Data Adapter Crash

jjdahl
jjdahl used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2008

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2008

Commented:
"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?
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2008

Commented:
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.

Author

Commented:
This is for state clients and their database connection could be SQL or Oracle.  Would a SQLDataAdapter work for that?  What about Oracle?
Most Valuable Expert 2012
Top Expert 2008
Commented:
There is an Oracle client from Microsoft, or ODP.NET from Oracle, that would be the OracleDataAdapter.  

Each of these data adapter classes implements the IDbDataAdapter pattern, so they would be easily switchable, dependent on a configuration option.

If you are doing bulk inserts into SQL Server, I would suggest using the SqlBulkCopy class, since it is optimized for SQL Server bulk inserts.

Author

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

=D

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial