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
