Declarative Transactions using ADO.NET and without Enterprise Services

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace RSI.Transactions
{
	///	<summary> 
	///		Class for running a Sql query against the main database.
	///	</summary>

	public class Sql
	{
		///	<summary>
		///		Queries the WS30 database
		///		<param name='statement'>Sql statement to execute</param>
		///		<returns>DataSet filled with the results of running the query</returns>
		///	</summary>
		
		static public DataSet RunSP(string procName, string tableName, DataSet dataSet, params IDataParameter[] parameters)
		{
			SqlConnection dbConnection				= (SqlConnection)DbConnectionUtil.Connection;
			SqlTransaction dbTransaction			= (SqlTransaction)ContextUtil.DbTransaction;
			if((dbConnection == null) || (dbTransaction == null))
				throw new System.Exception("No Connection!");

			SqlDataAdapter DSCommand				= new SqlDataAdapter();
			DSCommand.SelectCommand					= new SqlCommand(procName, dbConnection, dbTransaction);
			DSCommand.SelectCommand.CommandType		= CommandType.StoredProcedure;
			
			if(parameters != null)
			{
				foreach ( SqlParameter parameter in parameters )
					DSCommand.SelectCommand.Parameters.Add( parameter );
			}

			DSCommand.Fill(dataSet, tableName);
			return dataSet;
		}

		static public SqlDataReader RunSPReader(string procName, params IDataParameter[] parameters)
		{
			SqlConnection dbConnection				= (SqlConnection)DbConnectionUtil.Connection;
			SqlTransaction dbTransaction			= (SqlTransaction)ContextUtil.DbTransaction;
			if((dbConnection == null) || (dbTransaction == null))
				throw new System.Exception("No Connection!");

			SqlCommand sqlCommand	= new SqlCommand(procName, dbConnection, dbTransaction);
			sqlCommand.CommandType	= CommandType.StoredProcedure;

			if(parameters != null)
			{
				foreach ( SqlParameter parameter in parameters )
					sqlCommand.Parameters.Add(parameter);
			}

			return sqlCommand.ExecuteReader();
		}

		static public void RunSP(string procName, params IDataParameter[] parameters) 
		{
			SqlConnection dbConnection		= (SqlConnection)DbConnectionUtil.Connection;
			SqlTransaction dbTransaction	= (SqlTransaction)ContextUtil.DbTransaction;
			if((dbConnection == null) || (dbTransaction == null))
				throw new System.Exception("No Connection!");

			SqlCommand sqlCommand		= new SqlCommand(procName, dbConnection, dbTransaction);
			sqlCommand.CommandType		= CommandType.StoredProcedure;
			
			if(parameters != null)
			{
				foreach ( SqlParameter parameter in parameters )
				   sqlCommand.Parameters.Add( parameter );
			}
			sqlCommand.ExecuteNonQuery();
		}

		static public int RunSPReturnInt(string procName, params IDataParameter[] parameters) 
		{
			SqlConnection dbConnection		= (SqlConnection)DbConnectionUtil.Connection;
			SqlTransaction dbTransaction	= (SqlTransaction)ContextUtil.DbTransaction;
			if((dbConnection == null) || (dbTransaction == null))
				throw new System.Exception("No Connection!");

			SqlCommand sqlCommand		= new SqlCommand(procName, dbConnection, dbTransaction);
			sqlCommand.CommandType		= CommandType.StoredProcedure;
			
			if(parameters != null)
			{
				foreach ( SqlParameter parameter in parameters )
				   sqlCommand.Parameters.Add( parameter );
			}
			SqlParameter sqlReturnValue = new SqlParameter( "ReturnValue",
												SqlDbType.Int,
												/* int size */ 4,
												ParameterDirection.ReturnValue,
												/* bool isNullable */ false,
												/* byte precision */ 0,
												/* byte scale */ 0,
												/* string srcColumn */ string.Empty,
												DataRowVersion.Default,
												/* value */ null );
			sqlCommand.Parameters.Add(sqlReturnValue);

			sqlCommand.ExecuteNonQuery();

			if(sqlReturnValue.Value is int)
			{
				return (int)sqlReturnValue.Value;
			}

			return 0;
		}

		static public IDataParameter CreateIntReturnValue(string name)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.Int, 4);
			param.Direction = ParameterDirection.Output;
			return param;
		}

		static public IDataParameter CreateStringReturnValue(string name, int nLength)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.NVarChar, nLength);
			param.Direction = ParameterDirection.Output;
			return param;
		}

		static public IDataParameter CreateDateTimeReturnValue(string name, DateTime dtValue)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.DateTime);
			param.Direction = ParameterDirection.Output;
			return param;
		}

		static public IDataParameter CreateParameterWithValue(string name, int nValue)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.Int, 4);
			param.Value = nValue;
			return param;
		}

		static public IDataParameter CreateParameterWithValue(string name, bool bValue)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.Bit, 1);
			param.Value = bValue;
			return param;
		}

		static public IDataParameter CreateParameterWithValue(string name, string strValue)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.NVarChar, strValue.Length);
			param.Value = strValue;
			return param;
		}

		static public IDataParameter CreateParameterWithValue(string name, DateTime dtValue)
		{
			SqlParameter param = new SqlParameter(name, SqlDbType.DateTime);
			param.Value = dtValue;
			return param;
		}

		static public DataSet RunSql(string commandText, string tableName, DataSet dataSet, params IDataParameter[] parameters) 
		{
			SqlConnection dbConnection		= (SqlConnection)DbConnectionUtil.Connection;
			SqlTransaction dbTransaction	= (SqlTransaction)ContextUtil.DbTransaction;
			if((dbConnection == null) || (dbTransaction == null))
				throw new System.Exception("No Connection!");

			SqlDataAdapter DSCommand	= new SqlDataAdapter();
			DSCommand.SelectCommand		= new SqlCommand(commandText, dbConnection, dbTransaction);

			if(parameters != null)
			{
				foreach ( SqlParameter parameter in parameters )
				   DSCommand.SelectCommand.Parameters.Add( parameter );
			}

			DSCommand.Fill(dataSet, tableName);
			return dataSet;
		}
	}
}