Skip to Content

ADO.NET Programming using interfaces

This article aims to show how to work with ADO.NET using System.Data interfaces. For example from System.Data.IDbConnection interface are derived all connection classes, eg. SqlConnection, OleDbConnection, OdbcConnection, MySqlConnection, OracleConnection & etc... Same as all ADO.NET commands are derived from System.Data.IDbCommand interface.

Many developers initialize these objects when they use them. I offer to implement wrapper class which will handle all ADO.NET stuff for developer. As this class works with low level interfaces, like IDbCommand, IDbConnection it can handle any ADO.NET provider. Such class demo example is shown below. 

Using such wrapper class your code will be more business logic based rather than filled with ADO.NET objects initialization which makes hard readable code and if you will find some ADO.NET objects initialization error you will require just to change wrapper class rather than to change all your program code where it used.

Here is example of such class:

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

/// <summary>
///
/// (C) Copyright 2009 Lythum.lt
/// Code written by Arvydas Grigonis
///
/// P.S.
///
/// This code is not tested and possible contains errors
/// It just shows how basic concepts
///
/// </summary>
namespace Lythum.Samples.EN
{
	public class Sql
	{
		#region Attributes
		IDbConnection _Connection;		// ADO.NET connection
		IDbTransaction _Transaction;	// ADO.NET transaction
		String _LastSql;

		#endregion

		#region CTOR
		public Sql  (IDbConnection connection)
		{
			// if connection is null
			if(connection==null)
				throw new ArgumentNullException("connection");

			_Connection = connection;	// remember connection
			_Transaction = null;	// reset transaction
		}

		#endregion

		#region Connect
		/// <summary>
		/// Connecting to db with transaction handling
		/// </summary>
		/// <param name="beginTransaction"></param>
		/// <param name="commitLastTransaction"></param>
		/// <returns></returns>
		public bool Connect (
					bool beginTransaction,
					bool commitLastTransaction)
		{
			bool retVal = Connect ();

			// if connected and transaction needed
			if (retVal && beginTransaction)
			{
				// start transaction
				TransactionStart (commitLastTransaction);
			}

			return retVal;
		}

		/// <summary>
		/// Connecting to db
		/// </summary>
		/// <returns></returns>
		public bool Connect ()
		{
			try
			{
				// connecting only if connection state is closed!
				if (_Connection.State == ConnectionState.Closed)
				{
					_Connection.Open ();
				}

				return true;
			}
			catch (Exception ex)
			{
				// error processing
				Error (ex);

				return false;
			}

		}

		public void Close ()
		{
			Close (true);
		}

		public void Close (bool commitLastTransaction)
		{
			TransactionStop (commitLastTransaction);

			Close ();
		}
		#endregion

		#region Commands
		/// <summary>
		/// This method will create command 
		/// </summary>
		/// <returns></returns>
		public IDbCommand CreateCommand (bool connect)
		{
			IDbCommand cmd = null;

			try
			{
				// if sucessful connected
				if (Connect ())
				{
					// creating command
					cmd = _Connection.CreateCommand ();

					cmd.CommandType = CommandType.Text;

					// if transaction exist assign it to command
					if (_Transaction != null)
					{
						cmd.Transaction = _Transaction;
					}

				}

			}
			catch (Exception ex)
			{
				// Error processing
				Error (ex);
			}

			return cmd;
		}
		#endregion

		#region Queries
		/// <summary>
		/// DataTable result Query
		/// Used for SELECT queries
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="closeConnection"></param>
		/// <returns></returns>
		public DataTable Query (string sql, bool closeConnection)
		{
			Debug.Print ("Sql::Query: " + sql);
			if (string.IsNullOrEmpty (sql))
				throw new ArgumentException ("empty sql");

			Error ();	// reset the error

			_LastSql = sql;	// remember last sql for debug purposes

			DataTable retVal = null;	// declare return value

			IDbCommand cmd = CreateCommand (true);	// creating command

			if (cmd != null)
			{
				try
				{
					cmd.CommandText = sql;	// assign sql query

					IDataReader reader = cmd.ExecuteReader ();	// executing reader

					retVal = new DataTable ("R");	// sukuriame datatable
					retVal.Load (reader);	// fullfilling datatable with query result
				}
				catch (Exception ex)
				{
					// error processing
					Error (ex);
				}
				finally
				{
					cmd.Dispose ();	// disposing command

					// if required clocing connection
					if (closeConnection && _Transaction == null)
					{
						Close ();
					}
				}
			}
			return retVal;
		}

		/// <summary>
		/// Scalar query, returns 1st column 1st row value
		/// useful for queries like:
		/// 
		/// SELECT COUNT(*) FROM somewhere
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="closeConnection"></param>
		/// <returns></returns>
		public string QueryScalar (string sql, bool closeConnection)
		{
			// executing query
			DataTable table = Query (sql, closeConnection);

			// if result exist return it
			if (table != null)
			{
				if (table.Rows.Count > 0 && table.Columns.Count > 0)
				{
					return table.Rows[0].ItemArray[0].ToString ();
				}

			}

			return string.Empty;
		}

		/// <summary>
		/// Execute type queries method, used for queries:
		/// 
		/// INSERT, DELETE, UPDATE
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="closeConnection"></param>
		/// <returns></returns>
		public int Execute (string sql, bool closeConnection)
		{
			Debug.Print ("ExecSQL: " + sql);

			if (string.IsNullOrEmpty (sql))
				throw new ArgumentException ("empty sql");

			int retVal = -1;
			_LastSql = sql;

			IDbCommand cmd = CreateCommand (true);

			if (cmd != null)
			{
				try
				{
					cmd.CommandText = sql;
					retVal = cmd.ExecuteNonQuery ();
				}
				catch (Exception ex)
				{
					Error (ex);
				}
				finally
				{
					cmd.Dispose ();

					// close if set to close and if transaction not in progress
					if (closeConnection && _Transaction == null)
					{
						Close ();
					}
				}

			}

			return retVal;
		}


		#endregion

		#region Transactions

		public void TransactionStart (bool commitLastTransaction)
		{
			TransactionStop (commitLastTransaction);

			_Transaction = _Connection.BeginTransaction ();
		}

		public void TransactionStop (bool commitLastTransaction)
		{
			if (_Transaction != null)
			{
				if (commitLastTransaction)
				{
					_Transaction.Commit ();
				}
				else
				{
					_Transaction.Rollback ();
				}

				_Transaction.Dispose ();
				_Transaction = null;
			}
		}
		#endregion

		#region Errors

		/// <summary>
		/// Error processing
		/// </summary>
		/// <param name="ex"></param>
		void Error (Exception ex)
		{
			Debug.Print (ex.Message);
		}

		/// <summary>
		/// Resets error state to no error
		/// </summary>
		void Error ()
		{
			Debug.Print ("Error reset");
		}

		#endregion

	}
}

Here is example how to use this class:

// initializing Sql class with an ADO.NET provider connection
Sql sql = new Sql (new System.Data.SqlClient (ConnectionString));

// select query
DataTable table = sql.Query ("SELECT * FROM somewhere");

// insert record
sql.Execute ("INSERT INTO soemewhere ...");

// scalar query
String result = sql.QueryScalar ("SELECT COUNT(*) FROM somewhere");


// working with transactions
sql.TransactionStart (true);	// starting transaction
sql.Execute ("INSERT INTO soemewhere ...");
sql.Execute ("INSERT INTO soemewhere ...");
sql.TransactionStop (true);		// commiting and closing transaction