Tuesday, July 23, 2013

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

Stored Procedure Creation with output variable :

Create Procedure NewUserp
(
@UserName varchar(50),
@Password varchar(20),
@ret int out
)
AS
BEGIN

if NOT EXISTS(select email_id from UserCredentials where email_id=@UserName)
BEGIN
insert into UserCredentials values(@UserName,@Password)
SET @ret=1;
END
else
BEGIN
SET @ret=0;
END

END

Access of Output Variable in C#

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