cannot perform batch insert operations

Question

4.50/5 (2 votes)

See more:

SQL-server-2000

, +

I have an application that extracts records from an excel file.
There are about 1000 records which must be inserted into a database from the excel file.
I store the records in about 4 collections (each one representing a row) so I have an SqlDataAdapter so that I can insert the records in one go.

Backend wise I created a stored procedure which is then used to perform the insert operation.

CREATE PROCEDURE [dbo].[spAddDebtor] 
@name char(50),
@person char(50),
@number char(50),
@address char(255)

AS
IF EXISTS(SELECT * FROM dbo.Debtor WHERE Debtor_Name = @Name)
     RETURN
INSERT INTO DEBTOR (Debtor_Name, Contact_Person, Contact_number,  Contact_Address)

VALUES (@name, @person, @number, @address)
GO

and in C# I use this method

// Insert records into database
        private void insertRecords()
        {
            // populate collections from Excel file
            populateAddress();
            populateContactPerson();
            populateNames();
            populatePhones();

            string sql = "SELECT * FROM DEBTOR";

            // Create data adapter
            SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString);

            // Create and fill dataset
            DataSet ds = new DataSet();
            da.Fill(ds, "debtor");
            
            // Get data table reference
            DataTable dt = ds.Tables["debtor"];

            sCommand.UpdatedRowSource = UpdateRowSource.None;

            SqlDataAdapter adpt = new SqlDataAdapter();

            
            try
            {
                // iterate through all records 
                // and perform insert on each iteration
                for (int i = 0; i < names.Count; i++)
                {
                    
                   
                   
                    sCommand.Parameters.AddWithValue
                           ("@debtor_name", Names[i].ToString());
                    sCommand.Parameters.AddWithValue
                           ("@contact_person", ContactPeople[i].ToString());
                    sCommand.Parameters.AddWithValue
                           ("@contact_number", Phones[i].ToString());
                    sCommand.Parameters.AddWithValue
                           ("@contact_address", Addresses[i].ToString());

                    

                   

                }

                adpt.InsertCommand = sCommand;

                

                // Specify the batch size
                Size = Names.Count;
                adpt.UpdateBatchSize = size;

                // Open the connection
                objConnection.Open();
                RowsAffected = adpt.Update(dt); // Execute the statement and show
                                                // number of rows affected
                objConnection.Close();
                
               
                

            }
            catch (SqlException ex)
            {
                throw ex;

            }
            finally
            {
                // Realease resources
                objConnection = null;
                objCommand = null;
                sCommand = null;
                sConnection = null;
                adpt = null;
                dt = null;
                ds = null;
                da = null;

            }
            
        }

The thing is this code runs with no errors but in the database the new records are not there.
I tried putting break point to check if the collections where populated with the excel data and it appears that they infact have the data the insertion is simply not happening.

I tested the sproc from sql itself at it seems to work with no problems does anyone have a suggestion as to how I can go about it?

Posted 16-Feb-11 21:06pm

2gizzo

Updated 16-Feb-11 21:37pm

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 17-Feb-11 3:37am

Edited for Grammar and Readability.

Albin Abel 17-Feb-11 4:56am

SqlDataAdapter adpt = new SqlDataAdapter(); no connection and query string defined for this adapter. How it knows where to connect?

sCommand is not initialized in this method. So we cannot understand what insert query you are passing in

if you want to update a block of statements use a transaction block, so that if anything fails you can roll back

2gizzo 17-Feb-11 5:10am

