My Technical Notes

Tuesday, 19 February 2013

Calling a Stored Procedure in C#

For the following code to work, we need to add a reference to System.Configuration - if we want to retrieve the connection string from the application configuration file. It may be faster to edit the project file and add the following line:

<Reference Include="System.configuration" />
underneath the <ItemGroup> start tag. Using Statements:

using System.Data;
using System.Data.SqlClient;
using System.Configuration; // if connection string in config file 
Code:

// variables
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;
string sql = "dbo.spName"; // or a select statement
DataSet outputDS = new DataSet();

// code... 
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(sql, connection)
    {
        CommandType = CommandType.StoredProcedure // or CommandType.Text for SQL
    };

    // add an input parameter
    {
        SqlParameter parameter = command.CreateParameter();
        parameter.ParameterName = "inputParameterName";
        parameter.Value = 14;
        command.Parameters.Add(parameter);
    }

    connection.Open();

    SqlDataAdapter adapter = new SqlDataAdapter(command);
    adapter.Fill(outputDS);

    connection.Close();
}
Adding an output parameter of type DECIMAL(18, 2):

    // second parameter is an output parameter
    {
        SqlParameter parameter = command.CreateParameter();
        parameter.ParameterName = "outputParamName";
        parameter.DbType = DbType.Decimal;

        // decimal output parameter therefore we need to set the size to 9
        // and give a scale and precision DECIMAL(18, 2) => DECIMAL(Precision, Scale);
        parameter.Size = 9;
        parameter.Scale = 2;
        parameter.Precision = 18;

        parameter.Direction = ParameterDirection.Output;
        command.Parameters.Add(parameter);
        outputParam = parameter;
    }

No comments: