Inserting data multiple times into an SQL table, from a single button on Windows Forms

Question

I am inserting data from a Windows Forms form into an SQL database as below:

public partial class Form1 : Form
{
    SqlConnection c = new SqlConnection();
    string q = "Trusted_Connection = true; ";

    public Form1()
    {
        InitializeComponent(); c.ConnectionString = q;
        MessageBox.Show("Connecting Database");
    }

    private void button1_Click(object sender, EventArgs e)
    {
        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.ExecuteReader();
    }

But, when I click the button again, then it says "There is already an open DataReader associated with this Command which must be closed first.".

How do I deal with it?

*UPDATE: * I have changed the button click event code as below:

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.

Answer 1

A reader needs an open connection because you retrieve the results one at a time. You'll want to do an ExecuteNonQuery() for inserts, deletes and updates. You also need to close your connection afterwards. The alternative is to wrap your inserts into a loop if you plan to do multiple inserts.

There are a couple of ways you can do this. This way is useful if you plan to reuse the same connection object and command object over and over again:

public partial class Form1 : Form
{
    SqlConnection _cn;
    SqlCommand _cmd;

    const string ConnString = "Enter your connection string here";
    readonly string _insertQuery;
    const string UsernameParm = "@username";

    public Form1()
    {
        InitializeComponent(); 
        _cn = new SqlConnection(ConnString);

        _cmd = new SqlCommand(InsertQuery, _cn);        
        _cmd.Parameters.Add(UsernameParm, SqlDbType.VarChar);

        _insertQuery = String.Format("INSERT INTO checkmultiuser(username) VALUES ({0})",
                                     UsernameParm);
    }

    private void button1_Click(object sender, EventArgs e)
    {        
        _cmd.Parameters[UsernameParm].Value = textBox1.Text;

        try
        {
            _cn.Open();
            _cmd.ExecuteNonQuery();
        }
        catch (Exception ex) // probably best to catch specific exceptions
        {
            // handle it
        }
        finally
        {
            _cn.Close();
        }
    }
}

Just be sure to dispose of the connection and command objects (when the form closes or where ever makes the most sense for your application).

Using blocks are a safer alternative, but they will dispose of the object each time (although connections use connection pooling by default):

public partial class Form1 : Form
{
    const string ConnString = "Enter your connection string here";
    readonly string _insertQuery;
    const string UsernameParm = "@username";

    public Form1()
    {
        InitializeComponent();             

        _insertQuery = String.Format("INSERT INTO checkmultiuser(username) VALUES ({0})",
                                     UsernameParm);
    }

    private void button1_Click(object sender, EventArgs e)
    {  
        using (var cn = new SqlConnection(ConnString))
        {
            using (var cmd = new SqlCommand(InsertQuery, cn))
            {
                cmd.Parameters.Add(UsernameParm, SqlDbType.VarChar);
                cmd.Parameters[UsernameParm].Value = textBox1.Text;

                cn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Any combination will work as well. You could setup the connection once and just wrap the command object in a using block. I know some people are not fans of nested using blocks (since behind the scenes it is try(try finally) finally)).

Answer 2

What Jason said.

Whilst you're there it might be worth perhaps changing your code slightly to:

private void button1_Click(object sender, EventArgs e)

{
  string w = "insert into checkmultiuser(username) values (@username)";
  c.Open();
  using (SqlCommand cmd = new SqlCommand(w, c))
  {
     cmd.Parameters.Add("@username", SqlDbType.VarChar);
     cmd.Parameters["@username"].Value = textBox1.Text;
     cmd.ExecuteNonQuery();
  }

}

to ensure the objects are correctly disposed of.

Could perhaps put it in a try{} block too, and close your connection in the finally{ c.Close();}