sqlhelper.cs

比较好的异步数据库访问示例

//===============================================================================
// Improved Data Access Application Block for .NET
// Original version can be found at:
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// Copyright (C) 2002-2003 Jurgen Appelo, Ordina Public
// 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;
using System.Diagnostics; // needed for logging errors to the EventLog
using System.Configuration; // needed for obtaining ConfigurationSettings
using System.Runtime.Remoting.Messaging; // needed for asynchronous method calls

namespace Ordina.Data.SqlClient.CrudHelper
{
	/// <summary>
	/// The SqlHelper class is intended to be used as a thin layer between an
	/// application and an SQL server database. It abstracts the technical issues
	/// involved in fetching and updating data.
	/// </summary>
	public sealed class SqlHelper
	{
		#region private utility methods & constructors

		//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 is used to attach array of IDataParameters to an IDbCommand.
		/// 
		/// 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 IDataParameters to be added to command</param>
		private static void AttachParameters(IDbCommand command, IDataParameter[] 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>
		/// This method assigns an array of values to an array of IDataParameters.
		/// </summary>
		/// <param name="commandParameters">array of IDataParameters to be assigned values</param>
		/// <param name="parameterValues">array of objects holding the values to be assigned</param>
		private static void AssignParameterValues(IDataParameter[] 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 have parameters to put them in
			if (commandParameters.Length != parameterValues.Length)
			{
				throw new ArgumentException("Parameter count does not match Parameter Value count.");
			}

			//iterate through the parameters, 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 IDbCommand to be prepared</param>
		/// <param name="connection">a valid IDbConnection, on which to execute this command</param>
		/// <param name="transaction">a valid IDbTransaction, 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 IDataParameters to be associated with the command or 'null' if no parameters are required</param>
		private static void PrepareCommand(IDbCommand command, IDbConnection connection, 
			IDbTransaction transaction, CommandType commandType, string commandText, 
			IDataParameter[] commandParameters)
		{
			//if the provided connection is not open, we will open it
			//the calling method must remember to close it afterwards!
			if (connection.State != ConnectionState.Open)
			{
				connection.Open();
			}

			//associate the connection with the command
			command.Connection = (SqlConnection)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 = (SqlTransaction)transaction;
			}

			//set the command type
			command.CommandType = commandType;

			//attach the command parameters if they are provided
			if (commandParameters != null)
			{
				AttachParameters(command, commandParameters);
			}

			return;
		}

		/// <summary>
		/// This method logs an SQL Server error to the event log.
		/// </summary>
		/// <param name="sqlex">The error raised by the SqlClient data provider.</param>
		private static void LogException(SqlException sqlex)
		{
			EventLog el = new EventLog();
			el.Source = (ConfigurationSettings.AppSettings["ErrorLog"] != null) ? ConfigurationSettings.AppSettings["ErrorLog"] : "SqlHelper";
			string strMessage;
			// Write main error message to the event log
			strMessage = "Exception Number: " + sqlex.Number + 
				" (" + sqlex.Message + ") has occurred";
			el.WriteEntry(strMessage, EventLogEntryType.Error);

			foreach (SqlError sqle in sqlex.Errors)
			{
				// Write individual errors to the event log
				strMessage = "Message: " + sqle.Message +
					" Number: " + sqle.Number +
					" Procedure: " + sqle.Procedure +
					" Server: " + sqle.Server +
					" Source: " + sqle.Source +
					" State: " + sqle.State +
					" Severity: " + sqle.Class +
					" LineNumber: " + sqle.LineNumber;
				el.WriteEntry(strMessage, EventLogEntryType.Error);
			}
		}

		#endregion private utility methods & constructors

		#region public methods and properties

		/// <summary>
		/// The method creates an SqlConnection object and returns it casted as IDbConnection.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns>An SqlConnection object casted as IDbConnection</returns>
		public static IDbConnection GetConnection(string connectionString)
		{
			return new SqlConnection(connectionString);
		}

		#endregion public factory methods

		#region ExecuteNonQuery

		/// <summary>
		/// Execute a command that returns no resultset
		/// </summary>
		/// <param name="connection">a valid IDbConnection, on which to execute this command</param>
		/// <param name="transaction">a valid IDbTransaction, 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 IDataParameters to be associated with the command or 'null' if no parameters are required</param>
		/// <returns>an int representing the number of rows affected by the command</returns>
		private static int ExecuteNonQuery(IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
		{
			//create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			//remember the state of the connection because PrepareCommand might change it
			ConnectionState connState = connection.State;
			//link all objects to the command
			PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
			
			int retval;
			try
			{
				//finally, execute the command.
				retval = cmd.ExecuteNonQuery();
			}
			catch (SqlException e)
			{
				//Handle data access exception condition
				LogException(e);
				//Wrap the current exception and re-throw the new exception
				throw e;
			}
			finally
			{
				//detach the IDataParameters from the command object, so they can be used again.
				cmd.Parameters.Clear();
				//close the connection if it was closed when we started
				if (connState == ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			
			return retval;
		}

		/// <summary>
		/// Execute a command that returns no resultset
		/// </summary>
		/// <param name="connectionString">a valid connection string for a connection yet to be opened</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 IDataParameters
			return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
		}

		/// <summary>
		/// Execute a command that returns no resultset
		/// </summary>
		/// <param name="connectionString">a valid connection string for a connection yet to be opened</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command</param>