sqlhelper.cs

这个是网上相册系统的一个架构设计说明

 // ===============================================================================
 //  Microsoft Data Access Application Block for .NET微软.NET数据访问程序块
 //   http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp (可在此网页查看)
 // 
 //  SQLHelper.cs
 // 
 //  This file contains the implementations of the SqlHelper and SqlHelperParameterCache
 //  classes. 这个文件实现了SqlHelper类和SqlHelperParameterCache类
 // 其中SqlHelper类执行各种方式的数据操作处理，而SqlHelperParameterCache类则是获得存储过程的参数集合
 //  For more information see the Data Access Application Block Implementation Overview. 
 //  
 // ===============================================================================
 //  Copyright (C) 2000-2001 Microsoft Corporation
 //  All rights reserved.
 //  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
 //  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
 //  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
 //  FITNESS FOR A PARTICULAR PURPOSE.
 // ============================================================================== 
 
using  System;
using  System.Data;
using  System.Xml;
using  System.Data.SqlClient;
using  System.Collections;


namespace  DAC
{
	/**/ ///   <summary> 
	///  The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
	///  common uses of SqlClient.
	///  SqlHelper类用来封装数据处理
	///   </summary> 
	public   sealed   class  SqlHelper
	{
		#region  private utility methods & constructors 
 
		public   static   string  CONNSTR = System.Configuration.ConfigurationSettings.AppSettings[ " SqlServer " ].ToString();
		// Since this class provides only static methods, make the default constructor private to prevent 
		// instances from being created with "new SqlHelper()". 
		private  SqlHelper()   {} 
 
 
 
		/**/ ///   <summary> 
		///  这个方法用来将命令对象和一组参数对象联系起来
		///  
		///  This method will assign a value of DbNull to any parameter with a direction of
		///  InputOutput and a value of null.  给输出类型参数对象赋空值
		///  
		///  This behavior will prevent default values from being used, but
		///  this will be the less common case than an intended pure output parameter (derived as InputOutput)
		///  where the user provided no input value.
		///   </summary> 
		///   <param name="command"> The command to which the parameters will be added </param> 
		///   <param name="commandParameters"> an array of SqlParameters tho be added to command </param> 
		private   static   void  AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
		{
			foreach  (SqlParameter p  in  commandParameters)
			{
				// check for derived output value with no value assigned 
				if  ((p.Direction  ==  ParameterDirection.InputOutput)  &&  (p.Value  ==   null ))
				{
					p.Value  =  DBNull.Value;
				} 
                
				command.Parameters.Add(p);
			} 
		} 
 
		/**/ ///   <summary> 
		///  这个方法用来给一组参数对象赋值
		///   </summary> 
		///   <param name="commandParameters"> array of SqlParameters to be assigned values </param> 
		///   <param name="parameterValues"> array of objects holding the values to be assigned </param> 
		private   static   void  AssignParameterValues(SqlParameter[] commandParameters,  object [] parameterValues)
		{
			if  ((commandParameters  ==   null )  ||  (parameterValues  ==   null )) 
			{
				// do nothing if we get no data 
				return ;
			} 
 
			//  we must have the same number of values as we pave parameters to put them in 
			if  (commandParameters.Length  !=  parameterValues.Length)
			{
				throw   new  ArgumentException( " Parameter count does not match Parameter Value count. " );
			} 
 
			// iterate through the SqlParameters, assigning the values from the corresponding position in the 
			// value array 
			for  ( int  i  =   0 , j  =  commandParameters.Length; i  <  j; i ++ )
			{
				commandParameters[i].Value  =  parameterValues[i];
			} 
		} 
 
		/**/ ///   <summary> 
		///  This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
		///  to the provided command.
		///   </summary> 
		///   <param name="command"> the SqlCommand to be prepared </param> 
		///   <param name="connection"> a valid SqlConnection, on which to execute this command </param> 
		///   <param name="transaction"> a valid SqlTransaction, or 'null' </param> 
		///   <param name="commandType"> the CommandType (stored procedure, text, etc.) </param> 
		///   <param name="commandText"> the stored procedure name or T-SQL command </param> 
		///   <param name="commandParameters"> an array of SqlParameters to be associated with the command or 'null' if no parameters are required </param> 
		private   static   void  PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType,  string  commandText, SqlParameter[] commandParameters)
		{
			// if the provided connection is not open, we will open it 
			if  (connection.State  !=  ConnectionState.Open)
			{
				connection.Open();
			} 
 
			// associate the connection with the command 
			command.Connection  =  connection;

			// set the command text (stored procedure name or SQL statement) 
			command.CommandText  =  commandText;

			// if we were provided a transaction, assign it. 
			if  (transaction  !=   null )
			{
				command.Transaction  =  transaction;
			} 
 
			// set the command type 
			command.CommandType  =  commandType;

			// attach the command parameters if they are provided 
			if  (commandParameters  !=   null )
			{
				AttachParameters(command, commandParameters);
			} 
 
			return ;
		} 
 
 
		#endregion  private utility methods & constructors 
 
