how to use the return value of stored procedure in code to verify

Question

Here is my SQL Server stored procedure to verify user credentials.

ALTER procedure [dbo].[ValidateUserCredentials]
   @Username varchar(50),
   @Password varchar(50),
   @result varchar(50) out
AS
BEGIN
   IF EXISTS(select Username, Password 
             from Users
             where Username = @Username
               AND Password = @Password)
   BEGIN
      set @result = 1
   END
   ELSE
   BEGIN
      set @result = 0
   END

END

Here is my method in reuse class.

public bool executeInsertprocedure(string spName, SqlParameter[] sqlParameters, out string message)
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = spName;
    cmd.Parameters.AddRange(sqlParameters);

    conn.Open();

    try
    {
       cmd.ExecuteNonQuery();
       message = sqlParameters[sqlParameters.Length - 1].Value.ToString();
       return true;
    }
    catch
    {
       message = "Something is wrong!";
       return false;
    }
}

Here I am calling it in my BLL.

public void verifyUserCredentials(string userName, string password,string message,out string sp_message)
{
   SqlParameter[] param = new SqlParameter[3];
   param[0] = new SqlParameter("@UserName", SqlDbType.VarChar);
   param[0].Value = userName;

   param[1] = new SqlParameter("@Password", SqlDbType.VarChar);
   param[1].Value = password;

   param[2] = new SqlParameter("@result", SqlDbType.VarChar);
   param[2].Direction = ParameterDirection.Output;
   //param[2].Value = sp_message;

   objRuse = new Reuse();
   objRuse.executeInsertprocedure("ValidateUserCredentials", param, ??);
}

and here is my login page.

protected void btnSignin_Click(object sender, EventArgs e)
{
       objBll = new bll();
       objBll.verifyUserCredentials(txtSignInUsername.Text,txtSignInPassword.Text, ??);

       if(?? == true)
       {
            Session["Username"]=txtSignInUsername;
            Response.Redirect("Default.aspx");
        }
        else
        {
            Response.Redirect("Login.aspx");
        }

    }

What I should put in ?? places in my code. How do I get the returned value of stored procedure.

[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! If you return a `0` or `1` - you should use a `BIT` or `TINYINT` - but most certainly **not** a `Varchar(50)` ! — marc_s, Jun 22 '14 at 20:02
Try replacing `sqlParameters` with `cmd`: `cmd[sqlParameters.Length - 1].Value.ToString()` - taken from: http://stackoverflow.com/questions/23712034/returning-sql-server-output-parameter-to-c-sharp-by-stored-procedure — Adam Wenger, Jun 22 '14 at 20:12
You shouldn't store passwords in plain text (with few exceptions) — Martin Smith, Jun 22 '14 at 22:13

Rahul · Answer 1 · 2014-06-23T14:43:46.617

2

Change your procedure to have @result int out or @result bit out instead of varchar(50)

Change method executeInsertprocedure

public bool executeInsertprocedure(string spName, SqlParameter[] sqlParameters, out int message)
{ 
   try
    {
       cmd.ExecuteNonQuery();
       message = cmd.Parameters["@result"].Value;       
       (message == 1) ? return true : return false;
    }
}

your verifyUserCredentials method

public void verifyUserCredentials(string userName, string password,string message,out int sp_message)
{
   objRuse = new Reuse();
   objRuse.executeInsertprocedure("ValidateUserCredentials", param, sp_message);
}

Your btnSignin_Click event

protected void btnSignin_Click(object sender, EventArgs e)
{
 int errorid;
 objBll = new bll();
 if(objBll.verifyUserCredentials(txtSignInUsername.Text,txtSignInPassword.Text, out errorid))
       {          
            Session["Username"]=txtSignInUsername;
            Response.Redirect("Default.aspx");
        }
       else 
         {
           //Else part code
         }
}

edited Jun 23 '14 at 14:43

answered Jun 22 '14 at 20:51

Rahul

76,197
13
71
125

it didn't work! executeInsertProcedure occuring error. plus i didn't get the button_Click logic! – user3765397 Jun 23 '14 at 13:46
@user3765397, mind posting the error as well? method `verifyUserCredentials` returns bool. So in button_click you are calling it and checking it's return type in if condition. – Rahul Jun 23 '14 at 13:49
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'sqlParameters' and no extension method 'sqlParameters' accepting a first argument of type 'System.Data.SqlClient.SqlCommand' could be found (are you missing a using directive or an assembly reference?) – user3765397 Jun 23 '14 at 14:05
in btn_Click event where would b the else condition if user didn't logged in? – user3765397 Jun 23 '14 at 14:05
@user3765397, check edited answer. There was a small typo present and so you were getting that error. It should be `cmd.Parameters[...` instead of `cmd.SqlParameters[...` – Rahul Jun 23 '14 at 14:47
@user3765397, don't forget to accept the answer if it helped by clicking the check button under voting button. – Rahul Jun 23 '14 at 16:41
after changing it to smd.Parameteers[].... it is showing me this error! Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?) – user3765397 Jun 23 '14 at 18:41
@user3765397, read the answer carefully; I did mentioned that you need to change the procedure as well, making the out parameter datatype to `int`. My answer gives you detail explanation and from here you can easily make out the rest. You certainly can't expect me to fix all your code error ... right? Just debug your code putting a break point if there is any more error. Hope this make sense to you. – Rahul Jun 23 '14 at 18:46

how to use the return value of stored procedure in code to verify

1 Answers1