C#_会员管理系统：开发六（数据搜索）
增加界面中的搜索功能
会员资料管理界面(VIPManager.cs):
详细代码如下：
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Configuration; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Drawing; 8 using System.Linq; 9 using System.Text; 10 using System.Threading.Tasks; 11 using System.Windows.Forms; 12 13 namespace 会员管理系统 14 { 15 public partial class VIPManager : Form 16 { 17 public VIPManager() 18 { 19 InitializeComponent(); 20 } 21 22 //连接字符串 获取配置文件里的连接路径,多次需要调用，放在外面方便 23 static string connStr = ConfigurationManager.ConnectionStrings["str"].ConnectionString; 24 //窗体运行自动加载 25 private void VipManager_Load(object sender, EventArgs e) 26 { 27 //刷新数据 28 Refresh(); 29 cmbforfieldSelecting.Text = "全局搜索"; 30 cmbforfieldSelecting.Items.Add("全局搜索"); 31 cmbforfieldSelecting.Items.Add("编号"); 32 cmbforfieldSelecting.Items.Add("名字"); 33 cmbforfieldSelecting.Items.Add("性别"); 34 cmbforfieldSelecting.Items.Add("年龄"); 35 cmbforfieldSelecting.Items.Add("地址"); 36 cmbforfieldSelecting.Items.Add("电话"); 37 } 38 39 //写一个刷新数据的方法(跟查看数据一样) 40 public void Refresh(bool isAdded = false) 41 { 42 //查询数据库字符串 43 string sql = String.Format("select vId '{0}',vName '{1}',vGender '{2}',vAge '{3}',vAddress '{4}',vPhone '{5}' from VipInformation", "编号", "名字", "性别", "年龄", "地址", "电话"); 44 //连接数据库对象 45 SqlConnection conn = new SqlConnection(connStr); 46 //操作数据库对象 47 SqlCommand cmd = new SqlCommand(sql, conn); 48 //创建表对象 49 System.Data.DataTable dt = new System.Data.DataTable(); 50 //创建数据库填充操作对象(语句) 51 SqlDataAdapter sda = new SqlDataAdapter(cmd); 52 //把数据填充进dt表中 53 sda.Fill(dt); 54 //指定dgvManager控件的数据源:dt 55 dgvManager.DataSource = dt; 56 57 //if (isAdded) 58 //{ 59 // if (dt.Rows.Count > 0) 60 // dgvManager.Rows[0].Selected = false; 61 // dgvManager.Rows[dt.Rows.Count - 1].Selected = true; 62 //} 63 } 64 65 //刷新数据界面 66 private void btnView_Click(object sender, EventArgs e) 67 { 68 //刷新数据 69 Refresh(); 70 } 71 72 //添加数据 73 private void btnAdd_Click(object sender, EventArgs e) 74 { 75 //判断文本框是否为空，提示数据完整性 76 if (txtName.Text == "" || txtGender.Text == "" || txtAge.Text == "" || txtAddress.Text == "" || txtPhone.Text == "") 77 { 78 MessageBox.Show("数据不能为空，请填写齐全"); 79 return; 80 } 81 //插入数据库字符串 82 string sql = string.Format("insert into VipInformation values('{0}','{1}',{2},'{3}','{4}')",txtName.Text.Trim(),txtGender.Text.Trim(),txtAge.Text.Trim(),txtAddress.Text.Trim(),txtPhone.Text.Trim()); 83 //连接数据库对象 84 SqlConnection conn = new SqlConnection(connStr); 85 //操作数据库对象 86 SqlCommand cmd = new SqlCommand(sql, conn); 87 //创建表对象 88 System.Data.DataTable dt = new DataTable(); 89 //创建数据库填充操作对象(语句) 90 SqlDataAdapter sda = new SqlDataAdapter(cmd); 91 //把数据填充进dt表中 92 sda.Fill(dt); 93 //指定dgvManager控件的数据源:dt 94 dgvManager.DataSource = dt; 95 //刷新数据 96 Refresh(); 97 } 98 99 //删除数据 100 private void btnDelete_Click(object sender, EventArgs e) 101 { 102 //使用sql删除语句,where 1=1 就是没有条件，等于全部数据删除 103 string sql = "delete from VipInformation where 1=1"; 104 //如果选中某行则执行 105 if (dgvManager.CurrentRow.Selected) 106 { 107 sql = sql + " and vid=" + Convert.ToInt32(dgvManager.CurrentRow.Cells[0].Value.ToString()); 108 } 109 int n = 0; 110 //创建连接数据库对象 111 SqlConnection conn = new SqlConnection(connStr); 112 //创建操作数据库对象 113 SqlCommand cmd = new SqlCommand(sql, conn); 114 //打开数据库 115 conn.Open(); 116 //取得ExecuteNonQuery返回的受影响行数，无影响则为0 117 n = cmd.ExecuteNonQuery(); 118 if (n == 0) 119 { 120 MessageBox.Show("删除操作失败!不存在的ID"); 121 conn.Close(); 122 return; 123 } 124 else if (n > 0) 125 { 126 MessageBox.Show("删除操作成功!"); 127 } 128 //关闭数据库连接 129 conn.Close(); 130 //刷新数据界面 131 Refresh(); 132 } 133 134 //修改数据 135 private void btnSave_Click(object sender, EventArgs e) 136 { 137 if (txtName.Text == "" || txtGender.Text == "" || txtAge.Text == "" || txtAddress.Text == "" || txtPhone.Text == "") 138 { 139 MessageBox.Show("所提供的数据不完整，请填写完整数据"); 140 return; 141 } 142 int n = 0; 143 //更新SQL语句 144 string sqlupdate = "update VipInformation set vName='" + txtName.Text + "',vgender='" + txtGender.Text + "',vage=" + txtAge.Text + ",vaddress='" + txtAddress.Text + "',vphone='" + txtPhone.Text + "' where vid='" + dgvManager.CurrentRow.Cells[0].Value.ToString() + "'"; 145 SqlConnection conn = new SqlConnection(connStr); 146 SqlCommand cmd = new SqlCommand(sqlupdate, conn); 147 conn.Open(); 148 n = cmd.ExecuteNonQuery(); 149 if (n == 0) 150 { 151 MessageBox.Show("修改操作失败！"); 152 conn.Close(); 153 return; 154 } 155 else if (n > 0) 156 { 157 MessageBox.Show("修改操作成功!"); 158 } 159 conn.Close(); 160 Refresh(); 161 } 162 163 //点击dgvManager在文本框上显示 164 private void dgvManager_CellContentClick(object sender, DataGridViewCellEventArgs e) 165 { 166 txtName.Text = dgvManager.CurrentRow.Cells[1].Value.ToString(); 167 txtGender.Text = dgvManager.CurrentRow.Cells[2].Value.ToString(); 168 txtAge.Text = dgvManager.CurrentRow.Cells[3].Value.ToString(); 169 txtAddress.Text = dgvManager.CurrentRow.Cells[4].Value.ToString(); 170 txtPhone.Text = dgvManager.CurrentRow.Cells[5].Value.ToString(); 171 } 172 173 string selectedValue; 174 //事件索引改变时触发 175 private void cmbforfieldSelecting_SelectedIndexChanged(object sender, EventArgs e) 176 { 177 string strSelected = cmbforfieldSelecting.Text; 178 switch (strSelected) 179 { 180 case "全局搜索": 181 selectedValue = "全局搜索"; 182 break; 183 case "编号": 184 selectedValue="vid"; 185 break; 186 case "名字": 187 selectedValue = "vname"; 188 break; 189 case "性别": 190 selectedValue = "vgender"; 191 break; 192 case "年龄": 193 selectedValue = "vage"; 194 break; 195 case "地址": 196 selectedValue = "vaddress"; 197 break; 198 case "电话": 199 selectedValue = "vphone"; 200 break; 201 default: 202 selectedValue = "全局搜索"; 203 break; 204 } 205 } 206 207 private void txtDataforQuery_TextChanged(object sender, EventArgs e) 208 { 209 string sql = ""; 210 if (txtDataforQuery.Text.Trim() == "") 211 { 212 //执行查询语句 213 sql = "select * from VipInformation"; 214 } 215 else if (cmbforfieldSelecting.Text.Trim() == "全局搜索" || selectedValue == "全局搜索") 216 { 217 //全字段搜索 218 sql = "select * from VipInformation where vName like '%" + txtDataforQuery.Text.Trim() + "%' or vgender like '%" + txtDataforQuery.Text.Trim() + "%' or vage like '%" + txtDataforQuery.Text.Trim() + "%' or vaddress like '%" + txtDataforQuery.Text.Trim() + "%' or vphone like '%" + txtDataforQuery.Text.Trim() + "%'"; 219 } 220 else if (selectedValue == "vid" || selectedValue == "vname" || selectedValue == "vgender" || selectedValue == "vage" || selectedValue == "vaddress" || selectedValue == "vphone") 221 { 222 //通过相应的字段进行搜索 223 sql = "select * from VipInformation where " + selectedValue + " like '%" + txtDataforQuery.Text.Trim() + "%'"; 224 } 225 226 SqlConnection conn = new SqlConnection(connStr); 227 SqlCommand cmd = new SqlCommand(sql, conn); 228 conn.Open(); 229 DataTable dt = new DataTable(); 230 SqlDataAdapter sda = new SqlDataAdapter(cmd); 231 sda.Fill(dt); 232 dgvManager.DataSource = dt; 233 conn.Close(); 234 } 235 236 private void btnBack_Click(object sender, EventArgs e) 237 { 238 VIPMain vmain = new VIPMain(); 239 vmain.Show(); 240 this.Hide(); 241 } 242 243 private void btnClose_Click(object sender, EventArgs e) 244 { 245 VIPLog vpl = new VIPLog(); 246 vpl.GetExitTime(); 247 vpl.AddMsg(); 248 //彻底的退出 249 System.Environment.Exit(0); 250 } 251 252 253 } 254 }
日志查看(VIPLog.cs)：
详细代码如下：
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 using System.Windows.Forms; 10 using System.Data.SqlClient; 11 using System.Configuration; 12 13 namespace 会员管理系统 14 { 15 public partial class VIPLog : Form 16 { 17 public VIPLog() 18 { 19 InitializeComponent(); 20 } 21 //连接数据库字符串多次调用，放在外面省代码量 22 public static string connstr = ConfigurationManager.ConnectionStrings["str"].ToString(); 23 24 private void btnClose_Click(object sender, EventArgs e) 25 { 26 //获取当前时间 27 GetExitTime(); 28 //添加当前的用户信息到日志中 29 AddMsg(); 30 //退出程序 31 System.Environment.Exit(0); 32 } 33 34 private void btnBack_Click(object sender, EventArgs e) 35 { 36 VIPMain vm = new VIPMain(); 37 vm.Show(); 38 this.Hide(); 39 } 40 41 private void VIPLog_Load(object sender, EventArgs e) 42 { 43 //AddMsg(); 44 ShowMsg(); 45 } 46 //写一个窗体加载自动从数据库获取数据并显示在datagridview的方法 47 public void ShowMsg() 48 { 49 //连接数据库对象 50 SqlConnection conn = new SqlConnection(connstr); 51 string sql = "select [id] 编号,vName 名字,situation 状态,[time] 登录或退出时间,vtype 权限 from [log]"; 52 //操作数据库对象 53 SqlCommand cmd = new SqlCommand(sql, conn); 54 //创建表对象 55 DataTable dt = new DataTable(); 56 //创建数据库填充操作对象(语句) 57 SqlDataAdapter sda = new SqlDataAdapter(cmd); 58 //把数据填充进dt表中 59 sda.Fill(dt); 60 //指定dgvManager控件的数据源:dt 61 dgvLog.DataSource = dt; 62 } 63 //写一个窗体加载(或者退出)自动添加当前用户登录信息进数据库的方法 64 public void AddMsg() 65 { 66 //连接数据库对象 67 SqlConnection conn = new SqlConnection(connstr); 68 69 string sql = string.Format("insert into [Log](vName,situation,time) values('{0}','{1}','{2}')",VIPLogin.uid,VIPLogin.situation,VIPLogin.time); 70 //操作数据库对象 71 SqlCommand cmd = new SqlCommand(sql,conn); 72 //打开数据库连接 73 conn.Open(); 74 //执行操作数据库对象并返回受影响条数 75 int n = cmd.ExecuteNonQuery(); 76 //关闭数据库连接 77 conn.Close(); 78 } 79 //写一个获取当前系统时间的方法(退出时间) 80 //退出时顺手修改登录状态 81 public void GetExitTime() 82 { 83 VIPLogin.time = DateTime.Now; 84 VIPLogin.situation = "退出"; 85 } 86 87 //日志查询 88 private void btnLogQuery_Click(object sender, EventArgs e) 89 { 90 //初始化 showSql语句 91 string sql = ""; 92 //如果文本框txtQuery 输入的内容为空值 93 if (txtLogQuery.Text.Trim() == "") 94 { 95 //显示所有数据 96 sql = "select * from [log]"; 97 } 98 else 99 { 100 //否则 进行 全局搜索 将文本框里的内容与 各字段 进行 匹配 101 sql = "select * from [Log] where [id] like '%" + txtLogQuery.Text.Trim() + "%' or vname like '%" + txtLogQuery.Text.Trim() + "%' or situation like '%" + txtLogQuery.Text.Trim() + "%' or [time] like '%" + txtLogQuery.Text.Trim() + "%' or vtype like '%" + txtLogQuery.Text.Trim() + "%'"; 102 } 103 SqlConnection conn = new SqlConnection(connstr); 104 SqlCommand cmd = new SqlCommand(sql, conn); 105 conn.Open(); 106 DataTable dt = new DataTable(); 107 SqlDataAdapter sda = new SqlDataAdapter(cmd); 108 sda.Fill(dt); 109 dgvLog.DataSource = dt; 110 } 111 112 } 113 }
