SqlHelper详解.doc

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（51）:微软提供的Data Access Application Block中的SQLHelper类中封装了最常用的数据操作，各个使用者调用他而写的代码也有很大区别。对于一个返回DataSet的方法我原来是这样写的：复制到剪贴板CODE:public DataSet GetDepartmentMemberList(int departmentID)trystring sql = OA_Department_GetDepartment_Members;string conn = ConfigurationSettings.AppSettingsstrConnection;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);现在我是这样来写的：/连接字符串复制到剪贴板CODE:private string _connectionString = ConfigurationSettings.AppSettingsstrConnection;public string ConnectionStringget return this._connectionString;set this._connectionString = value;public DataSet GetNewsToIndexPage(int NewsTypeID)return GetDataSet(yzb_GetNewsToIndexPage,GetNewsToIndexPage_Parameters(NewsTypeID);/设置存储过程参数复制到剪贴板CODE:private SqlParameter GetNewsToIndexPage_Parameters(int NewsTypeID)SqlParameter p = SqlHelper.MakeInParam(NewsTypeID,SqlDbType.Int,4,NewsTypeID);return p;/这里才真正调用SqlHelper复制到剪贴板CODE:private DataSet GetDataSet(string sql, params SqlParameter p)return SqlHelper.ExecuteDataset(ConnectionString,CommandType.StoredProcedure,sql,p);代码更加灵活，更加安全了：P做为一个懒人，大笨狼51aspx我经常这样写：复制到剪贴板CODE:System.Data.DataTable dt=SqlHelper.ExecuteDataset(SqlHelper.CONN_STRING_NON_DTC,CommandType.Text,sql).Tables0; 直接返回DataTable， 返回表集合基本不用，只返回一个table用于绑定。 string conn = ConfigurationSettings.AppSettingsstrConnection; 我写在SqlHelper.CONN_STRING_NON_DTC里面 MakeInParam代码烦琐，如果查询输入条件不可能有组合SQL, 我直接exec pronamr paraargs 如果查询输入条件存在安全问题，或者带返回参数 再用复制到剪贴板CODE:SqlParameter queryParam=new SqlParameter new SqlParameter(UserID,SqlDbType.Int) ; queryParam0.Value=this.UserID; 组合sql语句用到和string.Format技巧.例如：复制到剪贴板CODE:sql=UPDATE Test_User_StatisticSET Test_User_Statistic.IsQualified=0, Test_User_Statistic.Reason=1WHERE UserID=2 ;sql= string.Format(sql,IsQualified,Reason,userID);SqlHelper.ExecuteNonQuery(SqlHelper.CONN_STRING_NON_DTC,CommandType.Text,sql);其中sql语句可以借助SQL的查询分析器生成。这么干要保证安全，如果有输入漏洞，建议还是用SqlParameter缓存参数，因为它会把攻击性的代码，比如带单引号分号的，当作普通字符处理。 Enterprise Library: Data Access Application Block使用向导2007年09月25日 星期二 下午 04:20Data Access Application Block有如下好处：l减少编写重复累赘代码去执行标准的任务。l帮助在应用程序内部和跨企业应用维护一致的数据访问实践。l降低改变物理数据目标的难度。l减轻开发人员学习不同数据库类型的不同编程模型。l当移植应用程序到不同数据库类型时，减少重写代码的数量。Data Access Application Block依赖项：lData Access Application Block依赖其他的application blocks和Enterprise Library中的代码。lConfiguration Application Block Data Access Application Block使用该Configuration Application Block读取配置信息。lCommon类库功能，如instrumentation 提供不同功能用来显示事件和系统管理的数据。l此外，application block使用XML文件存放配置信息，推荐采用Enteprise Library配置工具来修改这些配置信息。使用Enteprise Library配置工具创建配置文件：创建DemoDAAP应用程序，使用Configuration Console配置app.config文件。应注意Connection String的设置，在本示例中，设置Integrated Security为False，并添加User ID和Password参数及其参数值，Database属性值为Northwind。选择合适的重载方法每一个数据访问方法均有许多重载，如下的描述和指导帮助你选择合适的重载：l一些重载方法接受DBCommandWrapper对象，DBCommandWrapper在一个对象内同时封装了command和参数。允许你同时增加输入/输出参数和设置这些参数的类型。这些重载提供了很好的灵活性和对每一个方法的控制。l一些重载方法接受存储过程和存储过程参数集合，当调用仅有输入参数的存储过程时，调用这些重载比较方便。l一些重载方法接受System.Data.CommandType和字符串命令，当执行在线的SQL语句或没有参数的存储过程时，调用这些重载比较方便。l最后，上述的每一个重载都包含一个接受事务的重载方法，当在现有事务内执行方法时，允许你使用合适的重载类型。未完，Part 2待续。* Updated *Enterprise Library: Data Access Application Block使用向导，Part 2 *作者：Rickie Lee (rickieleemail#)本文参考Enterprise Library: Data Access Application Block Quick Start范例，简单编写一个DEMO。Enterprise Library: Data Access Application Block使用向导 Part 22007年09月25日 星期二 下午 04:21本文是前一篇postingEnterprise Library: Data Access Application Block使用向导，Part 1的Part 2，主要演示使用Data Access Application Block (Enterprise Library)的代码部分。Data Access Application Block使用向导：1增加对Microsoft.Practices.EnterpriseLibrary.Data.dll和Microsoft.Practices.EnterpriseLibrary.Configuration.dll的引用，并在代码在添加：using Microsoft.Practices.EnterpriseLibrary.Data;2调用代码示例：(1) ExecuteDataSet方法Database db = DatabaseFactory.CreateDatabase();DataSet dsCustomers = db.ExecuteDataSet(CommandType.Text, Select * From Customers );customerGrid.DataSource = dsCustomers.Tables0;(2) ExecuteReader方法Database db = DatabaseFactory.CreateDatabase();string sqlCommand = Select top 5 * From Customers;DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);StringBuilder readerData = new StringBuilder();using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper)while (dataReader.Read()readerData.Append(dataReaderContactName);readerData.Append(Environment.NewLine);txtResult.Text = readerData.ToString();为了执行SQL语句，上述代码使用GetSqlStringCommandWrapper方法创建合适command wrapper对象，然后作为参数传递给ExecuteReader方法。(3) ExecuteNonQuery方法Database db = DatabaseFactory.CreateDatabase();string sqlCommand = getProductDetails;DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);/ Add paramtersint productID=1;/ Input parameters can specify the input valuedbCommandWrapper.AddInParameter(ProductID, DbType.Int32, productID);/ Output parameters specify the size of the return datadbCommandWrapper.AddOutParameter(ProductName, DbType.String, 40);dbCommandWrapper.AddOutParameter(UnitPrice, DbType.Currency, 8);dbCommandWrapper.AddOutParameter(QtyPerUnit, DbType.String, 20);db.ExecuteNonQuery(dbCommandWrapper);/ Row of data is captured via output parametersstring results = string.Format(CultureInfo.CurrentCulture, 0, 1, 2:C, 3 ,dbCommandWrapper.GetParameterValue(ProductID),dbCommandWrapper.GetParameterValue(ProductName),dbCommandWrapper.GetParameterValue(UnitPrice),dbCommandWrapper.GetParameterValue(QtyPerUnit);txtResult.Text = results;为了执行存储过程，上述代码使用GetStoredProcCommandWrapper方法创建合适的command wrapper对象，然后作为参数传递给ExecuteNonQuery方法。 (4) ExecuteScalar方法Database db = DatabaseFactory.CreateDatabase();string sqlCommand = GetProductName;int productID=1;DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand, productID);/ Retrieve ProdcutName. ExecuteScalar returns an object, so/ we cast to the correct type (string).string productName = (string) db.ExecuteScalar(dbCommandWrapper);txtResult.Text = productName;Demo程序界面如下（注意在项目属性窗口中设置Post-build Event Command Line属性值：copy $(ProjectDir)*.config $(TargetDir)）：*作者：Rickie Lee (rickieleemail#)本文参考Enterprise Library: Data Access Application Block Quick Start范例，简单编写一个DEMO。References:1. Enterprise Library: Data Access Application Block Quick Start2. Rickie, Microsoft patterns & practices Enterprise Library January 2005 中文稿, /rickie/archive/2005/01/30/99443.html3. Rickie, Enterprise Library released! /rickie/archive/2005/01/29/99106.htmlMicrosoft Corporation 摘要：数据访问应用程序块 (Data Access Application Block) 是一个 .NET 组件，它包含经过优化的数据访问代码，可以帮助用户调用存储过程以及向 SQL Server 数据库发出 SQL 文本命令。它返回 SqlDataReader、DataSet 和 XmlReader 对象。您可以在自己的基于 .NET 的应用程序中将其作为构造块来使用，以减少需要创建、测试和维护的自定义代码的数量。您可以下载完整的 Visual C# 和 Visual Basic .NET 源代码以及综合文档。注意，本文档引用了一些仅在数据访问应用程序块的 2.0 版本中提供的功能。简介您是否正在从事基于 .NET 的应用程序数据访问代码的设计和开发工作？您是否曾经有过这种感觉 总是觉得自己在反复编写相同的数据访问代码？您是否曾经将数据访问代码包装在 Helper 函数中，以便能够在一行中调用存储过程？如果是，那么 Microsoft? Data Access Application Block for .NET 正是您所需要的。数据访问应用程序块将有关访问 Microsoft SQL Server 数据库的性能和资源管理方面的最佳做法封装在一起。您可以很方便地在自己的基于 .NET 的应用程序中将其作为构造块使用，从而减少需要创建、测试和维护的自定义代码的数量。特别是，数据访问应用程序块可以帮助您： 调用存储过程或 SQL 文本命令。 指定参数详细信息。 返回 SqlDataReader、DataSet 或 XmlReader 对象。 使用强类型的 DataSet。 例如，在引用了数据访问应用程序块的应用程序中，您可以在一行代码中调用存储过程并生成 DataSet，如下所示： Visual BasicDim ds As DataSet = SqlHelper.ExecuteDataset( _ connectionString, _ CommandType.StoredProcedure, _ getProductsByCategory, _ new SqlParameter(CategoryID, categoryID) C#DataSet ds = SqlHelper.ExecuteDataset( connectionString, CommandType.StoredProcedure, getProductsByCategory, new SqlParameter(CategoryID, categoryID); 注：.NET 的应用程序块是在对成功的基于 .NET 的应用程序进行详细研究的基础上设计的。它以源代码的形式提供，您可以原样使用，也可以针对自己的应用程序进行自定义。该应用程序块并不代表未来 Microsoft ADO.NET 程序库的发展方向。Microsoft ADO.NET 程序库是为在各种使用情况下实现对数据访问行为的精确控制而生成的。将来的 ADO.NET 版本可能会使用不同的模型来实现这个方案。本概述的其余部分包括以下内容：本页内容数据访问应用程序块包括哪些内容？下载和安装数据访问应用程序块使用数据访问应用程序块内部设计常见问题反馈和支持更多信息合作者数据访问应用程序块包括哪些内容？提供了数据访问应用程序块的源代码以及快速入门示例应用程序，您可以使用这些应用程序测试其功能。数据访问应用程序块还包括综合文档，可以帮助您使用和了解所提供的代码。Visual Studio .NET 项目提供了数据访问应用程序块的 Microsoft Visual Basic? .NET 和 Microsoft Visual C#? 源代码，以及每种语言的快速入门示例客户端应用程序，您可以使用这些应用程序测试通用的方案。这有助于加深您对数据访问应用程序块的工作原理的了解。您还可以自定义源代码以满足自己的需要。您可以编译 Visual Basic 和 C# Microsoft.ApplicationBlocks.Data 项目，以生成一个名为 Microsoft.ApplicationBlocks.Data.dll 的程序集。该程序集包括一个名为 SqlHelper 的类（其中包含用于执行数据库命令的核心功能）和一个名为 SqlhelperParameterCache 的类（它提供参数发现和缓存功能）。文档数据访问应用程序块的文档主要包括以下内容： 使用数据访问应用程序块开发应用程序。本部分包含一些快速入门示例，涵盖了许多常见的用例。这些示例可以帮助您快速而方便地开始使用数据访问应用程序块。 数据访问应用程序块的设计与实现。本部分包括背景设计原理信息，以便用户深入了解数据访问应用程序块的设计与实现。 部署和运行。本部分包括安装信息，其中包含部署和更新选项以及与安全性有关的信息。 参考。本部分包含完整的 API 参考资料，其中介绍了构成数据访问应用程序块的类和接口。 系统要求要运行 Data Access Application Block 版本 2.0，您需要满足以下条件： Microsoft Windows?2000、Windows XP Professional 或 Windows 2003 操作系统 Microsoft .NET 框架软件开发工具包 (SDK) 版本 1.1、版本 1.1 Microsoft Visual Studio?2003 开发系统 运行 SQL Server 7.0 或更高版本的数据库服务器 要运行 Data Access Application Block 版本 1.0，您需要满足以下条件： Microsoft Windows 2000 或 Windows XP Professional .NET 框架 SDK 的 RTM 版本 Visual Studio .NET 的 RTM 版本（推荐，但不必需） 运行 SQL Server 7.0 或更高版本的数据库服务器 返回页首下载和安装数据访问应用程序块您可以获取一个包含已签名的数据访问应用程序块程序集和综合文档的 Windows 安装程序文件。 安装进程将在您的 Programs 菜单中创建一个 Microsoft Application Blocks for .NET 子菜单。在 Microsoft Application Blocks for .NET 子菜单上，有一个 Data Access 子菜单，它包含用于启动文档和启动 Data Access Application Block Visual Studio.NET 解决方案的选项。您可以从 Microsoft Download Center 下载 Data Access Application Block 版本 1.0 您可以从 Microsoft Download Center 下载 Data Access Application Block 版本 2.0返回页首使用数据访问应用程序块本节讨论如何使用数据访问应用程序块来执行数据库命令和管理参数。图 1 显示了数据访问应用程序块的主要元素。图 1. 数据访问应用程序块SqlHelper 类提供了一组静态方法，可以用来对 SQL Server 数据库执行多种不同类型的命令。 SqlHelperParameterCache 类提供命令参数缓存功能，可以用来提高性能。该类由许多 Execute 方法（尤其是那些只执行存储过程的重载方法）在内部使用。数据访问客户端也可以直接使用它来缓存特定命令的特定参数集。使用 SqlHelper 类执行命令SqlHelper 类提供了 13 个 Shared (Visual Basic) 或 static (C#) 方法，如上图所示。实现的每种方法都提供一组一致的重载。这提供了一种很好的使用 SqlHelper 类来执行命令的定义模式，同时为开发人员选择访问数据的方式提供了必要的灵活性。每种方法的重载都支持不同的方法参数，因此开发人员可以确定传递连接、事务和参数信息的方式。类中实现的所有方法都支持以下重载：Visual BasicExecute* (ByVal connection As SqlConnection, _ ByVal commandType As CommandType, _ ByVal CommandText As String)Execute* (ByVal connection As SqlConnection, _ ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal ParamArray commandParameters() As SqlParameter)Execute* (ByVal connection As SqlConnection, ByVal spName As String, _ ByVal ParamArray parameterValues() As Object)Execute* (ByVal transaction As SqlTransaction, _ ByVal commandType As CommandType, _ ByVal commandText As String)Execute* (ByVal transaction As SqlTransaction, _ ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal ParamArray commandParameters() As SqlParameter)Execute* (ByVal transaction As SqlTransaction, _ ByVal spName As String, _ ByVal ParamArray parameterValues() As Object)C#Execute* (SqlConnection connection, CommandType commandType, string commandText)Execute* (SqlConnection connection, CommandType commandType, string commandText, params SqlParameter commandParameters)Execute* (SqlConnection connection, string spName, params object parameterValues)Execute* (SqlConnection connection, CommandType commandType, string commandText)Execute* (SqlConnection connection, CommandType commandType, string commandText, params SqlParameter commandParameters)Execute* (SqlConnection connection, string spName, params object parameterValues)除了这些重载以外，所有方法（ExecuteXmlReader、UpdateDataset 和 CreateCommand 除外）都提供了重载，以允许连接信息作为连接字符串而不是作为连接对象传递，如下面的方法签名所示：Visual BasicExecute* (ByVal connectionString As String, _ ByVal commandType As CommandType, _ ByVal commandText As String)Execute* (ByVal connectionString As String, _ ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal ParamArray commandParameters() As SqlParameter)Execute* (ByVal connectionString As String, ByVal spName As String, _ ByVal ParamArray parameterValues() As Object)C#Execute* (string connectionString, CommandType commandType, string commandText)Execute* (string connectionString, CommandType commandType, string commandText, params SqlParameter commandParameters)Execute* (string connectionString, string spName, params object parameterValues) 注ExecuteXmlReader 不支持连接字符串，这是因为与 SqlDataReader 对象不同，在关闭 XmlReader 时，XmlReader 对象不提供自动关闭连接的方法。如果客户端传递了连接字符串，那么当客户端完成对 XmlReader 的操作后，将无法关闭与 XmlReader 相关联的连接对象。UpdateDataset 使用已有的连接，而 CreateCommand 使用 SqlConnection 对象。通过引用数据访问应用程序块程序集并导入 Microsoft.ApplicationBlocks.Data 命名空间，您可以编写使用任何一种 SqlHelper 类方法的代码，如下面的代码示例所示：Visual BasicImports Microsoft.ApplicationBlocks.DataC#using Microsoft.ApplicationBlocks.Data;导入该命名空间后，您可以调用任何 Execute* 方法，如下面的代码示例所示：Visual BasicDim ds As DataSet = SqlHelper.ExecuteDataset( _ SERVER=(local);DATABASE=Northwind;INTEGRATED SECURITY=True;, _ CommandType.Text, SELECT * FROM Products)C#DataSet ds = SqlHelper.ExecuteDataset( SERVER=DataServer;DATABASE=Northwind;INTEGRATED SECURITY=sspi;, CommandType.Text, SELECT * FROM Products);使用 SqlHelperParameterCache 类管理参数SqlHelperParameterCache 类提供了三个可以用来管理参数的公共共享方法。它们是： CacheParameterSet。用于将 SqlParameters 数组存储到缓存中。 GetCachedParameterSet。用于检索缓存的参数数组的副本。 GetSpParameterSet。一种重载方法，用于检索指定存储过程的相应参数（首先查询一次数据库，然后缓存结果以便将来查询）。 缓存和检索参数通过使用 CacheParameterSet 方法，可以缓存 SqlParameter 对象数组。此方法通过将连接字符串和命令文本连接起来创建一个键，然后将参数数组存储在 Hashtable 中。为了从缓存中检索参数，使用了 GetCachedParameterSet 方法。此方法返回一个 SqlParameter 对象数组，这些对象已使用特定缓存（该缓存与传递给该方法的连接字符串和命令文本相对应）中参数的名称、方向和数据类型等进行了初始化。注 用作参数集的键的连接字符串通过简单的字符串比较进行匹配。用于从 GetCachedParameterSet 中检索参数的连接字符串必须与用来通过 CacheParameterSet 来存储这些参数的连接字符串完全相同。语法不同的连接字符串即使语义相同，也不会被认为是匹配的。以下代码显示了如何使用 SqlHelperParameterCache 类来缓存和检索 Transact-SQL 语句的参数。Visual BasicInitialize the connection string and command textThese will form the key used to store and retrieve the parametersConst CONN_STRING As String = _ SERVER=(local); DATABASE=Northwind; INTEGRATED SECURITY=True;Dim sql As String = _ SELECT ProductName FROM Products WHERE CategoryID=Cat + _ AND SupplierID = SupCache the parametersDim paramsToStore(1) As SqlParameterparamsToStore(0) = New SqlParameter(Cat, SqlDbType.Int)paramsToStore(1) = New SqlParameter(Sup, SqlDbType.Int)SqlHelperParameterCache.CacheParameterSet(CONN_STRING, sql, _ paramsToStore)Retrieve the parameters from the cacheDim storedParams(1) As SqlParameterstoredParams = SqlHelperParameterCache.GetCachedParameterSet( CONN_STRING, sql)storedParams(0).Value = 2storedParams(1).Value = 3Use the parameters in a commandDim ds As DataSetds = SqlHelper.ExecuteDataset(CONN_STRING, CommandType.Text, sql, _ storedParams)C#/ Initialize the connection string and command text/ These will form the key used to store and retrieve the parametersconst string CONN_STRING = SERVER=(local); DATABASE=Northwind; INTEGRATED SECURITY=True;string spName = SELECT ProductName FROM Products WHERE CategoryID=Cat + AND SupplierID = Sup;/Cache the parametersSqlParameter paramsToStore = new SqlParameter2;paramsToStore0