设计一个用户管理系统，对系统中的用户进行管理。假定，用户表中有下列字段：用户名，密码，电话和 email 等信息。要求，1）利用 SQL server 首先创建用户数据表；2）实现对用户的增加、删除、修改和查询等功能。
（0）根据学生在使用CMS系统和Discuz系统时，发现其数据库的后台操作界面很简单，因此根据sql语句的基本组成，设置where条件选择输入区和更新插入的更新区。
（1）只有在勾选该字段时，才允许输入该字段的值
（2）使用listview列出数据查询结果
（3）支持数据浏览，无须手动输入查询语句
以下只是简单的几组测试，其他的sql组合同理能够运行
（1） 浏览数据
注：选择浏览操作的示意图
（2）选择查看符合相应条件的数据
注：设置选择的条件示意图
注：执行选择后的结果
（3）插入数据
注：插入数据示意
注：数据插入后，浏览数据结果
（4）更新数据
注：设置更新条件示意图
注：执行更新操作后
（5）删除数据
注：设置删除条件示意图
注：删除操作执行后
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data;using System.Data.Sql;using System.Data.SqlClient;namespace 数据库操作_WNFORM{ public partial class Form1 : Form { PRivate SqlConnection sqlcon; private SqlCommand sqlcom; private SqlDataReader sda; private string conn_str = @”Data Source=./SQLEXPRESS;AttachDbFilename=|DataDirectory|/info.mdf;Integrated Security=True;User Instance=True”; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { listView_res.Columns.Add(“用户名”); listView_res.Columns.Add(“密 码”); listView_res.Columns.Add(“手机号”); listView_res.Columns.Add(“邮箱号”); listView_res.Columns[0].Width = 250; listView_res.Columns[1].Width = 340; listView_res.Columns[2].Width = 245; listView_res.Columns[3].Width = 325; } private void checkBox1_CheckedChanged(object sender, EventArgs e) { textBox_name.Enabled = true; } private void checkBox3_CheckedChanged(object sender, EventArgs e) { textBox_pass.Enabled = true; } private void checkBox2_CheckedChanged(object sender, EventArgs e) { textBox_name2.Enabled = true; } private void checkBox6_CheckedChanged(object sender, EventArgs e) { textBox_pass2.Enabled = true; } private void checkBox4_CheckedChanged(object sender, EventArgs e) { textBox_tel.Enabled = true; } private void checkBox5_CheckedChanged(object sender, EventArgs e) { textBox_mail.Enabled = true; } private void checkBox7_CheckedChanged(object sender, EventArgs e) { textBox_tel2.Enabled = true; } private void checkBox8_CheckedChanged(object sender, EventArgs e) { textBox_mail2.Enabled = true; } private void init_controler() { checkBox1.Checked = false; checkBox2.Checked = false; checkBox3.Checked = false; checkBox4.Checked = false; checkBox5.Checked = false; checkBox6.Checked = false; checkBox7.Checked = false; checkBox8.Checked = false; textBox_mail.Text = “”; textBox_name.Text = “”; textBox_pass.Text = “”; textBox_tel.Text = “”; textBox_mail2.Text = “”; textBox_name2.Text = “”; textBox_pass2.Text = “”; textBox_tel2.Text = “”; textBox_mail.Enabled = false; textBox_name.Enabled = false; textBox_pass.Enabled = false; textBox_tel.Enabled = false; textBox_mail2.Enabled = false; textBox_name2.Enabled = false; textBox_pass2.Enabled = false; textBox_tel2.Enabled = false; } private string [] get_where() { string [] res=new string[4]; if (textBox_name.Text != null) { res[0]= textBox_name.Text.ToString(); } if (textBox_pass.Text != null) { res[1]= textBox_pass.Text.ToString(); } if (textBox_tel.Text != null) { res[2]= textBox_tel.Text.ToString(); } if (textBox_mail.Text != null) { res[3]= textBox_mail.Text.ToString(); } return res; } private string[] get_value() { string[] res = new string[4]; if (textBox_name2.Text != null) { res[0] = textBox_name2.Text.ToString(); } if (textBox_pass2.Text != null) { res[1] = textBox_pass2.Text.ToString(); } if (textBox_tel2.Text != null) { res[2] = textBox_tel2.Text.ToString(); } if (textBox_mail2.Text != null) { res[3] = textBox_mail2.Text.ToString(); } return res; } //执行数据库查询 private string execu_sql() { string res = “”; sqlcon = new SqlConnection(conn_str); sqlcom = new SqlCommand(); sqlcon.Open(); sqlcom.Connection = sqlcon; string sw = “”; sw = comboBox1.SelectedItem.ToString(); MessageBox.Show(sw); / 浏览数据 查询数据 插入数据 更新数据 删除数据 / switch (sw) { case “浏览数据”: res = “select * from [用户表]”; sqlcom.CommandText = res; int count = 0; try { sda = sqlcom.ExecuteReader(); listView_res.Items.Clear(); while (sda.Read()) { ListViewItem lvi = new ListViewItem(); lvi.Text = sda.GetString(0); lvi.SubItems.Add(sda.GetString(1)); lvi.SubItems.Add(sda.GetString(2)); lvi.SubItems.Add(sda.GetString(3)); lvi.UseItemStyleForSubItems = false; lvi.SubItems[0].BackColor = Color.HotPink; lvi.SubItems[1].BackColor = Color.LightSkyBlue; lvi.SubItems[2].BackColor = Color.SpringGreen; lvi.SubItems[3].BackColor = Color.Orange; listView_res.Items.Add(lvi); count++; } if(count==0) { MessageBox.Show(“没有查询到匹配的数据”); } } catch (Exception ex) { MessageBox.Show(“出现数据查询错误，请重试” + ex.Message); } finally { sqlcon.Close(); } break; case “查询数据”: res = “select * from [用户表]”; string [] wh=get_where(); bool tags=false; if(wh!=null) { res+=” where “; if (wh[0]!= “”) { res += ” 用户名=@user “; //res+=wh[0]; tags=true; } if (wh[1] != “”) { if(tags) res+=”and “; res+=” 密码=@pass “; //res+=wh[1]; tags=true; } if (wh[2] != “”) { if(tags) res += ” and “; res+=” 电话=@tel “; //res+=wh[2]; tags=true; } if(wh[3]!=”“) { if(tags) res += ” and “; res+=” 邮箱=@mail “; // res+=wh[1]; } sqlcom.CommandText = res; if (wh != null) { if (wh[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, wh[0]); } if (wh[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass”, wh[1]); } if (wh[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel”, wh[2]); } if (wh[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail”, wh[3]); } } try { sda = sqlcom.ExecuteReader(); listView_res.Items.Clear(); if (sda.Read()) { ListViewItem lvi = new ListViewItem(); lvi.Text = sda.GetString(0); lvi.SubItems.Add(sda.GetString(1)); lvi.SubItems.Add(sda.GetString(2)); lvi.SubItems.Add(sda.GetString(3)); lvi.UseItemStyleForSubItems = false; lvi.SubItems[0].BackColor = Color.HotPink; lvi.SubItems[1].BackColor = Color.LightSkyBlue; lvi.SubItems[2].BackColor = Color.SpringGreen; lvi.SubItems[3].BackColor = Color.Orange; listView_res.Items.Add(lvi); } else { MessageBox.Show(“没有查询到匹配的数据，请检查原因”); } } catch (Exception ex) { MessageBox.Show(“出现数据查询错误，请重试” + ex.Message); } finally { sqlcon.Close(); } } break; case “插入数据”: res = “insert into [用户表](“; string [] vl=get_value(); bool tagi=false; if (vl != null) { if (vl[0] != “”) { res += “用户名”; tagi = true; } if (vl[1] != “”) { if (tagi) res+=” , “; res+=”密码 “; tagi = true; } if (vl[2] != “”) { if (tagi) res+=” , “; res+=”电话”; tagi = true; } if (vl[3] != “”) { if (tagi) res+=” , “; res+=”邮箱”; } } res += ” ) values(“; /*** 构造values *******/ tagi=false; if (vl != null) { if (vl[0] != “”) { res += “@user”; //res += vl[0]; tagi = true; } if (vl[1] != “”) { if (tagi) res+=” , “; res+=”@pass”; //res += vl[1]; tagi = true; } if (vl[2] != “”) { if (tagi) res+=” , “; res+=”@tel”; // res += vl[2]; tagi = true; } if (vl[3] != “”) { if (tagi) res+=” , “; res+=”@mail”; //res += vl[1]; } } res += ” )”; sqlcom.CommandText = res; if (vl != null) { if (vl[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, vl[0]); } if (vl[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass”, vl[1]); } if (vl[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel”, vl[2]); } if (vl[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail”, vl[3]); } } int i = sqlcom.ExecuteNonQuery(); if (i > 0) { MessageBox.Show(“插入成功,请浏览数据”); } else { MessageBox.Show(“插入失败，请检查原因”); } sqlcon.Close(); break; case “更新数据”: res = “update [用户表] set”; string [] vlu=get_value(); string[] whu = get_where(); bool tagu=false; if (vlu != null) { if (vlu[0] != “”) { res += ” 用户名=”; res += ” @user2”; tagu = true; } if (vlu[1] != “”) { if (tagu) res+=” , “; res+=” 密码=”; res += ” @pass2”; tagu = true; } if (vlu[2] != “”) { if (tagu) res+=” , “; res+=” 电话=”; res += ” @tel2”; tagu = true; } if (vlu[3] != “”) { if (tagu) res+=” , “; res+=” 邮箱=”; res+=” @mail2”; } } tagu = false; if (whu != null) { res+=” where “; if (whu[0] != “”) { res += ” 用户名=@user “; //res+=wh[0]; tagu = true; } if (whu[1] != “”) { if (tagu) res+=”and “; res+=” 密码=@pass “; //res+=wh[1]; tagu = true; } if (whu[2] != “”) { if (tagu) res += ” and “; res+=” 电话=@tel “; //res+=wh[2]; tagu = true; } if (whu[3] != “”) { if (tagu) res += ” and “; res+=” 邮箱=@mail “; // res+=wh[1]; } } / * * */ sqlcom.CommandText = res; if (vlu != null) { if (vlu[0] != “”) { sqlcom.Parameters.AddWithValue(“@user2”, vlu[0]); } if (vlu[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass2”, vlu[1]); } if (vlu[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel2”, vlu[2]); } if (vlu[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail2”, vlu[3]); } } if (whu != null) { if (whu[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, whu[0]); } if (whu[1] != “”) { sqlcom.Parameters.AddWithValue(“@pass”, whu[1]); } if (whu[2] != “”) { sqlcom.Parameters.AddWithValue(“@tel”, whu[2]); } if (whu[3] != “”) { sqlcom.Parameters.AddWithValue(“@mail”, whu[3]); } } int pcount = sqlcom.ExecuteNonQuery(); if (pcount > 0) { MessageBox.Show(“更新成功,请浏览数据”); } else { MessageBox.Show(“更新失败，请检查原因”); } sqlcon.Close(); break; case “删除数据”: res = “delete from [用户表]”; string [] whd=get_where(); bool tagd=false; if (whd != null) { res+=” where “; if (whd[0] != “”) { res += ” 用户名=@user “; //res+=wh[0]; tagd = true; } if (whd[1] != “”) { if (tagd) res+=”and “; res+=” 密码=@pass “; //res+=wh[1]; tagd = true; } if (whd[2] != “”) { if (tagd) res += ” and “; res+=” 电话=@tel “; //res+=wh[2]; tagd = true; } if (whd[3] != “”) { if (tagd) res += ” and “; res+=” 邮箱=@mail “; // res+=wh[1]; } sqlcom.CommandText = res; if (whd != null) { if (whd[0] != “”) { sqlcom.Parameters.AddWithValue(“@user”, whd[0]); } if (whd[1] != null) { sqlcom.Parameters.AddWithValue(“@pass”, whd[1]); } if (whd[2] != null) { sqlcom.Parameters.AddWithValue(“@tel”, whd[2]); } if (whd[3] != null) { sqlcom.Parameters.AddWithValue(“@mail”, whd[3]); } } try { int rc=sqlcom.ExecuteNonQuery(); if (rc > 0) { MessageBox.Show(“数据删除成功，请浏览数据”); } else { MessageBox.Show(“数据删除失败，请检查原因”); } } catch (Exception ex) { MessageBox.Show(“出现数据查询错误，请重试” + ex.Message); } finally { sqlcon.Close(); } } break; } return res; } private void button1_Click(object sender, EventArgs e) { init_controler(); } private void button2_Click(object sender, EventArgs e) { if (comboBox1.SelectedItem == null) { MessageBox.Show(“请选择右上方的操作后再执行确认”); return; } execu_sql(); init_controler(); } }}
新闻热点
疑难解答
