用户管理
来源:互联网 发布:淘宝服务市场的代运营 编辑:程序博客网 时间:2024/05/16 11:53
用户管理
1、用户查询
private void btnQuery_Click(object sender, EventArgs e){
string strcon = ConfigurationManager.ConnectionStrings["sqlserver2008"].ConnectionString;
SqlConnection conn = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
string sqlstr = "select A.UserId,A.UserName,A.Error,B.Name,isnull(B.Sfzh,'还未填写')Sfzh,B.Tell,B.Address,B.Age,(case B.General when 1 then '男' when 0 then '女' end)性别 from T_User A LEFT OUTER JOIN T_UserInfo B on A.UserId=B.UserId where ";
string sqlwhere = string.Empty;
#region // switch
switch (comboBox1.Text)
{
case "姓名":
sqlwhere = "B.Name like @name";
cmd.Parameters.AddWithValue("@name","%"+txtcontent.Text+"%");
break;
case "用户名":
sqlwhere = "A.UserName like @username";
cmd.Parameters.AddWithValue("@username","%"+txtcontent.Text+"%");
break;
case "电话":
sqlwhere = "B.Tell like @tell"; cmd.Parameters.AddWithValue("@tell", "%" + txtcontent.Text + "%");
break;
case "地址":
sqlwhere = "B.Address like @address";
cmd.Parameters.AddWithValue("@address", "%" + txtcontent.Text + "%");
break;
default:
sqlwhere = "B.Sfzh like @sfzh";
cmd.Parameters.AddWithValue("@sfzh", "%" + txtcontent.Text + "%");
break;
}
#endregion
cmd.CommandText = sqlstr + sqlwhere;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
this.dataGridView1.DataSource = dt;
}
else
{
MessageBox.Show("您查询的数据不存在");
}
}
2、添加用户
private void btnSave_Click(object sender, EventArgs e){
string strcon = ConfigurationManager.ConnectionStrings["sqlserver2008"].ConnectionString;
SqlConnection conn = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "insert into T_User (UserName,Password) values(@username,@password) select @@identity";
cmd.Parameters.AddWithValue("@username", txtUserName.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result > 0)
{
int general = 0;
switch (txtgeneral.Text)
{
case "男":
general = 1;
break;
default:
general = 0;
break;
}
cmd.CommandText = "insert into T_UserInfo(UserId,Name,Sfzh,Tell,Address,Age,General) values(@userid,@name,@sfzh,@tell,@address,@age,@general)";
cmd.Parameters.AddWithValue("@userid", result);
cmd.Parameters.AddWithValue("@name", txtName.Text);
cmd.Parameters.AddWithValue("@sfzh", txtSfzh.Text);
cmd.Parameters.AddWithValue("@tell", txtTell.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@age", txtage.Text);
cmd.Parameters.AddWithValue("@general", general);
int result1 = cmd.ExecuteNonQuery();
if (result1 > 0)
{
MessageBox.Show("插入数据成功");
}
}
else
{
MessageBox.Show("数据插入不成功");
}
}
3、解锁
private void btnUnlock_Click(object sender, EventArgs e){
string strcon = ConfigurationManager.ConnectionStrings["sqlserver2008"].ConnectionString;
SqlConnection conn = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
int id = Convert.ToInt32(this.dataGridView1.SelectedCells[0].Value);
cmd.CommandText = "update T_User set Error=0 where UserId=@userid";
cmd.Parameters.AddWithValue("@userid", id);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("解锁成功");
}
else
{
MessageBox.Show("解锁失败");
}
}
4、刷新(也就是从数据库重新获取数据)
private void btnFresh_Click(object sender, EventArgs e){
string strcon = ConfigurationManager.ConnectionStrings["sqlserver2008"].ConnectionString;
SqlConnection conn = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
string sqlstr = "select A.UserId,A.UserName,A.Error,B.Name,isnull(B.Sfzh,'还未填写')Sfzh,B.Tell,B.Address,B.Age,(case B.General when 1 then '男' when 0 then '女' end)性别 from T_User A LEFT OUTER JOIN T_UserInfo B on A.UserId=B.UserId where ";
string sqlwhere = string.Empty;
switch (comboBox1.Text)
{
case "姓名":
sqlwhere = "B.Name like @name";
cmd.Parameters.AddWithValue("@name", "%" + txtcontent.Text + "%");
break;
case "用户名":
sqlwhere = "A.UserName like @username";
cmd.Parameters.AddWithValue("@username", "%" + txtcontent.Text + "%");
break;
case "电话":
sqlwhere = "B.Tell like @tell"; cmd.Parameters.AddWithValue("@tell", "%" + txtcontent.Text + "%");
break;
case "地址":
sqlwhere = "B.Address like @address";
cmd.Parameters.AddWithValue("@address", "%" + txtcontent.Text + "%");
break;
default:
sqlwhere = "B.Sfzh like @sfzh";
cmd.Parameters.AddWithValue("@sfzh", "%" + txtcontent.Text + "%");
break;
}
//拼接sql语句
cmd.CommandText = sqlstr + sqlwhere;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
this.dataGridView1.DataSource = dt;
}
else
{
MessageBox.Show("您查询的数据不存在");
}
}