FrankSharp FrankSharp - 3 months ago 31
ASP.NET (C#) Question

Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database.

This a button insert I make work well.

private void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(dc.Con);
SqlCommand cmd = new SqlCommand("Command String", con);

da.InsertCommand = new SqlCommand("INSERT INTO tblContacts VALUES (@FirstName, @LastName)", con);
da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

con.Open();
da.InsertCommand.ExecuteNonQuery();
con.Close();

dt.Clear();
da.Fill(dt);
}


This is the start of the button to call the procedure named
sp_Add_contact
to add a contact. The two parameters for
sp_Add_contact(@FirstName,@LastName)
. I searched on google for some good example but I found nothing interesting.

private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(dc.Con);
SqlCommand cmd = new SqlCommand("Command String", con);
cmd.CommandType = CommandType.StoredProcedure;

???

con.Open();
da. ???.ExecuteNonQuery();
con.Close();

dt.Clear();
da.Fill(dt);
}

Answer

It's pretty much the same as running a query. In your original code you are creating a command object, putting it in the cmd variable, and never use it. Here, however, you will use that instead of da.InsertCommand.

Also, use a using for all disposable objects, so that you are sure that they are disposed properly:

private void button1_Click(object sender, EventArgs e) {
  using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

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