stored procedure use in login Sent Me Right Code

Guest User

ALTER PROCEDURE [dbo].[UIPKGUSER_spVerifyCredentials]
    @inUsername varchar(100),
    @inPassword varchar(100),
    @outUserCount int output,
    @outRoleName varchar(100) output
AS
BEGIN
    SET NOCOUNT ON;
    
    select @outUserCount = COUNT(*)
    from tbluser
    where username = @inUsername
        and password = @inPassword;
    
    if (@outUserCount > 0)
    begin
        select @outRoleName = rolename
        from tbluser
        where username = @inUsername
            and password = @inPassword;
    end
    else
        SET @outRoleName = '';

END

HERE IS CONNECTION string :-

DatabaseHelper.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString;

SEND ME LOGIN Right Code

This is Table

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
Accepted answer
Why are you using datatable with output parameters? You can simply do the following:
if (UserCount > 0)
{
    DashBoard dash = new DashBoard();
    dash.Show();
    this.Hide();
}
else
{
    bunifuSnackbar1.Show(this, "Invalid User id Password", Bunifu.UI.WinForms.BunifuSnackbar.MessageTypes.Error);
    return;
}
If you want to use datatable then you need to modify your storeprocedure because in query you are setting the value of output parameters instead of select their values.
Guest User4y ago
Thank You For All Accept Answer
Guest User4y ago
string RoleName = Convert.ToString(cmd.Parameters["@outRoleName"].Value); \\ how to this Role Use for admin and dutyincharge
Guest User4y ago
DashBoard Code Form Load Events

if(DatabaseHelper.type == "A")
{
Btn_Dashboard.Enabled = true;
Btn_Config.Enabled = true;
Btn_Report.Enabled = true;
Btn_Adjustment.Enabled = true;
Btn_Attendance.Enabled = true;
btnAbout.Enabled = true;
}
else if(DatabaseHelper.type == "U")
{
Btn_Dashboard.Enabled = false;
Btn_Config.Enabled = false;
Btn_Report.Enabled = true;
Btn_Adjustment.Enabled = true;
Btn_Attendance.Enabled = true;
btnAbout.Enabled = true;
}
Guest User4y ago
How To Use Role Name

string RoleName = Convert.ToString(cmd.Parameters["@outRoleName"].Value);

this is right code or worng code...

if (RoleName[3].ToString() == "ADMINISTRATOR")
{
DatabaseHelper.type = "A";
}
else if (RoleName[3].ToString() == "DUTYINCHARGE")
{
DatabaseHelper.type = "U";
}
Guest User4y ago
private void btnlogin_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(DatabaseHelper.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString);
SqlCommand cmd = new SqlCommand("UIPKGUSER_spVerifyCredentials", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@inUsername", txt_Username.Text);
cmd.Parameters.AddWithValue("@inPassword", txt_Password.Text);
cmd.Parameters.Add("@outUserCount", SqlDbType.Int);
cmd.Parameters["@outUserCount"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@outRoleName", SqlDbType.VarChar, 100);
cmd.Parameters["@outRoleName"].Direction = ParameterDirection.Output;
try
{
connection.Open();
cmd.ExecuteNonQuery();
int UserCount = Convert.ToInt32(cmd.Parameters["@outUserCount"].Value);
string RoleName = Convert.ToString(cmd.Parameters["@outRoleName"].Value);
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
DashBoard dash = new DashBoard();
dash.Show();
this.Hide();
}
else
{
bunifuSnackbar1.Show(this, "Invalid User id Password", Bunifu.UI.WinForms.BunifuSnackbar.MessageTypes.Error);
return;
}
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex.Message);
}
finally
{
connection.Close();
}
}

