string connectionString = "Data Source=DESKTOP-546JL3N\\SQLEXPRESS;Initial
Catalog=lab3;Integrated Security=True";
//best way to use connection login code
using (SqlConnection con=new SqlConnection(connectionString))
{
try
{
con.Open();
string sqlquery = "select * from usser where usernamee=@user and
pasword=@pass";
using(SqlCommand cmd=new SqlCommand(sqlquery, con))
{
cmd.Parameters.AddWithValue("@user", textBox1.Text);
cmd.Parameters.AddWithValue("@pass", textBox2.Text);
SqlDataReader rd= cmd.ExecuteReader();
rd.Read();
if (rd.HasRows)
{
Form2 fr = new Form2();
fr.UserName = rd["fullname"].ToString();
fr.UserRole = rd["rolee"].ToString();
fr.Show();
this.Hide();
}
else
{
label3.Visible = true;
label3.Text = "username or password is wrong";
}
con.Close();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
//read data
try
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
int id = Convert.ToInt32(idtxt.Text);
SqlCommand cmd = new SqlCommand("select * from usser where id=@id", con);
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
userTxt.Text = rd["usernamee"].ToString();
passtxt.Text = rd["pasword"].ToString();
fullnametxt.Text = rd["fullname"].ToString();
comboBox1.SelectedItem = rd["rolee"].ToString();
}
con.Close();
showData();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
private void showData()
{ //show data in data gridview using method call
try {
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from usser", con);
DataSet ds = new DataSet();
da.Fill(ds, "usser");
dataGridView1.DataSource = ds.Tables["usser"];
con.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//insert code
try {
SqlConnection con = new SqlConnection(connectionString);
con.Open();
int id= Convert.ToInt32(idtxt.Text);
SqlCommand cmd = new SqlCommand("insert into usser
(id,usernamee,pasword,fullname,rolee)values (@id,@user,@pass,@name,@role)",con);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@user", userTxt.Text);
cmd.Parameters.AddWithValue("@pass", passtxt.Text);
cmd.Parameters.AddWithValue("@name", fullnametxt.Text);
cmd.Parameters.AddWithValue("@role", comboBox1.SelectedItem.ToString());
cmd.ExecuteNonQuery();
con.Close();
showData();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
//search code
try
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("select * from usser where fullname like
'%' + @name + '%'", con);
cmd.Parameters.AddWithValue("@name", textBox1.Text);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "usser");
dataGridView1.DataSource = ds.Tables["usser"];
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//update code
try
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
int id = Convert.ToInt32(idtxt.Text);
SqlCommand cmd = new SqlCommand("update usser set
usernamee=@user,pasword=@pass,fullname=@name,rolee=@role where id=@id", con);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@user", userTxt.Text);
cmd.Parameters.AddWithValue("@pass", passtxt.Text);
cmd.Parameters.AddWithValue("@name", fullnametxt.Text);
cmd.Parameters.AddWithValue("@role", comboBox1.SelectedItem.ToString());
cmd.ExecuteNonQuery();
con.Close();
showData();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//delete code
try
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
int id = Convert.ToInt32(idtxt.Text);
SqlCommand cmd = new SqlCommand("delete from usser where id=@id", con);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
con.Close();
showData();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//Sql server codes
create database lab3
use lab3
create table usser(
id int primary key,
usernamee varchar(10)unique,
pasword varchar(10),
fullname varchar(30),
rolee varchar(10),
)
insert into usser values (1,'abc','abc','sara','Admin'),(2,'abc1','abc','sara','User')
