using winforms to insert data in sql database , drawback of opening connection frequently

Question

I am developing a front-end sales application.

Is this an efficient way of inserting data multiple times into a sql table, from a single button:

private void button1_Click(object sender, EventArgs e)
{
    c.Open();
    string w = "insert into checkmultiuser(username) values (@username)";

    SqlCommand cmd = new SqlCommand(w, c);

    cmd.Parameters.Add("@username", SqlDbType.VarChar);
    cmd.Parameters["@username"].Value = textBox1.Text;
    //cmd.ExecuteNonQuery();
    cmd.ExecuteReader();
    c.Close();    
}

What are its drawbacks? One would be that again and again the connection is opened and closed when the button is clicked which would effect the speed greatly.

Answer 1

You ar edoing the right way: see this question: to close connection to database after i use or not? too.

Answer 2

Perhaps don't do the database insert for each entry, but store each entry in a DataSet, then insert them all at once, a la a save button.

For each entry do this:

String s = textBox1.Text;

If ( *\Enter validation logic*\ )
{
  //Insert data into DataSet
}
else
{
  //Throw error for user.
}

Then once you're ready to commit to DB, insert each item from the DataSet, similar to the examples in the other answers here.

Answer 3

I would open the connection once when the form opens and re-use that connection until the form is closed.

As for inserting records, the code you have is right.

Answer 4

From a resource management point of view it would be better if you can work out how many times you need to insert the data and then perform the operation in the one button click, perhaps iterating through a loop until the correct amount of insert operations has been completed. This means you are not constantly opening and closing the connection with each button press but instead opening it, performing the insert queries and closing the connection.

Also I recommend that you implement your code with the "using" statement, this way it will automatically handle the disposal and release of resources.

private void button1_Click(object sender, EventArgs e, string[] value)
{
      try
      {
           using(SQLConnection c = new SQLConnection(connectionString))  
           using(SQLCommand cmd = new SQLCommand(c))
           {
               c.Open();

               string w = "insert into checkmultiuser(username) values (@username)";

               cmd.CommandText = w;
               cmd.Parameters.Add("@username", SqlDbType.VarChar);    

               for(int i = 0; i < value.Length; i++)
               {
                   cmd.Parameters["@username"].Value = value[i];    
                   cmd.ExecuteReader();
               }
           }
        }
        catch(Exception e)
        {
             Console.WriteLine(e.Message);
        }
}

If you can create the SQLConnection in the method then it will also allow you create it in a using statement, again taking care of managing and releasing resources.

In terms of the statement you are using I can't see any problems with it, you're using parameterized queries which is a good step to take when interacting with SQL databases.

References:

try-catch - MSDN

Answer 5

I don't think you should have to worry about the time lag due to opening and closing a connection, particularly if it is happening on a manually triggered button click event. Human perceivable response time is about 200 milliseconds. At best, I'd guess someone could click that button once every 100 milliseconds or so. Plenty of time to open and close a connection.

If, however, you are dealing with a routine that will be connecting to your database, you could pass in the connection, include a using statement as Mr. Keeling mentioned already, and just verify that it is ready.

Here is yet another approach, which returns a DataTable (since your original post displayed executing a Data Reader):

public static DataTable UpdateRoutine(SQLConnection c, string value) {
  const string w = "insert into checkmultiuser(username) values (@username)";
  DataTable table = new DataTable();
  using(SQLCommand cmd = new SQLCommand(w, c)) {
    cmd.Parameters.Add("@username", SqlDbType.VarChar);
    cmd.Parameters["@username"].Value = value;
    try {
      if ((cmd.Connection.State & ConnectionState.Open) != ConnectionState.Open) {
        cmd.Connection.Open();
      }
      using (SqlDataReader r = cmd.ExecuteReader()) {
        table.Load(r);
      }
    }
    return table;
  } catch(SqlException err) { // I try to avoid catching a general exception.
    MessageBox.Show(err.Message, "SQL Error");
  }
  return null;
}