Hi my name is vishal for past 3 days i have been breaking my head on how to block/restrict a user on 3 unsuccessful attempts by him/her into application in c# windows forms with sql server 2008.
So i am developing an application named:Mini Project, which has a mdi parent form named:MDIParent1. I have developed a form named:frmUser which enabled to register normal user and manager into the application. Given below is my c# code of frmUser:
using System.Data.SqlClient;
namespace Mini_Project
{
public partial class frmUser : Form
{
int pUserID;
public frmUser()
{
InitializeComponent();
string manager = ("Select n.manager_id as manager_id,(m.manager_first_name+' '+m.manager_last_name+'|'+right('000'+convert(varchar,n.manager_id),5)) as Name from managerdetail m,manager n where n.manager_id=m.manager_id and m.status=1");
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(manager, conn);
dt.Load(cmd.ExecuteReader());
cboManager.DataSource = dt;
cboManager.ValueMember = "manager_id";
cboManager.DisplayMember = "Name";
cboManager.SelectedValue = 0;
}
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
int autoGenId = -1;
cmd = new SqlCommand("Insert into [dbo].[User7](username,email,user_first_name,user_last_name,user_dob,user_sex,password,user_type,manager_id,status,row_upd_date,created_by)"+"Values(@username,@email,@user_first_name,@user_last_name,@user_dob,@user_sex,@password,@user_type,@manager_id,@status,GetDate(),@created_by); Select @autoGenId = SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@username", txtUsername.Text.ToString());
cmd.Parameters.AddWithValue("@email", txtEmailID.Text.ToString());
cmd.Parameters.AddWithValue("@user_first_name", txtFName.Text.ToString());
cmd.Parameters.AddWithValue("@user_last_name", txtLName.Text.ToString());
cmd.Parameters.AddWithValue("@user_dob", dtDOB.Value);
if (cboGender.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@user_sex", "Male");
}
else if (cboGender.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@user_sex", "Female");
}
else if (cboGender.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@user_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
if (cboType.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@user_type", 0);
}
cmd.Parameters.AddWithValue("@manager_id", cboManager.SelectedValue);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@created_by", pUserID);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 3, txtUsername.Text.ToString() + "User detail was added successfully");
MessageBox.Show("User Detail was added successfully", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
private void cboType_SelectedIndexChanged(object sender, EventArgs e)
{
if (cboType.SelectedIndex == 0)
{
btnManager.Visible = false;
}
else if (cboType.SelectedIndex == 1)
{
if (MessageBox.Show("Do you want to create a new manager?", "Task", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
btnManager.Visible = true;
}
else
{
btnManager.Visible = false;
MessageBox.Show("Please select a manager from manager list in the current form", "Task", MessageBoxButtons.OK, MessageBoxIcon.Information);
cboType.SelectedIndex = 0;
}
}
}
private void btnManager_Click(object sender, EventArgs e)
{
this.Close();
frmManager h = new frmManager();
h.Show();
}
Given below is my c# code of frmManager:
using System.Data.SqlClient;
namespace Mini_Project
{
public partial class frmManager : Form
{
int pUserID;
public frmManager()
{
InitializeComponent();
}
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
int autoGenId = -1;
cmd = new SqlCommand("Insert into manager(manager_dob,manager_sex,created_date,user_id,username,password)" + "Values(@manager_dob,@manager_sex,GetDate(),@user_id,@username,@password); Select @autoGenId = SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@manager_dob", dtDOB.Value);
if (cboGender.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@manager_sex", "Male");
}
else if (cboGender.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@manager_sex", "Female");
}
else if (cboGender.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@manager_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
cmd = new SqlCommand("Insert into managerdetail(manager_first_name,manager_last_name,row_upd_date,manager_id,email,status,user_id)" + "Values(@manager_first_name,@manager_last_name,GetDate(),@manager_id,@email,@status,@user_id)", conn);
cmd.Parameters.AddWithValue("@manager_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@manager_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@manager_id", autoGenId);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("Insert into [dbo].[User7](username,email,user_first_name,user_last_name,user_dob,user_sex,password,user_type,status,manager_id,row_upd_date,created_by)"+"Values(@username,@email,@user_first_name,@user_last_name,@user_dob,@user_sex,@password,@user_type,@status,@manager_id,GetDate(),@created_by)", conn);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@user_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@user_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@user_dob", dtDOB.Value);
if (cboGender.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@user_sex", "Male");
}
else if (cboGender.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@user_sex", "Female");
}
else if (cboGender.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@user_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.AddWithValue("@user_type", 1);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@manager_id", autoGenId);
cmd.Parameters.AddWithValue("@created_by", pUserID);
cmd.ExecuteNonQuery();
MDIParent1 u = new MDIParent1();
u.updateUserActivities(autoGenId, 2, txtUsername.Text.ToString() + "Manager detail was added successfully");
MessageBox.Show("Manager Detail was added successfully", "Task", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
}
The above c# code with sql server 2008 of frmUser and frmManager Works with no problem at all!
Given below is my structure of my table named:User7 in sql server 2008:
ColumnName DataType AlowNulls
username nvarchar(30) Yes
email nvarchar(70) Yes
user_first_name nvarchar(50) Yes
user_last_name nvarchar(50) Yes
user_dob date Yes
user_sex nvarchar(20) Yes
password nvarchar(15) Yes
user_type Int Yes
user_id(auto-incrementpk) Int No
manager_id Int Yes
row_upd_date datetime Yes
status bit Yes
created_by Int Yes
Given below is my c# code of my login form named:frmLogin
using System.Data.SqlClient;
namespace Mini_Project
{
public partial class frmLogin : Form
{
public frmLogin()
{
InitializeComponent();
}
private void btnLogin_Click(object sender, EventArgs e)
{
if ((txtPassword.Text == "password") && (txtUsername.Text.ToLower() == "admin"))
{
MDIParent1 h = new MDIParent1();
h.Show();
this.Close();
}
else
{
string username = txtUsername.Text;
string password = txtPassword.Text;
bool validUser = ValidateUser(username, password);
if (validUser)
{
MDIParent1 m = new MDIParent1();
m.Show();
this.Close();
}
else
{
MessageBox.Show("Invalid user name or password. Please try with another user name or password", "Task", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtUsername.Focus();
}
}
}
private bool ValidateUser(string username, string password)
{
bool success = false;
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Select @count = Count(*) from [dbo].[User7] where username=@username and password=@password", conn);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.Add("@count", SqlDbType.Int).Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
if (Convert.ToInt32(cmd.Parameters["@count"].Value) > 0)
{
success = true;
}
else
{
success = false;
}
conn.Close();
return success;
}
}
}
The above c# code of frmLogin(login form) works with no problem at all!
However what i want is when a user wrongly/enters incorrect password for more than 3 times then he/she should be blocked an entire day from entering into to application through the frmLogin!
Can anyone help me Please! Can anyone tell me what modifications must i do in my c# code and where?! Can anyone tell me what field should i need to add to my table(User7) in sql server 2008 in order to achieve my required result along with c# code? Can anyone help me/guide me solve my problem?! Any help/guidance in solving of this problem would be greatly appreciated!
