Use SQL Server Stored procedure output in asp.net c#

Question

I'm new to working with stored procedures.

We have an existing system which uses stored procedures that check usernames and url paths. The Stored procedure will check whether user details exist. If it exists it returns 1 if not it returns 0.

I am trying to write asp.net code to call this stored procedure by providing it with the user details and path and then use the returned 0 or 1 value in asp.net.

You should check http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/ Happy Coding!!! — Ravi Vanapalli, Jul 02 '12 at 11:45

score 8 · Accepted Answer · edited May 30 '14 at 14:37

Looks like you need stored procedure with output parameter

int errorId = 0;

using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    using(SqlCommand cmd = new SqlCommand("YourStoredProcedureName", sqlConnection))
    {
    cmd.CommandType=CommandType.StoredProcedure;
    SqlParameter parm=new SqlParameter("@username", SqlDbType.VarChar); 
    parm.Value="mshiyam";
    parm.Direction =ParameterDirection.Input ; 
    cmd.Parameters.Add(parm); 

    SqlParameter parm2=new SqlParameter("@path",SqlDbType.VarChar); 
    parm2.value = "Some Path";
    parm2.Direction=ParameterDirection.Output;
    cmd.Parameters.Add(parm2); 


    SqlParameter parm3 = new SqlParameter("@errorId",SqlDbType.Int);
    parm3.Direction=ParameterDirection.Output; 
    cmd.Parameters.Add(parm3); 

    sqlConnection.Open(); 
    sqlConnection.ExecuteNonQuery();

    errorId = cmd.Parameters["@errorId"].Value; //This will 1 or 0
   }

}

Great that you're putting your `SqlConnection` into a `using()....` block - but you should also do the same for your `SqlCommand` ! — marc_s, Jul 02 '12 at 12:12
Sorry @marc_s typed it far too quickly.... I will update the post now — HatSoft, Jul 02 '12 at 12:18

score 1 · Answer 2 · edited Jul 02 '12 at 12:12

Use the following code,

SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn);
cmd.CommandType=CommandType.StoredProcedure;
SqlParameter parm=new SqlParameter("@username",SqlDbType.VarChar);
parm.Value=strUser;
parm.Direction =ParameterDirection.Input ; 
cmd.Parameters.Add(parm); 
parm=new SqlParameter("@url",SqlDbType.VarChar);
parm.Value=strUrl;
parm.Direction =ParameterDirection.Input ; 
cmd.Parameters.Add(parm); 
parm=new SqlParameter("@errorID",SqlDbType.Int); 
parm.Direction=ParameterDirection.Output; // This is important!
cmd.Parameters.Add(parm); 
cn.Open(); 
cmd.ExecuteNonQuery();
cn.Close(); 

// Print the output value
Console.WriteLine(cmd.Parameters["@errorID"].Value); 
Console.ReadLine();

score 0 · Answer 3 · answered Jul 02 '12 at 13:20

int errorId = 0;

SqlCommand cmd = new SqlCommand("YourSPName", cn);
cmd.CommandType=CommandType.StoredProcedure;
SqlParameter parm=new SqlParameter("@username",SqlDbType.VarChar);
parm.Value=strUser;
parm.Direction =ParameterDirection.Input ; 
cmd.Parameters.Add(parm); 
parm=new SqlParameter("@url",SqlDbType.VarChar);
parm.Value=strUrl;
parm.Direction =ParameterDirection.Input ; 
cmd.Parameters.Add(parm); 
parm=new SqlParameter("@errorID",SqlDbType.Int); 
parm.Direction=ParameterDirection.Output; // This is important!
cmd.Parameters.Add(parm); 
cn.Open(); 
errorId = (int)cmd.ExecuteNonQuery();
cn.Close(); 

sqlConnection.Open(); 
sqlConnection.ExecuteNonQuery();

Put Conditions:

if(errorId == 1)
{
   `"Allow User here"`
}
if(errorId==0)
{
   `Redirect when you to redirect to use.`
}

Use SQL Server Stored procedure output in asp.net c#

3 Answers3

Linked