Simple Login using asp.net with c# using stored procedure

Question

bankerrajendra 0 Newbie Poster

18 Years Ago

Hi all!!!,
Happy Friendship day

So, please provide me help in the codding of Simple Login Page using asp.net(c#) with stored procedure.

Thanx,
With regds,
Rajendra Banker

.net:c#

5 Contributors
5 Replies
3K Views
7 Years Discussion Span
Latest Post 10 Years Ago Latest Post by Genc_1

Dani AI

Generated 11 Months Ago

A few tips to make this simple login both correct and secure:

Instead of counting rows, return a single scalar using EXISTS. It short-circuits on the first match and avoids the Output-parameter-null issue that hit. You can also skip output parameters entirely and read a single bit/int. See T‑SQL EXISTS semantics in the docs. Microsoft Learn: EXISTS (Transact‑SQL)

Example stored procedure (returns 1 row, 1 column):

CREATE PROCEDURE dbo.Login_Check
  @UserName     nvarchar(50),
  @PasswordHash varbinary(32)  -- e.g., PBKDF2-SHA256 hash bytes
AS
BEGIN
  SET NOCOUNT ON;

  SELECT CAST(CASE WHEN EXISTS (
           SELECT 1
           FROM dbo.Users u
           WHERE u.UserName = @UserName
             AND u.PasswordHash = @PasswordHash
         ) THEN 1 ELSE 0 END AS bit) AS IsValid;
END

C# call pattern (note the input parameters and null-safe scalar read):

using (var con = new SqlConnection(cs))
using (var cmd = new SqlCommand("dbo.Login_Check", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = userName;
    cmd.Parameters.Add("@PasswordHash", SqlDbType.VarBinary, 32).Value = passwordHash;

    con.Open();
    object r = cmd.ExecuteScalar();
    bool isValid = Convert.ToInt32(r == DBNull.Value ? 0 : r) == 1;
}

Notes:

In the examples posted by , the username/password parameters were not added to the command. Always add all SP parameters by name and type, and use DBNull.Value for nulls when needed. Microsoft Learn: Configuring parameters and parameter data types
Do not store plain text passwords. Store a salt and a slow hash. You can hash in C# with PBKDF2: Rfc2898DeriveBytes.Pbkdf2(...). Microsoft Learn: Rfc2898DeriveBytes.Pbkdf2 If you must hash in SQL, prefer SHA2_256 or SHA2_512 with HASHBYTES. Microsoft Learn: HASHBYTES (Transact‑SQL)

All 5 Replies

vuyiswamb 17 Posting Whiz

18 Years Ago

hi bankerrajendra

First you have to Create a Storedprocure that counts the Records that match the username and password,

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
Set @Results = (Select count(*) from User_Table
where User_name =@User_name  and U_Password = @U_Password)

And after you are done with this , you have to do the Following in your C# Code

using System.Data.SqlClient;

String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB";
            SqlConnection con = new SqlConnection(strcon);
            SqlCommand cmdselect = new SqlCommand();
            cmdselect.CommandTimeout = 0;
            cmdselect.CommandType = CommandType.StoredProcedure;
            cmdselect.Connection = con;
            cmdselect.CommandText = "prclogin_check";
            cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);
            cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output;
            int Res;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Res = (int)cmdselect.Parameters["@Results"].Value;
                con.Close();

            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }

And in your Business logic or in your Form you can create a Function that will test if the Count of Records is greater than 0,if its less than that , then its invalid login

Hope this Helps

ramiljoaquin 0 Light Poster

18 Years Ago

hi bankerrajendra

First you have to Create a Storedprocure that counts the Records that match the username and password,

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
Set @Results = (Select count(*) from User_Table
where User_name =@User_name  and U_Password = @U_Password)

And after you are done with this , you have to do the Following in your C# Code

using System.Data.SqlClient;

String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB";
            SqlConnection con = new SqlConnection(strcon);
            SqlCommand cmdselect = new SqlCommand();
            cmdselect.CommandTimeout = 0;
            cmdselect.CommandType = CommandType.StoredProcedure;
            cmdselect.Connection = con;
            cmdselect.CommandText = "prclogin_check";
            cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);
            cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output;
            int Res;

            try
            {
                con.Open();

                cmdselect.ExecuteNonQuery();
                Res = (int)cmdselect.Parameters["@Results"].Value;
                con.Close();

            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }

