Sunday, July 21, 2013

Access output variable of stored procedure with Select Command in C#

Stored Procedure Creation with output variable :

alter procedure checkLoginp
(
@username varchar(50),
@Password varchar(20),
@retVal int out
)
AS
BEGIN
if EXISTS(select email_id,Password from UserCredentials where email_id=@username and Password=@Password)
BEGIN
Set @retVal=1
END
ELSE
BEGIN
set @retVal=0
END
END

Access of Output Variable in C#

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public int checkLogin(string Username, string Password)
        {
            int returnValue = -1;
            string str = ConfigurationManager.ConnectionStrings["ConnectForum"].ConnectionString;
            SqlConnection conn = new SqlConnection(str);
            if (conn.State == ConnectionState.Closed)
            {
               
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText ="checkLoginp";
                cmd.Parameters.AddWithValue("@username", Username);
                cmd.Parameters.AddWithValue("@Password", Password);
                SqlParameter param = cmd.Parameters.Add("@retVal", SqlDbType.Int);
                param.Direction = ParameterDirection.Output;
                conn.Open();
                cmd.ExecuteNonQuery();
                
                    returnValue = Convert.ToInt32(cmd.Parameters["@retVal"].Value);
              
                cmd.Dispose();
                conn.Close();
            }

            return returnValue;
        }