【类库】私房干货.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 存储过程相关操作

--创建存储过程，

GO create proc [dbo].[sel] (

@sno char()

)

as

select * from student where sno=@sno

exec sel @sno=''

--查看

GO create proc sel2

as

select * from student

exec sel2

--修改

GO create proc updat @sno char(), @sex char()

as

update student set sex=@sex where sno=@sno

select * from student  exec updat @sno='', @sex='女'

--删除

GO create proc dele @sno char()

as

delete student where sno=@sno

select * from student

exec dele @sno=''

--插入

GO create proc inser @sno char(), @sname char(), @sex char(), @sage smallint, @sdept char()

as

insert into student values(@sno,@sname,@sex,@sage,@sdept)

exec inser @sno='', @sname='tom', @sex='男', @sage=, @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 类库源码分享

数据层类库封装源码 访问密码 023d