And in your Business logic or in your Form you can create a Function that will test if the Count of Records is greater than 0,if its less than that , then its invalid login

Hope this Helps

Hi vuyiswamb!

Your store procedure will get more faster if we will change it to something like this:

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
IF EXISTS (Select * from User_Table
where User_name =@User_name and U_Password = @U_Password)
Set @Results =1

Here is the reason why:
http://www.sqlteam.com/article/using-exists

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

vuyiswamb 17 Posting Whiz · Answer 1 · 2008-08-07T19:00:11+00:00

vuyiswamb 17 Posting Whiz

18 Years Ago

Thanks i will consider it

luv_me 0 Newbie Poster · Answer 2 · 2009-09-17T17:23:22+00:00

hi bankerrajendra

First you have to Create a Storedprocure that counts the Records that match the username and password,

Help with Code Tags
C# Syntax (Toggle Plain Text)

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
Set @Results = (Select count(*) from User_Table

where User_name =@User_name and U_Password = @U_Password)

Create Procedure prclogin_check ( @User_name varchar(20), @U_Password varchar(23), @Results int Output ) Set @Results = (Select count(*) from User_Table where User_name =@User_name and U_Password = @U_Password)

And after you are done with this , you have to do the Following in your C# Code

Help with Code Tags
C# Syntax (Toggle Plain Text)

using System.Data.SqlClient;

String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB";
SqlConnection con = new SqlConnection(strcon);
SqlCommand cmdselect = new SqlCommand();
cmdselect.CommandTimeout = 0;
cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Connection = con;
cmdselect.CommandText = "prclogin_check";
cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);
cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output;
int Res;

try
{
con.Open();

cmdselect.ExecuteNonQuery();
Res = (int)cmdselect.Parameters["@Results"].Value;
con.Close();

}
catch (SqlException e)
{
MessageBox.Show(e.Message);
}

using System.Data.SqlClient; 
String strcon = "User id = sa;Password= topman;Server=myServer;Database=MyDB"; 
SqlConnection con = new SqlConnection(strcon); 
SqlCommand cmdselect = new SqlCommand(); 
cmdselect.CommandTimeout = 0; 
cmdselect.CommandType = CommandType.StoredProcedure; 
cmdselect.Connection = con; 
cmdselect.CommandText = "prclogin_check"; 
cmdselect.Parameters.Add("@Results",SqlDbType.Int,4); 
cmdselect.Parameters["@Results"].Direction = ParameterDirection.Output; 
int Res; 
try { 
  con.Open(); 
  cmdselect.ExecuteNonQuery(); 
  Res = (int)cmdselect.Parameters["@Results"].Value; 
  con.Close(); 
} catch (SqlException e) { 
  MessageBox.Show(e.Message); 
}

And in your Business logic or in your Form you can create a Function that will test if the Count of Records is greater than 0,if its less than that , then its invalid login

Hope this Helps

Hi vuyiswamb!

Your store procedure will get more faster if we will change it to something like this:

Create Procedure prclogin_check
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
IF EXISTS (Select * from User_Table
where User_name =@User_name and U_Password = @U_Password)
Set @Results =1

I WAS MAKING A LOGIN FORM I GOT YOUR CODE BUT I HAVE PROBLEM WITH ONE LINE AS ITS GIVING AN ERROR

THE CODE WHICH GIVES ERROR IS ::

 Res = (int)cmd.Parameters["@results"].Value;

so when the user id & pass are fine i dont get any results and its fine
but if the pass nd uid does not matches it gives the error ::

"INVALIDCASTEXCEPTION WAS UNHANDELED"

instead of code

Res= (int)cmd.Parameters["@results"].Value;

i even tried

Int32 Res= Convert.ToInt32(cmdselect.Parameters["@Results"].Value);

but then i got d sub error as

"Object cannot be cast from DBNull to other types."

SO guys i think the code which u have given which is ::

cmdselect.Parameters.Add("@Results",SqlDbType.Int,4);

above we should change this SqlDbType to some other type i think.

Please help...

Genc_1 0 Newbie Poster · Answer 3 · 2016-04-29T22:38:12+00:00

HOW TO MAKE STORE PROCEDURE WITH :
USERNAME
PASSWORD AND
ROLE (ADMIN AND USER)
PLZ I HAVE this in project help me

Simple Login using asp.net with c# using stored procedure

Dani AI

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers