Stored Procedure DAL

Question

How to create a common DAL layer method for stored procedure which has output parameters.

When stored procedure has output parameters, you will not be able to retrieve the results in dataset or data reader as collection. Please tell me which is the best way to have a common method to accept any input and output parameters and return values from output parameters.

score 1 · Accepted Answer · answered Dec 29 '11 at 07:53

1

This Link : https://web.archive.org/web/20211020111631/https://www.4guysfromrolla.com/articles/070203-1.aspx Contains all the Information From where to download the SqlHelper and how to use and implement in your project..

Which might help you

answered Dec 29 '11 at 07:53

Pranay Rana

175,020
35
237
263

score 1 · Answer 2 · answered Dec 29 '11 at 07:53

Something like that:

public static void DalWrapper(int id, string str, out int resultid, out string resultstring)
{
  * call the SP passing id and str
  * assign resulting values to resultid and resultstring
}

Depending on needs out can be changed to ref

score 1 · Answer 3 · edited Dec 29 '11 at 07:56

You can use ExecuteNonQuery and the get the value of the parameter

using (SqlConnection conn = new SqlConnection())    
{
    SqlCommand cmd = new SqlCommand("sp", conn);   cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@param", SqlDbType.Int, int.MaxValue, ParameterDirection.Output));

    conn.Open();
    cmd.ExecuteNonQuery();
    int id = cmd.Parameters["@param"].Value;

    conn.Close();    
 }

Stored Procedure DAL

3 Answers3