this code not work for me Help
Muhammad Imran Ansari4y ago
Check the below C# code to get the values:
SqlConnection sqlConn = new SqlConnection("YourConnectionString");
SqlCommand cmd = new SqlCommand("UIPKGUSER_spVerifyCredentials", sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@inUsername", "admin");
cmd.Parameters.AddWithValue("@inPassword", "admin");

cmd.Parameters.Add("@outUserCount", SqlDbType.Int);
cmd.Parameters["@outUserCount"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@outRoleName", SqlDbType.VarChar, 100);
cmd.Parameters["@outRoleName"].Direction = ParameterDirection.Output;

try
{
    sqlConn.Open();
    cmd.ExecuteNonQuery();
    int UserCount = Convert.ToInt32(cmd.Parameters["@outUserCount"].Value);
    string RoleName = Convert.ToString(cmd.Parameters["@outRoleName"].Value);
}
catch (Exception ex)
{
    // throw the exception
}
finally
{
    sqlConn.Close();
}
Amit Mohanty4y ago
c# code:
public DataTable CheckUser(USER_ID, PASSWORD)
{
    DataTable dt_Dtls = new DataTable();
    SqlConnection con = new SqlConnection("your connection string");
    SqlCommand command = new SqlCommand("LOGINUSER", con);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@USERID", USER_ID);
    command.Parameters.AddWithValue("@PASSWD", PASSWORD);
    if (con.State == ConnectionState.Open)
    {
        SqlDataAdapter da = new SqlDataAdapter(command);
        da.Fill(dt_Dtls);
        con.Close();
    }
    else
    {
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(command);
        da.Fill(dt_Dtls);
        con.Close();
    }
    return dt_Dtls;
}

public void Login()
{
    if (txtUserId.Text == string.Empty)
    {
        lblMsg.Text = "PLEASE ENTER USER ID !!!";
        txtUserId.Select();
    }
    else if (txtPassword.Text == string.Empty)
    {
        lblMsg.Text = "PLEASE ENTER PASSWORD !!!";
        txtPassword.Select();
    }
    else
    {
        DataTable dt_Chk = CheckUser(txtUserId.Text, txtPassword.Text);
        if (dt_Chk.Rows.Count > 0)
        {
            int UserExists, UserId;
            UserExists = int.Parse(dt_Chk.Rows[0]["USER_CHK"].ToString());
            UserId = int.Parse(dt_Chk.Rows[0]["USER_ID"].ToString());
            if (UserExists == 0)
            {
                txtPassword.Text = "";
                txtUserId.Select();
                lblMsg.Text = dt_Chk.Rows[0]["USER_MSG"].ToString();
            }
            else
            {
                if (UserId > 0)
                {
                    frmMaster frm = new frmMaster();
                    this.Hide();
                    frm.Show();
                }
                else
                {
                    txtPassword.Text = "";
                    txtPassword.Select();
                    lblMsg.Text = dt_Chk.Rows[0]["USER_MSG"].ToString();
                }
            }
        }
        else
        {
            lblMsg.Text = "INVALID USER ID !!!";
        }
    }
}
Guest User4y ago
c# login code need
Amit Mohanty4y ago
May below code will be helpful for you.
CREATE PROCEDURE [dbo].[LOGINUSER]
(
    @USERID NVARCHAR(50),
    @PASSWD NVARCHAR(50)
)
AS
BEGIN
    DECLARE @TB TABLE
    (
        USER_CHK INT,
        USER_MSG NVARCHAR(200),
        USER_ID INT,
        USERNAME NVARCHAR(100)
    )
    -- COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS FOR CHECKING CASE SENSITIVE
    -- 0 FOR INVALID USER ID, 1 FOR VALID USER ID
    DECLARE @USER_CHK INT = 0
    DECLARE @USER_ID INT = 0
    DECLARE @USERNAME NVARCHAR(100)
    IF EXISTS (SELECT * FROM USER_DETAILS WHERE LOGIN_ID = @USERID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS)
    BEGIN
        SET @USER_CHK = 1
        IF EXISTS (SELECT * FROM USER_DETAILS WHERE LOGIN_ID = @USERID AND PASSWORD = @PASSWD COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS)
        BEGIN
            SET @USER_ID = (SELECT USER_ID FROM USER_DETAILS WHERE LOGIN_ID = @USERID AND PASSWORD = @PASSWD)
            SET @USERNAME = (SELECT USER_NAME FROM USER_DETAILS WHERE LOGIN_ID = @USERID AND PASSWORD = @PASSWD)
            INSERT INTO @TB VALUES(@USER_CHK, 'VALID USER !!!', @USER_ID, @USERNAME)
        END
        ELSE
        BEGIN
            INSERT INTO @TB VALUES(@USER_CHK, 'INVALID PASSWORD !!!', @USER_ID, @USERNAME)
        END
    END
    ELSE
    BEGIN
        SET @USER_CHK=0
        INSERT INTO @TB VALUES(@USER_CHK, 'INVALID USER ID !!!', @USER_ID, @USERNAME)
    END
    SELECT USER_CHK, USER_MSG, USER_ID, USERNAME FROM @TB
END
Gajendra Jangid4y ago
1. ALTER PROCEDURE [dbo].[UIPKGUSER_spVerifyCredentials]
2. @inUsername varchar(100),
3. @inPassword varchar(100)
4. AS
5. BEGIN
6. SET NOCOUNT ON;
8. select rolename from tbluser
9. where username = @inUsername
10. and password = @inPassword;
11. END

Muhammad Imran Ansari4y ago

What is your question? You want to get Output parameter in C# code?