【类库】私房干货.Net数据层方法的封装
【类库】私房干货.Net数据层方法的封装
作者:白宁超
时间:2016年3月5日22:51:47
摘要:继上篇《Oracle手边常用70则脚本知识汇总》文章的发表,引起很多朋友关注。便促使笔者收集整理此文。本文主要针是对微软技术对数据库(下文案例采用的)操作时,调用执行方法的封装,这也是数年逐渐学习、吸收、实践、完成的一个类库。其中不免有不合理之处,亦或是不符合个别读者的使用习惯。在此,共享此文,权当互相学习。(本文原创,转载注明出处:私房干货.Net数据层方法的封装)
1 概述
本文分以下几个部分,第一部分概述,整个文章布局;第二部分介绍类的引用;第三部分介绍公用连接字符串;第四部分分别介绍不含参数执行方法(返回影响行数)、含参数执行方法(返回影响行数)、不含参数执行方法(返回对象)、含参数执行方法(返回对象)、不含参数查询方法(返回对象)、含参数的查询方法(返回对象)、调用存储过程的方法(返回对象)、调用分页的方法;第五部分对SQL Server的部分核心常用语句进行补充;最后附上完整类库。
2 类库的引用
笔试之前从事net技术,对类的引用习惯见到诸如using System;但是这点在后来至今使用java引用是不一样的;这也是笔者看到using引用亲切之所在。本类引用如下:
using System; //系统类库 using System.Collections.Generic; using System.Linq; //Linq类库,对linq操作使用 using System.Text; using System.Configuration; //数据库配置使用 using System.Data.SqlClient; using System.Data;
诸如以上方法的引用方式,如何使用大家都清楚,但是对using调用底层,建议有兴趣的朋友参照《C#高级编程》,此处不是强调的重点,大家知道这些引用即可
3 公用连接字符串
数据库的连接分为两种:其一便是写下单页面的数据库连接(即每个页面重复一样的连接语句)其二便是在公共资源文件中统一配置,采用如下语句调用即可:
static string connstr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; //读取配置文件中的连接字符串
这样配置的优点,显而易见,优化代码,减少冗余,便于修改和调用。相反,逐个页面去写连接语句,如果需要修改,很大的工作量且容易漏改。这里也是强调封装好处。
4 类库方法逐个剖析
4.1 不含参数执行方法的封装(返回影响行数)
本方法执行非查询sql语句,返回受影响行数,如果执行非增删改则返回-1,详细内容如下:
/// <summary> /// 执行非查询sql语句,返回受影响行数,如果执行非增删改则返回-1 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组</param> public static int ExecuteNonParaQuery(string sql) { int res = -1; using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); res = cmd.ExecuteNonQuery(); } } return res; }
4.2 含参数执行方法的封装(返回影响行数)
本方法执行非查询sql语句,返回受影响行数,如果执行非增删改则返回-1,详细内容如下:
/// <summary> /// 执行非查询sql语句,返回受影响行数,如果执行非增删改则返回-1 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组</param> /// <returns>影响行数res</returns> public static int ExecuteNonQuery(string sql, params SqlParameter[] paras) { int res = -1; using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null || paras.Length > 0) { cmd.Parameters.AddRange(paras); } conn.Open(); res = cmd.ExecuteNonQuery(); } } return res; }
4.3 不含参数执行方法(返回对象)
本方法执行读取数据,返回一个对象,详细方法剖析如下:
/// <summary> /// 执行查询sql语句,返回一个无参数dataset对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras"></param> /// <returns>返回dataset 对象</returns> public static DataSet GetDataSetNotPara(string sql) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 cmd.CommandType = CommandType.StoredProcedure; conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(ds); } } } return ds; }
4.4 含参数执行方法(返回对象)
本方法执行读取数据,返回一个对象,详细方法剖析如下:
/// <summary> /// 执行读取数据,返回一个对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组</param> /// <returns>返回一个对象o</returns> public static object ExecuteScalar(string sql, params SqlParameter[] paras) { object o = null; using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null) { cmd.Parameters.AddRange(paras); } conn.Open(); o = cmd.ExecuteScalar(); } } return o; }
4.5 不含参数查询方法(返回对象)
本方法执行查询sql语句,返回一个无参数dataset对象,详细方法剖析如下:
/// <summary> /// 执行查询sql语句,返回一个无参数dataset对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras"></param> /// <returns>返回dataset 对象</returns> public static DataTable GetDataTableNotPara(string sql) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(dt); } } } return dt; }
4.6 含参数的查询方法(返回对象)
本方法执行查询sql语句,返回一个参数dataset对象,详细方法剖析如下:
/// <summary> /// 执行查询sql语句,返回一个dataset对象 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">查询参数</param> /// <returns>返回dataset 对象</returns> public static DataSet GetDataSet(string sql, params SqlParameter[] paras) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 cmd.CommandType = CommandType.StoredProcedure; //添加参数 cmd.Parameters.AddRange(paras); conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(ds); } } } return ds; }
4.7 调用存储过程的方法(返回对象)
本方法可以执行sql语句或存储过程,详细方法剖析如下:
/// <summary> /// 可以执行sql语句或存储过程 /// </summary> /// <param name="text"></param> /// <param name="ct"></param> /// <param name="param"></param> /// <returns></returns> public static DataTable ProcGetTable(string sql, params SqlParameter[] param) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { //根据传来的参数。决定是sql语句还是存储过程 cmd.CommandType = CommandType.StoredProcedure; //添加参数 cmd.Parameters.AddRange(param); //cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20).Value = param[0]; //cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 20).Value = param[1]; conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(dt); } } } return dt; }
4.8 调用分页的方法
本方法实现分页功能,详细方法剖析如下:
/// <summary> /// 实现分页功能 /// </summary> /// <param name="sql">sql语句</param> /// <param name="paras">参数数组(显示index页和每页显示条数size)</param> /// <returns>查询结果</returns> public static DataTable GetParaTable(string sql, params SqlParameter[] paras) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connstr)) { using (SqlDataAdapter da = new SqlDataAdapter(sql, conn)) { if (paras != null) { da.SelectCommand.Parameters.AddRange(paras); } da.SelectCommand.CommandType = CommandType.StoredProcedure; da.Fill(ds); } } return ds.Tables[0]; } } }
5 SQL Server的部分核心常用语句进行补充
5.1 去掉重复行:
select distinct 字段 from 表
5.2 格式化查询
select sname as '姓名',2013-sage as '出生日期' from student
select sname,'出生日期',2013-sage from student
select 姓名=sname,出生日期=2013-sage from student
5.3 条件查询
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit<5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
5.4 匹配查询
select * from student where sname like '刘__'
select * from student where sname like '_表__'
select * from student where sname like '%表%'
5.5 算术元算查询
select grade*(1+0.2) as 总成绩,grade/(10) as 绩点 from sc
5.6 分组函数查询
select COUNT(*) as 总人数 from student select COUNT(distinct sno) as '选修的总人数' from sc select AVG(grade) as '平均成绩' from sc where sno='10021' select MAX(grade) as 'MAX成绩' from sc where sno='10021' select MIN(grade) as 'MIN成绩' from sc where sno='10021' select SUM(grade) as '总成绩' from sc where sno='10021' select SUM(grade)/COUNT(grade) as '平均成绩' from sc where sno='10021' select SUM(grade) as '总成绩' from sc group by sno having sum(grade)>100
5.7 等值连接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
5.8 自身连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno
select B.sname as '同一个系' from student A,student B where A.sname='白居易' and A.sdept=B.sdept
5.9 外连接
select A.*,B.* from student A left join sc B on A.sno=B.sno
select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
5.10 复合条件连接
select * from sc select * from course
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
5.11 字符串连接查询
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade<60) group by sname
5.12 子查询
select * from student where sage>(select AVG(sage) from student)
5.13 sql创建用户
sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--权限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
5.14 视图相关操作
--视图的创建 create view VIEW_STUGrade(学号,姓名,课程,成绩) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and student.sdept='软件' --查看视图 select * from VIEW_STUGrade --视图修改 alter view VIEW_STUGrade(学号,姓名,课程,成绩) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and student.sdept='软件' with check option --更新失败后不影响视图查看 --视图更新 update VIEW_STUGrade set 姓名='王超' where 学号='10022' select * from student where sno='10022' /* 1,可更新视图: a,单个基本表导出的 2,不可更新视图 a 两个以上基本表导出的 b 视图字段来自表达式或者函数 c 嵌套查询的表 d 分组子句使用distinct */ --删除视图 drop view VIEW_STUGrade
5.15 函数相关操作
--创建函数 CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime ) RETURNS TABLE AS RETURN ( select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差 )
5.16 存储过程相关操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建存储过程, GO create proc [dbo].[sel] ( @sno char(10) ) as select * from student where sno=@sno exec sel @sno='10021' --查看 GO create proc sel2 as select * from student exec sel2 --修改 GO create proc updat @sno char(10), @sex char(2) as update student set sex=@sex where sno=@sno select * from student exec updat @sno='10021', @sex='女' --删除 GO create proc dele @sno char(10) as delete student where sno=@sno select * from student exec dele @sno='10029' --插入 GO create proc inser @sno char(10), @sname char(20), @sex char(2), @sage smallint, @sdept char(15) as insert into student values(@sno,@sname,@sex,@sage,@sdept) exec inser @sno='10029', @sname='tom', @sex='男', @sage=100, @sdept='sc' select * from student
5.17 触发器相关操作
--触发器 use Studets GO create trigger insert_Tri ON student after insert as print '有新数据插入!' GO create trigger update_Tri on student after update as print '有数据更新!' ------ GO create trigger delete_Tri on student after delete as print '有数据删除! --修改触发器 GO alter trigger delete_Tri on student after delete as if '王帅' in (select sname from deleted) print '该信息不许删除!' rollback transaction --执行存储过程查看触发器使用情况 exc inser @sno='10029', @sname='王帅', @sex='男', @sage=25, @sdept='国贸' exec updat @sno='10029', @sex='女' exec dele @sno='10029' --查看,修改,删除触发器 /* sp_*+触发器名称 sp_helptext:触发器正文信息 sp_help:查看一般信息,触发器名称,属性,创建时间,类型 sp_depends:引用或指定表的所有触发器 sp_helptrigger:指定信息 */ sp_help delete_Tri sp_helptext delete_Tri sp_depends delete_Tri sp_helptrigger student --删除触发器 drop trigger delete_Tri
6 类库源码分享
作者:白宁超,工学硕士,现工作于四川省计算机研究院,研究方向是自然语言处理和机器学习。曾参与国家自然基金项目和四川省科技支撑计划等多个省级项目。著有《自然语言处理理论与实战》一书。 自然语言处理与机器学习技术交流群号:436303759 。
出处:http://www.cnblogs.com/baiboy/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。