sqlhelper.cs

SharpNuke源代码

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

// Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
// documentation files (the "Software"), to deal in the Software without restriction, including without limitation
// the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and
// to permit persons to whom the Software is furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all copies or substantial portions
// of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
// TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
// THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
// CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
// DEALINGS IN THE SOFTWARE.
//



// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
// common uses of SqlClient.

// ===============================================================================
// Release history
// VERSION	DESCRIPTION
//   2.0	Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================

namespace Microsoft.ApplicationBlocks.Data
{
	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() 
		{
		} // New
		
		// This method is used to attach array of SqlParameters to a SqlCommand.
		// 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.
		// Parameters:
		// -command - The command to which the parameters will be added
		// -commandParameters - an array of SqlParameters to be added to command
		private static void AttachParameters (SqlCommand command, SqlParameter[] commandParameters)
		{
			if (command == null)
			{
				throw (new ArgumentNullException("command"));
			}
			if (commandParameters != null)
			{
				foreach (SqlParameter parameter in commandParameters)
				{
					if (parameter != null)
					{
						// Check for derived output value with no value assigned
						if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
						{
							parameter.Value = DBNull.Value;
						}
						command.Parameters.Add(parameter);
					}
				}
			}
		} // AttachParameters
		
		// This method assigns dataRow column values to an array of SqlParameters.
		// Parameters:
		// -commandParameters: Array of SqlParameters to be assigned values
		// -dataRow: the dataRow used to hold the stored procedure' s parameter values
		private static void AssignParameterValues (SqlParameter[] commandParameters, DataRow dataRow)
		{
			if (commandParameters == null || dataRow == null)
			{
				// Do nothing if we get no data
				return;
			}
			
			// Set the parameters values
			int i = 0;
			foreach (SqlParameter commandParameter in commandParameters)
			{
				// Check the parameter name
				if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1)
				{
					throw (new Exception(string.Format("Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: \' {1}\' .", i, commandParameter.ParameterName)));
				}
				if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != - 1)
				{
					commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
				}
				i = i + 1;
			}
		}
		
		// This method assigns an array of values to an array of SqlParameters.
		// Parameters:
		// -commandParameters - array of SqlParameters to be assigned values
		// -array of objects holding the values to be assigned
		private static void AssignParameterValues (SqlParameter[] commandParameters, object[] parameterValues)
		{
			int i;
			int j;
			
			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."));
			}
			
			// Value array
			j = commandParameters.Length;
			for (i = 0; i < j; i++)
			{
				// If the current array value derives from IDbDataParameter, then assign its Value property
				if (parameterValues[i] is IDbDataParameter)
				{
					IDbDataParameter paramInstance = ((IDbDataParameter) parameterValues[i]);
					if (paramInstance.Value == null)
					{
						commandParameters[i].Value = DBNull.Value;
					}
					else
					{
						commandParameters[i].Value = paramInstance.Value;
					}
				}
				else if (parameterValues[i] == null)
				{
					commandParameters[i].Value = DBNull.Value;
				}
				else
				{
					commandParameters[i].Value = parameterValues[i];
				}
			}
		} // AssignParameterValues
		
		// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
		// to the provided command.
		// Parameters:
		// -command - the SqlCommand to be prepared
		// -connection - a valid SqlConnection, on which to execute this command
		// -transaction - a valid SqlTransaction, or ' null'
		// -commandType - the CommandType (stored procedure, text, etc.)
		// -commandText - the stored procedure name or T-SQL command
		// -commandParameters - an array of SqlParameters to be associated with the command or ' null' if no parameters are required
		private static void PrepareCommand (SqlCommand command, SqlConnection connection, SqlTransaction transaction, 
			CommandType commandType, string commandText, SqlParameter[] commandParameters, ref bool mustCloseConnection)
		{
			if (command == null)
			{
				throw (new ArgumentNullException("command"));
			}
			if (commandText == null || commandText.Length == 0)
			{
				throw (new ArgumentNullException("commandText"));
			}
			
			// If the provided connection is not open, we will open it
			if (connection.State != ConnectionState.Open)
			{
				connection.Open();
				mustCloseConnection = true;
			}
			else
			{
				mustCloseConnection = false;
			}
			
			// 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)
			{
				if (transaction.Connection == null)
				{
					throw (new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"));
				}
				command.Transaction = transaction;
			}
			
			// Set the command type
			command.CommandType = commandType;
			
			// Attach the command parameters if they are provided
			if (commandParameters != null)
			{
				AttachParameters(command, commandParameters);
			}
		} // PrepareCommand
		
		#endregion
		
		#region "ExecuteNonQuery"
		
		// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
		// the connection string.
		// e.g.:
		//  Dim result As Integer =  ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders")
		// Parameters:
		// -connectionString - a valid connection string for a SqlConnection
		// -commandType - the CommandType (stored procedure, text, etc.)
		// -commandText - the stored procedure name or T-SQL command
		// Returns: An int representing the number of rows affected by the command
		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));
		} // ExecuteNonQuery
		
		// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
		// using the provided parameters.
		// e.g.:
		// Dim result As Integer = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24))
		// Parameters:
		// -connectionString - a valid connection string for a SqlConnection
		// -commandType - the CommandType (stored procedure, text, etc.)
		// -commandText - the stored procedure name or T-SQL command
		// -commandParameters - an array of SqlParamters used to execute the command
		// Returns: An int representing the number of rows affected by the command
		public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			if (connectionString == null || connectionString.Length == 0)
			{
				throw (new ArgumentNullException("connectionString"));
			}
			// Create & open a SqlConnection, and dispose of it after we are done
			SqlConnection connection = null;
			try
			{
				connection = new SqlConnection(connectionString);
				connection.Open();
				
				// Call the overload that takes a connection in place of the connection string
				return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
			}
			finally
			{
				if (connection != null)
				{
					connection.Dispose();
				}
			}
		} // ExecuteNonQuery
		
		// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
		// the connection string using the provided parameter values.  This method will discover the parameters for the
		// stored procedure, and assign the values based on parameter order.
		// This method provides no access to output parameters or the stored procedure' s return value parameter.
		// e.g.:
		//  Dim result As Integer = ExecuteNonQuery(connString, "PublishOrders", 24, 36)
		// Parameters:
		// -connectionString - a valid connection string for a SqlConnection
		// -spName - the name of the stored procedure
		// -parameterValues - an array of objects to be assigned as the input values of the stored procedure
		// Returns: An int representing the number of rows affected by the command
		public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
		{
			if (connectionString == null || connectionString.Length == 0)
			{
				throw (new ArgumentNullException("connectionString"));
			}
			if (spName == null || spName.Length == 0)
			{
				throw (new ArgumentNullException("spName"));
			}
			
			SqlParameter[] commandParameters;
			
			// If we receive parameter values, we need to figure out where they go