public int ExecuteNonQuery(string sql)
{
int rowsUpdated = 0;
try
{
var con = CONNECTION.OpenCon();
OracleCommand cmd = new OracleCommand(sql, con);
rowsUpdated = cmd.ExecuteNonQuery();
cmd.Dispose();
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteNonQuery Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ExecuteNonQuery Error!", ex);
}
return(-2);
}
return(rowsUpdated);
}
public object ExecuteScalar(string sql)
{
object value = null;
try
{
var con = CONNECTION.OpenCon();
var cmd = new OracleCommand(sql, con);
value = cmd.ExecuteScalar();
cmd.Dispose();
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteScalar Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ExecuteScalar Error!", ex);
}
return(null);
}
return(value);
}
public string ExecuteReadTableName(string columnName)
{
try
{
var dt = new DataTable();
var sql = string.Format(@"SELECT name FROM sqlite_master where sql LIKE('%{0}%')", columnName);
var con = CONNECTION.OpenCon();
var cmd = new SqliteCommand(sql, con);
var reader = cmd.ExecuteReader();
dt.Load(reader);
cmd.Dispose();
CONNECTION.CloseCon(con);
if (dt == null || dt.Rows.Count <= 0)
{
return(string.Empty);
}
var dr = dt.Rows[0];
return(dr["name"].ToString());
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteReadTableName Error!",
Ex = ex,
});
return(string.Empty);
}
}
public DataTable ExecuteReadTableSchema(string sql)
{
var dt = new DataTable();
try
{
var con = CONNECTION.OpenCon();
var cmd = new OracleCommand(sql, con);
var reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Close();
cmd.Dispose();
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteReadTableSchema Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ExecuteReadTableSchema Error!", ex);
}
return(null);
}
return(dt);
}
public DataTable ExecuteReadTable(string sql)
{
var dt = new DataTable();
try
{
var con = CONNECTION.OpenCon();
var cmd = new SqlCommand(sql, con);
var reader = cmd.ExecuteReader();
var schemaTbl = reader.GetSchemaTable();
dt.Load(reader);
//GetTableName
if (schemaTbl.Rows.Count <= 0)
{
return(dt);
}
var schemaRow = schemaTbl.Rows[0];
var tableName = schemaRow[DbCIC.BaseTableName].ToString();
if (string.IsNullOrEmpty(tableName))
{
tableName = ExecuteReadTableName(dt.Columns[0].ColumnName);
}
dt.TableName = tableName;
reader.Close();
cmd.Dispose();
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteReadTable Error!",
AdditionalMessage = $"SQL: {sql}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ExecuteReadTable Error!", ex);
}
return(null);
}
return(dt);
}
public bool UpdateTable(DataTable table, string tableName, bool setInsertOn = true, bool setModfyOn = true, string additionalMessage = "")
{
try
{
TableHelper.SetDefaultColumnValues(table, setInsertOn, setModfyOn);
var con = CONNECTION.OpenCon();
var adapter = new MySqlDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
var cmd = new MySqlCommandBuilder(adapter);
adapter.Update(table);
cmd.Dispose();
adapter.Dispose();
CONNECTION.CloseCon(con);
return(true);
}
catch (DBConcurrencyException cex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable DBConcurrencyError!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = cex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", cex);
}
return(false);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", ex);
}
return(false);
}
}
public int ExecuteNonQuery(List <string> sqlList)
{
int rowsUpdated = 0;
var currentSql = string.Empty;
try
{
var con = CONNECTION.OpenCon();
foreach (var sql in sqlList)
{
currentSql = sql;
var cmd = new SqlCommand(sql, con);
var cmdResult = cmd.ExecuteNonQuery();
if (cmdResult == -2)
{
return(-2);
}
rowsUpdated += cmdResult;
cmd.Dispose();
}
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteNonQuery Error!",
AdditionalMessage = $"SQL: {currentSql}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ExecuteNonQuery Error!", ex);
}
return(-2);
}
rowsUpdated = rowsUpdated == -2 ? 0 : rowsUpdated;
return(rowsUpdated);
}
public string ExecuteReadTableName(string columnName)
{
var currentSql = string.Empty;
try
{
var dt = new DataTable();
var sql = string.Format(@"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name = '{0}'", columnName);
var con = CONNECTION.OpenCon();
currentSql = sql;
var cmd = new SqlCommand(sql, con);
var reader = cmd.ExecuteReader();
dt.Load(reader);
cmd.Dispose();
CONNECTION.CloseCon(con);
if (dt == null || dt.Rows.Count <= 0)
{
return(string.Empty);
}
var dr = dt.Rows[0];
return(dr[DbCIC.TableName].ToString());
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteReadTableName Error!",
AdditionalMessage = $"SQL: {currentSql}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ExecuteReadTableName Error!", ex);
}
return(null);
}
}
public int ExecuteNonQuery(List <string> sqlList)
{
int rowsUpdated = 0;
try
{
var con = CONNECTION.OpenCon();
foreach (var sql in sqlList)
{
var cmd = new SqliteCommand(sql, con);
var cmdResult = cmd.ExecuteNonQuery();
if (cmdResult == -2)
{
return(-2);
}
rowsUpdated += cmdResult;
cmd.Dispose();
}
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteNonQuery Error!",
Ex = ex,
});
return(-2);
}
rowsUpdated = rowsUpdated == -2 ? 0 : rowsUpdated;
return(rowsUpdated);
}
