How to pass parameter to store procedure in C#

This example shows you how to pass parameter to store procedure in the best way? Highlighted function fetches the parameter list from store procedure and GetParameters() function map the supplied parameters with fetch parameters.
One thing to remember, don't ignore the parameter sequence when supplying. It's same as a store procedure sequence.

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

public class SqlHelper

{

    #region ExecuteDataset Without Parameter

    public DataSet ExecuteDataset(string commandText)

    {

        string connectionString = GetConnectionString();

        SqlConnection sqlConnection = new SqlConnection(connectionString);

        return ExecuteDataset(sqlConnection, CommandType.StoredProcedure, commandText);

    }

    #endregion

    #region ExecuteDataset With Parameter

    public DataSet ExecuteDataset(string commandText, params object[] parameters)

    {

        string connectionString = GetConnectionString();

        SqlConnection sqlConnection = new SqlConnection(connectionString);

        SqlParameter[] sqlParameter = GetParameters(sqlConnection, commandText, parameters);

        return ExecuteDataset(sqlConnection, CommandType.StoredProcedure, commandText, sqlParameter);

    }

    #endregion

    #region ExecuteNonQuery Without Parameter

    public int ExecuteNonQuery(string commandText)

    {

        string connectionString = GetConnectionString();

        SqlConnection sqlConnection = new SqlConnection(connectionString);

        SqlParameter[] sqlParameter = GetParameters(sqlConnection, commandText);

        return ExecuteNonQuery(sqlConnection, CommandType.StoredProcedure, commandText, sqlParameter);

    }

    #endregion

    #region ExecuteNonQuery With Parameter

    public int ExecuteNonQuery(string commandText, params object[] parameters)

    {

        string connectionString = GetConnectionString();

        SqlConnection sqlConnection = new SqlConnection(connectionString);

        SqlParameter[] sqlParameter = GetParameters(sqlConnection, commandText, parameters);

        return ExecuteNonQuery(sqlConnection, CommandType.StoredProcedure, commandText, sqlParameter);

    }

    #endregion

    #region ExecuteReader Without Parameter

    public SqlDataReader ExecuteReader(string commandText)

    {

        string connectionString = GetConnectionString();

        SqlConnection sqlConnection = new SqlConnection(connectionString);

        SqlParameter[] sqlParameter = GetParameters(sqlConnection, commandText);

        return ExecuteReader(sqlConnection, CommandType.StoredProcedure, commandText, sqlParameter);

    }

    #endregion

    #region ExecuteReader With Parameter

    public SqlDataReader ExecuteReader(string commandText, params object[] parameters)

    {

        string connectionString = GetConnectionString();

        SqlConnection sqlConnection = new SqlConnection(connectionString);

        SqlParameter[] sqlParameter = GetParameters(sqlConnection, commandText, parameters);

        return ExecuteReader(sqlConnection, CommandType.StoredProcedure, commandText, sqlParameter);

    }

    #endregion

    #region Connection String

    private string GetConnectionString()

    {

        string connection = "Data Source=192.168.1.1;Initial Catalog=Test;Integrated Security=false;User Id=sa;Password=admin@123;";

        return connection;

    }

    #endregion

    #region Common Function

    public int ExecuteNonQuery(SqlConnection sqlConnection, CommandType commandType, string commandText, params SqlParameter[] sqlParameter)

    {

        if (sqlConnection == null) throw new ArgumentNullException("sqlConnection");

        using (SqlCommand sqlCommand = new SqlCommand())

        {

            PrepareCommand(sqlConnection, sqlCommand, commandType, commandText, sqlParameter);

            try

            {

                return sqlCommand.ExecuteNonQuery();

            }

            catch (Exception) { throw; }

            finally { sqlConnection.Close(); }

        }

    }

    private DataSet ExecuteDataset(SqlConnection sqlConnection, CommandType commandType, string commandText, params SqlParameter[] sqlParameter)

    {

        DataSet dataSet = new DataSet();

        if (sqlConnection == null) throw new ArgumentNullException("sqlConnection");

        using (SqlCommand sqlCommand = new SqlCommand())

        {

            PrepareCommand(sqlConnection, sqlCommand, commandType, commandText, sqlParameter);

            using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))

