C#窗体简单增删改查
2021-01-17 21:16
标签：man command des view dataset build eve color ESS 数据库为sqlserver，结构如下： 数据库数据 开发工具为visual studio2013 首先先拖动控件来搭建出简单的页面如下，其中增删改查按钮这些控件都在toolstripe中，下面是DataGridView控件以显示数据库中数据 设置控件属性达到如下效果。 结构： 源代码： 连接数据库语句DBhelper.cs：(注意添加using System.Data.SqlClient;) 主页面Form1.cs 编辑页面Edit.cs 页面： 运行结果如下： C#窗体简单增删改查 标签：man command des view dataset build eve color ESS 原文地址：https://www.cnblogs.com/qq1793033075/p/12188268.html
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace xueshengchengjiguanli
{
class DBhelper
{
private string connString ="server=.;database=student1;user=sa;pwd=root;";//连接数据库自己的用户名密码
private SqlConnection connection;
public SqlConnection Connection
{
get
{
if (connection == null)
{
connection = new SqlConnection(connString);
}
return connection;
}
}
//打开数据库连接
public void OpenConnection()
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
else if (Connection.State == ConnectionState.Broken)
{
Connection.Close();
Connection.Open();
}
}
//关闭数据库
public void CloseConnection()
{
if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
{
Connection.Close();
}
}
}
}
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Drawing;
7 using System.Linq;
8 using System.Text;
9 using System.Threading.Tasks;
10 using System.Windows.Forms;
11
12 namespace xueshengchengjiguanli
13 {
14 public partial class Form1 : Form
15 {
16 public Form1()
17 {
18 InitializeComponent();
19 }
20 DataSet ds = new DataSet();
21 //获取数据方法
22 private void GetDB()
23 {
24 ds = new DataSet();
25 DBhelper dbhelper = new DBhelper();
26 try
27 {
28 string sql = @"Select Sno 学号,Sname 姓名,Sclass 班级,SChinese 语文,SMath 数学,SEnglish 英语,SChinese+SMath+SEnglish 总分,
29 (SChinese+SMath+SEnglish)/3 平均分 from Score order by SChinese+SMath+SEnglish desc";
30 SqlDataAdapter adapter = new SqlDataAdapter(sql, dbhelper.Connection);
31 adapter.Fill(ds, "Score");
32 this.dgv.DataSource = this.ds.Tables["Score"];
33 }
34 catch (Exception)
35 {
36
37 MessageBox.Show("数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
38 }
39 }
40
41 //退出按钮被点击
42 private void tsbtnExit_Click(object sender, EventArgs e)
43 {
44 this.Close();
45 }
46 //窗体加载事件
47 private void Form1_Load(object sender, EventArgs e)
48 {
49 GetDB();
50 }
51 //添加按钮被点击
52 private void tsbtnIn_Click(object sender, EventArgs e)
53 {
54 Edit ed = new Edit();
55 ed.ShowDialog();
56 GetDB();
57 }
58 //修改按钮被点击
59 private void tsbtnUpdate_Click(object sender, EventArgs e)
60 {
61 Edit ed = new Edit();
62 ed.Sno = Convert.ToInt32(this.dgv.SelectedCells[0].Value);
63 ed.ShowDialog();
64 GetDB();
65 }
66 //删除按钮被点击
67 private void tsbtnDelete_Click(object sender, EventArgs e)
68 {
69 Delete();
70 }
71 //删除方法
72 private void Delete()
73 {
74
75 if (this.dgv.CurrentRow != null)
76 {
77 DialogResult dr = MessageBox.Show("确定要删除：" + dgv.CurrentRow.Cells[1].Value +"相关成绩信息", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
78 if (dr == DialogResult.OK)
79 {
80 DBhelper helper = new DBhelper();
81 try
82 {
83 //sql语句
84 StringBuilder sb = new StringBuilder();
85 sb.AppendFormat("delete from Score where Sno={0}", Convert.ToInt32(dgv.CurrentRow.Cells[0].Value));
86 //执行工具
87 SqlCommand cmd = new SqlCommand(sb.ToString(), helper.Connection);
88 //打开数据库连接
89 helper.OpenConnection();
90 //执行
91 int result = cmd.ExecuteNonQuery();
92 if (result == 1)
93 {
94 MessageBox.Show("删除成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
95 //重新绑定dgv
96 this.GetDB();
97 }
98 }
99 catch (Exception)
100 {
101
102 MessageBox.Show("数据库操作失败", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
103 }
104 finally
105 {
106 helper.CloseConnection();
107 }
108 }
109 }
110
111 }
112 //查询按钮被点击
113 private void toolStripButton1_Click(object sender, EventArgs e)
114 {
115 this.Search();
116 }
//查询方法
117 private void Search()
118 {
119 ds = new DataSet();
120 DBhelper dbHelper = new DBhelper();
121 try
122 {
123 string strSql = @"Select Sno 学号,Sname 姓名,Sclass 班级,SChinese 语文,SMath 数学,SEnglish 英语,SChinese+SMath+SEnglish 总分,
124 (SChinese+SMath+SEnglish)/3 平均分 from Score
125 where 1=1";
126 if (toolStripTextBox1.Text.Trim() != null && toolStripTextBox1.Text.Trim().Length > 0)
127 {
128 strSql += " and Sname like ‘%" + toolStripTextBox1.Text.Trim() + "%‘";
129 }
130 if (toolStripTextBox2.Text.Trim() != null && toolStripTextBox2.Text.Trim().Length > 0)
131 {
132 strSql += " and Sclass like ‘%" + toolStripTextBox2.Text.Trim() + "%‘";
133 }
134 strSql += "order by SChinese+SMath+SEnglish desc";
135 SqlDataAdapter adapter = new SqlDataAdapter(strSql, dbHelper.Connection);
136 adapter.Fill(ds, "score");
137 this.dgv.DataSource = this.ds.Tables["score"];
138 }
139 catch (Exception)
140 {
141 MessageBox.Show("数据库操作错误！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
142 }
143 }
144
145
146 }
147 }
1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Drawing;
7 using System.Linq;
8 using System.Text;
9 using System.Threading.Tasks;
10 using System.Windows.Forms;
11
12 namespace xueshengchengjiguanli
13 {
14 public partial class Edit : Form
15 {
16 //变量
17 public int Sno = -1;
18 DataSet ds = new DataSet();
19 public Edit()
20 {
21 InitializeComponent();
22 }
23 //窗体加载
24 private void Edit_Load(object sender, EventArgs e)
25 {
26 if (Sno == -1)//没有被选定的行数
27 {
28
29 }
30 else//修改
31 {
32 GetInfo();
33 this.btnSave.Text = "修改";
34 }
35 }
36 //保存按钮
37 private void btnSave_Click(object sender, EventArgs e)
38 {
39 if (CheckItem())
40 {
41 if (this.Sno == -1)//新增
42 {
43 if (CheckSnoExit())
44 {
45 InsertDB();
46 }
47 }
48 else//更新
49 {
50 UpdateScore();
51 }
52 }
53 }
54 //非空验证
55 private bool CheckItem()
56 {
57 bool checkValue = true;
58 if (this.textBox1.Text.Trim().Length == 0)
59 {
60 MessageBox.Show("学号不能为空", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
61 checkValue = false;
62 this.textBox1.Text = " ";
63 }
64 return checkValue;
65 }
66 //增加方法
67 //查重
68 private bool CheckSnoExit()
69 {
70 bool exit = true;
71 DBhelper dbhelper = new DBhelper();
72 try
73 {
74 StringBuilder sb = new StringBuilder();
75 sb.AppendFormat("select * from Score where Sno=‘{0}‘", textBox1.Text.Trim());
76 SqlCommand cmd = new SqlCommand(sb.ToString(), dbhelper.Connection);
77 dbhelper.OpenConnection();
78 SqlDataReader reader = cmd.ExecuteReader();
79 if (reader.Read())
80 {
81 MessageBox.Show("该学号已存在", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
82 exit = false;
83 }
84 reader.Close();
85 }
86 catch (Exception)
87 {
88 MessageBox.Show("数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
89 }
90 finally
91 {
92 dbhelper.CloseConnection();
93 }
94 return exit;
95 }
96 //执行增加
97 private void InsertDB()
98 {
99 DBhelper helper = new DBhelper();
100 try
101 {
102 //SQL语句
103 StringBuilder sb = new StringBuilder();
104 sb.AppendLine("insert into Score");
105 sb.AppendFormat("values(‘{0}‘,‘{1}‘,‘{2}‘,‘{3}‘,‘{4}‘,‘{5}‘)", textBox1.Text.Trim(),textBox2.Text.Trim(),comboBox1.Text.Trim(),textBox4.Text.Trim(),textBox5.Text.Trim(),textBox6.Text.Trim());
106 //执行工具
107 SqlCommand cmd = new SqlCommand(sb.ToString(), helper.Connection);
108 //打开连接
109 helper.OpenConnection();
110 //执行
111 int result = cmd.ExecuteNonQuery();
112 if (result > 0)
113 {
114 MessageBox.Show("添加成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
115 this.Close();
116 }
117 }
118 catch (Exception)
119 {
120 MessageBox.Show("添加数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
121 }
122 finally
123 {
124 helper.CloseConnection();
125 }
126 }
127
128 //通过ID查找类别
129 private void GetInfo()
130 {
131 DBhelper dbhelper = new DBhelper();
132 try
133 {
134 //SQL语句
135 StringBuilder sb = new StringBuilder();
136 sb.AppendLine("select Sno,Sname,Sclass,SChinese,SMath,SEnglish");
137 sb.AppendLine("from Score");
138 sb.AppendFormat("where Sno={0}", Sno);
139 //执行工具
140 SqlCommand cmd = new SqlCommand(sb.ToString(), dbhelper.Connection);
141 //打开连接
142 dbhelper.OpenConnection();
143 //执行
144 SqlDataReader reader = cmd.ExecuteReader();
145 if (reader.Read())
146 {
147 textBox1.Text = reader["Sno"].ToString();
148 textBox2.Text = reader["Sname"].ToString();
149 comboBox1.Text = reader["Sclass"].ToString();
150 textBox4.Text = reader["SChinese"].ToString();
151 textBox5.Text = reader["SMath"].ToString();
152 textBox6.Text = reader["SEnglish"].ToString();
153 }
154 reader.Close();
155 }
156 catch (Exception)
157 {
158 MessageBox.Show("操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
159 }
160 finally
161 {
162 dbhelper.CloseConnection();
163 }
164
165 }
166 //修改
167 private void UpdateScore()
168 {
169 DBhelper helper = new DBhelper();
170 try
171 {
172 StringBuilder sql = new StringBuilder();
173 //修改数据库语句
174 sql.AppendLine("update Score");
175 sql.AppendFormat("set Sname=‘{0}‘,Sclass=‘{1}‘,SChinese=‘{2}‘,SMath=‘{3}‘,SEnglish=‘{4}‘", textBox2.Text.Trim(), comboBox1.Text.Trim(), textBox4.Text.Trim(), textBox5.Text.Trim(), textBox6.Text.Trim());
176 sql.AppendFormat("where Sno={0}", Sno);
177 //执行工具
178 SqlCommand cmd = new SqlCommand(sql.ToString(), helper.Connection);
179 //打开数据库连接
180 helper.OpenConnection();
181 //执行
182 int result = cmd.ExecuteNonQuery();
183 //判断
184 if (result == 1)
185 {
186
187 MessageBox.Show("修改成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
188 this.Close();
189 }
190 else
191 {
192
193 MessageBox.Show("修改失败", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
194 }
195 }
196 catch (Exception)
197 {
198
199 MessageBox.Show("修改数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
200 }
201 finally
202 {
203 helper.CloseConnection();
204 }
205
206 }
207 //取消按钮
208 private void button2_Click(object sender, EventArgs e)
209 {
210 this.Close();
211 }
212 }
213 }
上一篇：C# 泛型（Generic）