CODEWITHSUNDEEP
c#sql
Iatrochemist
Iatrochemist

Reputation: 276

authentication using stored procedure

i have written a stored procedure for user authentication in SQL, how should i get the return value from this procedure later in my c#.Net code to enable the user to log in ? 

create procedure userLogin_proc
@userName nvarchar(45),
@passCode nvarchar(20)

as
begin

select userName,passCode from userLogin where username = @userName and passCode = @passCode
end

Upvotes: 0

Views: 2656

Answers (3)

Sourav 'Abhi' Mitra
Sourav 'Abhi' Mitra

Reputation: 2520

Do not store the plain text password in the database. Look up a hashing method.In my example below i use SHA256.

class User
{
    public string UserName { get; private set; }
    public string Password { get; private set; }
    public User(string userName, string plainTextPassword)
    {
        this.UserName = userName;
        this.Password = GetHash(plainTextPassword);
    }
    public string GetHash(string toHash)
    {
        return BitConverter.ToString(new SHA256Managed().ComputeHash(Encoding.UTF8.GetBytes(toHash))).Replace("-", string.Empty);
    }
    public void Save() { /* Save UserName and the Hashed Password to database */ }
    public bool ValidateLogin(string userNameEntered, string passwordEntered)
    {
        string userName; string password = string.Empty;
        string ConnectionString = "Your Connection String";
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            con.Open();
            string CommandText = "SELECT UserName, Password FROM userLogin WHERE Username = @UserName";
            using (SqlCommand cmd = new SqlCommand(CommandText))
            {
                cmd.Connection = con;
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = userNameEntered;
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    userName = rdr["UserName"].ToString();
                    password = rdr["Password"].ToString();
                }
            }
        }
        if (password.Equals(GetHash(passwordEntered))) return true;
        return false;
    }
}

This example shows a crude example of hashing as well as retrieving from the database.

Upvotes: 1

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

use output parameter to return userid, if login successful, user id will be returned, else 0 will be returned

create procedure userLogin_proc
@userName nvarchar(45),
@passCode nvarchar(20)
@UserId int=0 OUTPUT
as
begin

select @UserId =id from userLogin where username = @userName and passCode = @passCode
end

and on C# side do this after ExecuteReader:

SqlDataReader reader=cmd.ExecuteReader();
int UserId = (int)cmd.Parameters["@UserId"].Value;

you can read more about output parameters here:

http://www.codeproject.com/Questions/136351/How-to-retrieve-output-parameter-from-Store-proced

Upvotes: 3

Wim
Wim

Reputation: 12082

Change it to a SELECT COUNT(1) FROM userLogin.... and then use ExecuteScalar() on the SqlDataReader object.

As a side note, it's not a good idea to store your passwords in the DB in plain text, but hash them instead, preferably with a salt value.

Upvotes: 1

Related Questions