c#连接数据库的增删改查实例

本次实现的是visual studio 的窗体应用程序+sql server的增删改查操作。

1.首先看一下数据库的表结构，比较简单，只有一个表

 2.看一下项目的目录，只有标注的三个文件是需要修改的。

 

3.然后看一下界面的实现，只有以下两个界面，Form1这个界面的上半部分是一个ToolStrip,下面的部分是DataGridView。Edit界面就是一些label,textBox以及button的组合，直接通过拖拽建议相应的界面即可。建立完页面后注意要修改相应插件的名称，就如第三张图所示，第三张图对应的是第一张图的DataGridView的名称。其他的插件的名称可以在源代码中找对应的，比较容易。

 

 

 4.具体代码实现

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 zengshangaicha
 13 {
 14     public partial class Form1 : Form
 15     {
 16         public Form1()
 17         {
 18             InitializeComponent();
 19         }
 20 
 21         DataSet ds = new DataSet();
 22         //获取数据方法
 23         private void GetDB()
 24         {
 25            
 26             ds = new DataSet();
 27             DBhelper dbhelper = new DBhelper();
 28             try
 29             {
 30                 string sql = @"Select Sno 学号,Sname 姓名,Sclass 班级,SChinese 语文,SMath 数学,SEnglish 英语,SChinese+SMath+SEnglish 总分,
 31                                   (SChinese+SMath+SEnglish)/3 平均分 from Score order by SChinese+SMath+SEnglish desc";
 32                 SqlDataAdapter adapter = new SqlDataAdapter(sql, dbhelper.Connection);
 33                 adapter.Fill(ds, "Score");
 34                 this.dgv.DataSource = this.ds.Tables["Score"];
 35             }
 36             catch (Exception)
 37             {
 38 
 39                 MessageBox.Show("数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 40             }
 41         }
 42 
 43         //退出按钮被点击
 44         private void tsbtnExit_Click(object sender, EventArgs e)
 45         {
 46             this.Close();
 47         }
 48         //窗体加载事件
 49         private void Form1_Load(object sender, EventArgs e)
 50         {
 51             GetDB();
 52         }
 53         //添加按钮被点击
 54         private void tsbtnIn_Click(object sender, EventArgs e)
 55         {
 56             Edit ed = new Edit();
 57             ed.ShowDialog();
 58             GetDB();
 59         }
 60         //修改按钮被点击
 61         private void tsbtnUpdate_Click(object sender, EventArgs e)
 62         {
 63             Edit ed = new Edit();
 64             ed.Sno = Convert.ToInt32(this.dgv.SelectedCells[0].Value);
 65             ed.ShowDialog();
 66             GetDB();
 67         }
 68         //删除按钮被点击
 69         private void tsbtnDelete_Click(object sender, EventArgs e)
 70         {
 71             Delete();
 72         }
 73         //删除方法
 74         private void Delete()
 75         {
 76 
 77             if (this.dgv.CurrentRow != null)
 78             {
 79                 DialogResult dr = MessageBox.Show("确定要删除：" + dgv.CurrentRow.Cells[1].Value + "相关成绩信息", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
 80                 if (dr == DialogResult.OK)
 81                 {
 82                     DBhelper helper = new DBhelper();
 83                     try
 84                     {
 85                         //sql语句
 86                         StringBuilder sb = new StringBuilder();
 87                         sb.AppendFormat("delete from Score where Sno={0}", Convert.ToInt32(dgv.CurrentRow.Cells[0].Value));
 88                         //执行工具
 89                         SqlCommand cmd = new SqlCommand(sb.ToString(), helper.Connection);
 90                         //打开数据库连接
 91                         helper.OpenConnection();
 92                         //执行
 93                         int result = cmd.ExecuteNonQuery();
 94                         if (result == 1)
 95                         {
 96                             MessageBox.Show("删除成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 97                             //重新绑定dgv
 98                             this.GetDB();
 99                         }
100                     }
101                     catch (Exception)
102                     {
103 
104                         MessageBox.Show("数据库操作失败", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
105                     }
106                     finally
107                     {
108                         helper.CloseConnection();
109                     }
110                 }
111             }
112 
113         }
114         //查询按钮被点击
115         private void toolStripButton1_Click(object sender, EventArgs e)
116         {
117             this.Search();
118         }     //查询方法
119         private void Search()
120         {
121             ds = new DataSet();
122             DBhelper dbHelper = new DBhelper();
123             try
124             {
125                 string strSql = @"Select Sno 学号,Sname 姓名,Sclass 班级,SChinese 语文,SMath 数学,SEnglish 英语,SChinese+SMath+SEnglish 总分,
126                                   (SChinese+SMath+SEnglish)/3 平均分 from Score
127                                  where 1=1";
128                 if (toolStripTextBox1.Text.Trim() != null && toolStripTextBox1.Text.Trim().Length > 0)
129                 {
130                     strSql += " and Sname like '%" + toolStripTextBox1.Text.Trim() + "%'";
131                 }
132                 if (toolStripTextBox2.Text.Trim() != null && toolStripTextBox2.Text.Trim().Length > 0)
133                 {
134                     strSql += " and Sclass like '%" + toolStripTextBox2.Text.Trim() + "%'";
135                 }
136                 strSql += "order by SChinese+SMath+SEnglish desc";
137                 SqlDataAdapter adapter = new SqlDataAdapter(strSql, dbHelper.Connection);
138                 adapter.Fill(ds, "score");
139                 this.dgv.DataSource = this.ds.Tables["score"];
140             }
141             catch (Exception)
142             {
143                 MessageBox.Show("数据库操作错误！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
144             }
145         }
146 
147         
148     }
149 }

Form1

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 zengshangaicha
 13 {
 14     public partial class Edit : Form
 15     {
 16         public int Sno = -1;
 17         DataSet ds = new DataSet();
 18         public Edit()
 19         {
 20             InitializeComponent();
 21         }
 22         //窗体加载
 23         private void Edit_Load(object sender, EventArgs e)
 24         {
 25             if (Sno == -1)//没有被选定的行数
 26             {
 27 
 28             }
 29             else//修改
 30             {
 31                 GetInfo();
 32                 this.btnSave.Text = "修改";
 33             }
 34         }
 35         //保存按钮
 36         private void btnSave_Click(object sender, EventArgs e)
 37         {
 38             if (CheckItem())
 39             {
 40                 if (this.Sno == -1)//新增
 41                 {
 42                     if (CheckSnoExit())
 43                     {
 44                         InsertDB();
 45                     }
 46                 }
 47                 else//更新
 48                 {
 49                     UpdateScore();
 50                 }
 51             }
 52         }
 53         //非空验证
 54         private bool CheckItem()
 55         {
 56             bool checkValue = true;
 57             if (this.textBox1.Text.Trim().Length == 0)
 58             {
 59                 MessageBox.Show("学号不能为空", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 60                 checkValue = false;
 61                 this.textBox1.Text = " ";
 62             }
 63             return checkValue;
 64         }
 65         //增加方法
 66         //查重
 67         private bool CheckSnoExit()
 68         {
 69             bool exit = true;
 70             DBhelper dbhelper = new DBhelper();
 71             try
 72             {
 73                 StringBuilder sb = new StringBuilder();
 74                 sb.AppendFormat("select * from Score where Sno='{0}'", textBox1.Text.Trim());
 75                 SqlCommand cmd = new SqlCommand(sb.ToString(), dbhelper.Connection);
 76                 dbhelper.OpenConnection();
 77                 SqlDataReader reader = cmd.ExecuteReader();
 78                 if (reader.Read())
 79                 {
 80                     MessageBox.Show("该学号已存在", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 81                     exit = false;
 82                 }
 83                 reader.Close();
 84             }
 85             catch (Exception)
 86             {
 87                 MessageBox.Show("数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 88             }
 89             finally
 90             {
 91                 dbhelper.CloseConnection();
 92             }
 93             return exit;
 94         }
 95         //执行增加
 96         private void InsertDB()
 97         {
 98             DBhelper helper = new DBhelper();
 99             try
100             {
101                 //SQL语句
102                 StringBuilder sb = new StringBuilder();
103                 sb.AppendLine("insert into Score");
104                 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());
105                 //执行工具
106                 SqlCommand cmd = new SqlCommand(sb.ToString(), helper.Connection);
107                 //打开连接
108                 helper.OpenConnection();
109                 //执行
110                 int result = cmd.ExecuteNonQuery();
111                 if (result > 0)
112                 {
113                     MessageBox.Show("添加成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
114                     this.Close();
115                 }
116             }
117             catch (Exception)
118             {
119                 MessageBox.Show("添加数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
120             }
121             finally
122             {
123                 helper.CloseConnection();
124             }
125         }
126 
127         //通过ID查找类别
128         private void GetInfo()
129         {
130             DBhelper dbhelper = new DBhelper();
131             try
132             {
133                 //SQL语句
134                 StringBuilder sb = new StringBuilder();
135                 sb.AppendLine("select Sno,Sname,Sclass,SChinese,SMath,SEnglish");
136                 sb.AppendLine("from Score");
137                 sb.AppendFormat("where Sno={0}", Sno);
138                 //执行工具
139                 SqlCommand cmd = new SqlCommand(sb.ToString(), dbhelper.Connection);
140                 //打开连接
141                 dbhelper.OpenConnection();
142                 //执行
143                 SqlDataReader reader = cmd.ExecuteReader();
144                 if (reader.Read())
145                 {
146                     textBox1.Text = reader["Sno"].ToString();
147                     textBox2.Text = reader["Sname"].ToString();
148                     comboBox1.Text = reader["Sclass"].ToString();
149                     textBox4.Text = reader["SChinese"].ToString();
150                     textBox5.Text = reader["SMath"].ToString();
151                     textBox6.Text = reader["SEnglish"].ToString();
152                 }
153                 reader.Close();
154             }
155             catch (Exception)
156             {
157                 MessageBox.Show("操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
158             }
159             finally
160             {
161                 dbhelper.CloseConnection();
162             }
163 
164         }
165         //修改
166         private void UpdateScore()
167         {
168             DBhelper helper = new DBhelper();
169             try
170             {
171                 StringBuilder sql = new StringBuilder();
172                 //修改数据库语句
173                 sql.AppendLine("update Score");
174                 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());
175                 sql.AppendFormat("where Sno={0}", Sno);
176                 //执行工具
177                 SqlCommand cmd = new SqlCommand(sql.ToString(), helper.Connection);
178                 //打开数据库连接
179                 helper.OpenConnection();
180                 //执行
181                 int result = cmd.ExecuteNonQuery();
182                 //判断
183                 if (result == 1)
184                 {
185 
186                     MessageBox.Show("修改成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
187                     this.Close();
188                 }
189                 else
190                 {
191 
192                     MessageBox.Show("修改失败", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
193                 }
194             }
195             catch (Exception)
196             {
197 
198                 MessageBox.Show("修改数据库操作错误", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
199             }
200             finally
201             {
202                 helper.CloseConnection();
203             }
204 
205         }
206         //取消按钮
207         private void button2_Click(object sender, EventArgs e)
208         {
209             this.Close();
210         }
211 
212        
213     }
214 }

Edit

1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Threading.Tasks;
 6 using System.Data;
 7 using System.Data.SqlClient;
 8 using System.Windows.Forms;
 9 
10 namespace zengshangaicha
11 {
12     class DBhelper
13     {
14        
15         private string connString = "server=.;database=student1;user=sa;pwd=000000;";//连接数据库自己的用户名密码
16         private SqlConnection connection;
17 
18         public SqlConnection Connection
19         {
20             get
21             {
22                 if (connection == null)
23                 {
24                    
25                     connection = new SqlConnection(connString);
26                 }
27                 return connection;
28             }
29 
30         }
31         //打开数据库连接
32         public void OpenConnection()
33         {
34             if (Connection.State == ConnectionState.Closed)
35             {
36                
37                 Connection.Open();
38             }
39             else if (Connection.State == ConnectionState.Broken)
40             {
41                 Connection.Close();
42                 Connection.Open();
43             }
44         }
45         //关闭数据库
46         public void CloseConnection()
47         {
48             if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
49             {
50                 Connection.Close();
51             }
52         }
53     }
54 }

DBhelper

4.在复制代码的时候，需要注意的是，在下面这张图里面的点击事件是不会自动添加的，需要自己手动写一下，具体格式如第二张图所示。

 

至此，增删改查完毕。

以下是原文链接：https://www.cnblogs.com/qq1793033075/p/12188268.html