How to convert SqlDBHelper class used for Sql server with MySql ?
My SqlDBHelper class with Sql server is as follows:
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Remoting;
using System.Reflection;
using System.Runtime.Remoting.Activation;
using System.Configuration;
using NestIT.CSharp.Utilities.Config;
using NestIT.CSharp.Utilities.SqlDBException;
using System;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
namespace DAL
{
public class SqlDBHelper
{
private string strConnection = NestIT.CSharp.Utilities.Config.Configuration.ConnectionString();
private SqlConnection objConnection;
private SqlTransaction objTransaction;
private bool _IsGlobalTransaction;
public SqlDBHelper()
{
}
public void Open()
{
if (objConnection == null)
{
objConnection = new SqlConnection(strConnection);
}
if (objConnection.State == ConnectionState.Closed)
{
objConnection.Open();
}
}
private void Close()
{
if (!(objConnection == null && objConnection.State == ConnectionState.Open))
{
objConnection.Close();
}
}
public DataSet ExecuteQuery(string strQuery)
{
DataSet ds;
try
{
Open();
SqlDataAdapter objAdapter;
objAdapter = new SqlDataAdapter(strQuery, objConnection);
ds = new DataSet();
objAdapter.Fill(ds, "myTable");
objAdapter.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e)
{
throw new SqlDatabaseException("SqlDatabase.ExecuteQuery() failed -" + e.Message, e);
}
finally
{
Close();
}
return ds;
}
public DataSet ExecuteProcedure(string strProcedure, Hashtable htParameters)
{
DataSet ds;
try
{
Open();
SqlDataAdapter objAdapter;
objAdapter = new SqlDataAdapter(strProcedure, objConnection);
IDictionaryEnumerator objEnumerator;
objEnumerator = htParameters.GetEnumerator();
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
objAdapter.SelectCommand.CommandTimeout = 360;
while (objEnumerator.MoveNext())
{
objAdapter.SelectCommand.Parameters.Add(new SqlParameter(objEnumerator.Key.ToString(), SqlDbType.VarChar, 8000));
objAdapter.SelectCommand.Parameters[objEnumerator.Key.ToString()].Value = objEnumerator.Value;
}
ds = new DataSet();
objAdapter.Fill(ds, "myTable");
objAdapter.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e)
{
throw new SqlDatabaseException("SqlDatabase.ExecuteProcedure() failed - " + e.Message, e);
}
finally
{
Close();
}
return ds;
}
public int ExecuteNonQuery(string strQuery)
{
int Records;
try
{
Open();
if (!(_IsGlobalTransaction))
{
objTransaction = objConnection.BeginTransaction();
}
SqlCommand objCommand = new SqlCommand(strQuery, objConnection);
objCommand.Transaction = objTransaction;
Records = objCommand.ExecuteNonQuery();
if (!(_IsGlobalTransaction))
{
objTransaction.Commit();
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
if (!(_IsGlobalTransaction))
{
objTransaction.Rollback();
}
throw new SqlDatabaseException("SqlDatabase.ExecuteNonQuery() failed", e1);
}
finally
{
if (!(_IsGlobalTransaction))
{
Close();
}
}
return Records;
}
public int ExecuteNonQueryProcedure(string strProcedure, Hashtable htParameters)
{
int Records;
try
{
Open();
if (!(_IsGlobalTransaction))
{
objTransaction = objConnection.BeginTransaction();
}
SqlCommand objCommand = new SqlCommand(strProcedure, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Transaction = objTransaction;
IDictionaryEnumerator objEnumerator;
objEnumerator = htParameters.GetEnumerator();
while (objEnumerator.MoveNext())
{
objCommand.Parameters.Add(new SqlParameter(objEnumerator.Key.ToString(), SqlDbType.NVarChar, 1024));
objCommand.Parameters[objEnumerator.Key.ToString()].Value = objEnumerator.Value;
}
Records = objCommand.ExecuteNonQuery();
if (!(_IsGlobalTransaction))
{
objTransaction.Commit();
}
objCommand.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
if (!(_IsGlobalTransaction))
{
objTransaction.Rollback();
}
throw new SqlDatabaseException("SqlDatabase.ExecuteNonQueryProcedure() failed", e1);
}
finally
{
if (!(_IsGlobalTransaction))
{
objTransaction = null;
}
if (!(_IsGlobalTransaction))
{
Close();
}
}
return Records;
}
public Hashtable GetValueProcedure(string strProcedure, ArrayList arrParameters, ArrayList arrOuputParameters)
{
Hashtable OutputTable = new Hashtable();
try
{
Open();
if (!(_IsGlobalTransaction))
{
objTransaction = objConnection.BeginTransaction();
}
SqlCommand objCommand = new SqlCommand(strProcedure, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Transaction = objTransaction;
objCommand.CommandTimeout = 0;
int intParamcounter;
for (intParamcounter = 0; intParamcounter <= arrParameters.Count - 1; intParamcounter++)
{
Params objParam = (Params)arrParameters[intParamcounter];
if (objParam.Direction != ParameterDirection.Output)
{
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Value = objParam.Value;
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
else
{
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
}
objCommand.ExecuteNonQuery();
int intOutputcounter;
for (intOutputcounter = 0; intOutputcounter <= arrOuputParameters.Count - 1; intOutputcounter++)
{
string OutParamName = arrOuputParameters[intOutputcounter].ToString();
OutputTable.Add(arrOuputParameters[intOutputcounter], objCommand.Parameters[OutParamName].Value);
}
if (!(_IsGlobalTransaction))
{
objTransaction.Commit();
}
objCommand.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
if (!(_IsGlobalTransaction))
{
objTransaction.Rollback();
}
OutputTable = null;
throw new SqlDatabaseException("SqlDatabase.GetValueProcedure() failed - " + e1.Message, e1);
}
finally
{
if (!(_IsGlobalTransaction))
{
Close();
}
}
return OutputTable;
}
public string GetValue(string strQuery)
{
string OutputValue = "";
try
{
Open();
SqlCommand objCommand = new SqlCommand(strQuery, objConnection);
SqlDataReader objReader;
objReader = objCommand.ExecuteReader();
if (objReader.Read())
{
if ((objReader[0]) != null)
{
OutputValue = objReader[0].ToString();
}
}
else
{
OutputValue = "";
}
objCommand.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
throw new SqlDatabaseException("SQLDatabase.GetValue() failed", e1);
}
finally
{
Close();
}
return OutputValue;
}
public string GetValueInTrans(string strQuery)
{
string OutputValue = "";
try
{
Open();
if (!(_IsGlobalTransaction))
{
objTransaction = objConnection.BeginTransaction();
}
SqlCommand objCommand = new SqlCommand(strQuery, objConnection);
objCommand.CommandType = CommandType.Text;
objCommand.Transaction = objTransaction;
SqlDataReader objReader;
objReader = objCommand.ExecuteReader();
if (objReader.Read())
{
if ((objReader[0]) != null)
{
OutputValue = objReader[0].ToString();
}
}
else
{
OutputValue = "";
}
if (!(_IsGlobalTransaction))
{
objTransaction.Commit();
}
objCommand.Dispose();
objReader.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
if (!(_IsGlobalTransaction))
{
objTransaction.Rollback();
}
throw new SqlDatabaseException("SQLDatabase.GetValue() failed", e1);
}
finally
{
if (!(_IsGlobalTransaction))
{
Close();
}
}
return OutputValue;
}
public Hashtable SqlBatchExecutor(string strProcedure, ArrayList arrParameters, ArrayList arrOutputParameters, string SqlString)
{
Hashtable OutputTable = new Hashtable();
try
{
Open();
if (!(_IsGlobalTransaction))
{
objTransaction = objConnection.BeginTransaction();
}
SqlCommand objCommand = new SqlCommand(strProcedure, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Transaction = objTransaction;
int intParamcounter;
for (intParamcounter = 0; intParamcounter <= arrParameters.Count - 1; intParamcounter++)
{
Params objParam = (Params)arrParameters[intParamcounter];
if (objParam.Direction != ParameterDirection.Output)
{
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type));
objCommand.Parameters[objParam.Name].Value = objParam.Value;
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
else
{
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
}
objCommand.ExecuteNonQuery();
int intOutputcounter;
for (intOutputcounter = 0; intOutputcounter <= arrOutputParameters.Count - 1; intOutputcounter++)
{
OutputTable.Add(arrOutputParameters[intOutputcounter], objCommand.Parameters[(int)arrOutputParameters[intOutputcounter]].Value);
}
if (arrOutputParameters.Count > 1)
{
SqlString = SqlString.Replace((char)arrOutputParameters[1], (char)objCommand.Parameters[(int)arrOutputParameters[1]].Value);
}
SqlCommand objQueryCommand = new SqlCommand(strProcedure, objConnection);
objQueryCommand.CommandType = CommandType.Text;
objQueryCommand.Transaction = objTransaction;
objQueryCommand.CommandText = SqlString;
objQueryCommand.ExecuteNonQuery();
if (!(_IsGlobalTransaction))
{
objTransaction.Commit();
}
objCommand.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
if (!(_IsGlobalTransaction))
{
objTransaction.Rollback();
}
OutputTable = null;
throw new SqlDatabaseException("SqlDatabase.GetValueProcedure() failed", e1);
}
finally
{
if (!(_IsGlobalTransaction))
{
Close();
}
}
return OutputTable;
}
public DataSet ExecuteQueryWithName(string strQuery, string tableName)
{
DataSet ds = new DataSet();
try
{
Open();
SqlDataAdapter objAdapter;
objAdapter = new SqlDataAdapter(strQuery, objConnection);
objAdapter.Fill(ds, tableName);
objAdapter.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e)
{
throw new SqlDatabaseException("SqlDatabase.ExecuteQuery() failed -" + e.Message, e);
}
finally
{
Close();
}
return ds;
}
public DataSet ExecuteProcedureWithName(string strProcedure, Hashtable htParameters, string tableName)
{
DataSet ds;
try
{
Open();
SqlDataAdapter objAdapter;
objAdapter = new SqlDataAdapter(strProcedure, objConnection);
IDictionaryEnumerator objEnumerator;
objEnumerator = htParameters.GetEnumerator();
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
objAdapter.SelectCommand.CommandTimeout = 360;
while (objEnumerator.MoveNext())
{
objAdapter.SelectCommand.Parameters.Add(new SqlParameter(objEnumerator.Key.ToString(), SqlDbType.VarChar, 8000));
objAdapter.SelectCommand.Parameters[objEnumerator.Key.ToString()].Value = objEnumerator.Value;
}
ds = new DataSet();
objAdapter.Fill(ds, tableName);
objAdapter.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e)
{
throw new SqlDatabaseException("SqlDatabase.ExecuteProcedure() failed - " + e.Message, e);
}
finally
{
Close();
}
return ds;
}
public void BeginTransaction()
{
try
{
Open();
_IsGlobalTransaction = true;
objTransaction = objConnection.BeginTransaction();
}
catch (Exception ex)
{
throw ex;
}
}
public void CommitTransaction()
{
try
{
if (!(objTransaction == null))
{
objTransaction.Commit();
}
Close();
}
catch (Exception ex)
{
throw ex;
}
}
public void RollbackTransaction()
{
try
{
if (!(objTransaction == null))
{
objTransaction.Rollback();
}
Close();
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet FetchDataProcedure(string strProcedure, ArrayList arrParameters, ArrayList arrOuputParameters, ref Hashtable htOutput)
{
DataSet ds;
SqlCommand objCommand = null;
try
{
Open();
SqlDataAdapter objAdapter;
objAdapter = new SqlDataAdapter();
objCommand = new SqlCommand(strProcedure, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Transaction = objConnection.BeginTransaction(IsolationLevel.ReadCommitted);
objCommand.CommandTimeout = 0;
int intParamcounter;
for (intParamcounter = 0; intParamcounter <= arrParameters.Count - 1; intParamcounter++)
{
Params objParam = (Params)arrParameters[intParamcounter];
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Value = objParam.Value;
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
for (intParamcounter = 0; intParamcounter <= arrOuputParameters.Count - 1; intParamcounter++)
{
Params objParam = (Params)arrOuputParameters[intParamcounter];
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Value = objParam.Value;
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
objAdapter.SelectCommand = objCommand;
ds = new DataSet();
objAdapter.Fill(ds);
int intOutputcounter;
for (intOutputcounter = 0; intOutputcounter <= arrOuputParameters.Count - 1; intOutputcounter++)
{
Params objParam = (Params)arrOuputParameters[intOutputcounter];
htOutput.Add(objParam.Name, objCommand.Parameters[objParam.Name].Value.ToString());
}
objCommand.Transaction.Commit();
objAdapter.Dispose();
}
catch (SqlException ex)
{
objCommand.Transaction.Rollback();
throw ex;
}
catch (Exception e)
{
throw new SqlDatabaseException("SqlDatabase.ExecuteProcedure() failed - " + e.Message, e);
}
finally
{
Close();
}
return ds;
}
public object ExecuteScalarProcedure(string strProcedure, ArrayList arrParameters, ArrayList arrOuputParameters)
{
object retval;
try
{
Open();
if (!(_IsGlobalTransaction))
{
objTransaction = objConnection.BeginTransaction();
}
SqlCommand objCommand = new SqlCommand(strProcedure, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Transaction = objTransaction;
int intParamcounter;
for (intParamcounter = 0; intParamcounter <= arrParameters.Count - 1; intParamcounter++)
{
Params objParam = (Params)arrParameters[intParamcounter];
if (objParam.Direction != ParameterDirection.Output)
{
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Value = objParam.Value;
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
else
{
objCommand.Parameters.Add(new SqlParameter(objParam.Name, objParam.Type, int.Parse(objParam.size)));
objCommand.Parameters[objParam.Name].Direction = objParam.Direction;
}
}
retval = objCommand.ExecuteScalar();
if (!(_IsGlobalTransaction))
{
objTransaction.Commit();
}
objCommand.Dispose();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception e1)
{
if (!(_IsGlobalTransaction))
{
objTransaction.Rollback();
}
throw new SqlDatabaseException("SqlDatabase.ExecuteScalarProcedure() failed - " + e1.Message, e1);
}
finally
{
if (!(_IsGlobalTransaction))
{
Close();
}
}
return retval;
}
}
}