Create a table like this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](20) NOT NULL,
[Password] [nvarchar](30) NOT NULL,
[FailedAttempts] [int] NULL,
[LastLoginAttempt] [datetime] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert some dummy data
insert into Users(UserName, Password)
select 'Mudassar', '1234'
union all
select 'John', '1234'
Then use the following SP
CREATE PROCEDURE ValidateLogin(@UserName VARCHAR(20), @Password VARCHAR(30))
AS
BEGIN
IF EXISTS(SELECT UserName FROM Users WHERE UserName = @UserName AND Password = @Password AND (ISNULL(FailedAttempts, 0) < 3 OR DATEDIFF(HOUR, GETDATE(), LastLoginAttempt) <= 24))
BEGIN
UPDATE Users
SET FailedAttempts = NULL,
LastLoginAttempt = GETDATE()
WHERE UserName = @UserName
SELECT 'TRUE'
END
ELSE
BEGIN
IF (SELECT ISNULL(FailedAttempts, 0) FROM Users WHERE UserName = @UserName) < 3
BEGIN
UPDATE Users
SET FailedAttempts = ISNULL(FailedAttempts, 0) + 1,
LastLoginAttempt = GETDATE()
WHERE UserName = @UserName
SELECT 'FALSE'
END
ELSE
BEGIN
SELECT 'DISABLED'
END
END
END
Finally in code
protected void Login(object sender, EventArgs e)
{
string conString = ConfigurationManager.ConnectionStrings["constr_users"].ConnectionString;
string query = "ValidateLogin";
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", "Mudassar");
cmd.Parameters.AddWithValue("@Password", "1234");
con.Open();
string code = cmd.ExecuteScalar().ToString();
con.Close();
switch (code)
{
case "TRUE":
Response.Write("Valid");
break;
case "INVALID":
Response.Write("Invalid");
break;
case "DISABLED":
Response.Write("Disabled");
break;
}
}
}