sConnection and sCommand is initialized in the constructor here is how the constuctor looks
<pre>
// Constructor definition
public Uploader(string path, string connectStr)
{
this.path = path;
ConnectionString = connectStr;
constr = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=" + path + @";Extended Properties=""Excel 12.0;HDR=YES;""";
objConnection = new OleDbConnection(constr);
objCommand = new OleDbCommand();
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.Text;
objCommand.CommandText = "SELECT * FROM [Sheet1$]";
sConnection = new SqlConnection(connectStr);
sCommand = new SqlCommand();
sCommand.Connection = sConnection;
sCommand.CommandType = CommandType.StoredProcedure;
sCommand.CommandText = "spAddDebtor";
sCommand.CommandTimeout = 2000;

size = 0;
rowsaffected = 0;

// Initialize collections
names = new List<string>();
phones = new List<string>();
addresses = new List<string>();
contactPeople = new List<string>();

}
</pre>

shankark 17-Feb-11 5:26am

Check this link.
Performing Batch Updates with a DataAdapter
http://msdn.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

2gizzo 17-Feb-11 7:20am

Already did but I modified it to use a stored procedure instead of SQL INSERT but its not inserting, what surprises me the most is why am I not getting an error of any sort?

2 solutions

Solution 1

Did you give rights to the user used in the connection string to execute the SP?

Sorry cannot help you with the code as I use Linq.

Posted 17-Feb-11 3:07am

milenalukic

Updated 17-Feb-11 3:09am

v2

Comments

2gizzo 17-Feb-11 9:32am

How would I go about doing the same task using LINQ? can you give some examples?

2gizzo 17-Feb-11 10:37am

the user is sa which has full access rights anyway right

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Ashish · Accepted Answer · 2011-02-17T06:55:00

Concentrate on your following code

for (int i = 0; i < names.Count; i++)
               {



                   sCommand.Parameters.AddWithValue
                          ("@debtor_name", Names[i].ToString());
                   sCommand.Parameters.AddWithValue
                          ("@contact_person", ContactPeople[i].ToString());
                   sCommand.Parameters.AddWithValue
                          ("@contact_number", Phones[i].ToString());
                   sCommand.Parameters.AddWithValue
                          ("@contact_address", Addresses[i].ToString());





               }

You are Looping through the names List and adding parameter to sCommnand object.
If you directly run sCommand.ExecuteNonQuery();, it will throw an exception as there are more parameters than actually specified int the Stored Procedure.

Also you are calling

RowsAffected = adpt.Update(dt);

From your code there is no relation of sCommand & dt object.
adpt.InsertCommand = sCommand; is meaningless in this context beacuse dt has no extra rows added.

Solution 1:
you may call like this

for (int i = 0; i &lt; names.Count; i++)
               {



                   sCommand.Parameters.AddWithValue
                          ("@debtor_name", Names[i].ToString());
                   sCommand.Parameters.AddWithValue
                          ("@contact_person", ContactPeople[i].ToString());
                   sCommand.Parameters.AddWithValue
                          ("@contact_number", Phones[i].ToString());
                   sCommand.Parameters.AddWithValue
                          ("@contact_address", Addresses[i].ToString());

//New Code to directly call the stored procedures
               sCommand.ExecuteNonQuery();
               sCommand.Parameters.Clear();

               }

//Comment following lines

/*
                //Specify the batch size
                Size = Names.Count;
                adpt.UpdateBatchSize = size;

                // Open the connection
                objConnection.Open();
                RowsAffected = adpt.Update(dt); 
                // Execute the statement and show
                // number of rows affected
                objConnection.Close();

*/

Solution 2:

C#

for (int i = 0; i < names.Count; i++)
    {



             /* sCommand.Parameters.AddWithValue
                     ("@debtor_name", Names[i].ToString());
              sCommand.Parameters.AddWithValue
                     ("@contact_person", ContactPeople[i].ToString());
              sCommand.Parameters.AddWithValue
                     ("@contact_number", Phones[i].ToString());
              sCommand.Parameters.AddWithValue
                     ("@contact_address", Addresses[i].ToString());
        */

            //New Code for sol 2 :
            DataRow f = dt.NewRow();
            f["@debtor_name"] = Names[i].ToString();
            f["@contact_person"] = ContactPeople[i].ToString();
            f["@contact_number"] = Phones[i].ToString();
            f["@contact_address"] = Addresses[i].ToString();

            dt.Rows.Add(f);



  }

         // Specify the batch size
         Size = Names.Count;
         adpt.UpdateBatchSize = size;   // NOW THIS WILL BE CALLED ..

         // Open the connection
         objConnection.Open();
         RowsAffected = adpt.Update(dt); // Execute the statement and show
                                         // number of rows affected
         objConnection.Close();