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

[SoulOnFire]

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.

[SoulOnFire]

LOL... era para por todo o codigo mas acho k é muita coisa....

saquem o ficheiro do 1º post...

aguardo comentarios, criticas e sugestões!!

[Hipnoted]

Concordo com tudo o que disseste.

C# é e será uma das linguagens mais poderosas do futuro.

Continua a postar tutoriais, eu sou um dos interessados. 😛

[SoulOnFire]

😛 espero ter tempo para colocar aki uns porreiros

[Evil Knievel]

Aonde é que eu ja vi isto... lol...

[SoulOnFire]

Aonde é que eu ja vi isto... lol...

Sim... foi mesmo ai!!! 😁

[skin]

Este artigo já se encontra na wiki: http://wiki.portugal-a-programar.pt/dev_net:csharp:snippet:sqlhelper