Print OUT parameter to c# winform

Question

I am developing winform app in c# language using 3 layer architecture. i need to print sql server error (@ErrorPrint) in winform message. i have tried several ways but i cannot get my resut. My code goes as... SQL SERVER PROCEDURE AS

 CREATE PROC [dbo].[sp_CheckLogin]
        @userName VARCHAR(20),
        @PassCode VARCHAR(20),
        @ErrorPrint varchar(200) OUT
        AS
        BEGIN   
            --SELECT COUNT(*) from UserTable where UserName=@userName and  @PassCode=CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('PharmaPro',PassCode))
            SELECT COUNT(*) FROM UserTable WHERE UserName=@userName AND @PassCode=CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('PharmaPro',PassCode)) AND IntruderLocked=0 and IsBlocked=0   
        END
        
        IF EXISTS ( SELECT * FROM UserTable WHERE IsBlocked=1 )
            BEGIN
                SET @ErrorPrint= 'Your account is blocked. Contact administrator'
            END
            
            
        IF NOT EXISTS (SELECT * FROM UserTable WHERE PassCode=@PassCode)
            BEGIN   
                UPDATE UserTable SET IntruderCapture=IntruderCapture+1      
            END 
        
        IF  EXISTS(SELECT * FROM  UserTable WHERE IntruderCapture>3)
            BEGIN
                UPDATE UserTable SET IntruderLocked=1
            END
            
        IF EXISTS (SELECT * FROM UserTable WHERE IntruderLocked=1)
            BEGIN
                SET @ErrorPrint='Intruder locked. Contact administrator'
            END

My Data logic layer goes like

  public string login_details(Login_Entity Users)
                {
                    SqlConnection connection = new SqlConnection(conn);
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("sp_CheckLogin", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@userName", Users.UserName_Details);           
                    cmd.Parameters.AddWithValue("@PassCode", Users.Password_Details);
                    cmd.Parameters.Add("@ErrorPrint", SqlDbType.Char, 500);
                    cmd.Parameters["@ErrorPrint"].Direction = ParameterDirection.Output;
                    string message = cmd.Parameters["@ErrorPrint"].Value.ToString();
                    cmd.ExecuteScalar().ToString();
                    connection.Close();
                    return message;
                }

similary presentation code goes like

private void button1_Click(object sender, EventArgs e) {

    if (txtUserName.Text == "" && txtPassword.Text == "")
    {
        lblError.Text = ("UserName and Password is Empty");
        txtUserName.Focus();
    }
    else if (txtUserName.Text == "")
    {
        lblError.Text = ("UserName is Empty");
        txtUserName.Focus();
    }
    else if (txtPassword.Text == "")
    {
        lblError.Text = ("Password is Empty");
        txtPassword.Focus();
    }
    else
    {                
        Login_Entity LE = new Login_Entity();
        Login_BL LB = new Login_BL();
        LE.UserName_Details = txtUserName.Text;               
        LE.Password_Details = txtPassword.Text;
        try
        {
            string _login = LB.Login_BLL(LE);
            int i = int.Parse(_login);
            if (i > 0)
            {
                Home hm = new Home();
                hm.Show();
                username = txtUserName.Text;
                hm.lblusername.Text = username;
                this.Hide();
            }
            else
            {
               
                lblError.Text = ("UserName/Pasword Error");
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            LB = null;
        }
    }
}

I need to print SP @ErrorPrint from WinForms

Dan Guzman Dan Guzman · Accepted Answer · 2020-09-04T09:22:40

Use ExecuteNonQuery instead of ExecuteScalar and retrieve the output parameter value after executing the query:

cmd.Parameters["@ErrorPrint"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string message = cmd.Parameters["@ErrorPrint"].Value.ToString();
connection.Close();
return message;

Avoid using the sp_ stored procedure name prefix as that denotes system stored procedures. See the documentation.

Print OUT parameter to c# winform

1 Answers