|
| using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
namespace SpeedDataAccess
{
/// <summary>
/// This abstract class should be inherited by all data classes
/// used within the SPEED project. This base provides the ability
/// for descendant class to participate in a database transaction
/// initiated by a business object.
/// </summary>
public class BaseData
{
private bool m_bIsInTransaction = false;
private SqlConnection m_Cn;
private SqlTransaction m_Txn;
public BaseData()
{
m_bIsInTransaction = false;
}
//--------------------------------------------------
//This constructor is used when the data object is
//participating in a database transaction;
//--------------------------------------------------
// TODO: add contructor for participating in transaction
public BaseData(SqlConnection cn, SqlTransaction txn)
{
m_Cn = cn;
Transaction = txn;
if(txn != null)
{
m_bIsInTransaction = true;
}
}
//---------------------------------------------------
//Returns a boolean indicating whether or not this
//object is currently participating in a transaction.
//----------------------------------------------------
public bool IsInTransaction()
{
return m_bIsInTransaction;
}
//----------------------------------------------------
//Responsible for creating and opening a database
//connection. If the object is part of a transaction
//the database connection passed during the constructor
//is returned.
//-----------------------------------------------------
protected SqlConnection OpenDbConnection(string sCnString)
{
if (IsInTransaction())
{
return m_Cn;
}
else
{
m_Cn = new SqlConnection(sCnString);
m_Cn.Open();
return m_Cn;
}
}
//------------------------------------------------------------
//Used to close the database connection used by this class.
//A special transactional check is done so the connection is
//not closed if this object is participating in a transaction.
//------------------------------------------------------------
protected void CloseDbConnection()
{
if (!IsInTransaction())
{
if (m_Cn != null)
{
m_Cn.Close();
}
}
}
//----------------------------------------------------
//Returns a reference to the internal connection object
//used by this class.
//-----------------------------------------------------
protected SqlConnection Connection
{
get
{
if (m_Cn == null)
{
m_Cn = new SqlConnection();
}
return m_Cn;
}
}
//----------------------------------------------------------
//Allows the interal Transaction object of this class to be set
//and returned.
//-------------------------------------------------------------
protected SqlTransaction Transaction
{
get
{
return m_Txn;
}
set
{
m_Txn = value;
}
}
// ****************************************
**************************************
//ExecuteSQL() is an overloaded method that executes either a Stored
Procedure
//or embedded SQL. If you are trying to execute a stored procedure,
pass the arguments
//as an Array of SQLParameters. If there are no parms, pass null.
//
//By Reference Return Values:
//1. DataSet
//2. IDataReader
//3. Nothing (executes and update,insert,delete)
// ****************************************
****************************************
*
public void ExecuteSQL(string sProcName, SqlParameter[] parms,
DataTable dt)
{
try
{
OpenDbConnection(SpeedUDL.SPEED_CONNECT);
//Create and Oracle Command object.
SqlCommand cmd = CreateSQLCommand(sProcName, parms);
//Set the connection object of the command.
cmd.Connection = this.Connection;
//Create and Data Adapter object used to fill the DataSet.
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
CloseDbConnection();
}
}
public void ExecuteSQL(string sSQL, DataTable dt)
{
try
{
OpenDbConnection(SpeedUDL.SPEED_CONNECT);
//Create and Oracle Command object.
SqlCommand cmd = CreateSQLCommand(sSQL);
//Set the connection object of the command.
cmd.Connection = this.Connection;
//Create and Data Adapter object used to fill the DataSet.
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
CloseDbConnection();
}
}
public void ExecuteSQL(string sProcName, SqlParameter[] parms, ref
IDataReader dr)
{
try
{
//Create and Oracle Command object.
SqlCommand cmd = CreateSQLCommand(sProcName, parms);
//Open a database connection
OpenDbConnection(SpeedUDL.SPEED_CONNECT);
//Set the connection object of the command.
cmd.Connection = Connection;
//Get the DataReader Object. If not in a tranasction have Reader
//close the connection.
if (IsInTransaction())
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception e)
{
CloseDbConnection();
throw e;
}
}
public void ExecuteSQL(string sSQL, ref IDataReader dr)
{
try
{
//Create and Oracle Command object.
SqlCommand cmd = CreateSQLCommand(sSQL);
OpenDbConnection(SpeedUDL.SPEED_CONNECT);
//Set the connection object of the command.
cmd.Connection = Connection;
//Get the DataReader Object. If not in a tranasction have Reader
//close the connection.
if (IsInTransaction())
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception e)
{
CloseDbConnection();
throw e;
}
}
public void ExecuteSQL(string sProcName, SqlParameter[] parms)
{
try
{
OpenDbConnection(SpeedUDL.SPEED_CONNECT);
//Create and Oracle Command object.
SqlCommand cmd = CreateSQLCommand(sProcName, parms);
//Set the connection object of the command.
cmd.Connection = this.Connection;
//Execute the Procedure.
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseDbConnection();
}
}
public void ExecuteSQL(string sSQL)
{
try
{
OpenDbConnection(SpeedUDL.SPEED_CONNECT);
//Create and Oracle Command object.
SqlCommand cmd = CreateSQLCommand(sSQL);
//Set the connection object of the command.
cmd.Connection = this.Connection;
//Execute the Procedure.
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseDbConnection();
}
}
// ****************************************
***************************************
//The following 2 methods are used to create SQLCommand Objects.
Pass a SQL
//Parameter array of null if there are no arguments to the
procedure.
// ****************************************
****************************************
private SqlCommand CreateSQLCommand(string sProcName, SqlParameter[]
parms)
{
//create the command object
SqlCommand cmd = new SqlCommand();
//set the command type
cmd.CommandType = CommandType.StoredProcedure;
//set the command text
cmd.CommandText = sProcName;
//loop through the parameters array to build the parm collection
if (parms != null)
{
for (int i=0;i < parms.Length; i++)
{
cmd.Parameters.Add (parms[i]);
}
}
//set the transaction of the command object
cmd.Transaction = Transaction;
return cmd;
}
private SqlCommand CreateSQLCommand(string sSQL)
{
//Create Command object
SqlCommand cmd = new SqlCommand();
//set the command type
cmd.CommandType = CommandType.Text;
// set the command text
cmd.CommandText = sSQL;
//Set the transaction of the command object
cmd.Transaction = Transaction;
return cmd;
}
// ****************************************
**************************************
//The following 2 methods are used to create SQLParameters.
// ****************************************
**************************************
protected SqlParameter CreateSqlParameter(string sParmName, object
sValue, SqlDbType sqlType)
{
return CreateSqlParameter(sParmName, sValue, sqlType,
ParameterDirection.Input);
}
protected SqlParameter CreateSqlParameter(string sParmName, object
sValue, SqlDbType sqlType, int iSize)
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = sParmName;
parm.SqlDbType = sqlType;
parm.Direction = ParameterDirection.Input;
parm.Value = sValue;
parm.Size = iSize;
return parm;
}
protected SqlParameter CreateSqlParameter(string sParmName, object
sValue, SqlDbType sqlType, ParameterDirection parmDirection)
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = sParmName;
parm.SqlDbType = sqlType;
parm.Direction = parmDirection;
parm.Value = sValue;
return parm;
}
protected SqlParameter CreateSqlParameter(string sParmName, object
sValue, SqlDbType sqlType, int iSize, ParameterDirection
parmDirection)
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = sParmName;
parm.SqlDbType = sqlType;
parm.Direction = parmDirection;
parm.Value = sValue;
parm.Size = iSize;
return parm;
}
}
}
|
|