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

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 (@user开发者_JAVA技巧name)";

    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.

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)).

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();}

First of all, you should use ExecuteNonQuery to handle INSERTS, UPDATES and DELETE. ExecuteNonQuery is used when you are not expecting a value back from the database.

But if you actually want to use a DataReader in another situation, you should use it like shown below.

using (SqlDataReader reader= cmd.ExecuteReader())
{
        while (reader.Read())
        {
            //Do the reading
        }
 }

This ensures that the SqlDataReader gets closed, once the intended job is done.

Another potential fix it to set MARS (Multiple Active Result Sets) to true in your connection string.

You can try :

cmd.ExecuteReader(CommandBehavior.CloseConnection);