How To Call Stored Procedure In C#

Stored procedures, which are pre-compiled database elements containing SQL statements, are stored on the database server. They are utilized by multiple applications to execute specific tasks or retrieve data from the database. Invoking stored procedures from your C# codebase offers a structured and modular strategy for database operations, enhancing performance and security. The following steps will guide you through the process of calling stored procedures in C#.

Establish a Database Connection:

You need to establish a connection to the database by utilizing the appropriate connection string prior to executing a stored procedure. The connection string contains essential details such as the database server credentials and other relevant parameters. You can employ the SqlConnection class provided by the System for this purpose. Make sure to utilize the Data.SqlClient namespace to create a connection object and initiate the database connection.

using System.Data.SqlClient;
string connectionString = "YourConnectionString";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

Create a Command Object:

Instantiate a command object to execute the desired stored procedure once the database connection is established. This command object, belonging to the SqlCommand class, allows you to specify both the connection object and the stored procedure's name.

SqlCommand command = new SqlCommand("YourStoredProcedureName", connection);
command.CommandType = CommandType.StoredProcedure;

Add Parameters (if any):

If your stored procedure necessitates parameters, you can include them by utilizing the Parameters attribute on the command object. The stored procedure has the ability to obtain values from your C# application through these parameters.

command.Parameters.AddWithValue("@ParameterName", value);

Specify the parameter name along with the "@" symbol and its corresponding value. Repeat this process for each parameter required by your stored procedure.

Execute the Stored Procedure:

Invoke the ExecuteNonQuery function of the command object to execute the stored procedure. This method is commonly employed to perform operations such as insert, update, and delete, which do not return any results.

command.ExecuteNonQuery();

Instead of employing the Execute Reader function, you can make use of it when your stored procedure produces a result set. This function provides you with a SqlDataReader object, allowing you to retrieve the result set.

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    // Process the result set
}
reader.Close();

Close the Connection:

Terminate the database connection to release resources once the stored procedure has been executed and any results have been managed accordingly. To close the connection, invoke the Close method on the connection object.

connection.Close();

You can easily call stored procedures from your C# program by adhering to these guidelines. Leveraging stored procedures can enhance the security and efficiency of your database tasks, while also promoting code reusability and manageability. It is essential to proactively manage any potential exceptions that might occur during the execution to ensure smooth operation of your application.

Handling Output Parameters:

Retrieve values returned to the calling code by output parameters in stored procedures using the Parameters collection of the command object. Once the stored procedure has finished executing, you can access the output parameter values by referring to the parameter name and Value property.

SqlParameter outputParameter = new SqlParameter("@OutputParam", SqlDbType.NVarChar, 50);
outputParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParameter);
command.ExecuteNonQuery();
string outputValue = command.Parameters["@OutputParam"].Value.ToString();

Transaction Management:

You may employ transactions when your application requires multiple database tasks to be performed concurrently. Transactions ensure that all operations are either completed successfully or none are executed on the database. Initiating a transaction involves executing the stored procedures within it, followed by committing or rolling back the transaction based on the outcomes of the operations.

SqlTransaction transaction = connection.BeginTransaction();

try
{
    // Set the transaction for the command object
    command.Transaction = transaction;

    // Execute the stored procedure(s)

    // Commit the transaction if everything is successful
    transaction.Commit();
}
catch (Exception ex)
{
    // Rollback the transaction if any error occurs
    transaction.Rollback();
    // Handle the exception
}

Stored Procedure Return Value:

The return value in stored procedures serves as an alternative means to output parameters. It signifies the execution state of the stored procedure through an integer value. To access this return value post executing the stored procedure, you can retrieve it from the Return Value field within the command object.

SqlParameter returnValue = new SqlParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);

command.ExecuteNonQuery();

int storedProcedureReturnValue = Convert.ToInt32(command.Parameters["@ReturnValue"].Value);

Error Handling:

It is essential to handle any anomalies that might occur during the execution of the stored procedure. To effectively capture any exceptions related to the database, wrap your code within a try-catch block. Moreover, consider examining the SqlException class to address specific problems related to SQL Server accordingly.

try
{
    // Execute the stored procedure
}
catch (SqlException ex)
{
    // Handle SQL Server-related errors
}
catch (Exception ex)
{
    // Handle other exceptions
}

Parameterized Queries:

By implementing parameterized queries, the database server can enhance query execution efficiency, thereby minimizing the vulnerability to SQL injection attacks and improving performance. Instead of concatenating values directly into the SQL expression, you can employ parameter placeholders and assign values to them through the Parameters collection.

string query = "SELECT * FROM TableName WHERE ColumnName = @Value";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@Value", value);

You can effectively invoke stored procedures in C# to enhance the efficiency and security of your database operations by following additional recommendations and best practices.