作者：白寧超
時間：2016年3月5日22:51:47
摘要：繼上篇《Oracle手邊常用70則腳本知識匯總》文章的發表，引起很多朋友關注。便促使筆者收集整理此文。本文主要針是對微軟技術對數據庫（下文案例采用的）操作時，調用執行方法的封裝，這也是數年逐漸學習、吸收、實踐、完成的一個類庫。其中不免有不合理之處，亦或是不符合個別讀者的使用習慣。在此，共享此文，權當互相學習。（本文原創，轉載注明出處：私房干貨.Net數據層方法的封裝）
本文分以下幾個部分，第一部分概述，整個文章布局；第二部分介紹類的引用；第三部分介紹公用連接字符串；第四部分分別介紹不含參數執行方法（返回影響行數）、含參數執行方法（返回影響行數）、不含參數執行方法（返回對象）、含參數執行方法（返回對象）、不含參數查詢方法（返回對象）、含參數的查詢方法（返回對象）、調用存儲過程的方法（返回對象）、調用分頁的方法；第五部分對SQL Server的部分核心常用語句進行補充；最後附上完整類庫。
筆試之前從事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#高級編程》，此處不是強調的重點，大家知道這些引用即可
數據庫的連接分為兩種：其一便是寫下單頁面的數據庫連接（即每個頁面重復一樣的連接語句）其二便是在公共資源文件中統一配置，采用如下語句調用即可：
static string connstr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; //讀取配置文件中的連接字符串
這樣配置的優點，顯而易見，優化代碼，減少冗余，便於修改和調用。相反，逐個頁面去寫連接語句，如果需要修改，很大的工作量且容易漏改。這裡也是強調封裝好處。
本方法執行非查詢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;
}
本方法執行非查詢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;
}
本方法執行讀取數據，返回一個對象，詳細方法剖析如下：
/// <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;
}
本方法執行讀取數據，返回一個對象，詳細方法剖析如下：
/// <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;
}
本方法執行查詢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;
}
本方法執行查詢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;
}
本方法可以執行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;
}
本方法實現分頁功能，詳細方法剖析如下：
/// <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];
}
}
}
select distinct 字段 from 表
select sname as '姓名',2013-sage as '出生日期' from student
select sname,'出生日期',2013-sage from student
select 姓名=sname,出生日期=2013-sage from student
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)
select * from student where sname like '劉__'
select * from student where sname like '_表__'
select * from student where sname like '%表%'
select grade*(1+0.2) as 總成績,grade/(10) as 績點 from sc
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
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
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
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
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'
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
select * from student where sage>(select AVG(sage) from student)
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
--視圖的創建 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
--創建函數 CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime ) RETURNS TABLE AS RETURN ( select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差 )
--創建存儲過程， 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 studentView Code
--觸發器 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
數據層類庫封裝源碼 訪問密碼 023d
