How to Pass Parameters to Stored Procedures in C#?

Efficient Data Manipulation with Stored Procedures in C#

C# offers robust database interaction capabilities, including data insertion, updates, and deletions. Stored procedures provide an efficient way to handle complex database operations. However, effectively passing parameters to these procedures can be a challenge. This guide clarifies the process.

The example provided (private void btnAdd_Click) illustrates a simple insertion. The complexity arises when invoking a stored procedure. The solution involves these steps:

1. SqlCommand Object Creation: Instantiate a SqlCommand object (assigned to cmd), specifying the stored procedure's name as the command text.
2. Command Type Specification: Set the CommandType property of cmd to CommandType.StoredProcedure to indicate execution of a stored procedure.
3. Parameter Addition: Utilize the cmd.Parameters collection to append parameters, ensuring name and data type consistency with the stored procedure's definition.
4. Stored Procedure Execution: Establish a database connection using a using statement for resource management, and execute the command with cmd.ExecuteNonQuery().

The following example demonstrates parameter passing to sp_Add_contact, which accepts @FirstName and @LastName 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();
    }
  }
}</code>

This approach, combined with the using statement for reliable resource cleanup, ensures successful parameter passing to stored procedures, enabling efficient data manipulation within your C# applications. Note the use of AddWithValue for simplified parameter addition.

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