asked on

Update to table en-mass, not one wimpy insert at a time...

I'd like to insert records into a table en-mass, rather than via a for loop, doing an INSERT statement for each row in an array. What's a better, faster, stronger, etc way? Thanks :) Andre

Records to insert were obtained by executing a SELECT on the source table OutScan.dbf, and outputting an array. This array was looped through, and an INSERT statement was done for each row.

ArrayList aScannedWeights = GetScannedWeightsFromOutscan(sBatch, sSequence);
                        for (int k=0; k<aScannedWeights.Count; k++)
                        {
                              string sScannedWeight = (string)aScannedWeights[k];
                              INSERT INTO phy_det (Track) values "SomeValue"
                        }

Bob Learned

You could use a DataTable, and an OleDbDataAdapter to Fill the DataTable, and Update the DataTable.

Bob

andremara

ASKER

Thanks for that Bob.
This is probably basic to understanding data tables, but I assume from your answer then, that a datatable is 'bound' to underlying data and what occurs on datatable happens to the data. could you show a very simple example of the syntax to accomplish this?
Thanks
Andre

Bob Learned

C# 2005 example:

DataTable dt = new DataTable();
using (OleDbDataAdapter adapter = new OleDbDataAdapter ("command text", "connection string"))
{
adapter.Fill(dt);
dt.Rows[0]["Name"] = "Bob";
adapter.Update(dt);
}

Bob

andremara

ASKER

Bob,
that's a good start. What I'm needing is to take a bunch of records that are already in a table table, and append them into another database. The example above helps when I'm changing existing data, but not for the situation described here. Might you have a code snippet to get me rolling?
Thanks
Andre

Bob Learned

DataTable dtSource = new DataTable();
DataTable dtTarget = new DataTable();
using (OleDbDataAdapter adapterSource = new OleDbDataAdapter ("command text", "connection string"))
{
adapterSource.Fill(dtSource);

foreach (DataRow dr in dtSource.Rows)
dtTarget.ImportRow(dr);

using (OleDbDataAdapter adapterTarget = new OleDbDataAdapter ("command text", "connection string"))
{
adapterTarget.Update(dtTarget);
}
}

Bob

andremara

ASKER

....meant to say DataTable (not table table). Actually my initial code example shows data populating an array, but I'm thinking it could just as easily populate a DataTable. That then has to be appended into another database. Hope I'm keeping this clear!
Andre

Bob Learned

What is the target database type? What .NET version are you using?

Bob

andremara

ASKER

wow - that's simple - I'll try it and keep you posted. Do you believe it's a lot faster than doing an SQL insert statement for each row in the target? My hope is that it will be.
Andre

Bob Learned

C# 2005 has the SqlBulkCopy, if you are writing to an SQL Server database, and it is highly optimized for speed.

Bob

andremara

ASKER

That's good to know for later - I have just migrated to VS2005, but am writing against....Foxpro tables using ODBC drivers. Later on, will convert into MSSQL though.

andremara

ASKER

Bob,
regarding your question on what version of .NET, according to the Default Web Site Properties (r-clicking default web), I'm using 1.1.4322. I do have 2.0 installed, so I'm vague as to whether VS2005 automatically picks 2.0 at runtime. So the answer is, either 1.1.4322 or 2.0.
Andre

Bob Learned

Is this a web application? What IDE version are you using?

Bob

andremara

ASKER

Bob,
This is a webservice, and oh! I just r-clicked under the WebService, and sure enough, 2.0.50727 showed up. I just converted the solution file to VS2005. IDE VS2005 Professional Edition.
Andre

andremara

ASKER

I've constructed the following, and am stuck figuring what should be in myCommand for the adapterTarget. The target is the PHY_DET foxpro free table into which I want to dump dtTarget:

