using System;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Text;
namespace FatBelly.Utilities.DatabaseAccess
{
/// <summary>
/// This class inherits the IDBManager interface and completes the
/// database connections, processing, etc
/// </summary>
public sealed class DatabaseManager : IDBManager, IDisposable
{
private IDbConnection idbConnection;
private IDataReader idataReader;
private IDbCommand idbCommand;
private DBProvider providerType;
private IDbTransaction idbTransaction = null;
private IDbDataParameter[] idbParameters = null;
private string strConnection;
public DatabaseManager()
{
// Get the application configuration file.
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
// Get the conectionStrings section.
ConnectionStringsSection csSection = config.ConnectionStrings;
ConnectionStringSettings csConnection = csSection.ConnectionStrings["STAGE"];
strConnection = csConnection.ConnectionString;
}
public DatabaseManager(DBProvider providerType)
{
this.providerType = providerType;
}
public DatabaseManager(DBProvider providerType, string
connectionString)
{
this.providerType = providerType;
this.strConnection = connectionString;
}
public IDbConnection Connection
{
get
{
return idbConnection;
}
}
public IDataReader DataReader
{
get
{
return idataReader;
}
set
{
idataReader = value;
}
}
public DBProvider DBProviderType
{
get
{
return providerType;
}
set
{
providerType = value;
}
}
public string DBConnectionString
{
get
{
return strConnection;
}
set
{
strConnection = value;
}
}
public IDbCommand Command
{
get
{
return idbCommand;
}
}
public IDbTransaction Transaction
{
get
{
return idbTransaction;
}
}
public IDbDataParameter[] Parameters
{
get
{
return idbParameters;
}
}
public void Open()
{
idbConnection =
DatabaseManagerFactory.GetConnection(this.providerType);
idbConnection.ConnectionString = this.DBConnectionString;
if (idbConnection.State != ConnectionState.Open)
idbConnection.Open();
this.idbCommand = DatabaseManagerFactory.GetCommand(this.DBProviderType);
}
public void Close()
{
if (idbConnection.State != ConnectionState.Closed)
idbConnection.Close();
}
public void Dispose()
{
GC.SuppressFinalize(this);
this.Close();
this.idbCommand = null;
this.idbTransaction = null;
this.idbConnection = null;
}
public void CreateParameters(int paramsCount)
{
idbParameters = new IDbDataParameter[paramsCount];
idbParameters = DatabaseManagerFactory.GetParameters(this.DBProviderType,
paramsCount);
}
/// <summary>
/// Add parameter, but no value or direction
/// </summary>
/// <param name="index">The index of the parameter to add in to the collection</param>
/// <param name="paramName">The name of the parameter</param>
public void AddParameters(int index, string paramName)
{
if (index < idbParameters.Length)
{
idbParameters[index].ParameterName = paramName;
idbParameters[index].Value = DBNull.Value;
}
}
/// <summary>
/// Add parameter, including direction but no value
/// </summary>
/// <param name="index">The index of the parameter to add in to the collection</param>
/// <param name="paramName">The name of the parameter</param>
/// <param name="pParamDirection">The parameter direction</param>
public void AddParameters(int index, string paramName, ParameterDirection pParamDirection)
{
if (index < idbParameters.Length)
{
idbParameters[index].ParameterName = paramName;
idbParameters[index].Value = DBNull.Value;
idbParameters[index].Direction = pParamDirection;
}
}
/// <summary>
/// Add parameter, with value but no direction
/// </summary>
/// <param name="index">The index of the parameter to add in to the collection</param>
/// <param name="paramName">The name of the parameter</param>
/// <param name="paramValue">The value of the parameter</param>
public void AddParameters(int index, string paramName, object paramValue)
{
if (index < idbParameters.Length)
{
idbParameters[index].ParameterName = paramName;
if (paramValue.GetType().Name.Equals("Boolean") == true)
{
idbParameters[index].Value = (bool)paramValue == true ? 1 : 0;
}
else
{
idbParameters[index].Value = paramValue;
}
}
}
/// <summary>
/// Add parameter, including value and direction
/// </summary>
/// <param name="index">The index of the parameter to add in to the collection</param>
/// <param name="paramName">The name of the parameter</param>
/// <param name="paramValue">The value of the parameter</param>
/// <param name="pParamDirection">The parameter direction</param>
public void AddParameters(int index, string paramName, object paramValue, ParameterDirection pParamDirection)
{
if (index < idbParameters.Length)
{
idbParameters[index].ParameterName = paramName;
if (paramValue.GetType().Name.Equals("Boolean") == true)
{
idbParameters[index].Value = (bool)paramValue == true ? 1 : 0;
}
else
{
idbParameters[index].Value = paramValue;
}
idbParameters[index].Direction = pParamDirection;
}
}
public void SetParameterValue(int index, object paramValue)
{
if (index < idbParameters.Length)
{
if (paramValue.GetType().Name.Equals("Boolean") == true)
{
idbParameters[index].Value = (bool)paramValue == true ? 1 : 0;
}
else
{
idbParameters[index].Value = paramValue;
}
}
}
public void BeginTransaction()
{
if (this.idbTransaction == null)
idbTransaction =
DatabaseManagerFactory.GetTransaction(this.DBProviderType);
this.idbCommand.Transaction = idbTransaction;
}
public void CommitTransaction()
{
if (this.idbTransaction != null)
this.idbTransaction.Commit();
idbTransaction = null;
}
public IDataReader ExecuteReader(CommandType commandType, string commandText)
{
// If this is an ODBC call, then we need to change the command string
if (this.providerType == DBProvider.Odbc && commandType.Equals(CommandType.StoredProcedure))
{
StringBuilder fullCommandText = new StringBuilder("");
bool hasReturn = false;
if ((this.Parameters == null) == false)
{
for (int i = 0; i < this.Parameters.Length; i++)
{
fullCommandText.Append(" ?");
if (this.Parameters[i].Direction == ParameterDirection.ReturnValue)
{
hasReturn = true;
}
}
}
if (hasReturn == true)
{
commandText = "{ ? call " + commandText + fullCommandText.ToString() + "}";
}
else
{
commandText = "{ call " + commandText + fullCommandText.ToString() + "}";
}
}
this.idbCommand = DatabaseManagerFactory.GetCommand(this.DBProviderType);
idbCommand.Connection = this.Connection;
PrepareCommand(idbCommand, this.Connection, this.Transaction,
commandType,
commandText, this.Parameters);
this.DataReader = idbCommand.ExecuteReader();
idbCommand.Parameters.Clear();
return this.DataReader;
}
public void CloseReader()
{
if (this.DataReader != null)
this.DataReader.Close();
}
private void AttachParameters(IDbCommand command,
IDbDataParameter[] commandParameters)
{
foreach (IDbDataParameter idbParameter in commandParameters)
{
if ((idbParameter.Direction == ParameterDirection.InputOutput)
&&
(idbParameter.Value == null))
{
idbParameter.Value = DBNull.Value;
}
command.Parameters.Add(idbParameter);
}
}
private void PrepareCommand(IDbCommand command, IDbConnection connection,
IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
}
public int ExecuteNonQuery(CommandType commandType, string commandText)
{
// If this is an ODBC call, then we need to change the command string
if (this.providerType == DBProvider.Odbc && commandType.Equals(CommandType.StoredProcedure))
{
commandText = "{ call " + commandText;
for (int i = 0; i < this.Parameters.Length; i++)
{
commandText += " ?";
}
commandText += " }";
}
this.idbCommand = DatabaseManagerFactory.GetCommand(this.DBProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
int returnValue = idbCommand.ExecuteNonQuery();
idbCommand.Parameters.Clear();
return returnValue;
}
public object ExecuteScalar(CommandType commandType, string commandText)
{
// If this is an ODBC call, then we need to change the command string
if (this.providerType == DBProvider.Odbc && commandType.Equals(CommandType.StoredProcedure))
{
commandText = "{ call " + commandText;
for (int i = 0; i < this.Parameters.Length; i++)
{
commandText += " ?";
}
commandText += " }";
}
this.idbCommand = DatabaseManagerFactory.GetCommand(this.DBProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction,
commandType,
commandText, this.Parameters);
object returnValue = idbCommand.ExecuteScalar();
idbCommand.Parameters.Clear();
return returnValue;
}
public DataSet ExecuteDataSet(CommandType commandType, string commandText)
{
// If this is an ODBC call, then we need to change the command string
if (this.providerType == DBProvider.Odbc && commandType.Equals(CommandType.StoredProcedure))
{
commandText = "{ call " + commandText;
for (int i = 0; i < this.Parameters.Length; i++)
{
commandText += " ?";
}
commandText += " }";
}
this.idbCommand = DatabaseManagerFactory.GetCommand(this.DBProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction,commandType,commandText, this.Parameters);
IDbDataAdapter dataAdapter = DatabaseManagerFactory.GetDataAdapter(this.DBProviderType);
dataAdapter.SelectCommand = idbCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
idbCommand.Parameters.Clear();
return dataSet;
}
}
}