		#region  ExecuteUpdate 

 
		private static void AssignParameterBoundColumn(DataTable tbl, SqlParameter[] commandParameters)
		{
			foreach(SqlParameter para in commandParameters)
			{
				string colName = para.ParameterName;
				if(colName[0] == '@')
					colName = colName.Substring(1);
				if(colName.IndexOf("Original_") == 0)
				{
					colName = colName.Substring("Original_".Length);
					para.SourceVersion = DataRowVersion.Original;
				}

				if(tbl.Columns.Contains(colName))
					para.SourceColumn = colName;
			}
		}
		/**/ ///   <summary> 
		///  执行一个指定连接串上的一个SqlCommand（不返回记录集也没有任何参数）
		///  Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
		///  the connection string. 
		///   </summary> 
		///   <remarks> 
		///  e.g.:  
		///   int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
		///   </remarks> 
		///   <param name="connectionString"> a valid connection string for a SqlConnection </param> 
		///   <param name="commandType"> the CommandType (stored procedure, text, etc.) </param> 
		///   <param name="commandText"> the stored procedure name or T-SQL command </param> 
		///   <returns> an int representing the number of rows affected by the command </returns> 
		public   static   int  ExecuteUpdate( DataTable tbl, string  connectionString, string  spInsName, string spUptName, string spDelName)
		{
			using  (SqlConnection cn  =   new  SqlConnection(connectionString))
			{
				cn.Open();

				SqlCommand sqlInsertCommand = new System.Data.SqlClient.SqlCommand(spInsName);
				SqlCommand sqlUpdateCommand = new System.Data.SqlClient.SqlCommand(spUptName);
				SqlCommand sqlDeleteCommand = new System.Data.SqlClient.SqlCommand(spDelName);

				sqlDeleteCommand.CommandType = sqlUpdateCommand.CommandType = sqlInsertCommand.CommandType = CommandType.StoredProcedure;
				sqlDeleteCommand.Connection = sqlUpdateCommand.Connection = sqlInsertCommand.Connection = cn;
			
				// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) 
				SqlParameter[] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spInsName);
				// assign the provided DataColumns to these parameters based on parameter order 
				AssignParameterBoundColumn(tbl, commandParameters);
				AttachParameters(sqlInsertCommand, commandParameters);

				commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spUptName);
				// assign the provided DataColumns to these parameters based on parameter order 
				AssignParameterBoundColumn(tbl, commandParameters);
				AttachParameters(sqlUpdateCommand, commandParameters);

				commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spDelName);
				// assign the provided DataColumns to these parameters based on parameter order 
				AssignParameterBoundColumn(tbl, commandParameters);
				AttachParameters(sqlDeleteCommand, commandParameters);

				SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
				sqlDataAdapter.DeleteCommand = sqlDeleteCommand;
				sqlDataAdapter.InsertCommand = sqlInsertCommand;
				sqlDataAdapter.UpdateCommand = sqlUpdateCommand;
			
				return sqlDataAdapter.Update(tbl);
			}
			
		} 
 
		
 
		#endregion  ExecuteUpdate 
 
		#region  ExecuteNonQuery 
 
		/**/ ///   <summary> 
		///  执行一个指定连接串上的一个SqlCommand（不返回记录集也没有任何参数）
		///  Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
		///  the connection string. 
		///   </summary> 
		///   <remarks> 
		///  e.g.:  
		///   int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
		///   </remarks> 
		///   <param name="connectionString"> a valid connection string for a SqlConnection </param> 
		///   <param name="commandType"> the CommandType (stored procedure, text, etc.) </param> 
		///   <param name="commandText"> the stored procedure name or T-SQL command </param> 
		///   <returns> an int representing the number of rows affected by the command </returns> 
		public   static   int  ExecuteNonQuery( string  connectionString, CommandType commandType,  string  commandText)
		{
			// pass through the call providing null for the set of SqlParameters 
			return  ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[]) null );
		} 
 
		/**/ ///   <summary> 
		///  执行一个指定连接串上的一个SqlCommand（不返回记录集），使用指定的参数集 
		///  using the provided parameters.
		///   </summary> 
		///   <remarks> 
		///  e.g.:  
		///   int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
		///   </remarks>