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

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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
   }

}

Method 2

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();

Method 3

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.`
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0