            {

                try

                {

                    sqlDataAdapter.Fill(dataSet);

                }

                catch (Exception) { throw; }

                finally { sqlConnection.Close(); }

            }

        }

        return dataSet;

    }

    public SqlDataReader ExecuteReader(SqlConnection sqlConnection, CommandType commandType, string commandText, params SqlParameter[] sqlParameter)

    {

        if (sqlConnection == null) throw new ArgumentNullException("sqlConnection");

        using (SqlCommand sqlCommand = new SqlCommand())

        {

            PrepareCommand(sqlConnection, sqlCommand, commandType, commandText, sqlParameter);

            try

            {

                return sqlCommand.ExecuteReader();

            }

            catch (Exception) { sqlConnection.Close(); throw; }

        }

    }

    private SqlParameter[] GetParameters(SqlConnection sqlConnection, string spName, params object[] parameters)

    {

        SqlParameter[] sqlParameters = new SqlParameter[0];

        try

        {

            sqlParameters = DeriveParameters(sqlConnection, spName, false);

            List<SqlParameter> items = new List<SqlParameter>();

            int index = 0;

            foreach (SqlParameter sqlParameter in sqlParameters)

            {

                items.Add(new SqlParameter(sqlParameter.ParameterName, parameters[index++]));

            }

            sqlParameters = items.ToArray();

        }

        catch (IndexOutOfRangeException ex)

        {

            throw new Exception($"Procedure or function {spName} has too many or less arguments specified.", ex);

        }

        catch (Exception)

        {

            throw;

        }

        return sqlParameters;

    }

    private SqlParameter[] DeriveParameters(SqlConnection sqlConnection, string spName, bool includeReturnValueParameter)

    {

        if (sqlConnection == null) throw new ArgumentNullException("sqlConnection");

        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        SqlCommand sqlCommand = new SqlCommand(spName, sqlConnection);

        sqlCommand.CommandType = CommandType.StoredProcedure;

        sqlConnection.Open();

        SqlCommandBuilder.DeriveParameters(sqlCommand);

        sqlConnection.Close();

        if (!includeReturnValueParameter)

        {

            sqlCommand.Parameters.RemoveAt(0);

        }

        SqlParameter[] discoveredParameters = new SqlParameter[sqlCommand.Parameters.Count];

        sqlCommand.Parameters.CopyTo(discoveredParameters, 0);

        foreach (SqlParameter discoveredParameter in discoveredParameters)

        {

            discoveredParameter.Value = DBNull.Value;

        }

        return discoveredParameters;

    }

    private void PrepareCommand(SqlConnection sqlConnection, SqlCommand sqlCommand, CommandType commandType, string commandText, SqlParameter[] sqlParameter)

    {

        if (sqlCommand == null) throw new ArgumentNullException("sqlCommand");

        if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");

        sqlConnection.Open();

        sqlCommand.Connection = sqlConnection;

        sqlCommand.CommandText = commandText;

        sqlCommand.CommandType = commandType;

        if (sqlParameter != null && sqlParameter.Length > 0)

        {

            AttachParameters(sqlCommand, sqlParameter);

        }

    }

    private void AttachParameters(SqlCommand sqlCommand, SqlParameter[] sqlParameters)

    {

        if (sqlCommand == null) throw new ArgumentNullException("sqlCommand");

        if (sqlParameters != null)

        {

            foreach (SqlParameter sqlParameter in sqlParameters)

            {

                if (sqlParameter != null)

                {

                    if ((sqlParameter.Direction == ParameterDirection.InputOutput ||

                        sqlParameter.Direction == ParameterDirection.Input) &&

                        (sqlParameter.Value == null))

                    {

                        sqlParameter.Value = DBNull.Value;

                    }

                    sqlCommand.Parameters.Add(sqlParameter);

                }

            }

        }

    }

    #endregion

}

class Program

{

    static void Main(string[] args)

    {

        try

        {

            SqlHelper sqlHelper = new SqlHelper();

            DataTable dataTable = new DataTable();

            dataTable.Columns.Add("Column1", typeof(string));

            dataTable.Columns.Add("Column1", typeof(string));

            DataRow dataRow = dataTable.NewRow();

            dataRow["Column1"] = "1";

            dataRow["Column2"] = "Ram";

            dataTable.Rows.Add(dataRow);

            //Insert Data

            int resultCount = sqlHelper.ExecuteNonQuery("usp_WithParamter", "Param1", dataTable);

            //Read Data in DataSet

            DataSet dsWithoutParamter = sqlHelper.ExecuteDataset("usp_WithoutParamter");

            //Read Data in SqlDataReader

            SqlDataReader sqlDataReader = sqlHelper.ExecuteReader("usp_WithoutParamter");

            while (sqlDataReader.Read())

            {

                string Param1 = Convert.ToString(sqlDataReader["Param1"]);

                string Param2 = Convert.ToString(sqlDataReader["Param2"]);

            }

        }

        catch (Exception exception)

        {

        }

    }

}

Scripts

CREATE TYPE tt_DemoTableType

AS TABLE

(

        Param1 VARCHAR(10)

       ,Param2 VARCHAR(10)

)

CREATE PROC usp_WithParamter

 @Param1 VARCHAR(10)

,@Param2 tt_DemoTableType READONLY

AS

BEGIN

       SELECT * INTO TestTable FROM @Param2

END

CREATE PROC usp_WithoutParamter

AS

BEGIN

       SELECT * FROM TestTable

END

Note:- 

1. You can modified the functionality as per your requirements.

2. Highlighted function is important part to fetch Parameter from Store Procedure Or Function.