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.

Solution 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
   }

}

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

Comments

[marc_s]

Great that you're putting your SqlConnection into a using().... block - but you should also do the same for your SqlCommand !

[HatSoft]

Sorry @marc_s typed it far too quickly.... I will update the post now

[Maury Markowitz]

Don't you mean cmd.ExecuteNonQuery();`?