//// Update PHY_DET as per TheLearnedOne's comments via Experts-Exchange.
DataTable dtSource = new DataTable();
DataTable dtTarget = new DataTable();
string sConnectionString = HostDatabase.HostDatabaseConnectionString();
OleDbConnection connection = new OleDbConnection(sConnectionString);
connection.Open();
sSQL = "SELECT batch+sequence as track, rectype,batch, sequence, wgt2, userid as who ";
SQL += "FROM \""+ sDatabaseRootDirectory+ "\\OUTSCAN WHERE batch ='" + sBatch + "'" + "AND sequence ='" + sSequence + "'";
OleDbCommand myCommand = new OleDbCommand(sSQL, connection);
try
{
OleDbDataAdapter adapterSource = new OleDbDataAdapter(myCommand);
adapterSource.Fill(dtSource);
}
catch (System.Exception ex)
{
connection.Close();
string sError = "The following SQL command caused an error:\r\n" + sSQL;
throw new DataManagerException(sError, ex);
}
foreach (DataRow dr in dtSource.Rows)
dtTarget.ImportRow(dr);

try
{
OleDbDataAdapter adapterTarget = new OleDbDataAdapter(myCommand);
adapterTarget.Update(dtTarget);
}
catch (System.Exception ex)
{
connection.Close();
string sError = "The following SQL command caused an error:\r\n" + sSQL;
throw new DataManagerException(sError, ex);
}

Bob Learned

You don't need some of those objects:

//// Update PHY_DET as per TheLearnedOne's comments via Experts-Exchange.
DataTable dtSource = new DataTable();
DataTable dtTarget = new DataTable();

string sConnectionString = HostDatabase.HostDatabaseConnectionString();
sSQL = "SELECT batch+sequence as track, rectype,batch, sequence, wgt2, userid as who ";
SQL += "FROM \"" + sDatabaseRootDirectory + "\\OUTSCAN WHERE batch ='" + sBatch + "'" + "AND sequence ='" + sSequence + "'";
try
{
using (OleDbDataAdapter adapterSource = new OleDbDataAdapter(sSQL, sConnectionString))
adapterSource.Fill(dtSource);
}
catch (System.Exception ex)
{
string sError = "The following SQL command caused an error:\r\n" + sSQL;
throw new DataManagerException(sError, ex);
}

foreach (DataRow dr in dtSource.Rows)
dtTarget.ImportRow(dr);

try
{
using (OleDbDataAdapter adapterTarget = new OleDbDataAdapter(sSQL, sConnectionString))
adapterTarget.Update(dtTarget);
}
catch (System.Exception ex)
{
string sError = "The following SQL command caused an error:\r\n" + sSQL;
throw new DataManagerException(sError, ex);
}

I don't see where the connection string between the two data adapters is different.

Bob

andremara

ASKER

Bob,
Thanks for the shortened code. If I understand it correctly, the PHY_DET table is to be updated somehow with these statements, but my confusion arises from not knowing what to put in the sSQL statement below. As you know, my goal is to append records into the PHY_Det from those in dtTarget, so how does *that go?

using (OleDbDataAdapter adapterTarget = new OleDbDataAdapter(sSQL, sConnectionString))
adapterTarget.Update(dtTarget);

Thanks for your patience :)
Andre

Bob Learned

You have imported rows from source to target. All the rows states are going to be 'Added'. When you call the Update method, then it will insert the records into the target table. The connection string needs to be to the target database, and not the source database.

Bob

andremara

ASKER

I'm confused - the sSQL statement used to create adapterSource tells the adapter what table and records to retrieve, so I would think sSQL needs to remain as a parameter when creating adapterSource. What I'm missing is the understanding around how to say, "use the phy_det file, and associate it with adaptertarget", so that adapterTarget.Update(dtTarget) will know what the target table is.
Andre

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

And a valid connection string to get to the target database.

Bob

andremara

ASKER

I see. Now I'm thinking this way will be a LOT slower than my original method, because phy_det has over a million records, and sele * from phy_det would take a while, but I'll try.
thanks
andre

Bob Learned

You just need a table:

sele * from phy_det Where 1 = 0

Bob

andremara

ASKER

got it - thanks bob.