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(); } } } }