Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » ADO.NET » Downloads
 
Add your own
alternative version

Declarative Transactions using ADO.NET and without Enterprise Services.

, 26 Oct 2001
Sometimes it is nice to prototype up a simple database application. This code may help, by providing the automatic transactional model of COM+ in a non-COM+ environment. This example uses "Interception" to provide automatic transactioning support for non-COM+ classes.
declarativetransactions_src.zip
codeproject_template
TransactionAttribute
bin
Debug
obj
Debug
temp
TempPE
TransactionAttribute.csproj.user
TransactionExample
bin
Debug
obj
Debug
temp
TempPE
TransactionExample.csproj.user
TransactionExample.suo
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;
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Sandy Place

Canada Canada
No Biography provided

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 27 Oct 2001
Article Copyright 2001 by Sandy Place
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid