Jump to content

[C#] SQLHELPER - Class para facilitar o acesso a Base de Dados SQL


SoulOnFire

Recommended Posts

Boas a Todos!

Visto que começo a ver algum interesse por esta linguagem, vou começar, consoante o meu tempo livre (que não é assim muito), a disponibilizar aqui uma linhas de código para começar a divulgar um pouco mais c# aqui no fórum.

Eu trabalho diariamente em c# e posso-vos garantir que é uma linguagem em expansão e que terá grande futuro.

Para começar deixo aqui uma class interessante que pode ajudar na realização de todo o tipo de operações com base de dados SQL Server!

Até pode parecer difícil de perceber mas é bastante fácil de utilizar e para os mais curiosos e experientes podem ver quais os métodos utilizados

aqui fica toda a class!!!

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


namespace HSS.Classes
{
/// <summary>
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient
/// </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 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.
       /// </summary>
       /// <param name="command">The command to which the parameters will be added</param>
       /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
       private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
       {
		if( command == null ) throw new ArgumentNullException( "command" );
		if( commandParameters != null )
		{
			foreach (SqlParameter p in commandParameters)
			{
				if( p != null )
				{
					// Check for derived output value with no value assigned
					if ( ( p.Direction == ParameterDirection.InputOutput ||
						p.Direction == ParameterDirection.Input ) &&
						(p.Value == null))
					{
						p.Value = DBNull.Value;
					}
					command.Parameters.Add(p);
				}
			}
		}
       }

       /// <summary>
       /// This method assigns dataRow column values to an array of SqlParameters
       /// </summary>
       /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
       /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
       private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
       {
           if ((commandParameters == null) || (dataRow == null))
           {
               // Do nothing if we get no data
               return;
           }

		int i = 0;
           // Set the parameters values
           foreach(SqlParameter commandParameter in commandParameters)
           {
			// Check the parameter name
			if( commandParameter.ParameterName == null ||
				commandParameter.ParameterName.Length <= 1 )
				throw new System.Data.DataException(
					string.Format(CultureInfo.CurrentCulture ,
						"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++;
           }
       }

       /// <summary>
       /// This method assigns an array of values to an array of SqlParameters
       /// </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(String.Format(CultureInfo.CurrentCulture,"Parameter count does not match Parameter Value count.",null));
           }

           // 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++)
           {
               // 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];
			}
           }
       }

       /// <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>
       /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
       private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out 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)
		{
			mustCloseConnection = true;
			connection.Open();
		}
		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);
           }
           return;
       }

       #endregion private utility methods & constructors

       #region ExecuteNonQuery

       /// <summary>
       /// 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>
       /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
       /// using the provided parameters
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
       /// </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>
       /// <param name="commandParameters">An array of SqlParamters used to execute the 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, 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
           using (SqlConnection 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);
           }
       }

       /// <summary>
       /// 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 query the database to discover the parameters for the
       /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
       /// </summary>
       /// <remarks>
       /// This method provides no access to output parameters or the stored procedure's return value parameter.
       ///
       /// e.g.: 
       ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
       /// </remarks>
       /// <param name="connectionString">A valid connection string for a SqlConnection</param>
       /// <param name="spName">The name of the stored prcedure</param>
       /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
       /// <returns>An int representing the number of rows affected by the command</returns>
       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" );

           // If we receive parameter values, we need to figure out where they go
           if ((parameterValues != null) && (parameterValues.Length > 0))
           {
               // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
               SqlParameter[] commandParameters = SqlHelperParameterCache.GetSPParameterSet(connectionString, spName);

               // Assign the provided values to these parameters based on parameter order
               AssignParameterValues(commandParameters, parameterValues);

               // Call the overload that takes an array of SqlParameters
               return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
           }
           else
           {
			// Otherwise we can just call the SP without params
               return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
           }
       }

       /// <summary>
       /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
       /// </remarks>
       /// <param name="connection">A valid 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(SqlConnection connection, CommandType commandType, string commandText)
       {
           // Pass through the call providing null for the set of SqlParameters
           return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
       }

       /// <summary>
       /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
       /// using the provided parameters.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
       /// </remarks>
       /// <param name="connection">A valid SqlConnection</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 SqlParamters used to execute the command</param>
       /// <returns>An int representing the number of rows affected by the command</returns>
       public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
       {	
		if( connection == null ) throw new ArgumentNullException( "connection" );

           // Create a command and prepare it for execution
           SqlCommand cmd = new SqlCommand();
		bool mustCloseConnection = false;
           PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

           // Finally, execute the command
           int retval = cmd.ExecuteNonQuery();

           // Detach the SqlParameters from the command object, so they can be used again
           cmd.Parameters.Clear();
		if( mustCloseConnection )
			connection.Close();
           return retval;
       }

       /// <summary>
       /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
       /// using the provided parameter values.  This method will query the database to discover the parameters for the
       /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
       /// </summary>
       /// <remarks>
       /// This method provides no access to output parameters or the stored procedure's return value parameter.
       ///
       /// e.g.: 
       ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
       /// </remarks>
       /// <param name="connection">A valid SqlConnection</param>
       /// <param name="spName">The name of the stored procedure</param>
       /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
       /// <returns>An int representing the number of rows affected by the command</returns>
       public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
       {
		if( connection == null ) throw new ArgumentNullException( "connection" );
		if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

           // If we receive parameter values, we need to figure out where they go
           if ((parameterValues != null) && (parameterValues.Length > 0))
           {
               // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
               SqlParameter[] commandParameters = SqlHelperParameterCache.GetSPParameterSet(connection, spName);

               // Assign the provided values to these parameters based on parameter order
               AssignParameterValues(commandParameters, parameterValues);

               // Call the overload that takes an array of SqlParameters
               return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
           }
           else
           {
			// Otherwise we can just call the SP without params
               return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
           }
       }

       /// <summary>
       /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
       /// </remarks>
       /// <param name="transaction">A valid SqlTransaction</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(SqlTransaction transaction, CommandType commandType, string commandText)
       {
           // Pass through the call providing null for the set of SqlParameters
           return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
       }

       /// <summary>
       /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
       /// using the provided parameters.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
       /// </remarks>
       /// <param name="transaction">A valid SqlTransaction</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 SqlParamters used to execute the command</param>
       /// <returns>An int representing the number of rows affected by the command</returns>
	public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
	{
		if( transaction == null ) throw new ArgumentNullException( "transaction" );
		if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );

		// Create a command and prepare it for execution
		SqlCommand cmd = new SqlCommand();
		bool mustCloseConnection = false;
		PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

		// Finally, execute the command
		int retval = cmd.ExecuteNonQuery();

		// Detach the SqlParameters from the command object, so they can be used again
		cmd.Parameters.Clear();
		return retval;
	}

       /// <summary>
       /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
       /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the
       /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
       /// </summary>
       /// <remarks>
       /// This method provides no access to output parameters or the stored procedure's return value parameter.
       ///
       /// e.g.: 
       ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
       /// </remarks>
       /// <param name="transaction">A valid SqlTransaction</param>
       /// <param name="spName">The name of the stored procedure</param>
       /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
       /// <returns>An int representing the number of rows affected by the command</returns>
       public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
       {
		if( transaction == null ) throw new ArgumentNullException( "transaction" );
		if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
		if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

		// If we receive parameter values, we need to figure out where they go
           if ((parameterValues != null) && (parameterValues.Length > 0))
           {
               // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
               SqlParameter[] commandParameters = SqlHelperParameterCache.GetSPParameterSet(transaction.Connection, spName);

               // Assign the provided values to these parameters based on parameter order
               AssignParameterValues(commandParameters, parameterValues);

               // Call the overload that takes an array of SqlParameters
               return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
           }
           else
           {
			// Otherwise we can just call the SP without params
               return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
           }
       }

       #endregion ExecuteNonQuery

       #region ExecuteDataSet

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
       /// the connection string.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(connString, CommandType.StoredProcedure, "GetOrders");
       /// </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>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText)
       {
           // Pass through the call providing null for the set of SqlParameters
           return ExecuteDataSet(connectionString, commandType, commandText, (SqlParameter[])null);
       }

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
       /// using the provided parameters.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
       /// </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>
       /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(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
           using (SqlConnection connection = new SqlConnection(connectionString))
           {
               connection.Open();

               // Call the overload that takes a connection in place of the connection string
               return ExecuteDataSet(connection, commandType, commandText, commandParameters);
           }
       }

       /// <summary>
       /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
       /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the
       /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
       /// </summary>
       /// <remarks>
       /// This method provides no access to output parameters or the stored procedure's return value parameter.
       ///
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(connString, "GetOrders", 24, 36);
       /// </remarks>
       /// <param name="connectionString">A valid connection string for a SqlConnection</param>
       /// <param name="spName">The name of the stored procedure</param>
       /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(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" );

		// If we receive parameter values, we need to figure out where they go
           if ((parameterValues != null) && (parameterValues.Length > 0))
           {
               // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
               SqlParameter[] commandParameters = SqlHelperParameterCache.GetSPParameterSet(connectionString, spName);

               // Assign the provided values to these parameters based on parameter order
               AssignParameterValues(commandParameters, parameterValues);

               // Call the overload that takes an array of SqlParameters
               return ExecuteDataSet(connectionString, CommandType.StoredProcedure, spName, commandParameters);
           }
           else
           {
			// Otherwise we can just call the SP without params
               return ExecuteDataSet(connectionString, CommandType.StoredProcedure, spName);
           }
       }

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(conn, CommandType.StoredProcedure, "GetOrders");
       /// </remarks>
       /// <param name="connection">A valid 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>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText)
       {
           // Pass through the call providing null for the set of SqlParameters
           return ExecuteDataSet(connection, commandType, commandText, (SqlParameter[])null);
       }

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
       /// using the provided parameters.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
       /// </remarks>
       /// <param name="connection">A valid SqlConnection</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 SqlParamters used to execute the command</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
	public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
	{
		if( connection == null ) throw new ArgumentNullException( "connection" );

		// Create a command and prepare it for execution
		SqlCommand cmd = new SqlCommand();
		bool mustCloseConnection = false;
		PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

		// Create the DataAdapter & DataSet
		using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
		{
			DataSet ds = new DataSet();
			ds.Locale=CultureInfo.CurrentCulture;

			// Fill the DataSet using default values for DataTable names, etc
			da.Fill(ds);

			// Detach the SqlParameters from the command object, so they can be used again
			cmd.Parameters.Clear();

			if( mustCloseConnection )
				connection.Close();

			// Return the dataset
			return ds;
		}	
	}

       /// <summary>
       /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
       /// using the provided parameter values.  This method will query the database to discover the parameters for the
       /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
       /// </summary>
       /// <remarks>
       /// This method provides no access to output parameters or the stored procedure's return value parameter.
       ///
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(conn, "GetOrders", 24, 36);
       /// </remarks>
       /// <param name="connection">A valid SqlConnection</param>
       /// <param name="spName">The name of the stored procedure</param>
       /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(SqlConnection connection, string spName, params object[] parameterValues)
       {
		if( connection == null ) throw new ArgumentNullException( "connection" );
		if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

           // If we receive parameter values, we need to figure out where they go
           if ((parameterValues != null) && (parameterValues.Length > 0))
           {
               // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
               SqlParameter[] commandParameters = SqlHelperParameterCache.GetSPParameterSet(connection, spName);

               // Assign the provided values to these parameters based on parameter order
               AssignParameterValues(commandParameters, parameterValues);

               // Call the overload that takes an array of SqlParameters
               return ExecuteDataSet(connection, CommandType.StoredProcedure, spName, commandParameters);
           }
           else
           {
			// Otherwise we can just call the SP without params
               return ExecuteDataSet(connection, CommandType.StoredProcedure, spName);
           }
       }

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(trans, CommandType.StoredProcedure, "GetOrders");
       /// </remarks>
       /// <param name="transaction">A valid SqlTransaction</param>
       /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
       /// <param name="commandText">The stored procedure name or T-SQL command</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(SqlTransaction transaction, CommandType commandType, string commandText)
       {
           // Pass through the call providing null for the set of SqlParameters
           return ExecuteDataSet(transaction, commandType, commandText, (SqlParameter[])null);
       }

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
       /// using the provided parameters.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
       /// </remarks>
       /// <param name="transaction">A valid SqlTransaction</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 SqlParamters used to execute the command</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
	public static DataSet ExecuteDataSet(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
	{
		if( transaction == null ) throw new ArgumentNullException( "transaction" );
		if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );

		// Create a command and prepare it for execution
		SqlCommand cmd = new SqlCommand();
		bool mustCloseConnection = false;
		PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

		// Create the DataAdapter & DataSet
		using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
		{
			DataSet ds = new DataSet();
			ds.Locale=CultureInfo.CurrentCulture;

			// Fill the DataSet using default values for DataTable names, etc
			da.Fill(ds);

			// Detach the SqlParameters from the command object, so they can be used again
			cmd.Parameters.Clear();

			// Return the dataset
			return ds;
		}	
	}

       /// <summary>
       /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
       /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the
       /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
       /// </summary>
       /// <remarks>
       /// This method provides no access to output parameters or the stored procedure's return value parameter.
       ///
       /// e.g.: 
       ///  DataSet ds = ExecuteDataSet(trans, "GetOrders", 24, 36);
       /// </remarks>
       /// <param name="transaction">A valid SqlTransaction</param>
       /// <param name="spName">The name of the stored procedure</param>
       /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
       /// <returns>A dataset containing the resultset generated by the command</returns>
       public static DataSet ExecuteDataSet(SqlTransaction transaction, string spName, params object[] parameterValues)
       {
		if( transaction == null ) throw new ArgumentNullException( "transaction" );
		if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
		if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

		// If we receive parameter values, we need to figure out where they go
           if ((parameterValues != null) && (parameterValues.Length > 0))
           {
               // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
               SqlParameter[] commandParameters = SqlHelperParameterCache.GetSPParameterSet(transaction.Connection, spName);

               // Assign the provided values to these parameters based on parameter order
               AssignParameterValues(commandParameters, parameterValues);

               // Call the overload that takes an array of SqlParameters
               return ExecuteDataSet(transaction, CommandType.StoredProcedure, spName, commandParameters);
           }
           else
           {
			// Otherwise we can just call the SP without params
               return ExecuteDataSet(transaction, CommandType.StoredProcedure, spName);
           }
       }

       #endregion ExecuteDataSet

       #region ExecuteReader

       /// <summary>
       /// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
       /// we can set the appropriate CommandBehavior when calling ExecuteReader()
       /// </summary>
       private enum SqlConnectionOwnership	
       {
           /// <summary>Connection is owned and managed by SqlHelper</summary>
           Internal,
           /// <summary>Connection is owned and managed by the caller</summary>
           External
       }

       /// <summary>
       /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
       /// </summary>
       /// <remarks>
       /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
       ///
       /// If the caller provided the connection, we want to leave it to them to manage.
       /// </remarks>
       /// <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>
       /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
       /// <returns>SqlDataReader containing the results of the command</returns>
       private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
       {	
		if( connection == null ) throw new ArgumentNullException( "connection" );

		bool mustCloseConnection = false;
           // Create a command and prepare it for execution
           SqlCommand cmd = new SqlCommand();
		try
		{
			PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

			// Create a reader
			SqlDataReader dataReader;

			// Call ExecuteReader with the appropriate CommandBehavior
			if (connectionOwnership == SqlConnectionOwnership.External)
			{
				dataReader = cmd.ExecuteReader();
			}
			else
			{
				dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}

			// Detach the SqlParameters from the command object, so they can be used again.
			// HACK: There is a problem here, the output parameter values are fletched
			// when the reader is closed, so if the parameters are detached from the command
			// then the SqlReader can´t set its values.
			// When this happen, the parameters can´t be used again in other command.
			bool canClear = true;
			foreach(SqlParameter commandParameter in cmd.Parameters)
			{
				if (commandParameter.Direction != ParameterDirection.Input)
					canClear = false;
			}

			if (canClear)
			{
				cmd.Parameters.Clear();
			}

			return dataReader;
		}
		catch
		{
			if( mustCloseConnection )
				connection.Close();
			throw;
		}
       }

       /// <summary>
       /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
       /// the connection string.
       /// </summary>
       /// <remarks>
       /// e.g.: 
       ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
       /// </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>A SqlDataReader containing the resultset generated by the command</returns>
       public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
       {
           // Pass through the call providing null for the set of SqlParameters
           return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
       }

Artigo na wiki aqui.

Fikem Bem!!!Miguel Duarte - (SoulOnFire)O meu BLOG - XAML E WPF - http://wpfpt.wordpress.com/

Link to comment
Share on other sites

  • 5 months later...
  • 1 year later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.