public object ReturnObject(Hashtable paramaters, string commandText, ClsUtility.ObjectEnum Obj)
{
//int i;
//string cmdpara, cmddbtype;
//object cmdvalue;
SqlCommand theCmd = new SqlCommand();
SqlTransaction theTran = (SqlTransaction)this.Transaction;
SqlConnection cnn;
if (null == this.Connection)
{
cnn = (SqlConnection)DataMgr.GetConnection();
}
else
{
cnn = (SqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new SqlCommand(commandText, cnn);
}
else
{
theCmd = new SqlCommand(commandText, cnn, theTran);
}
//for (i = 1; i < Params.Count; )
//{
// cmdpara = Params[i].ToString();
// cmddbtype = Params[i + 1].ToString();
// //if (cmddbtype.Contains("binary"))
// cmdvalue = Params[i + 2];
// //else
// // cmdvalue = Params[i + 2].ToString();
// // theCmd.Parameters.Add(cmdpara, cmddbtype).Value = cmdvalue;
// theCmd.Parameters.AddWithValue(cmdpara,cmdvalue);//.Value = cmdvalue;
// i = i + 3;
//}
for (int i = 1; i <= paramaters.Count;)
{
theCmd.Parameters.Add((SqlParameter)paramaters[i]);
i++;
}
theCmd.CommandType = CommandType.StoredProcedure;
theCmd.CommandTimeout = DataMgr.CommandTimeOut();
string theSubstring = commandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
case "UPDATE":
theCmd.CommandType = CommandType.Text;
break;
case "INSERT":
theCmd.CommandType = CommandType.Text;
break;
case "DELETE":
theCmd.CommandType = CommandType.Text;
break;
case "TRUNCA":
theCmd.CommandType = CommandType.Text;
break;
case ";WITH ":
theCmd.CommandType = CommandType.Text;
break;
}
theCmd.Connection = cnn;
try
{
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
theAdpt.Fill(theDS);
theAdpt.Dispose();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theAdpt.Fill(theDT);
theAdpt.Dispose();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theAdpt.Fill(theDT);
theAdpt.Dispose();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
return(NoRowsAffected);
}
return(0);
}
catch (Exception err)
{
throw err;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
DataMgr.ReleaseConnection(cnn);
}
}
}
}
public object ReturnObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj, string pmmsType)
{
switch (pmmsType.Trim().ToLower())
{
case "mssql":
{
return(MsSQLObject(ConString, Params, CommandText, Obj));
}
//case "mysql":
// {
// return MySQLObject(ConString, Params, CommandText, Obj);
// }
// case "pgsql":
//{
// return PgSQLObject(ConString, Params, CommandText, Obj);
//}
//case "access":
// {
//return AccessObject(ConString, Params, CommandText, Obj);
//}
default:
{
return(MsSQLObject(ConString, Params, CommandText, Obj));
}
}
}
public object ReturnObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
SqlCommand theCmd = new SqlCommand();
SqlTransaction theTran = (SqlTransaction)this.Transaction;
SqlConnection cnn;
if (null == this.Connection)
{
cnn = (SqlConnection)GetConnection(ConString, "mssql");
}
else
{
cnn = (SqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new SqlCommand(CommandText, cnn);
}
else
{
theCmd = new SqlCommand(CommandText, cnn, theTran);
}
if (Params != null)
{
for (i = 1; i <= Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
}
theCmd.CommandTimeout = 0;
theCmd.CommandType = CommandType.StoredProcedure;
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
case "UPDATE":
theCmd.CommandType = CommandType.Text;
break;
case "RESTOR":
theCmd.CommandType = CommandType.Text;
break;
case "INSERT":
theCmd.CommandType = CommandType.Text;
break;
case "DELETE":
theCmd.CommandType = CommandType.Text;
break;
case "CREATE":
theCmd.CommandType = CommandType.Text;
break;
case "DROP S":
theCmd.CommandType = CommandType.Text;
break;
case "DROP V":
theCmd.CommandType = CommandType.Text;
break;
case "DBCC C":
theCmd.CommandType = CommandType.Text;
break;
case "DBCC S":
theCmd.CommandType = CommandType.Text;
break;
case "BACKUP":
theCmd.CommandType = CommandType.Text;
break;
case "EXEC S":
theCmd.CommandType = CommandType.Text;
break;
case "SET DA":
theCmd.CommandType = CommandType.Text;
break;
}
if (CommandText.Substring(0, 4).ToUpper() == "WITH") //CTE
{
theCmd.CommandType = CommandType.Text;
}
if (CommandText.IndexOf("SET OFFLINE") > 0 || CommandText.IndexOf("SET ONLINE") > 0)
{
theCmd.CommandType = CommandType.Text;
}
if (CommandText.Length >= 15)
{
if (CommandText.Substring(0, 15).ToUpper() == "DROP TABLE [TMP" || CommandText.Substring(0, 15).ToUpper() == "DROP TABLE [MGR" || CommandText.Substring(0, 15).ToUpper() == "DROP TABLE [TPS")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 10)
{
if (CommandText.Substring(0, 10).ToUpper() == "DROP SYNON")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 30)
{
if (CommandText.Substring(0, 30).ToUpper() == "DROP TABLE MST_PATIENT_DECODED")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 25)
{
if (CommandText.Substring(0, 25).ToUpper() == "DROP TABLE PERSON_DECODED")
{
theCmd.CommandType = CommandType.Text;
}
}
theCmd.Connection = cnn;
try
{
SqlCommand cm;
if (ClsUtility.SDate != "")
{
cm = new SqlCommand("SET Dateformat " + ClsUtility.SDate, cnn);
cm.ExecuteNonQuery();
cm.Dispose();
}
cm = null;
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
//theDS.Tables[0].BeginLoadData();
theAdpt.Fill(theDS);
//theDS.Tables[0].EndLoadData();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
return(NoRowsAffected);
}
if (null == this.Connection)
{
cnn.Close();
}
return(0);
}
catch (Exception err)
{
throw err;
//return null;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
cnn.Close();
}
}
}
}
private object MySQLObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
MySqlCommand theCmd = new MySqlCommand();
MySqlTransaction theTran = (MySqlTransaction)this.Transaction;
MySqlConnection cnn;
if (null == this.Connection)
{
cnn = (MySqlConnection)GetConnection(ConString, "mysql");
}
else
{
cnn = (MySqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new MySqlCommand(CommandText, cnn);
}
else
{
theCmd = new MySqlCommand(CommandText, cnn, theTran);
}
for (i = 1; i < Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
theCmd.CommandTimeout = 0;
theCmd.CommandType = CommandType.StoredProcedure;
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
//case "UPDATE":
// theCmd.CommandType = CommandType.Text;
// break;
//case "INSERT":
// theCmd.CommandType = CommandType.Text;
// break;
//case "RESTOR":
// theCmd.CommandType = CommandType.Text;
// break;
//case "DELETE":
// theCmd.CommandType = CommandType.Text;
// break;
case "CREATE":
theCmd.CommandType = CommandType.Text;
break;
//case "DBCC C":
// theCmd.CommandType = CommandType.Text;
// break;
//case "DBCC S":
// theCmd.CommandType = CommandType.Text;
// break;
//case "BACKUP":
// theCmd.CommandType = CommandType.Text;
// break;
//case "DROP S":
// theCmd.CommandType = CommandType.Text;
// break;
//case "DROP V":
// theCmd.CommandType = CommandType.Text;
// break;
//case "EXEC S":
// theCmd.CommandType = CommandType.Text;
// break;
//case "SET DA":
// theCmd.CommandType = CommandType.Text;
// break;
}
if (CommandText.IndexOf("SET OFFLINE") > 0 || CommandText.IndexOf("SET ONLINE") > 0)
{
theCmd.CommandType = CommandType.Text;
}
if (CommandText.Length >= 15)
{
if (CommandText.Substring(0, 15).ToUpper() == "DROP TABLE `TMP" || CommandText.Substring(0, 15).ToUpper() == "DROP TABLE `MGR" || CommandText.Substring(0, 15).ToUpper() == "DROP TABLE `TPS" || CommandText.Substring(0, 15).ToUpper() == "DROP PROCEDURE " || CommandText.Substring(0, 13).ToUpper() == "DROP FUNCTION")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 10)
{
if (CommandText.Substring(0, 10).ToUpper() == "DROP SYNON")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 22)
{
if (CommandText.Substring(0, 22).ToUpper() == "DROP TABLE MST_PATIENT")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 10)
{
if (CommandText.Substring(0, 14).ToUpper() == "SHOW DATABASES")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 10)
{
if (CommandText.Substring(0, 6).ToUpper() == "SOURCE")
{
theCmd.CommandType = CommandType.Text;
}
}
//int j = 0;
// SqlCommand comm = new SqlCommand("[dbo].[AdminIQToolsDB]", cnn);
// comm.CommandType = CommandType.StoredProcedure;
// j=comm.ExecuteNonQuery();
// comm.Dispose();
// comm = null;
theCmd.Connection = cnn;
try
{
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
//theDS.Tables[0].BeginLoadData();
theAdpt.Fill(theDS);
//theDS.Tables[0].EndLoadData();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
return(NoRowsAffected);
}
if (null == this.Connection)
{
cnn.Close();
}
return(0);
}
catch (Exception err)
{
throw err;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
cnn.Close();
}
}
}
}
public object ReturnObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj, string pmmsType)
{
switch (pmmsType.Trim().ToLower())
{
case "mssql":
{
return(MsSQLObject(ConString, Params, CommandText, Obj));
}
case "mysql":
{
return(MySQLObject(ConString, Params, CommandText, Obj));
}
default:
{
return(MsSQLObject(ConString, Params, CommandText, Obj));
}
}
}
public object ReturnObject(Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj, DataTable theDataTable, string tablename)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
ParameterDirection cmdDirection;
SqlCommand theCmd = new SqlCommand();
SqlTransaction theTran = (SqlTransaction)this.Transaction;
SqlConnection cnn = null;
StringBuilder strParam = new StringBuilder();
try
{
if (null == this.Connection)
{
cnn = (SqlConnection)DataMgr.GetConnection();
}
else
{
cnn = (SqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new SqlCommand(CommandText, cnn);
}
else
{
theCmd = new SqlCommand(CommandText, cnn, theTran);
}
for (i = 1; i < Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
if (Params[i + 2] != null)
{
if (Params[i + 2].GetType() != ParameterDirection.Output.GetType())
{
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
}
else
{
cmdDirection = (ParameterDirection)Params[i + 2];
theCmd.Parameters.AddWithValue(cmdpara, (SqlDbType)Params[i + 1]).Direction = cmdDirection;
}
}
else
{
cmdvalue = string.Empty;
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
}
i = i + 3;
}
if (theDataTable != null)
{
if (theDataTable.Rows.Count > 0)
{
theCmd.Parameters.AddWithValue(tablename, theDataTable);
}
}
theCmd.CommandType = CommandType.StoredProcedure;
theCmd.CommandTimeout = DataMgr.CommandTimeOut();
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
case "UPDATE":
theCmd.CommandType = CommandType.Text;
break;
case "INSERT":
theCmd.CommandType = CommandType.Text;
break;
case "DELETE":
theCmd.CommandType = CommandType.Text;
break;
}
theCmd.Connection = cnn;
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
theAdpt.Fill(theDS);
theAdpt.Dispose();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theAdpt.Fill(theDT);
theAdpt.Dispose();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theAdpt.Fill(theDT);
theAdpt.Dispose();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = 0;
NoRowsAffected = theCmd.ExecuteNonQuery();
if (theCmd.Parameters.Contains("@idNEW"))
{
if ((int)theCmd.Parameters["@idNEW"].Value > 0)
{
NoRowsAffected = (int)theCmd.Parameters["@idNEW"].Value;
}
}
return(NoRowsAffected);
}
return(0);
}
catch (Exception err)
{
for (i = 1; i < Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
if (Params[i + 2] != null)
{
if (Params[i + 2].GetType() != ParameterDirection.Output.GetType())
{
cmdvalue = Params[i + 2].ToString();
strParam.Append("Name: " + cmdpara + ", Type: " + cmddbtype + ", Value: " + cmdvalue + ", Direction: Input");
strParam.Append(Environment.NewLine);
}
else
{
cmdDirection = (ParameterDirection)Params[i + 2];
strParam.Append("Name: " + cmdpara + ", Type: " + cmddbtype + ", Direction: Output");
strParam.Append(Environment.NewLine);
}
}
i = i + 3;
}
CLogger.WriteLog("Namespace: DataAccess.Entity, Class: ClsObject, Method: ReturnObject - Call started.", CommandText, strParam.ToString(), err.ToString());
throw err;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
DataMgr.ReleaseConnection(cnn);
}
}
}
}
private object AccessObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
return(null);
}
public bool CreateErrorLogs(Exception expOccured, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
string fileName = @"c:\IQCare_Error_Logs_KNH.txt";
try
{
if (System.IO.File.Exists(fileName))
{
//append
System.IO.File.Open(fileName, System.IO.FileMode.Append);
System.IO.File.WriteAllText(fileName, "Error occured - Object = " + Obj.ToString() + Environment.NewLine +
" --- CommandText = " + Environment.NewLine +
" --- Params = " + Params.Values.ToString() +
"----- Exception Occured = " + expOccured.InnerException.ToString());
}
{
//create
System.IO.File.Create(fileName);
//append
System.IO.File.Open(fileName, System.IO.FileMode.Append);
System.IO.File.WriteAllText(fileName, "Error occured - Object = " + Obj.ToString() + Environment.NewLine +
" --- CommandText = " + Environment.NewLine +
" --- Params = " + Params.Values.ToString() +
"----- Exception Occured = " + expOccured.InnerException.ToString());
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public object ReturnMySQLObject(Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
MySqlCommand theCmd = new MySqlCommand();
MySqlTransaction theTran = (MySqlTransaction)this.Transaction;
MySqlConnection cnn = null;
StringBuilder strParam = new StringBuilder();
try
{
if (null == this.Connection)
{
cnn = (MySqlConnection)DataMgr.GetMySQLConnection();
}
else
{
cnn = (MySqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new MySqlCommand(CommandText, cnn);
}
else
{
theCmd = new MySqlCommand(CommandText, cnn, theTran);
}
for (i = 1; i < Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.Add(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
theCmd.CommandType = CommandType.StoredProcedure;
theCmd.CommandTimeout = DataMgr.CommandTimeOut();
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
case "UPDATE":
theCmd.CommandType = CommandType.Text;
break;
case "INSERT":
theCmd.CommandType = CommandType.Text;
break;
case "DELETE":
theCmd.CommandType = CommandType.Text;
break;
}
theCmd.Connection = cnn;
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
theAdpt.Fill(theDS);
theAdpt.Dispose();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theAdpt.Fill(theDT);
theAdpt.Dispose();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theAdpt.Fill(theDT);
theAdpt.Dispose();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
return(NoRowsAffected);
}
return(0);
}
catch (Exception err)
{
for (i = 1; i < Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
strParam.Append("Name: " + cmdpara + ", Type: " + cmddbtype + ", Value: " + cmdvalue + " ");
strParam.Append(Environment.NewLine);
i = i + 3;
}
CLogger.WriteLog("Namespace: DataAccess.Entity, Class: ClsObject, Method: ReturnMySQLObject - Call started.", CommandText, strParam.ToString(), err.ToString());
throw err;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
DataMgr.ReleaseMySQLConnection(cnn);
}
}
}
}
private object PgSQLObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
CommandText = CommandText.ToLower();
NpgsqlConnection conn = new NpgsqlConnection(ConString);
conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand theCmd = new NpgsqlCommand(CommandText, conn);
theCmd.CommandType = CommandType.StoredProcedure;
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
}
if (CommandText.Substring(0, 14).ToUpper() == "UPDATE IQTOOLS")
{
theCmd.CommandType = CommandType.Text;
}
if (CommandText.Substring(0, 4).ToUpper() == "WITH") //CTE
{
theCmd.CommandType = CommandType.Text;
}
for (i = 1; i <= Params.Count;)
{
cmdpara = Params[i].ToString();
if (cmdpara.Contains("@"))
{
cmdpara = cmdpara.Substring(1, cmdpara.Length - 1);
}
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
try
{
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
DataSet ds = new DataSet();
int j = 0;
int k = 0;
NpgsqlDataReader dr = theCmd.ExecuteReader();
for (j = 0; j <= 10; j++)
{
if (!dr.IsClosed)
{
dr.Close();
}
dr = theCmd.ExecuteReader();
for (k = 0; k < j; k++)
{
dr.NextResult();
}
//if (dr.HasRows)
//{
ds.Tables.Add();
//if (dr.HasRows)
//{
ds.Tables[j].Load(dr);
//}
//}
//else
//{
// break;
//}
}
//tran.Commit();
return(ds);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
DataTable theDT = new DataTable();
NpgsqlDataReader dr = theCmd.ExecuteReader();
theDT.Load(dr);
tran.Commit();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
DataTable theDT = new DataTable();
NpgsqlDataReader dr = theCmd.ExecuteReader();
theDT.Load(dr);
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
tran.Commit();
return(NoRowsAffected);
}
return(0);
}
catch (Exception err)
{
throw err;
}
finally
{
conn.Close();
}
}
private object MySQLObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
MySqlCommand theCmd = new MySqlCommand();
MySqlTransaction theTran = (MySqlTransaction)this.Transaction;
MySqlConnection cnn;
if (null == this.Connection)
{
cnn = (MySqlConnection)GetConnection(ConString, "mysql");
}
else
{
cnn = (MySqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new MySqlCommand(CommandText, cnn);
}
else
{
theCmd = new MySqlCommand(CommandText, cnn, theTran);
}
for (i = 1; i < Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
theCmd.CommandTimeout = 0;
theCmd.CommandType = CommandType.StoredProcedure;
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
case "CREATE":
theCmd.CommandType = CommandType.Text;
break;
}
if (CommandText.Length >= 10)
{
if (CommandText.Substring(0, 15).ToUpper() == "DROP TABLE `TMP" ||
CommandText.Substring(0, 15).ToUpper() == "DROP TABLE `MGR" ||
CommandText.Substring(0, 15).ToUpper() == "DROP TABLE `TPS" ||
CommandText.Substring(0, 15).ToUpper() == "DROP PROCEDURE " ||
CommandText.Substring(0, 13).ToUpper() == "DROP FUNCTION" ||
CommandText.Substring(0, 14).ToUpper() == "DELETE FROM AA" ||
CommandText.Substring(0, 14).ToUpper() == "INSERT INTO AA" ||
CommandText.Substring(0, 10).ToUpper() == "UPDATE AA_" ||
CommandText.Substring(0, 14).ToUpper() == "SHOW DATABASES" ||
CommandText.Substring(0, 19).ToUpper() == "DROP SCHEMA IQTOOLS" ||
CommandText.Substring(0, 21).ToUpper() == "CREATE SCHEMA IQTOOLS" ||
CommandText.Substring(0, 24).ToUpper() == "REPLACE INTO aa_Database"
)
{
theCmd.CommandType = CommandType.Text;
}
}
theCmd.Connection = cnn;
try
{
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
theDS.Tables[0].BeginLoadData();
theAdpt.Fill(theDS);
theDS.Tables[0].EndLoadData();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
MySqlDataAdapter theAdpt = new MySqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
return(NoRowsAffected);
}
if (null == this.Connection)
{
cnn.Close();
}
return(0);
}
catch (Exception err)
{
throw err;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
cnn.Close();
}
}
}
}
private object MsSQLObject(string ConString, Hashtable Params, string CommandText, ClsUtility.ObjectEnum Obj)
{
int i;
string cmdpara, cmdvalue, cmddbtype;
SqlCommand theCmd = new SqlCommand();
SqlTransaction theTran = (SqlTransaction)this.Transaction;
SqlConnection cnn;
if (null == this.Connection)
{
cnn = (SqlConnection)GetConnection(ConString, "mssql");
}
else
{
cnn = (SqlConnection)this.Connection;
}
if (null == this.Transaction)
{
theCmd = new SqlCommand(CommandText, cnn);
}
else
{
theCmd = new SqlCommand(CommandText, cnn, theTran);
}
for (i = 1; i <= Params.Count;)
{
cmdpara = Params[i].ToString();
cmddbtype = Params[i + 1].ToString();
cmdvalue = Params[i + 2].ToString();
theCmd.Parameters.AddWithValue(cmdpara, cmddbtype).Value = cmdvalue;
i = i + 3;
}
theCmd.CommandTimeout = 0;
theCmd.CommandType = CommandType.StoredProcedure;
string theSubstring = CommandText.Substring(0, 6).ToUpper();
switch (theSubstring)
{
case "SELECT":
theCmd.CommandType = CommandType.Text;
break;
case "UPDATE":
theCmd.CommandType = CommandType.Text;
break;
case "RESTOR":
theCmd.CommandType = CommandType.Text;
break;
case "INSERT":
theCmd.CommandType = CommandType.Text;
break;
case "DELETE":
theCmd.CommandType = CommandType.Text;
break;
case "CREATE":
theCmd.CommandType = CommandType.Text;
break;
case "DROP S":
theCmd.CommandType = CommandType.Text;
break;
case "DROP V":
theCmd.CommandType = CommandType.Text;
break;
case "DROP D":
theCmd.CommandType = CommandType.Text;
break;
case "DBCC C":
theCmd.CommandType = CommandType.Text;
break;
case "DBCC S":
theCmd.CommandType = CommandType.Text;
break;
case "BACKUP":
theCmd.CommandType = CommandType.Text;
break;
case "EXEC S":
theCmd.CommandType = CommandType.Text;
break;
case "SET DA":
theCmd.CommandType = CommandType.Text;
break;
case "WITH I": //TODO DONE for common table expressions start with an I for the CTE name
theCmd.CommandType = CommandType.Text;
break;
case "IF EXI":
theCmd.CommandType = CommandType.Text;
break;
case "USE CT":
theCmd.CommandType = CommandType.Text;
break;
}
if (CommandText.IndexOf("SET OFFLINE") > 0 || CommandText.IndexOf("SET ONLINE") > 0)
{
theCmd.CommandType = CommandType.Text;
}
if (CommandText.Length >= 15)
{
if (CommandText.Substring(0, 15).ToUpper() == "DROP TABLE [TMP" || CommandText.Substring(0, 15).ToUpper() == "DROP TABLE [MGR" || CommandText.Substring(0, 15).ToUpper() == "DROP TABLE [TPS")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 10)
{
if (CommandText.Substring(0, 10).ToUpper() == "DROP SYNON")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 22)
{
if (CommandText.Substring(0, 22).ToUpper() == "DROP TABLE MST_PATIENT")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 30)
{
if (CommandText.Substring(0, 30).ToUpper() == "DROP TABLE DTL_PATIENTCONTACTS")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 27)
{
if (CommandText.Substring(0, 28).ToUpper() == "DROP TABLE TMP_PATIENTMASTER")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 30)
{
if (CommandText.Substring(0, 14).ToUpper() == "OPEN SYMMETRIC")
{
theCmd.CommandType = CommandType.Text;
}
}
if (CommandText.Length >= 15)
{
if (CommandText.Substring(0, 15).ToUpper() == "CLOSE SYMMETRIC")
{
theCmd.CommandType = CommandType.Text;
}
}
//int j = 0;
// SqlCommand comm = new SqlCommand("[dbo].[AdminIQToolsDB]", cnn);
// comm.CommandType = CommandType.StoredProcedure;
// j=comm.ExecuteNonQuery();
// comm.Dispose();
// comm = null;
theCmd.Connection = cnn;
try
{
SqlCommand cm;
if (ClsUtility.SDate != "")
{
cm = new SqlCommand("SET Dateformat " + ClsUtility.SDate, cnn);
cm.ExecuteNonQuery();
cm.Dispose();
}
cm = null;
if (Obj == ClsUtility.ObjectEnum.DataSet)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataSet theDS = new DataSet();
//theDS.Tables[0].BeginLoadData();
theAdpt.Fill(theDS);
//theDS.Tables[0].EndLoadData();
return(theDS);
}
if (Obj == ClsUtility.ObjectEnum.DataTable)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT);
}
if (Obj == ClsUtility.ObjectEnum.DataRow)
{
SqlDataAdapter theAdpt = new SqlDataAdapter(theCmd);
DataTable theDT = new DataTable();
theDT.BeginLoadData();
theAdpt.Fill(theDT);
theDT.EndLoadData();
return(theDT.Rows[0]);
}
if (Obj == ClsUtility.ObjectEnum.ExecuteNonQuery)
{
int NoRowsAffected = theCmd.ExecuteNonQuery();
return(NoRowsAffected);
}
if (null == this.Connection)
{
cnn.Close();
}
return(0);
}
catch (Exception err)
{
throw err;
}
finally
{
if (null != cnn)
{
if (null == this.Connection)
{
cnn.Close();
}
}
}
}