Programmeren in ASP.net 2.0 N-Tier Tutorial/Common Laag/Database.cs

C#-code: "Database.cs"

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace MessageBoard.Common
{
	public sealed class Database
	{
		private DbProviderFactory _factory;
		private string _connectionString;
		private DbConnection _con;

		private Database(ConnectionStringSettings settings)
		{
		_factory = DbProviderFactories.GetFactory(settings.ProviderName);
		_connectionString = settings.ConnectionString;
		}

		private Database(string connectionString, DbProviderFactory factory)
		{
			_factory = factory;
			_connectionString = connectionString;
		}

		public static Database GetInstance(string connectionStringName)
		{
			if (connectionStringName == null)
				throw new ArgumentNullException("The name of the connectionstring cannot be null", "connectionStringName");
			if (connectionStringName == string.Empty)
				throw new ArgumentException("String cannot be an empty string", "connectionStringName");
			ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[connectionStringName];
			if (settings == null) throw new ArgumentException("Invalid or non-existing name for the connectionstring", "connectionStringName");
			return new Database(settings);
		}

		public static Database GetInstance(string connectionString, string provider)
		{
			if(string.IsNullOrEmpty(connectionString))
				throw new ArgumentException("Connection string cannot be null or empty.");
			if(string.IsNullOrEmpty(provider))
				throw new ArgumentException("The provider name cannot be null or empty.");
			DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
			if(factory == null)
				throw new ArgumentException("Invalid provider name.");
			return new Database(connectionString, factory);
		}

		public string ConnectionString
		{
			get
			{
				return _connectionString;
			}
		}

		private DbConnection Connection
		{
			get
			{
				if (_con == null || _con.State == ConnectionState.Closed)
				{
					_con = _factory.CreateConnection();
				}
				if(_con.State != ConnectionState.Open)
					_con.ConnectionString = ConnectionString;
				return _con;
			}
		}

		public DbTransaction BeginTransaction()
		{
			DbConnection con = _factory.CreateConnection();
			con.ConnectionString = ConnectionString;
			con.Open();
			return con.BeginTransaction();
		}

		public DbCommand CreateSqlCommand(string sql, params DbParameter[] parameters)
		{
			if (sql == null)
				throw new ArgumentNullException("SQL-statement cannot be null", "sql");
			if (sql == string.Empty)
				throw new ArgumentException("SQL-Statement cannot be an empty string", "sql");
			DbCommand cmd = _factory.CreateCommand();
			cmd.CommandText = sql;
			cmd.CommandType = CommandType.Text;
			if (parameters.Length > 0)
				cmd.Parameters.AddRange(parameters);
			return cmd;
		}

		public DbCommand CreateSPCommand(string storedProc, params DbParameter[] parameters)
		{
			if (storedProc == null) throw new ArgumentNullException("Stored procedure name cannot be null", "storedProc");
			if (storedProc == string.Empty) throw new ArgumentException("Stored procedure name cannot be an empty string", "storedProc");
			DbCommand cmd = _factory.CreateCommand();
			cmd.CommandText = storedProc;
			cmd.CommandType = CommandType.StoredProcedure;
			if (parameters.Length > 0)
				cmd.Parameters.AddRange(parameters);
			return cmd;
		}

		public DbParameter CreateParameter(string name, object value)
		{
			DbParameter par = _factory.CreateParameter();
			par.ParameterName = name;
			par.SourceColumnNullMapping = true;
			par.Value = value == null ? DBNull.Value : value;
			return par;
		}

		public DbParameter CreateParameter(string name, string srcColumn, object value)
		{
			DbParameter par = _factory.CreateParameter();
			par.ParameterName = name;
			par.SourceColumn = srcColumn;
			par.SourceColumnNullMapping = true;
			par.Value = value == null ? DBNull.Value : value;
			return par;
		}

		public DbParameter CreateParameter(string name, DbType type)
		{
			DbParameter par = _factory.CreateParameter();
			par.ParameterName = name;
			par.DbType = type;
			return par;
		}

		public DbParameter CreateParameter(string name, DbType type, int size)
		{
			DbParameter par = _factory.CreateParameter();
			par.ParameterName = name;
			par.DbType = type;
			par.Size = size;
			return par;
		}

		public DbParameter CreateParameter(string name, DbType type, int size, string srcColumn)
		{
			return CreateParameter(name, type, size, ParameterDirection.Input, false, srcColumn, DataRowVersion.Current, null);
		}

		public DbParameter CreateParameter(string name, DbType type, int size, ParameterDirection direction, bool isNullable, string srcColumn, DataRowVersion srcVersion, object value)
		{
			DbParameter par = _factory.CreateParameter();
			par.ParameterName = name;
			par.DbType = type;
			par.Size = size;
			par.Direction = direction;
			par.SourceColumnNullMapping = isNullable;
			par.SourceColumn = srcColumn;
			par.SourceVersion = srcVersion;
			par.Value = value;
			return par;
		}

		public void LoadDataSet(DbCommand cmd, DataSet ds, string tableName)
		{
			LoadDataSet(cmd, ds, new string[] { tableName });
		}

		public void LoadDataSet(DbTransaction trans, DbCommand cmd, DataSet ds, string tableName)
		{
			LoadDataSet(trans, cmd, ds, new string[] { tableName });
		}

		public void LoadDataSet(DbCommand cmd, DataSet ds, string[] tableNames)
		{
			using (DbConnection con = this.Connection)
			{
				PrepareCommand(cmd, con);
				DoLoadDataSet(cmd, ds, tableNames);
			}
		}

		public void LoadDataSet(DbTransaction trans, DbCommand cmd, DataSet ds, string[] tableNames)
		{
			PrepareCommand(cmd, trans);
			DoLoadDataSet(cmd, ds, tableNames);
		}

		public DataSet ExecuteDataSet(DbCommand cmd)
		{
			DataSet ds = new DataSet();
			LoadDataSet(cmd, ds, "Table");
			return ds;
		}

		public DataSet ExecuteDataSet(DbTransaction trans, DbCommand cmd)
		{
			DataSet ds = new DataSet();
			LoadDataSet(trans, cmd, ds, "Table");
			return ds;
		}

		public object ExecuteScalar(DbCommand cmd)
		{
			using (DbConnection con = this.Connection)
			{
				PrepareCommand(cmd, con);
				con.Open();
				return cmd.ExecuteScalar();
			}
		}

		public object ExecuteScalar(DbTransaction trans, DbCommand cmd)
		{
			PrepareCommand(cmd, trans);
			return cmd.ExecuteScalar();
		}

		public int ExecuteNonQuery(DbCommand cmd)
		{
			using (DbConnection con = this.Connection)
			{
				PrepareCommand(cmd, con);
				con.Open();
				return cmd.ExecuteNonQuery();
			}
		}

		public int ExecuteNonQuery(DbTransaction trans, DbCommand cmd)
		{
			PrepareCommand(cmd, trans);
			return cmd.ExecuteNonQuery();
		}

		public DbDataReader ExecuteReader(DbCommand cmd)
		{
			DbConnection con = this.Connection;
			PrepareCommand(cmd, con);
			con.Open();
			try
			{
				return cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch
			{
				con.Close();
				throw;
			}
		}

		public DbDataReader ExecuteReader(DbTransaction trans, DbCommand cmd)
		{
			PrepareCommand(cmd, trans);
			return cmd.ExecuteReader(CommandBehavior.Default);
		}

		public int UpdateDataSet(DataSet ds, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
		{
			if (insertCommand == null && updateCommand == null && deleteCommand == null)
				throw new ArgumentException("InsertCommand, UpdateCommand and DeleteCommand cannot be null all at the same time.");
			using (DbConnection con = this.Connection)
			{
				if (insertCommand != null)
					PrepareCommand(insertCommand, con);
				if (updateCommand != null)
					PrepareCommand(updateCommand, con);
				if (deleteCommand != null)
					PrepareCommand(deleteCommand, con);
				con.Open();
				return DoUpdateDataSet(ds, tableName, insertCommand, updateCommand, deleteCommand);
			}
		}

		public int UpdateDataSet(DbTransaction trans, DataSet ds, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
		{
			if (insertCommand != null)
				PrepareCommand(insertCommand, trans);
			if (updateCommand != null)
				PrepareCommand(updateCommand, trans);
			if (deleteCommand != null)
				PrepareCommand(deleteCommand, trans);
			return DoUpdateDataSet(ds, tableName, insertCommand, updateCommand, deleteCommand);
		}

		private int DoUpdateDataSet(DataSet ds, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
		{
			if (ds == null) throw new ArgumentNullException("DataSet cannot be null", "ds");
			if (tableName == null) throw new ArgumentNullException("The name of the table to update cannot be null.", "tableName");
			if (tableName == string.Empty) throw new ArgumentException("The name of the table cannot be an empty string.", "tableName");
			using (DbDataAdapter adapter = _factory.CreateDataAdapter())
			{
				if (insertCommand != null)
					adapter.InsertCommand = insertCommand;
				if (updateCommand != null)
					adapter.UpdateCommand = updateCommand;
				if (deleteCommand != null)
					adapter.DeleteCommand = deleteCommand;
				return adapter.Update(ds, tableName);
			}
		}

		private void PrepareCommand(DbCommand cmd, DbConnection con)
		{
			if (cmd == null) throw new ArgumentNullException("Command object cannot be null", "cmd");
			if (con == null) throw new ArgumentNullException("Connection object cannot be null", "con");
			cmd.Connection = con;
		}

		private void PrepareCommand(DbCommand cmd, DbTransaction trans)
		{
			if (cmd == null) throw new ArgumentNullException("Command object cannot be null", "cmd");
			if (trans == null) throw new ArgumentNullException("Transaction object cannot be null", "trans");
			cmd.Connection = trans.Connection;
			cmd.Transaction = trans;
		}

		private void DoLoadDataSet(DbCommand cmd, DataSet ds, string[] tableNames)
		{
			if (tableNames == null) throw new ArgumentNullException("Array with tablenames cannot be null", "tableNames");
			if (tableNames.Length == 0) throw new ArgumentException("The array with tablenames contains no elements", "tableNames");
			for (int i = 0; i < tableNames.Length; i++)
			{
				if (tableNames[i] == null) throw new ArgumentNullException("Table name cannot be null", string.Concat("tableNames[", i, "]"));
				if (tableNames[i] == string.Empty) throw new ArgumentException("Table name cannot be an empty string", string.Concat("tableNames[", i, "]"));
			}
			using (DbDataAdapter adapter = _factory.CreateDataAdapter())
			{
				adapter.SelectCommand = cmd;
				string systemCreatedTableNameRoot = "Table";
				for (int i = 0; i < tableNames.Length; i++)
				{
					string systemCreatedTableName = (i == 0)? systemCreatedTableNameRoot: systemCreatedTableNameRoot + i;
					adapter.TableMappings.Add(systemCreatedTableName, tableNames[i]);
				}
				adapter.Fill(ds);
			}
		}

		#region IDisposable Members

		public void Dispose()
		{
			Connection.Dispose();
		}

		#endregion
	}
}