none
C# inserting large number of listBox items into database table RRS feed

  • Question

  • Hi!

     

    I want to insert listBox items into database table. The following code snippet works for less number of listBox items:

     

    However, I want to insert thousands of listBox items into database table in which case the following code crashes!

     

    Please answer with a definite solution.

     

    Code snippet:

     

    SqlConnection connectionSql = new SqlConnection("Data Source=(local);Integrated Security=sspi");

    SqlCommand commandSql;

    for (int indexCounter = 0; indexCounter < listBoxEmployeeName.Items.Count; indexCounter++)

    {

    string insertEmployeeName = "INSERT INTO dbo.Employee (EmployeeName) VALUES ('" + listBoxEmployeeName.Items[indexCounter] + "')";

    commandSql = new SqlCommand(insertEmployeeName, connectionSql);

    try

    {

    connectionSql.Open();

    commandSql.ExecuteNonQuery();

    connectionSql.Close();

    }

    catch (Exception exceptionMessage)

    {

    MessageBox.Show(exceptionMessage.ToString());

    }

    }

    MessageBox.Show("Saved to Database!");

     

     

    Thank you.

    Tuesday, April 22, 2008 7:00 PM

All replies

  • Could you please provide the error you're getting? 

     

    Tuesday, April 22, 2008 7:08 PM
  • What you are doing wrong:
    1. you create a new SqlCommand for every iteration - bad, why not use the same one?
    2. you open for every iteration a connection to the database - bad, open the connection once and close it when you finished your work
    3. if you get an error, the connection remains open, you should use a finally block where you can close the connection

     

    SqlConnection connectionSql = new SqlConnection("Data Source=(local);Integrated Security=sspi");

    SqlCommand commandSql = new SqlCommand();

    commandSql.Connection=connectionSql;

    commandSql.CommandText="INSERT INTO dbo.Employee (EmployeeName) VALUES (@employeeName)";

    commandSql.Parameters.Add("@employeeName");

    try

    {

    connectionSql.Open();

    for (int indexCounter = 0; indexCounter < listBoxEmployeeName.Items.Count; indexCounter++)

    {

    commandSql.Parameters["employeeName"].Value=listBoxEmployeeName.Items[indexCounter].Text;

    commandSql.ExecuteNonQuery();

    }


    connectionSql.Close();

    }


    catch (Exception exceptionMessage)

    {

    MessageBox.Show(exceptionMessage.ToString());

    }

    finally

    {

    commandSql.Dispose()

    connectionSql.Close();

    connectionSql.Dispose();

    }

    MessageBox.Show("Saved to Database!");

    Tuesday, April 22, 2008 8:34 PM
  • Thank you for the reply! 

     

    However, I want the "commandSql.ExecuteNonQuery();" NOT IN FOR LOOP (vide code snippet).

     

    Having "commandSql.ExecuteNonQuery();" in "for loop" slows down the database insert and not advised either!

     

    Is there any other solution?

     

     

     

    Code snippet:

     

    SqlConnection connectionSql

    = new SqlConnection("Data Source=(local);Integrated Security=sspi");

    SqlCommand commandSql = new SqlCommand();

     

    private void buttonSave_Click(object sender, EventArgs e)

    {

    commandSql.Connection = connectionSql;

    commandSql.CommandText = "INSERT INTO dbo.Employee (EmployeeID, EmployeeName) VALUES (@employeeID, @employeeName)";

    commandSql.Parameters.AddWithValue("employeeID", "This is the value to be passed");

    commandSql.Parameters.AddWithValue("employeeName", "This is the value to be passed");

    try

    {

    connectionSql.Open();

    for (int indexCounter = 0; indexCounter < listBox1.Items.Count; indexCounter++)

    {

    commandSql.Parameters["employeeID"].Value

    = comboBox1.Items[indexCounter].ToString();

    commandSql.Parameters["employeeName"].Value

    = listBox1.Items[indexCounter].ToString();

    commandSql.ExecuteNonQuery();

    }

    connectionSql.Close();

    }

    catch (Exception exceptionMessage)

    {

    MessageBox.Show(exceptionMessage.ToString());

    }

    finally

    {

    commandSql.Dispose();

    connectionSql.Close();

    connectionSql.Dispose();

    }

    MessageBox.Show("Saved to Database!");

     

     

    Wednesday, April 23, 2008 6:34 AM
  • If you want highly performant operations you can use Batch operations, take a look here http://msdn2.microsoft.com/en-us/library/aadf8fk2.aspx
    and here
    http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622240
    and here
    http://www.codeproject.com/KB/database/PassingArraysIntoSPs.aspx
    I you can also create the script by using a StringBuilder and then execute it as a normal query
    Wednesday, April 23, 2008 8:58 AM