/// <summary>
/// Execute DataSet with query directly passed as parameter
/// </summary>
/// <param name="sSelectMySql">Select Query</param>
/// <param name="colParameterList">Array of NandanaDBRequest.Parameter containing Name and value pair</param>
/// <returns>DataSet Object</returns>
public override NandanaDataSet ExecuteDataSet(string sSelectMySql, ArrayList colParameterList)
{
// implementation code here...
MySqlConnection conMySql = new MySqlConnection();
MySqlCommand cmdMySql = new MySqlCommand();
MySqlDataAdapter daMySql;
NandanaMySqlDataSet oDataSetMySql = new NandanaMySqlDataSet();
NandanaDBRequest oRequest = new NandanaDBRequest(sSelectMySql, CommandType.Text, null, colParameterList);
try
{
PrepareCommand(cmdMySql, conMySql, null, oRequest);
daMySql = new MySqlDataAdapter(cmdMySql);
daMySql.Fill(oDataSetMySql.ReturnedDataSet);
}
catch (MySqlException ex)
{
throw (ex);
}
finally
{
if (cmdMySql != null)
{
cmdMySql.Parameters.Clear();
}
if (conMySql.State == ConnectionState.Open)
{
conMySql.Close();
}
}
return(oDataSetMySql);
}
/// <summary>
/// Execute DataSet with query directly passed as parameter
/// </summary>
/// <param name="sSelectSQL">Select Query</param>
/// <param name="colParameterList">Array of NandanaDBRequest.Parameter containing Name and value pair</param>
/// <returns>DataSet Object</returns>
public override NandanaDataSet ExecuteDataSet(string sSelectSQL, ArrayList colParameterList)
{
// implementation code here...
SqlConnection conSQL = new SqlConnection();
SqlCommand cmdSQL = new SqlCommand();
SqlDataAdapter daSQL;
NandanaSqlDataSet oDataSetSQL = new NandanaSqlDataSet();
NandanaDBRequest oRequest = new NandanaDBRequest(sSelectSQL, CommandType.Text, null, colParameterList);
try
{
PrepareCommand(cmdSQL, conSQL, null, oRequest);
daSQL = new SqlDataAdapter(cmdSQL);
daSQL.Fill(oDataSetSQL.ReturnedDataSet);
}
catch (SqlException ex)
{
throw (ex);
}
finally
{
if (cmdSQL != null)
{
cmdSQL.Parameters.Clear();
}
if (conSQL.State == ConnectionState.Open)
{
conSQL.Close();
}
}
return(oDataSetSQL);
}
/// <summary>
/// Implementation of ExecuteNonQuery for SQL specific
/// </summary>
/// <param name="Request">NandanaDBRequest object</param>
/// <returns>int value</returns>
public override int ExecuteNonQuery(NandanaDBRequest Request)
{
SqlConnection conSQL = new SqlConnection();
SqlCommand cmdSQL = new SqlCommand();
SqlParameter prmSQL = new SqlParameter();
SqlTransaction tranSQL = null;
int iVal = 0;
try
{
if (Request.Transaction != null)
{
tranSQL = Request.Transaction;
}
PrepareCommand(cmdSQL, conSQL, tranSQL, Request);
if (cmdSQL.CommandType == CommandType.StoredProcedure)
{
prmSQL.ParameterName = "@Return";
prmSQL.DbType = DbType.Int32;
prmSQL.Direction = ParameterDirection.ReturnValue;
cmdSQL.Parameters.Add(prmSQL);
}
iVal = cmdSQL.ExecuteNonQuery();
if (cmdSQL.CommandType == CommandType.StoredProcedure)
{
iVal = Convert.ToInt32(cmdSQL.Parameters["@Return"].Value.ToString());
}
}
catch (SqlException)
{
if (conSQL.State == ConnectionState.Open)
{
conSQL.Close();
}
throw;
}
finally
{
if (cmdSQL != null)
{
cmdSQL.Parameters.Clear();
}
if ((Request.Transaction == null) && (conSQL.State == ConnectionState.Open))
{
conSQL.Close();
}
}
return(iVal);
}
/// <summary>
/// Implementation of ExecuteNonQuery for MySql specific
/// </summary>
/// <param name="Request">NandanaDBRequest object</param>
/// <returns>int value</returns>
public override int ExecuteNonQuery(NandanaDBRequest Request)
{
MySqlConnection conMySql = new MySqlConnection();
MySqlCommand cmdMySql = new MySqlCommand();
MySqlParameter prmMySql = new MySqlParameter();
MySqlTransaction tranMySql = null;
int iVal = 0;
try
{
if (Request.Transaction != null)
{
tranMySql = Request.Transaction;
}
PrepareCommand(cmdMySql, conMySql, tranMySql, Request);
//if (cmdMySql.CommandType == CommandType.StoredProcedure)
//{
// prmMySql.ParameterName = "@Return";
// prmMySql.DbType = DbType.Int32;
// prmMySql.Direction = ParameterDirection.ReturnValue;
// cmdMySql.Parameters.Add(prmMySql);
//}
iVal = cmdMySql.ExecuteNonQuery();
//if (cmdMySql.CommandType == CommandType.StoredProcedure)
//{
// iVal = Convert.ToInt32(cmdMySql.Parameters["@Return"].Value.ToString());
//}
}
catch (MySqlException)
{
if (conMySql.State == ConnectionState.Open)
{
conMySql.Close();
}
throw;
}
finally
{
if (cmdMySql != null)
{
cmdMySql.Parameters.Clear();
}
if ((Request.Transaction == null) && (conMySql.State == ConnectionState.Open))
{
conMySql.Close();
}
}
return(iVal);
}
/// <summary>
/// Execute DataSet
/// </summary>
/// <param name="Request">NandanaDBRequest with connection details</param>
/// <returns>DataSet Object</returns>
public override NandanaDataSet ExecuteDataSet(NandanaDBRequest Request)
{
// implementation code here...
SqlConnection conSQL = new SqlConnection();
SqlCommand cmdSQL = new SqlCommand();
SqlDataAdapter daSQL;
NandanaSqlDataSet oDataSetSQL = new NandanaSqlDataSet();
SqlTransaction tranSQL = null;
try
{
if (Request.Transaction != null)
{
tranSQL = Request.Transaction;
}
PrepareCommand(cmdSQL, conSQL, tranSQL, Request);
daSQL = new SqlDataAdapter(cmdSQL);
daSQL.Fill(oDataSetSQL.ReturnedDataSet);
}
catch (SqlException ex)
{
if (conSQL.State == ConnectionState.Open)
{
conSQL.Close();
}
throw (ex);
}
finally
{
if (cmdSQL != null)
{
cmdSQL.Parameters.Clear();
}
if ((Request.Transaction == null) && (conSQL.State == ConnectionState.Open))
{
conSQL.Close();
}
}
return(oDataSetSQL);
}
/// <summary>
/// Execute DataSet
/// </summary>
/// <param name="Request">NandanaDBRequest with connection details</param>
/// <returns>DataSet Object</returns>
public override NandanaDataSet ExecuteDataSet(NandanaDBRequest Request)
{
// implementation code here...
MySqlConnection conMySql = new MySqlConnection();
MySqlCommand cmdMySql = new MySqlCommand();
MySqlDataAdapter daMySql;
NandanaMySqlDataSet oDataSetMySql = new NandanaMySqlDataSet();
MySqlTransaction tranMySql = null;
try
{
if (Request.Transaction != null)
{
tranMySql = Request.Transaction;
}
PrepareCommand(cmdMySql, conMySql, tranMySql, Request);
daMySql = new MySqlDataAdapter(cmdMySql);
daMySql.Fill(oDataSetMySql.ReturnedDataSet);
}
catch (MySqlException ex)
{
if (conMySql.State == ConnectionState.Open)
{
conMySql.Close();
}
throw (ex);
}
finally
{
if (cmdMySql != null)
{
cmdMySql.Parameters.Clear();
}
if ((Request.Transaction == null) && (conMySql.State == ConnectionState.Open))
{
conMySql.Close();
}
}
return(oDataSetMySql);
}
/// <summary>
/// Execute Scalar
/// </summary>
/// <param name="Request">NandanaDBRequest with connection details</param>
/// <returns>return 1X1 Object returned by ExecuteScalar</returns>
public override Object ExecuteScalar(NandanaDBRequest Request)
{
// implementation code here...
SqlConnection conSQL = new SqlConnection();
SqlCommand cmdSQL = new SqlCommand();
SqlTransaction tranSQL = null;
object oReturn;
try
{
if (Request.Transaction != null)
{
tranSQL = Request.Transaction;
}
PrepareCommand(cmdSQL, conSQL, tranSQL, Request);
oReturn = cmdSQL.ExecuteScalar();
}
catch (SqlException)
{
if (conSQL.State == ConnectionState.Open)
{
conSQL.Close();
}
throw;
}
finally
{
if (cmdSQL != null)
{
cmdSQL.Parameters.Clear();
}
if ((Request.Transaction == null) && (conSQL.State == ConnectionState.Open))
{
conSQL.Close();
}
}
return(oReturn);
}
/// <summary>
/// Execute DataReader
/// </summary>
/// <param name="Request">NandanaDBRequest with connection details</param>
/// <returns>DataReader Object</returns>
public override NandanaDataReader ExecuteDataReader(NandanaDBRequest Request)
{
// implementation code here....
SqlConnection conSQL = new SqlConnection();
SqlCommand cmdSQL = new SqlCommand();
SqlDataReader drSQL;
SqlTransaction tranSQL = null;
NandanaSqlDataReader oDataReaderSQL = new NandanaSqlDataReader();
try
{
conSQL.ConnectionString = GetConnectionString();
if (Request.Transaction != null)
{
tranSQL = Request.Transaction;
}
PrepareCommand(cmdSQL, conSQL, tranSQL, Request);
drSQL = cmdSQL.ExecuteReader();
oDataReaderSQL.ReturnedDataReader = drSQL;
}
catch (SqlException exSQL)
{
if (conSQL.State == ConnectionState.Open)
{
conSQL.Close();
}
throw (exSQL);
}
finally
{
cmdSQL.Parameters.Clear();
}
return(oDataReaderSQL);
}
/// <summary>
/// Execute DataReader
/// </summary>
/// <param name="Request">NandanaDBRequest with connection details</param>
/// <returns>DataReader Object</returns>
public override NandanaDataReader ExecuteDataReader(NandanaDBRequest Request)
{
// implementation code here....
MySqlConnection conMySql = new MySqlConnection();
MySqlCommand cmdMySql = new MySqlCommand();
MySqlDataReader drMySql;
MySqlTransaction tranMySql = null;
NandanaMySqlDataReader oDataReaderMySql = new NandanaMySqlDataReader();
try
{
conMySql.ConnectionString = GetConnectionString();
if (Request.Transaction != null)
{
tranMySql = Request.Transaction;
}
PrepareCommand(cmdMySql, conMySql, tranMySql, Request);
drMySql = cmdMySql.ExecuteReader();
oDataReaderMySql.ReturnedDataReader = drMySql;
}
catch (MySqlException exMySql)
{
if (conMySql.State == ConnectionState.Open)
{
conMySql.Close();
}
throw (exMySql);
}
finally
{
cmdMySql.Parameters.Clear();
}
return(oDataReaderMySql);
}
/// <summary>
/// Prepare Command to be executed
/// </summary>
/// <param name="cmdSQL">SqlCommand instance</param>
/// <param name="conSQL">SqlConnection instance. Transaction is handle internally</param>
/// <param name="tranSQL">Transaction instance. </param>
/// <param name="Request">NandanaDBRequest containing Requests</param>
private void PrepareCommand(SqlCommand cmdSQL, SqlConnection conSQL, SqlTransaction tranSQL, NandanaDBRequest Request)
{
if (tranSQL != null)
{
conSQL = tranSQL.Connection;
cmdSQL.Transaction = tranSQL;
}
else
{
conSQL.ConnectionString = GetConnectionString();
conSQL.Open();
}
cmdSQL.Connection = conSQL;
cmdSQL.CommandText = Request.Command;
cmdSQL.CommandType = Request.CommandType;
// add parameters if they exists.
if ((Request.Parameters != null) && (Request.Parameters.Count > 0))
{
foreach (NandanaDBRequest.Parameter oParam in Request.Parameters)
{
//if((oParam.ParamValue.GetType() == typeof(System.String))&& oParam.ParamValue.ToString().Length==0)
// oParam.ParamValue = DBNull.Value;
if (oParam.ParamValue == System.DBNull.Value)
{
oParam.ParamValue = DBNull.Value;
}
cmdSQL.Parameters.Add(oParam.ParamName, oParam.ParamValue);
}
}
}
/// <summary> /// Abstract function definition for ExecuteScalar /// </summary> /// <param name="Request">NandanaDBRequest Object containing all the DB details</param> /// <returns>1X1 object </returns> public abstract object ExecuteScalar(NandanaDBRequest Request);
/// <summary> /// Abstract function definition for ExecuteNonQuery /// </summary> /// <param name="Request">NandanaDBRequest Object containing all the DB details</param> /// <returns>Integer value whether query was successful or not</returns> public abstract int ExecuteNonQuery(NandanaDBRequest Request);
/// <summary> /// Abstract function definition for ExecuteDataSet /// </summary> /// <param name="Request">NandanaDBRequest Object containing all the DB details</param> /// <returns>NandanaDataSet object</returns> public abstract NandanaDataSet ExecuteDataSet(NandanaDBRequest Request);
/// <summary> /// Abstract function definition for ExecuteDataReader /// </summary> /// <param name="Request">NandanaDBRequest Object containing all the DB details</param> /// <returns>NandanaDataReader object</returns> public abstract NandanaDataReader ExecuteDataReader(NandanaDBRequest Request);
