本文实例为大家分享了C#实现学生模块的增删改查的具体代码,供大家参考,具体内容如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
|
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace text3_CRUD { public partial class Form1 : Form { //把连接数据库的字符串提取出来,就不用每次都要写,增加代码复用性 private string str = "data source=本地IP;initial catalog=数据库名;user ID=用户名;pwd=密码" ; public Form1() { InitializeComponent(); } private void TextBox5_TextChanged( object sender, EventArgs e) { } private void Form1_Load( object sender, EventArgs e) { } private void Label10_Click( object sender, EventArgs e) { } /// <summary> /// 添加学生信息档案 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ButAdd_Click( object sender, EventArgs e) { //获取各个文本框的数据 string name = txtname.Text; string sex = txtsex.Text; string college = txtcollege.Text; string id = txtid.Text; string grade = txtgrade.Text; string phone = txtphone.Text; string email = txtemail.Text; string qq = txtqq.Text; string room = txtroom.Text; using (var conn = new SqlConnection( this .str)) //定义一个数据库连接实例 { conn.Open(); //打开数据库 Form1 f = new Form1(); //实例化Form1窗体对象 if (f.Existence(id, conn)) //检查数据库 存不存在此条记录,存在则插入 { SqlParameter[] para = new SqlParameter[] //构建存储过程的输入参数 { new SqlParameter( "@name" ,name), new SqlParameter( "@sex" , sex), new SqlParameter( "@college" , college), new SqlParameter( "@id" , id), new SqlParameter( "@grade" , grade), new SqlParameter( "@phone" , phone), new SqlParameter( "@email" , email), new SqlParameter( "@qq" , qq), new SqlParameter( "@room" , room), }; string sql = "insert into Students values(@name, @sex, @college, @id, @grade, @phone, @email, @qq, @room);" ; //定义一个数据库操作指令集 SqlCommand com = new SqlCommand(sql, conn); //执行数据库操作指令 com.Parameters.AddRange(para); //將参数和命令对象的参数集合绑定 int result = ( int )com.ExecuteNonQuery(); //针对Connection执行的SQL语句,返回受影响的行数,result > 0则表示SQL语句执行成功 if (result > 0) { MessageBox.Show( "添加成功!" ); //弹窗显示“添加成功” this .Form1_Load_1( null , null ); //刷新数据 } else { MessageBox.Show( "添加失败!" ); } } else { MessageBox.Show( "数据已经存在!" ); } conn.Close(); //关闭数据库 //Application.Exit();//关闭整个应用程序 } } /// <summary> /// 根据ID值判断数据表Students中是否存在这个人,存在返回false,不存在返回true /// </summary> /// <param name="id"></param> /// <param name="conn"></param> /// <returns></returns> public bool Existence( string id, SqlConnection conn) { string txtStr = string .Format( "select id from Students where id = '{0}' " ,id); //定义一个数据库操作指令集 SqlDataAdapter sda = new SqlDataAdapter(txtStr, conn); //定义一个数据库适配器 DataSet ds = new DataSet(); //定义数据集合 sda.Fill(ds); //填充数据集合 DataTable dt = ds.Tables[0]; //將数据集合中的第一张表赋值给DataTable if (dt.Rows.Count > 0) //count > 0表示有数据 { return false ; } else { return true ; } } /// <summary> /// 对数据库进行的动态查询,不管用户掌握的信息有多少都可以查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BtnSelect_Click( object sender, EventArgs e) { //获取各个文本框的数据 string name = txtname.Text; string sex = txtsex.Text; string college = txtcollege.Text; string id = txtid.Text; string grade = txtgrade.Text; string phone = txtphone.Text; string email = txtemail.Text; string qq = txtqq.Text; string room = txtroom.Text; using (var conn = new SqlConnection( this .str)) //定义一个数据库连接实例 { conn.Open(); //打开数据库 StringBuilder sb = new StringBuilder(); //创建一个字符串变量 sb.Append( "select name, sex, college, id, grade,phone, email, qq, room from Students where 1=1" ); //判断用户有没有给出其它的查询条件,有则添加进sql语句 if (name != "" ) { sb.AppendFormat( " and name = '{0}'" , name); } if (sex != "" ) { sb.AppendFormat( " and sex = '{0}'" , sex); } if (college != "" ) { sb.AppendFormat( " and college = '{0}'" , college); } if (id != "" ) { sb.AppendFormat( " and id = '{0}'" , id); } if (grade != "" ) { sb.AppendFormat( " and grade = '{0}'" , grade); } if (phone != "" ) { sb.AppendFormat( " and phone = '{0}'" , phone); } if (email != "" ) { sb.AppendFormat( " and email = '{0}'" , email); } if (qq != "" ) { sb.AppendFormat( " and qq = '{0}'" , qq); } if (room != "" ) { sb.AppendFormat( " and room = '{0}'" , room); } string sql = sb.ToString(); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); //定义一个数据集合 adapter.Fill(ds); //填充数据集合 dataGridView1.DataSource = ds.Tables[0]; //把数据集合绑定到dataGridView上,dataGridView会以表格的形式显示出来 conn.Close(); //关闭数据库 } } /// <summary> /// 修改学生信息 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BtnUpdate_Click( object sender, EventArgs e) { //获取各个文本框的数据 string name = txtname.Text; string sex = txtsex.Text; string college = txtcollege.Text; string id = txtid.Text; string grade = txtgrade.Text; string phone = txtphone.Text; string email = txtemail.Text; string qq = txtqq.Text; string room = txtroom.Text; //构建存储过程的输入参数 SqlParameter[] para = new SqlParameter[] { new SqlParameter( "@name" ,name), new SqlParameter( "@sex" , sex), new SqlParameter( "@college" , college), new SqlParameter( "@id" , id), new SqlParameter( "@grade" , grade), new SqlParameter( "@phone" , phone), new SqlParameter( "@email" , email), new SqlParameter( "@qq" , qq), new SqlParameter( "@room" , room) }; using (var conn = new SqlConnection( this .str)) { conn.Open(); //打开数据库; string sql = "update Students set name = @name, sex = @sex, college = @college, id = @id, grade = @grade, phone = @phone, email = @email, qq = @qq, room = @room where id = @id" ; SqlCommand com = new SqlCommand(sql, conn); //执行数据库操作指令 com.Parameters.AddRange(para); //将参数和命令对象的参数集合绑定 int result = ( int )com.ExecuteNonQuery(); //查询返回的第一行第一列 if (result > 0) { MessageBox.Show( "修改成功!" ); this .Form1_Load_1( null , null ); //修改完数据后,重新刷新属性Form1窗口,以查看变化的内容 conn.Close(); //关闭数据库 } } //SqlDataAdapter sda = new SqlDataAdapter(); } /// <summary> /// 刷新DataGridView里的数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Form1_Load_1( object sender, EventArgs e) { using (var conn = new SqlConnection( this .str)) //定义一个数据库连接实例 { conn.Open(); //打开数据库 string sql = "select * from Students" ; //定义一个数据库操作指令 SqlDataAdapter sda = new SqlDataAdapter(sql, conn); //定义数据库适配器 DataSet ds = new DataSet(); //定义数据集合 sda.Fill(ds); //填充数据集合 dataGridView1.DataSource = ds.Tables[0]; conn.Close(); //关闭数据库 } } private void DataGridView1_CellClick( object sender, DataGridViewCellEventArgs e) { } /// <summary> /// 选中DataGridView的行,这一行的数据返回到对应的文本框 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void DataGridView1_CellClick_1( object sender, DataGridViewCellEventArgs e) { //SelectedRows[0] 获取用户选定行的集合(选中的第一行就是0,一次类推) //Cells["name"] 获取用于填充行的单元格集合(就是列) .Value就是它的值,最后ToString转字符串 txtname.Text = dataGridView1.SelectedRows[0].Cells[ "name" ].Value.ToString(); txtsex.Text = dataGridView1.SelectedRows[0].Cells[ "sex" ].Value.ToString(); txtcollege.Text = dataGridView1.SelectedRows[0].Cells[ "college" ].Value.ToString(); txtid.Text = dataGridView1.SelectedRows[0].Cells[ "id" ].Value.ToString(); txtgrade.Text = dataGridView1.SelectedRows[0].Cells[ "grade" ].Value.ToString(); txtphone.Text = dataGridView1.SelectedRows[0].Cells[ "phone" ].Value.ToString(); txtemail.Text = dataGridView1.SelectedRows[0].Cells[ "email" ].Value.ToString(); txtqq.Text = dataGridView1.SelectedRows[0].Cells[ "qq" ].Value.ToString(); txtroom.Text = dataGridView1.SelectedRows[0].Cells[ "room" ].Value.ToString(); } /// <summary> /// 删除某个学生的所有数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BtnDelete_Click( object sender, EventArgs e) { using (var conn = new SqlConnection( this .str)) //创建一个数据库连接实例 { conn.Open(); //连接数据库 string sql = string .Format( "delete from Students where id = '{0}'" , txtid.Text); //往数据库操作指令中传值 //如果传的值不是很多的话,就用这种方法;如果有很多就用SqlParameter[] SqlCommand com = new SqlCommand(sql, conn); //执行数据库删除指令 int result = ( int )com.ExecuteNonQuery(); //返回结果,result > 0则为修改成功 if (result > 0) { MessageBox.Show( "删除成功!" ); this .Form1_Load_1( null , null ); //刷新数据 conn.Close(); //关闭数据库 } } } /// <summary> /// 对文本框进行清空处理,方便重新输入下一个学生信息 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BtnClear_Click( object sender, EventArgs e) { txtname.Text = null ; txtsex.Text = null ; txtcollege.Text = null ; txtid.Text = null ; txtgrade.Text = null ; txtphone.Text = null ; txtemail.Text = null ; txtqq.Text = null ; txtroom.Text = null ; } } } |
Students表
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/kuguhuan/article/details/98478924