数据库操作类《SqlHelper》
一.背景
在看了一本书叫《Visual Studio 2010(C#)Windows数据库项目开发》后,觉得很多编程技术需要积累,因为一个简单的项目里包含的技术太多了,容易忘记。每次需要用到某个技术时,就免不了查阅资料,然后整理一堆操作后,发现浪费很多时间。
现在呢,由于这个书没有对数据库操作做一个公共类设计,每次访问数据库的时候都会造成冗余代码。为了解决这个问题,我查阅了很多网上资源,当然里面也有一丢丢自己的思考,总结成一个编程技术学习日记,以便日后自己翻阅。
二.设计
数据库的操作主要包括:查询,添加,删减,更新。目前只讨论“增删查改”的实现,数据库中的存储过程、视图、函数、触发器等知识点这里就不说了,后面在实现这个学生选课系统项目时再讨论吧。
0. 首先定义一个用于初始化SqlCommand对象的方法:
//初始化SqlCommand对象 public static void InitializeCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] paras) { if(conn.State != ConnectionState.Open()) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if(trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if(paras != null) foreach( SqlParameter item in paras) { cmd.Parameters.Add(item); } }
1. 查询
查询返回一个数据集DataSet或者数据表DataTable,通用型的数据库查询方法:
//查询数据,返回相应的数据表 public static DataTable GetDataTable(string connString, CommandType cmdType, string cmdText, params SqlParameter[] paras) { SqlCommand cmd = new SqlCommand(); using(SqlConnection conn = new SqlConnection(connString)) { InitializeCommand(cmd,conn,null,cmdType,cmdText,paras); using(SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); adapter.Fill(dt); cmd.Parameters.Clear(); return dt; } } }
查询返回第一列第一行的数据,即返回单个数据的查询方法:
//查询单个数据,返回第一列第一行的数据 public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] paras) { SqlCommand cmd = new SqlCommand(); using(SqlConnection conn = new SqlConnection(connString) { InitializeCommand(cmd,conn,null,cmdType,cmdText,paras); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return obj; } }
查询返回一个SqlDataReader数据读取器,当这个数据读取器在读取数据库数据的时候一直和数据库保持连接,并且一行一行地读取,此查询的方法:
//查询返回SqlDataReader数据读取器,用于一行一行地读取数据库数据 public static SqlDataReader ExecuteReader(string connString, CommandType cmdType,string cmdText,params SqlParameter[] paras) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connString); try { InitializeCommand(cmd,conn,null,cmdType,cmdText,paras); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return reader; } catch { conn.Close(); throw; } }
2. 添加、删除、更新
数据库的增删改可以通过传递不同的SQL命令,以一种通用型的方法来实现增删改。具体的方法为:
//针对数据库的增删改,设计一个通用型的方法,返回值为受影响的行数 public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] paras) { SqlCommand cmd = new SqlCommand(); using(SqlConnection conn = new SqlConnection(connString)) { InitializeCommand(cmd,conn,null,cmdType,cmdText,paras); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); retuen rows; } }
三.实践
写好了数据库操作类SqlHelper后,运用实际例子来验证一下。
1.打开数据库,创建数据库命名为XK,新建一个tb_Departdent表,预先插入三行数据,用于增删查改操作。
create database XK create table tb_Department ( DeptNO int not null primary key identity(1,1), DeptName nvarchar(32) null ) insert into tb_Department(DeptName) values(N'计算机应用工程系') insert into tb_Department(DeptName) values(N'建筑工程系') insert into tb_Department(DeptName) values(N'旅游系')
2.打开VS,新建一个窗体项目,将窗体UI设计成如下图所示。(toolstrip和datagridview控件的设置我这里就不说了,很简单,不知道可以在园里找找)
窗体在加载的时候将从数据库中读取tb_Department表中的全部数据,并显示在datagridview控件中。加载事件处理程序如下:
private void LoadData() { //首先定义用于查询的SQL脚本 string cmdText = @"select DeptNO,DeptName from tb_Department"; //使用SqlHelper操作类来读取表数据 //定义一个用于存储读取过来的表数据DataTable对象 DataTable dt = SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.Text, cmdText); this.dgvDept.DataSource = dt; } //窗体加载时导入表数据 private void DeptForm_Load(object sender, EventArgs e) { LoadData(); }
加载后的效果,groupbox控件设置为不可用。
3.增加、删除、修改的方法实现。
/// <summary> /// 插入数据 /// </summary> private void InsertData() { //判断系部名称是否为空 if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim())) { MessageBox.Show("系部名称不能为空!"); return; } //定义插入数据的Sql脚本 string cmdText = @"insert into tb_Department(DeptName) values(@DeptName)"; //SQL脚本参数设置 SqlParameter[] parameters = { //new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text), new SqlParameter("@DeptName",(object)this.txtDeptName.Text.Trim()) }; //执行插入,并返回受影响的行数 int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters); //判断是否插入成功,并提示 if (rows > 0) { //更新datagridview控件的数据 LoadData(); MessageBox.Show("插入成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("插入失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } /// <summary> /// 删除数据 /// </summary> private void DeleteData() { //确认是否删除 if (MessageBox.Show("确定要删除该行数据吗?","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Warning) == DialogResult.Yes) { //定义删除相应数据的SQL脚本 string cmdText = @"delete from tb_Department where DeptNO=@DeptNO"; //定义SQL参数 SqlParameter[] parameters = { new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text) }; //接受返回的受影响的行数 int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters); //判断是否插入成功,并提示 if (rows > 0) { //更新datagridview控件的数据 LoadData(); MessageBox.Show("删除成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("删除失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } } /// <summary> /// 更新数据 /// </summary> private void UpdateData() { //判断系部名称是否为空 if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim())) { MessageBox.Show("系部名称不能为空!"); return; } //确认是否修改 if (MessageBox.Show("确定要修改该行数据吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes) { //定义删除相应数据的SQL脚本 string cmdText = @"update tb_Department set DeptName=@DeptName where DeptNO=@DeptNO"; //定义SQL参数 SqlParameter[] parameters = { new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text), new SqlParameter("@DeptName",(object)this.txtDeptName.Text) }; //接受返回的受影响的行数 int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters); //判断是否插入成功,并提示 if (rows > 0) { //更新datagridview控件的数据 LoadData(); MessageBox.Show("修改成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("修改失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } }
4.为“提交”按钮编写点击处理事件之前,我们需要设计一个CmdType枚举来标识点击提交按钮执行的是新增,删除或者更新操作。
/// <summary> /// 用于标识命令操作的类型 /// </summary> public enum CmdType { Insert, Delete, Update }
5.分别为窗体的增加,删除,修改和提交按钮编写点击处理程序。
/// <summary> /// 用于标识SQL命令操作的类型 /// </summary> private CmdType cmdType { get; set; }
private void tsbInsert_Click(object sender, EventArgs e) { cmdType = CmdType.Insert; //将gbDept控件设置可用,textbox控件设为可用,并将Text属性清空 this.gbDept.Enabled = true; this.txtDeptName.Enabled = true; this.txtDeptName.Text = string.Empty; this.lbDeptNO.Text = "DeptNO";
} private void tsbDelete_Click(object sender, EventArgs e) { //将sql命令类型设置为删除 cmdType = CmdType.Delete; //启用groupbox控件,textbox设置不可用 this.gbDept.Enabled = true; this.txtDeptName.Enabled = false; //获取要删除的行数据,并赋值给相应控件 this.lbDeptNO.Text = dgvDept.SelectedRows[0].Cells["DeptNO"].Value.ToString(); this.txtDeptName.Text = dgvDept.SelectedRows[0].Cells["DeptName"].Value.ToString(); } private void tsbUpdate_Click(object sender, EventArgs e) { //将sql命令类型设置为删除 cmdType = CmdType.Update; //启用groupbox控件,textbox控件设置为可用 this.gbDept.Enabled = true; this.txtDeptName.Enabled = true; //获取要删除的行数据,并赋值给相应控件 this.lbDeptNO.Text = dgvDept.SelectedRows[0].Cells["DeptNO"].Value.ToString(); this.txtDeptName.Text = dgvDept.SelectedRows[0].Cells["DeptName"].Value.ToString(); } private void btnOK_Click(object sender, EventArgs e) { //执行增删改操作 switch (cmdType) { case CmdType.Insert: InsertData(); break; case CmdType.Delete: DeleteData(); break; case CmdType.Update: UpdateData(); break; } }
四.结果
应用程序的UI和编码工作做完之后,当然就是调试各项功能了。
1.增加功能:点击增加按钮,在系部名称输入要插入的系部:“哈哈系”,然后点击提交按钮,数据被插入到数据库中了,为了后续操作,再插入“嘿嘿系”和“嘟嘟系”。
2.删除功能:选择要删除的行数据(将“嘿嘿系”删除),点击删除按钮,窗体下方会显示要删除的行数据信息,点击提交按钮后会提示用户是否确定要删除数据,点击“是”后显示删除成功,datagridview控件中确实没有“嘿嘿系”了。
3.修改功能:选择要修改的行数据,窗体下方会显示相应的信息,textbox控件为可编辑,我们把“哈哈系”改成“光电系”,点击提交按钮,确认修改,datagridview控件中“哈哈系”变成了“光电系”,修改成功。
四.总结
本文主要针对数据库操作类SqlHelper的产生背景,设计过程,实践运用以及调试结果进行讲解,虽然描述的很粗糙,但是也算是一种数据库操作技术的积累。
实践运用遇到的主要问题:
- 新插入数据行中的自增长主键,由数据库自动增加,不受人为控制,比如在讲解增加功能的时候,新插入的行时,数据库会自动给自增长字段赋一个值,而这个值不是我们希望的。比如一个表中有Id,Name字段,Id为自增长主键,原有表中有1-a,2-b,3-c三行数据,现在删除3-c这一行的数据,然后在插入c,数据库会自动给Id赋值为4,即:4-c,而不是我们想要的3-c。
如何在插入新数据时,自动获得缺失的自增值来赋值给Id呢?这个问题我会在下一篇博客中解决。
上一篇: 如何秒变辣妈 产后瘦身你做对了吗
下一篇: 减肥的最好方法 9个方法轻松享瘦