<#@ Template Language="C#" #>
<#@ Assembly Name="System.dll" #>
<#@ Import Namespace="System" #>
<#@ Property Processor="PropertyProcessor" Name="DataAccessClassNamespace" #>
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;

namespace <#= DataAccessClassNamespace#>
{
    public sealed class Database : IDisposable
    {
        private DbProviderFactory _factory;
        private string _connectionString;
        private DbConnection _con;
		private string _providerName;

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

        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);
				FillParameterList(cmd, 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);
				FillParameterList(cmd, 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, 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 FillParameterList(DbCommand cmd, DbParameter[] parameters)
		{
			if (_providerName.ToLower() == "System.Data.SqlClient".ToLower())
			{
				cmd.Parameters.AddRange(parameters);
				return;
			}

			Regex regex = new Regex(@"@(\w+)");
			MatchCollection matches = regex.Matches(cmd.CommandText);

			foreach (Match m in matches)
			{
				foreach (DbParameter param in cmd.Parameters)
				{
					if (param.ParameterName == m.Value)
					{
						//Remove the @ sign at the beginning 
						//of the parametername
						//when adding it to the command
						//OleDbParameter parameter = new OleDbParameter(
						//    param.Name.Substring(1),param.Value);

						param.ParameterName = param.ParameterName.Substring(1);
						//parameter.Direction = param.Type;

						cmd.Parameters.Add(param);
						cmd.CommandText = cmd.CommandText.Replace(m.Value, "?");
					}
				}
			}
		}

        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
    }
}
