public List <cUsers> getUsers(string username)
{
List <cUsers> users = new List <cUsers>();
SqlConnection con = new SqlConnection(Helper.conStr);
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
con.Open();
string query = "select * from Users where uUserName LIKE '%" + username + "%'";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cUsers u1 = new cUsers();
u1.uId = Convert.ToInt32(reader["uId"]);
u1.uFullName = reader["uFullName"].ToString();
u1.uPassword = reader["uPassword"].ToString();
u1.uType = reader["uType"].ToString();
u1.uUserName = reader["uUserName"].ToString();
u1.uIsActive = Convert.ToBoolean(reader["uIsActive"]);
users.Add(u1);
}
reader.Close();
con.Close();
con.Dispose();
cmd.Dispose();
return(users);
}
private void btnAdd_Click(object sender, EventArgs e)
{
cUsers user = new cUsers();
user.uFullName = txtfullname.Text.Trim();
user.uPassword = txtPassword.Text.Trim();
user.uUserName = txtUsername.Text.Trim();
user.uType = cbType.Text.Trim();
user.uIsActive = cbIsActive.Checked;
if (user.uFullName != "")
{
if (user.uPassword != "")
{
if (user.uUserName != "")
{
if (user.uType != "")
{
//valid record or user
if (new cUserBLL().InsertUser(user) > 0)
{
MessageBox.Show("Data stored...!!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtfullname.Text = "";
txtPassword.Text = "";
txtUsername.Text = "";
cbType.Text = "";
cbIsActive.Checked = false;
}
else
{
MessageBox.Show("Data cannot be stored...!!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Type should be selected...!!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("User Name should be filled...!!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Password should be filled...!!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Full Name should be filled...!!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public int UpdateUser(cUsers user)
{
SqlConnection con = new SqlConnection(Helper.conStr);
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
con.Open();
string query = "UPDATE Users " +
"SET uFullName = '" + user.uFullName + "' , uUserName = '******' , uPassword = '******' , uType = '" + user.uType + "' , uIsActive = '" + user.uIsActive + "'" +
" WHERE uId ='" + user.uId + "'";
SqlCommand cmd = new SqlCommand(query, con);
var result = cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
con.Dispose();
return(result);
}
private void dgvUsers_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int selectedRowIndex = this.dgvUsers.SelectedCells[0].RowIndex;
int selectedColumnIndex = this.dgvUsers.SelectedCells[0].ColumnIndex;
if (selectedColumnIndex == 0)//update
{
//MessageBox.Show("update click "+this.dgvUsers.Rows[selectedRowIndex].Cells[4].Value);access row value
cUsers u1 = new cUsers();
u1.uId = Convert.ToInt32(this.dgvUsers.Rows[selectedRowIndex].Cells[2].Value);
u1.uFullName = this.dgvUsers.Rows[selectedRowIndex].Cells[3].Value.ToString();
u1.uUserName = this.dgvUsers.Rows[selectedRowIndex].Cells[4].Value.ToString();
u1.uPassword = this.dgvUsers.Rows[selectedRowIndex].Cells[5].Value.ToString();
u1.uType = this.dgvUsers.Rows[selectedRowIndex].Cells[6].Value.ToString();
u1.uIsActive = Convert.ToBoolean(this.dgvUsers.Rows[selectedRowIndex].Cells[7].Value);
if (u1.uType.ToUpper() == "ADMIN" || u1.uType.ToUpper() == "OPERATER")
{
new cUserBLL().UpdateUser(u1);
dgvUsers.DataSource = "";
dgvUsers.DataSource = new cUserBLL().getUsers();
this.dgvUsers.Columns[2].ReadOnly = true;
MessageBox.Show("Data Updated...!!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("User type must be one of \"Admin\" or \"Operater\"...!!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (selectedColumnIndex == 1)//delete
{
new cUserBLL().RemoveUser(Convert.ToInt32(this.dgvUsers.Rows[selectedRowIndex].Cells[2].Value));
dgvUsers.DataSource = "";
dgvUsers.DataSource = new cUserBLL().getUsers();
this.dgvUsers.Columns[2].ReadOnly = true;
MessageBox.Show("Data Deleted...!!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public int InsertUser(cUsers user)
{
SqlConnection con = new SqlConnection(Helper.conStr);
if (con.State.ToString() == "open")
{
con.Close();
}
con.Open();
string query = "INSERT into Users (uFullname,uUserName,uPassword,uType,uIsActive) values('" + user.uFullName + "','" + user.uUserName + "','" + user.uPassword + "','" + user.uType + "','" + user.uIsActive + "')";
SqlCommand cmd = new SqlCommand(query, con);
var result = cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
con.Dispose();
return(result);
}
public Users()
{
DataUsers = new cUsers();
}
public int UpdateUser(cUsers user)
{
return(new cUserDAL().UpdateUser(user));
}
public int InsertUser(cUsers user)
{
return(new cUserDAL().InsertUser(user));
}