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

C# Stored Procedure Execution with Parameters: A Comprehensive Guide

Stored procedures provide a robust and efficient method for performing database operations like insertions, updates, and deletions within your C# applications. This approach offers performance advantages and enhanced modularity over directly executing SQL commands. This tutorial demonstrates how to call a stored procedure that accepts parameters using C#.

Understanding the Stored Procedure

Let's assume you've already defined a stored procedure, sp_Add_Contact, which accepts two parameters: @FirstName and @LastName. This procedure inserts new contact records into your database.

Establishing the Database Connection

Begin by creating a SqlConnection object to establish a connection to your database. This connection will be utilized throughout the process.

<code class="language-csharp">using (SqlConnection con = new SqlConnection(dc.Con)) {
    // Database operations will be performed within this block
}</code>

Preparing the SqlCommand Object

Next, instantiate a SqlCommand object to represent the sp_Add_Contact stored procedure. Crucially, set the CommandType property to StoredProcedure to indicate that you're working with a stored procedure, not a direct SQL query.

<code class="language-csharp">using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
    cmd.CommandType = CommandType.StoredProcedure;
    // Parameter additions and execution will occur here
}</code>

Adding Parameters to the Command

Add the input parameters to the cmd object using the Parameters.Add method. Specify the parameter name, data type (SqlDbType), and assign the value from your application's user interface controls.

<code class="language-csharp">cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;</code>

Executing the Stored Procedure

With parameters defined, the stored procedure is ready for execution. Use the ExecuteNonQuery method to send the command to the database and apply the changes.

<code class="language-csharp">con.Open();
cmd.ExecuteNonQuery();
con.Close();</code>

Refreshing Data After Execution

After successfully executing the stored procedure, you might need to refresh the data displayed in your application. Use Clear and Fill operations on your DataTable to reflect the updated database state.

<code class="language-csharp">dt.Clear();
da.Fill(dt);</code>

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