Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-08-05
23
Medium Priority
?
208 Views
Last Modified: 2010-04-15
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"
                        }
0
Comment
Question by:andremara
  • 12
  • 11
23 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19637306
You could use a DataTable, and an OleDbDataAdapter to Fill the DataTable, and Update the DataTable.

Bob
0
 

Author Comment

by:andremara
ID: 19639240
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19639431
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:andremara
ID: 19639492
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19639519
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
0
 

Author Comment

by:andremara
ID: 19639544
....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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19639558
What is the target database type?  What .NET version are you using?

Bob
0
 

Author Comment

by:andremara
ID: 19639561
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
0
 
LVL 96

Expert Comment

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

Bob
0
 

Author Comment

by:andremara
ID: 19639657
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.
0
 

Author Comment

by:andremara
ID: 19639913
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19639955
Is this a web application?  What IDE version are you using?

Bob
0
 

Author Comment

by:andremara
ID: 19640043
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
0
 

Author Comment

by:andremara
ID: 19641142
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);
                }

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19641207
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
0
 

Author Comment

by:andremara
ID: 19641311
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19647744
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
0
 

Author Comment

by:andremara
ID: 19648517
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
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 19648588
If you want to write to the target 'phy_det' table, then all you need is:
   
     Select * From phy_det

for the command text.

Bob
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19648592
And a valid connection string to get to the target database.

Bob
0
 

Author Comment

by:andremara
ID: 19648622
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19648953
You just need a table:

sele * from phy_det Where 1 = 0

Bob
0
 

Author Comment

by:andremara
ID: 19649019
got it - thanks bob.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question