SqlHelper类详解

SqlHelper 类实现详细信息
SqlHelper 类用于通过一组静态方法来封装数据访问功能。该类不能被继承或实例化，因此将其声明为包含专用构造函数的不可继承类。

在 SqlHelper 类中实现的每种方法都提供了一组一致的重载。这提供了一种很好的使用 SqlHelper 类来执行命令的模式，同时为开发人员选择访问数据的方式提供了必要的灵活性。每种方法的重载都支持不同的方法参数，因此开发人员可以确定传递连接、事务和参数信息的方式。在 SqlHelper 类中实现的方法包括：

ExecuteNonQuery。此方法用于执行不返回任何行或值的命令。这些命令通常用于执行数据库更新，但也可用于返回存储过程的输出参数。
ExecuteReader。此方法用于返回 SqlDataReader 对象，该对象包含由某一命令返回的结果集。
ExecuteDataset。此方法返回 DataSet 对象，该对象包含由某一命令返回的结果集。
ExecuteScalar。此方法返回一个值。该值始终是该命令返回的第一行的第一列。
ExecuteXmlReader。此方法返回 FOR XML 查询的 XML 片段。
除了这些公共方法外，SqlHelper 类还包含一些专用函数，用于管理参数和准备要执行的命令。不管客户端调用什么样的方法实现，所有命令都通过 SqlCommand 对象来执行。在 SqlCommand 对象能够被执行之前，所有参数都必须添加到 Parameters 集合中，并且必须正确设置 Connection、CommandType、CommandText 和 Transaction 属性。SqlHelper 类中的专用函数主要用于提供一种一致的方式，以便向 SQL Server 数据库发出命令，而不考虑客户端应用程序调用的重载方法实现。SqlHelper 类中的专用实用程序函数包括：

AttachParameters：该函数用于将所有必要的 SqlParameter 对象连接到正在运行的 SqlCommand。
AssignParameterValues：该函数用于为 SqlParameter 对象赋值。
PrepareCommand：该函数用于对命令的属性（如连接、事务环境等）进行初始化。
ExecuteReader：此专用 ExecuteReader 实现用于通过适当的 CommandBehavior 打开 SqlDataReader 对象，以便最有效地管理与阅读器关联的连接的有效期。

SqlHelper体验谈：

以下载自CEOCIO(MVP/CSDN_WEB开发版主)的Blog:

微软提供的Data Access Application Block中的SQLHelper类中封装了最常用的数据操作，各个使用者调用他而写的代码也有很大区别。

对于一个返回DataSet的方法我原来是这样写的：

public DataSet GetDepartmentMemberList(int departmentID)
{
try
{
string sql = "OA_Department_GetDepartment_Members";
string conn = ConfigurationSettings.AppSettings["strConnection"];
SqlParameter[] p = 
{
SqlHelper.MakeInParam("@departmentID",SqlDbType.Int,4,departmentID)
};
DataSet ds = SqlHelper.ExecuteDataset(conn,CommandType.StoredProcedure,sql,p);
return ds;
}
catch(System.Data.SqlClient.SqlException er)
{
throw new Exception(er.Message);
}
}

现在我是这样来写的：

//连接字符串
private string _connectionString = ConfigurationSettings.AppSettings["strConnection"];
public string ConnectionString
{
get {return this._connectionString;}
set {this._connectionString = value;}
}

public DataSet GetNewsToIndexPage(int NewsTypeID)
{
return GetDataSet("yzb_GetNewsToIndexPage",GetNewsToIndexPage_Parameters(NewsTypeID));
}

//设置存储过程参数
private SqlParameter[] GetNewsToIndexPage_Parameters(int NewsTypeID)
{

SqlParameter[] p = 
{
SqlHelper.MakeInParam("@NewsTypeID",SqlDbType.Int,4,NewsTypeID)
};
return p;
}

//这里才真正调用SqlHelper
private DataSet GetDataSet(string sql, params SqlParameter[] p)
{
return SqlHelper.ExecuteDataset(ConnectionString,CommandType.StoredProcedure,sql,p);
}

做为一个懒人，我经常这样写：

System.Data.DataTable dt=SqlHelper.ExecuteDataset(SqlHelper.CONN_STRING_NON_DTC,CommandType.Text,sql).
Tables[0];

直接返回DataTable，返回表集合基本不用，只返回一个table用于绑定。

string conn = ConfigurationSettings.AppSettings["strConnection"];

我写在SqlHelper.CONN_STRING_NON_DTC里面

MakeInParam代码烦琐，如果查询输入条件不可能有组合SQL,
我直接exec pronamr paraargs

如果查询输入条件存在安全问题，或者带返回参数
再用

SqlParameter[] queryParam=new SqlParameter[] ...{ 
new SqlParameter("@UserID",SqlDbType.Int) 
};

queryParam[0].Value=this.UserID;

组合 sql语句用到@和string.Format技巧.例如：

sql=@"UPDATE Test_User_Statistic
SET 
Test_User_Statistic.[IsQualified]={0}, 
Test_User_Statistic.[Reason]='{1}'
WHERE UserID={2}" ;
sql= string.Format(sql,IsQualified,Reason,userID);
SqlHelper.ExecuteNonQuery(SqlHelper.CONN_STRING_NON_DTC,CommandType.Text,sql);

其中sql语句可以借助SQL的查询分析器生成。
这么干要保证安全，如果有输入漏洞，建议还是用SqlParameter[]缓存参数，因为它会把攻击性的代码，比如带单引号分号的，当作普通字符处理。

