How to Call a Stored Procedure with Parameters in C#?

Calling a stored procedure with parameters in C#

Question:

How do I call a stored procedure with parameters from my C# code? I can perform insert, update, and delete operations using command strings, but am not sure how to handle the stored procedures.

This is my current code which successfully inserts data using the command string:

<code class="language-csharp">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);
}</code>

Answer:

To call a stored procedure with parameters, you can follow these steps:

1. Create a SqlCommand object and set its CommandType property to CommandType.StoredProcedure.
2. Use the Parameters collection to add parameters to the SqlCommand object. Each parameter should specify its name, data type, and value.
3. Open the database connection and execute SqlCommand.

The following updated code demonstrates how to call the sp_Add_contact stored procedure with two parameters:

<code class="language-csharp">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.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);

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

    dt.Clear();
    da.Fill(dt);
}</code>

In order to improve the readability and maintainability of the code, it is recommended to use the AddWithValue method to add parameters, which will automatically infer the data type of the parameters. Please make sure your stored procedure sp_Add_contact actually exists and the parameter names are consistent with those in your code.

The above is the detailed content of How to Call a Stored Procedure with Parameters in C#?. For more information, please follow other related articles on the PHP Chinese website!