WinForm之窗体应用程序
基本简单数据库操作(增删改查)
1 using System;
2 using System.Collections.Generic;
3 using System.Windows.Forms;
4
5 namespace DataBaseOperation
6 {
7 static class Program
8 {
9 /// <summary>
10 /// 应用程序的主入口点。
11 /// </summary>
12 [STAThread]
13 static void Main()
14 {
15 Application.EnableVisualStyles();
16 Application.SetCompatibleTextRenderingDefault(false);
17 Application.Run(new frmMain());
18 }
19 }
20 }
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Text;
7 using System.Windows.Forms;
8
9 namespace DataBaseOperation
10 {
11 public partial class frmMain : Form
12 {
13 public frmMain()
14 {
15 InitializeComponent();
16 }
17
18 private void btnSelect_Click(object sender, EventArgs e)
19 {
20 frmSelect fs = new frmSelect();
21 fs.ShowDialog();
22 }
23
24 private void btnInsert_Click(object sender, EventArgs e)
25 {
26 frmInsert fi = new frmInsert();
27 fi.ShowDialog();
28 }
29
30 private void btnUpdate_Click(object sender, EventArgs e)
31 {
32 frmUpdate fu = new frmUpdate();
33 fu.ShowDialog();
34 }
35
36 private void btnDelete_Click(object sender, EventArgs e)
37 {
38 frmDelete fd = new frmDelete();
39 fd.ShowDialog();
40 }
41
42 private void frmMain_Load(object sender, EventArgs e)
43 {
44
45 this.Left = Screen.PrimaryScreen.WorkingArea.Width - this.Width;
46 this.Top = Screen.PrimaryScreen.WorkingArea.Height - this.Height;
47 }
48 }
49 }
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Text;
7 using System.Windows.Forms;
8 using System.Data.SqlClient;//
9
10 namespace DataBaseOperation
11 {
12 public partial class frmDelete : Form
13 {
14 public frmDelete()
15 {
16 InitializeComponent();
17 }
18
19 SqlDataAdapter sda;//
20 DataSet ds = new DataSet();//
21
22 private void frmDelete_Load(object sender, EventArgs e)
23 {
24 //窗体加载时查询表中全部信息
25
26 //1.
27 string sql = "select sid,sname,ssex,saddress,semail from students";
28
29 //2.
30 sda = new SqlDataAdapter(sql, DBHelper.connection);
31
32 int result = sda.Fill(ds);
33
34 if (result > 0)
35 {
36 this.dataGridView1.DataSource = ds.Tables[0];
37 }
38 else
39 {
40 MessageBox.Show("表中无信息");
41 }
42
43 }
44
45 private void 删除选中行ToolStripMenuItem_Click(object sender, EventArgs e)
46 {
47 if (this.dataGridView1.SelectedRows.Count > 0)
48 {
49 //1.
50 string id = this.dataGridView1.SelectedRows[0].Cells["sid"].Value.ToString();
51
52 //2.
53 string sql = string.Format("delete from students where sid={0}", id);
54
55 //3.
56
57 try
58 {
59 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
60 DBHelper.connection.Open();
61 int result = command.ExecuteNonQuery();
62 if (result > 0)
63 {
64 MessageBox.Show("成功删除该行信息!");
65 }
66 else
67 {
68 MessageBox.Show("操作失败!");
69 }
70 }
71 catch (Exception ex)
72 {
73
74 MessageBox.Show(ex.Message);
75 }
76 finally
77 {
78 DBHelper.connection.Close();
79 }
80 }
81 else
82 {
83 MessageBox.Show("请选中要删除的行");
84 }
85
86 //刷新控件中信息行
87 this.dataGridView1.Rows.Remove(this.dataGridView1.SelectedRows[0]);
88 }
89
90 private void btnSearchAll_Click(object sender, EventArgs e)
91 {
92 //1.清空ds中的表信息
93 ds.Tables.Clear();
94
95 //2.
96 int result = sda.Fill(ds);
97 if (result > 0)
98 {
99 this.dataGridView1.DataSource = ds.Tables[0];
100 }
101 else
102 {
103 MessageBox.Show("表中无信息");
104 }
105 }
106
107 private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
108 {
109
110 }
111
112 private void dataGridView1_MouseDoubleClick(object sender, MouseEventArgs e)
113 {
114
115 }
116
117 private void contextMenuStrip1_Opening(object sender, CancelEventArgs e)
118 {
119
120 }
121 }
122 }
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Text;
7 using System.Windows.Forms;
8 using System.Data.SqlClient;//
9
10 namespace DataBaseOperation
11 {
12 public partial class frmInsert : Form
13 {
14 public frmInsert()
15 {
16 InitializeComponent();
17 }
18
19 private void btnReset_Click(object sender, EventArgs e)
20 {
21 this.txtName.Text = "";
22 this.txtAddress.Text = "";
23 this.txtEmail.Text = "";
24 this.radMan.Checked = true;
25 this.txtName.Focus();
26 }
27
28 private void btnInsert_Click(object sender, EventArgs e)
29 {
30 //1.获取控件中用户输入的学员信息
31 string name = this.txtName.Text.Trim();
32 string sex;
33 if (this.radMan.Checked)
34 {
35 sex = "1";
36 }
37 else
38 {
39 sex = "0";
40 }
41 MessageBox.Show(sex);
42 string address = this.txtAddress.Text.Trim();
43 string email= this.txtEmail.Text.Trim();
44
45 //2.
46 string sql=string.Format("insert into students (sname,ssex,saddress,semail) values('{0}',{1},'{2}','{3}')",name,sex,address,email);
47
48 //3.
49 try
50 {
51 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
52 DBHelper.connection.Open();
53 int result = command.ExecuteNonQuery();
54 if (result > 0)
55 {
56 MessageBox.Show("成功添加一条学员信息");
57 }
58 else
59 {
60 MessageBox.Show("操作失败!");
61 }
62 }
63 catch (Exception ex)
64 {
65
66 MessageBox.Show(ex.Message);
67 }
68 finally
69 {
70 DBHelper.connection.Close();
71 }
72 }
73 }
74 }
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Text;
7 using System.Windows.Forms;
8 using System.Data.SqlClient;//
9
10 namespace DataBaseOperation
11 {
12 public partial class frmSelect : Form
13 {
14 public frmSelect()
15 {
16 InitializeComponent();
17 }
18
19 private void btnSearchName_Click(object sender, EventArgs e)
20 {
21 //根据学号查询学员姓名
22 string id = this.txtNum1.Text.Trim();
23 if (id != "")
24 {
25 string sql = string.Format("select sname from students where sid={0}", id);
26 try
27 {
28 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
29 DBHelper.connection.Open();
30 object name = command.ExecuteScalar();
31 if (name != null)
32 {
33 this.lblName.Text = "此学员的姓名为:" + name.ToString();
34 }
35 else
36 {
37 MessageBox.Show("查无此人!");
38 this.lblName.Text = "";
39 this.txtNum1.Text = "";
40 this.txtNum1.Focus();
41 }
42
43
44 }
45 catch (Exception ex)
46 {
47
48 MessageBox.Show(ex.Message);
49 }
50 finally
51 {
52 DBHelper.connection.Close();
53 }
54 }
55 else
56 {
57 MessageBox.Show("请输入学号!");
58 }
59 }
60
61 private void btnSearchStudentInfo_Click(object sender, EventArgs e)
62 {
63 //根据学号查询学员信息
64 string id = this.txtNum2.Text.Trim();
65 if (id != "")
66 {
67 string sql = string.Format("select sname,ssex,saddress,semail from students where sid={0}", id);
68 try
69 {
70 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
71 DBHelper.connection.Open();
72 SqlDataReader sdr = command.ExecuteReader();
73 if (sdr.Read())
74 {
75 this.txtName1.Text = sdr["sname"].ToString();
76 // MessageBox.Show("性别字段的值:"+sdr["ssex"].ToString());
77 if (sdr["ssex"].ToString().ToLower() == "true")
78 {
79 this.radMan1.Checked = true;
80 }
81 else
82 {
83 this.radWoman1.Checked = true;
84 }
85 this.txtAddress.Text = sdr["saddress"].ToString();
86 this.txtEmail.Text = sdr["semail"].ToString();
87 }
88 else
89 {
90 MessageBox.Show("查无此人!");
91 this.txtNum2.Text = "";
92 this.txtNum2.Focus();
93 this.txtName1.Text = "";
94 this.radMan1.Checked = true;
95 this.txtAddress.Text = "";
96 this.txtEmail.Text = "";
97 }
98
99 sdr.Close();
100
101
102 }
103 catch (Exception ex)
104 {
105
106 MessageBox.Show(ex.Message);
107 }
108 finally
109 {
110 DBHelper.connection.Close();
111 }
112 }
113 else
114 {
115 MessageBox.Show("请输入学号!");
116 }
117 }
118
119 DataSet ds = new DataSet();//创建数据集对象
120 SqlDataAdapter sda;//声明数据适配器
121
122 private void btnSearchBySex1_Click(object sender, EventArgs e)
123 {
124 //清空数据集中表信息
125 ds.Tables.Clear();
126
127 //根据性别查询学员信息
128 string sex;
129 if (this.radMan2.Checked)
130 {
131 sex = this.radMan2.Tag.ToString();
132 }
133 else
134 {
135 sex = this.radWoman2.Tag.ToString();
136 }
137 MessageBox.Show("性别的值为:" + sex);
138
139 string sql = string.Format("select sid,sname,ssex,saddress,semail from student where ssex={0}", sex);
140 //创建数据适配器对象
141 sda = new SqlDataAdapter(sql, DBHelper.connection);
142 int result = sda.Fill(ds);
143 if (result > 0)
144 {
145 this.dgvStudentInfo.DataSource = ds.Tables[0];
146 }
147 else
148 {
149 MessageBox.Show("无查询结果");
150 }
151
152
153 }
154
155 private void btnSearchBySex2_Click(object sender, EventArgs e)
156 {
157 //清空数据集中表信息
158 ds.Tables.Clear();
159
160 //根据性别查询学员信息
161 string sex;
162 if (this.cboSex1.Text != "")
163 {
164 if (this.cboSex1.Text == "男")
165 {
166 sex = "1";
167 }
168 else
169 {
170 sex = "0";
171 }
172 MessageBox.Show("性别的值为:" + sex);
173 }
174 else
175 {
176 MessageBox.Show("请选择性别");
177 return;
178 }
179
180
181 string sql = string.Format("select sid,sname,ssex,saddress,semail from students where ssex={0}", sex);
182 //创建数据适配器对象
183 sda = new SqlDataAdapter(sql, DBHelper.connection);
184 int result = sda.Fill(ds);
185 if (result > 0)
186 {
187 this.dgvStudentInfo.DataSource = ds.Tables[0];
188 }
189 else
190 {
191 MessageBox.Show("无查询结果");
192 }
193 }
194
195 private void btnSearchByName1_Click(object sender, EventArgs e)
196 {
197 //清空数据集中表信息
198 ds.Tables.Clear();
199
200 //根据学员姓名查询学员信息(模糊查询)
201 string name = this.txtName2.Text.Trim();
202 string sql = string.Format("select sid,sname,ssex,saddress,semail from students where sname like '%{0}%'", name);
203 //创建数据适配器对象
204 sda = new SqlDataAdapter(sql, DBHelper.connection);
205 int result = sda.Fill(ds);
206 if (result > 0)
207 {
208 this.dgvStudentInfo.DataSource = ds.Tables[0];
209 }
210 else
211 {
212 MessageBox.Show("无查询结果");
213 }
214
215 }
216
217 private void btnSearchBySex3_Click(object sender, EventArgs e)
218 {
219 //清空ListView中的项
220 this.lstStudentInfo.Items.Clear();
221
222 //根据性别查询学员信息
223 string sex;
224 if (this.radMan3.Checked)
225 {
226 sex = this.radMan3.Tag.ToString();
227 }
228 else
229 {
230 sex = this.radWoman3.Tag.ToString();
231 }
232 MessageBox.Show("性别的值为:" + sex);
233
234 string sql = string.Format("select sid,sname,ssex,saddress,semail from students where ssex={0}", sex);
235 try
236 {
237 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
238 DBHelper.connection.Open();
239 SqlDataReader sdr = command.ExecuteReader();
240 while (sdr.Read())
241 {
242 //1.
243 ListViewItem lvi = new ListViewItem(sdr["sid"].ToString());
244 //2.
245 if (sdr["ssex"].ToString().ToLower() == "true")
246 {
247 sex = "男";
248 }
249 else
250 {
251 sex = "女";
252 }
253 lvi.SubItems.AddRange(new string[] { sdr["sname"].ToString(), sex, sdr["saddress"].ToString(), sdr["semail"].ToString() });
254 //3.
255 this.lstStudentInfo.Items.Add(lvi);
256 }
257 //关闭sdr
258 sdr.Close();
259 }
260 catch (Exception ex)
261 {
262
263 MessageBox.Show(ex.Message);
264 }
265 finally
266 {
267 DBHelper.connection.Close();
268 }
269
270
271
272 }
273
274
275 private void btnSearchBySex4_Click(object sender, EventArgs e)
276 {
277 //清空ListView中的项
278 this.lstStudentInfo.Items.Clear();
279
280 //根据性别查询学员信息
281 string sex;
282 if (this.cboSex2.Text != "")
283 {
284 if (this.cboSex2.Text == "男")
285 {
286 sex = "1";
287 }
288 else
289 {
290 sex = "0";
291 }
292 MessageBox.Show("性别的值为:" + sex);
293 }
294 else
295 {
296 MessageBox.Show("请选择性别");
297 return;
298 }
299
300 string sql = string.Format("select sid,sname,ssex,saddress,semail from students where ssex={0}", sex);
301 try
302 {
303 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
304 DBHelper.connection.Open();
305 SqlDataReader sdr = command.ExecuteReader();
306 while (sdr.Read())
307 {
308 //1.
309 ListViewItem lvi = new ListViewItem(sdr["sid"].ToString());
310 //2.
311 if (sdr["ssex"].ToString().ToLower() == "true")
312 {
313 sex = "男";
314 }
315 else
316 {
317 sex = "女";
318 }
319 lvi.SubItems.AddRange(new string[] { sdr["sname"].ToString(), sex, sdr["saddress"].ToString(), sdr["semail"].ToString() });
320 //3.
321 this.lstStudentInfo.Items.Add(lvi);
322 }
323 //关闭sdr
324 sdr.Close();
325 }
326 catch (Exception ex)
327 {
328
329 MessageBox.Show(ex.Message);
330 }
331 finally
332 {
333 DBHelper.connection.Close();
334 }
335 }
336
337 private void btnSearchByName2_Click(object sender, EventArgs e)
338 {
339 //清空ListView中的项
340 this.lstStudentInfo.Items.Clear();
341
342 //根据学员姓名查询学员信息(模糊查询)
343 string name = this.txtName3.Text.Trim();
344 string sql = string.Format("select sid,sname,ssex,saddress,semail from students where sname like '%{0}%'", name);
345 try
346 {
347 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
348 DBHelper.connection.Open();
349 SqlDataReader sdr = command.ExecuteReader();
350 while (sdr.Read())
351 {
352 //1.
353 ListViewItem lvi = new ListViewItem(sdr["sid"].ToString());
354 //2.
355 string sex;
356 if (sdr["ssex"].ToString().ToLower() == "true")
357 {
358 sex = "男";
359 }
360 else
361 {
362 sex = "女";
363 }
364 lvi.SubItems.AddRange(new string[] { sdr["sname"].ToString(), sex, sdr["saddress"].ToString(), sdr["semail"].ToString() });
365 //3.将主键值写到lvi的Tag属性中
366 lvi.Tag = sdr["sid"].ToString();
367
368 //4.
369 this.lstStudentInfo.Items.Add(lvi);
370 }
371 //关闭sdr
372 sdr.Close();
373 }
374 catch (Exception ex)
375 {
376
377 MessageBox.Show(ex.Message);
378 }
379 finally
380 {
381 DBHelper.connection.Close();
382 }
383 }
384
385 private void dgvStudentInfo_CellClick(object sender, DataGridViewCellEventArgs e)
386 {
387 //单元格点击事件
388 if (this.dgvStudentInfo.SelectedRows.Count > 0)
389 {
390 string id = this.dgvStudentInfo.SelectedRows[0].Cells["sid"].Value.ToString();
391 MessageBox.Show(id);
392 }
393 }
394
395 private void lstStudentInfo_MouseClick(object sender, MouseEventArgs e)
396 {
397 //ListView控件点击事件
398 if (this.lstStudentInfo.SelectedItems.Count > 0)
399 {
400 string id = this.lstStudentInfo.SelectedItems[0].Tag.ToString();
401 MessageBox.Show(id);
402 }
403 }
404 }
405 }
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Text;
7 using System.Windows.Forms;
8 using System.Data.SqlClient;//
9
10 namespace DataBaseOperation
11 {
12 public partial class frmUpdate : Form
13 {
14 public frmUpdate()
15 {
16 InitializeComponent();
17 }
18
19 private void btnSearchStudentInfo_Click(object sender, EventArgs e)
20 {
21 //根据学号查询学员信息
22 string id = this.txtNum.Text.Trim();
23 if (id != "")
24 {
25 string sql = string.Format("select sname,ssex,saddress,semail from students where sid={0}", id);
26 try
27 {
28 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
29 DBHelper.connection.Open();
30 SqlDataReader sdr = command.ExecuteReader();
31 if (sdr.Read())
32 {
33 this.txtName.Text = sdr["sname"].ToString();
34 // MessageBox.Show("性别字段的值:"+sdr["ssex"].ToString());
35 if (sdr["ssex"].ToString().ToLower() == "true")
36 {
37 this.radMan.Checked = true;
38 }
39 else
40 {
41 this.radWoman.Checked = true;
42 }
43 this.txtAddress.Text = sdr["saddress"].ToString();
44 this.txtEmail.Text = sdr["semail"].ToString();
45
46 //激活或屏蔽窗体中部分控件
47 this.txtNum.Enabled = false;
48 this.txtName.Enabled = true;
49 this.txtAddress.Enabled = true;
50 this.txtEmail.Enabled = true;
51 this.radMan.Enabled = true;
52 this.radWoman.Enabled = true;
53 }
54 else
55 {
56 MessageBox.Show("查无此人!");
57 this.txtNum.Text = "";
58 this.txtNum.Focus();
59 this.txtName.Text = "";
60 this.radMan.Checked = true;
61 this.txtAddress.Text = "";
62 this.txtEmail.Text = "";
63 }
64
65 sdr.Close();
66
67
68 }
69 catch (Exception ex)
70 {
71
72 MessageBox.Show(ex.Message);
73 }
74 finally
75 {
76 DBHelper.connection.Close();
77 }
78 }
79 else
80 {
81 MessageBox.Show("请输入学号!");
82 }
83 }
84
85 private void btnUpdate_Click(object sender, EventArgs e)
86 {
87 //1.
88 string id = this.txtNum.Text.Trim();
89 string name = this.txtName.Text.Trim();
90 string sex;
91 if (this.radMan.Checked)
92 {
93 sex = "1";
94 }
95 else
96 {
97 sex = "0";
98 }
99 string address = this.txtAddress.Text.Trim();
100 string email = this.txtEmail.Text.Trim();
101
102 //2.
103 string sql = string.Format("update students set sname='{0}',ssex={1},saddress='{2}',semail='{3}' where sid={4}", name, sex, address, email, id);
104
105 //3.
106 try
107 {
108 SqlCommand command = new SqlCommand(sql, DBHelper.connection);
109 DBHelper.connection.Open();
110 int result = command.ExecuteNonQuery();
111 if (result > 0)
112 {
113 MessageBox.Show("更新完毕!");
114 //激活或屏蔽窗体中部分控件
115 this.txtNum.Enabled = true;
116 this.txtName.Enabled = false;
117 this.txtAddress.Enabled = false;
118 this.txtEmail.Enabled = false;
119 this.radMan.Enabled = false;
120 this.radWoman.Enabled = false;
121
122 }
123 else
124 {
125 MessageBox.Show("更新操作失败!");
126 }
127 }
128 catch (Exception ex)
129 {
130
131 MessageBox.Show(ex.Message);
132 }
133 finally
134 {
135 DBHelper.connection.Close();
136 }
137 }
138 }
139 }