SqlHelper可以回滚事务，但是我觉得事务还是存储过程本身来解决比较好：

DBTransaction = CS.BeginTransaction();
SqlHelper.ExecuteNonQuery(DBTransaction, CommandType.StoredProcedure, "pro_T_Accredit_Create", arParams);

p_intOutPut = Convert.ToInt32(arParams[3].Value.ToString());

if(p_intOutPut < 0)
...{
DBTransaction.Rollback(); //--回退事务
}

另外附采用存储过程回滚

CREATE PROCEDURE 。。。
@idint
。。。。
as
declare 。。。
begin transaction
insert 。。。
delete 。。。
if @@error <> 0 rollback transaction
commit transaction
GO

比较ASP回滚：

在编程中，经常需要使用事务。所谓事务，就是一系列必须都成功的操作，只要有一步操作失败，所有其他的步骤也必须撤销。比如用ASP开发一个网络硬盘系统，其用户注册部分要做的事有：

将用户信息记入数据库
为用户开个文件夹用于存储
初始化用户操作日志

这三步必须使用事务，否则万一磁盘操作失败，而没有撤销数据库操作，就会造成只能登陆而不能操作的"死用户"现象。
由于数据库系统特殊的发展历史，小至Access，大到DB2,无不带有事务支持。因此上述步骤可以如下表示：
On Error Resume Next
第一步：
在事务环境下把用户信息记入数据库
If Err Then
关闭连接
退出
Else
第二步：创建文件夹
If Err Then
回滚第一步数据库操作，退出
Else
第三步：在事务环境下操作日志数据库
If Err Then
回滚第一步操作，删除第二步建立的文件夹
退出
End If
End If
End If
提交第一步数据库操作的事务
提交第二步数据库操作的事务
End

每一步都需要进行判断，如果失败，还需要手工回滚前面多步操作，使程序变得复杂、难懂。如果今后更新了程序，增加其他步骤，还需要嵌套更多层的If...Else...End If，使程序流程更加复杂。

正确的解决办法是使用ASP的事务控制功能。IIS通过和MTS服务联系，可以控制多种支持事务的系统，当程序发出"失败"的信号时，所有支持事务的系统均将自动回滚，即使操作已经正式完成；对不支持事务的操作也提供了方便的手工回滚方式。上面的例子用ASP事务控制功能重写如下：

<%@ TRANSACTION = Required %>
On Error Resume Next

Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open ....
Conn.Execute "INSERT...."
Conn.Close
Set Conn=Nothing

Set Conn2=Server.CreateObject("ADODB.Connection")
Conn2.Open ....
Conn2.Execute "INSERT...."
Conn2.Close
Set Conn2=Nothing

Set FSO=Server.CreateObject("Scripting.FilesystemObject")
FSO.CreateFolder "...."

If Err Then
ObjectContext.SetAbort '通知所有支持事务的组件回滚，并运行手工回滚代码
Else
ObjectContext.SetComplete
End If
Set FSO=Nothing

Sub OnTransactionAbort
Response.Write "错误"
FSO.DeleteFile Server.Mappath("a.txt") 'FSO的手工回滚——删除文件夹
End Sub
Sub OnTransactionCommit
Response.Write "胜利完成任务"
End Sub
%>

第一行的<%@ TRANSACTION = Required %>表示这一页ASP文件需要MTS的事务支持。中间的各个操作都按普通顺序书写，而不用考虑回滚问题。在程序最后判断是否有错误。如果有，调用ObjectContext的SetAbort方法，IIS会通过MTS服务通知所有支持事务的组件回滚（主要是数据库），并且运行Sub OnTransactionAbort对不支持事务的操作手工回滚；如果没有发生错误，调用ObjectContext的SetComplete方法，则会运行Sub OnTransactionCommit来显示成功的消息。
整个ASP程序不需要为判断错误和回滚操作书写多余的代码，只须在最后进行判断，即使今后增加了多步操作，也只需要在Sub OnTransactionAbort中进行控制即可，非常方便，程序员可以专注于过程编写而不是书写纠错代码。
其实ASP还提供了许多更有用的功能，等着我们使用，千万不要以为ASP使用脚本语言，功能就一定弱。

比较ADO回滚：
<%
'Asp 中使用事务
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "course_dsn","course_user","course_password"
conn.begintrans '开始事务

sql="delete from user_info"
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3
if conn.errors.count>0 then '有错误发生
conn.rollbacktrans '回滚
set rs=nothing
conn.close
set conn=nothing
response.write "交易失败，回滚至修改前的状态！"
response.end
else
conn.committrans '提交事务
set rs=nothing
conn.close
set conn=nothing
response.write "交易成功！"
response.end
end if
%>

SqlHelper最开始出现好像是在Microsoft PetShop 作为DAL层的核心。

另外关于SqlDataReader，我感觉这个东西和ADO里面的RecordSet类似，就是需要Close.
如果做为参数和返回值传递是比较危险的，因为你要传出去就Close(),而究竟在哪里关闭容易被以往和忽略，所以我觉得还是用强类型的DataSet做为参数和返回值传递纪录集比较好。

据说下面的写法不用Close也会自动资源回收，不知道真的假的：

using (SqlDataReader reader=SqlHelper.ExecuteReader(SqlHelper.CONN_STRING_NON_DTC,CommandType.Text,sql))
...{
if(reader.Read())
...{
arr[0]=reader.GetString(1);
。。。。 }
}