How to Call Stored Procedure in C#

The pre-compiled database objects known as stored procedures include one or more SQL statements. They are kept on the database server and may be used by a variety of applications to carry out particular operations or get information out of the database. A modular and structured approach to database operations may be provided by calling stored procedures from your C# programmer, which can also aid with performance and security. We will look into calling stored procedures in C# in these steps.

Establish a Database Connection:

You must connect to the database using the correct connection string before invoking a stored procedure. The credentials for the database server as well as other pertinent parameters are included in the connection string. The SqlConnection class from the System is available for usage .To build a connection object and establish the database connection, use the Data.SqlClient namespace.

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

Create a Command Object:

Create a command object for the stored procedure you wish to call when the database connection has been made. The command object, which is an instance of the SqlCommand class, enables you to define the connection object and name of the stored procedure.

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

Add Parameters (if any):

If your stored procedure requires parameters, you may add them using the Parameters property on the command object. The saved procedure can receive values from your C# program via parameters.

The parameter name, complete with the "@" sign, and the associated value must be specified. For each parameter that your stored process needs, repeat this step.

Execute the Stored Procedure:

Call the ExecuteNonQuery method of the command object to run the stored procedure. This approach is used to carry out actions like insert, update, and delete that don't produce any output.

Instead of using the Execute Reader function, you can utilize it if your stored procedure returns a result set. It gives you a SqlDataReader object so you may get the result set.

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

Close the Connection:

Close the database connection to free the resources after running the stored procedure and handling the outcomes (if appropriate). To terminate the connection, use the Close method on the connection object.

You may simply invoke stored procedures from your C# application by following these instructions. Using stored procedures may improve your database operations' security and speed as well as encourage code reuse and maintainability. To make sure your application runs well, remember to handle any exceptions that may arise along the process.

Handling Output Parameters:

Output parameters in stored procedures frequently return values to the caller code. Use the Parameters collection of the command object to get these values. Following the saved procedure's completion, the parameter name and Value property may be used to get access to the output parameter values.

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 can utilize transactions if your application needs a number of database activities to be carried out simultaneously. Transactions guarantee that either every operation is successful or that no operation is executed to the database at all. You can start a transaction, run the stored procedures included in it, and then, depending on the success or failure of the operations, either commit or rollback the transaction.

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 for stored procedures is an additional option to output parameters. An integer representing the stored procedure's execution state is the return value. After running the stored procedure, you can get the return value using the Return Value field of 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's crucial to deal with any exceptions that may arise when the stored procedure is being run. For the purpose of catching any database-related exceptions, enclose your code in a try-catch block. Additionally, you may look in the SqlException class for certain SQL Server issues and manage them appropriately.

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

Parameterized Queries:

Using parameterized queries enables the database server to optimize query execution, which both reduces the risk of SQL injection attacks and boosts speed. You may utilize parameter placeholders and provide them values using the Parameters collection instead of concatenating values into the SQL expression.

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

You may efficiently call stored procedures in C# and improve the efficiency and security of your database interactions by adhering to some extra advice and best practices.

Next Topic#

← prev next →