public bool RenewTbl(string tableName, Dictionary <string, string> columns)
{
try
{
var colList = new List <ColumnData>();
foreach (var col in columns)
{
var colData = new ColumnData
{
Name = col.Key,
Type = col.Value,
};
if (col.Value == DbDEF.TxtNotNull)
{
colData.DefaultValue = "default";
}
colList.Add(colData);
}
return(RenewTbl(tableName, colList));
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "RenewTbl Error!",
Ex = ex,
});
return(false);
}
}
public bool InsertValue(string tableName, Dictionary <string, string> data, bool setInsertOn = true)
{
try
{
var sql = ScriptHelper.GetInsertSqlScript(tableName, data, setInsertOn);
var result = m_Execute.ExecuteNonQuery(sql);
if (result == -2)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "InsertValue Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("InsertValue Error!", ex);
}
return(false);
}
}
public bool CreateTable(string tableName, List <ColumnData> columns)
{
try
{
ColumnHelper.SetDefaultColumns(columns);
var sql = ScriptHelper.GetMySQLCreateTableSql(tableName, columns);
var result = m_Execute.ExecuteNonQuery(sql);
if (result == -2)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "CreateTable Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("CreateTable Error!", ex);
}
return(false);
}
}
public DataRow GetRow(string tableName, string where = null, string orderBy = null)
{
try
{
var whereCond = ConvertionHelper.GetWhere(where);
var orderCnd = ConvertionHelper.GetOrderBy(orderBy);
var sql = string.Format(@"SELECT * FROM {0} {1} {2}", tableName, whereCond, orderCnd);
return(GetRow(sql));
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetRow Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetRow Error!", ex);
}
return(null);
}
}
public bool TableExists(string table)
{
try
{
var result = m_Execute.ExecuteScalar(string.Format(@"SELECT name FROM sqlite_master WHERE type='table' AND name='{0}'", table));
if (result == null)
{
return(false);
}
return(result.ToString() == table);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "TableExists Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("TableExists Error!", ex);
}
return(false);
}
}
public DataSet GetDataSet(List <string> tblSqlDict, string dataSetName)
{
var ds = new DataSet(dataSetName);
try
{
foreach (var item in tblSqlDict)
{
var tbl = GetTable(item);
ds.Tables.Add(tbl);
}
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetDataSet Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetDataSet Error!", ex);
}
}
return(ds);
}
public string GetNextSortOrder(string tableName, string sortOrderColName, string where = null)
{
var lstSortOrder = string.Empty;
try
{
var result = GetLastSortOrder(tableName, sortOrderColName, where);
lstSortOrder = Convert.ToString(Convert.ToInt32(result) + 1);
SLLog.WriteInfo("GetNextSortOrder", "Getting next sort order successfully! => " + lstSortOrder, true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetNextSortOrder Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetNextSortOrder Error!", ex);
}
}
return(lstSortOrder);
}
public bool DeleteDatabase(string databaseName)
{
try
{
var result = m_Execute.ExecuteNonQuery(string.Format(@"DROP DATABASE {0}", databaseName));
if (result == -2)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "DeleteDatabase Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("DeleteDatabase Error!", ex);
}
return(false);
}
}
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 bool DeleteRows(string tableName, string where)
{
try
{
var whereCnd = ConvertionHelper.GetWhere(where);
var result = m_Execute.ExecuteNonQuery(string.Format(@"DELETE FROM {0} {1}", tableName, whereCnd));
if (result == -2)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "DeleteRows Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("DeleteRows Error!", ex);
}
return(false);
}
}
public bool ClearTable(string tableName)
{
try
{
var result = m_Execute.ExecuteNonQuery(string.Format(@"DELETE FROM {0}", tableName));
if (result == -2)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ClearTable Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ClearTable Error!", ex);
}
return(false);
}
}
public bool TableExists(string table)
{
try
{
var result = m_Execute.ExecuteScalar(string.Format(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{0}'", table));
if (result == null)
{
return(false);
}
return(result.ToString() == table);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "TableExists Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("TableExists Error!", ex);
}
return(false);
}
}
public bool DatabaseExists(string databaseName)
{
try
{
var result = m_Execute.ExecuteScalar(string.Format(@"SELECT name FROM master.dbo.sysdatabases WHERE name = '{0}'", databaseName));
if (result == null)
{
return(false);
}
return(result.ToString() == databaseName);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "DatabaseExists Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("DatabaseExists Error!", ex);
}
return(false);
}
}
public bool UpdateTables(List <DataTable> tableList)
{
try
{
var result = false;
foreach (DataTable tbl in tableList)
{
result = UpdateTable(tbl);
if (!result)
{
return(result);
}
}
return(result);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTables Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTables Error!", ex);
}
return(false);
}
}
public DataTable GetTableSchema(string tableName)
{
DataTable schemaTbl = new DataTable();
try
{
var sql = string.Format("SELECT * FROM {0}", tableName);
schemaTbl = m_Execute.ExecuteReadTableSchema(sql);
SLLog.WriteInfo("GetTableSchema", "Getting Schema Table successfully!", true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetTableSchema Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetTableSchema Error!", ex);
}
}
return(schemaTbl);
}
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 string GetValueFromColumn(string tableName, string columnName, string where)
{
var resultStr = string.Empty;
try
{
var whereCnd = ConvertionHelper.GetWhere(where);
var sql = string.Format(@"SELECT {1} FROM {0} {2}", tableName, columnName, whereCnd);
var tbl = GetTable(sql);
if (tbl.Rows.Count <= 0)
{
return(resultStr);
}
resultStr = tbl.Rows[0][columnName].ToString();
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetValueFromColumn Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetValueFromColumn Error!", ex);
}
}
return(resultStr);
}
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 GetLastSortOrder(string tableName, string sortOrderColName, string where = null)
{
var result = "0";
try
{
var whereCnd = ConvertionHelper.GetWhere(where);
var sql = string.Format(@"SELECT {0} FROM {1} {2} ORDER BY CAST({0} AS INTEGER) DESC", sortOrderColName, tableName, whereCnd);
var tbl = GetTable(sql);
if (tbl.Rows.Count <= 0)
{
return(result);
}
result = string.IsNullOrEmpty(tbl.Rows[0][sortOrderColName].ToString()) ? "0" : tbl.Rows[0][sortOrderColName].ToString();
SLLog.WriteInfo("GetLastSortOrder", "Getting last sort order successfully! => " + result, true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetLastSortOrder Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetLastSortOrder Error!", ex);
}
}
return(result);
}
public bool ClearDatabase(string databaseName)
{
try
{
var result = true;
var tbl = m_Get.GetTable(string.Format(@"SELECT NAME FROM {0} WHERE type = 'table' ORDER BY NAME", ConvertionHelper.GetMasterTable(Settings.Type)), "MASTER");
foreach (DataRow dr in tbl.Rows)
{
var clearResult = ClearTable(dr["NAME"].ToString());
if (!clearResult)
{
result = false;
}
}
return(result);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ClearDatabase Error!",
Ex = ex,
});
return(false);
}
}
public DataRow GetRow(string sql)
{
try
{
var tbl = GetTable(sql);
if (tbl.Rows.Count <= 0)
{
return(null);
}
return(tbl.Rows[0]);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetRow Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetRow Error!", ex);
}
return(null);
}
}
public bool UpdateOneValue(string tableName, string column, string value, string where, string additionalMessage = "")
{
try
{
var whereCnd = ConvertionHelper.GetWhere(where);
var sql = string.Format(@"UPDATE {0} SET {1} = '{2}', {3} = '{4}' {5}",
tableName, column, ConvertionHelper.CleanStringForSQL(value), DbCIC.ModifyOn, DateTime.Now.ToString(), whereCnd);
var result = m_Execute.ExecuteNonQuery(sql);
if (result == -2)
{
return(false);
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateOneValue Error!",
AdditionalMessage = additionalMessage,
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateOneValue Error!", ex);
}
return(false);
}
}
public bool ColumnExists(string tableName, string columnName)
{
try
{
var result = false;
var sql = string.Format("SELECT * FROM {0} WHERE ColumnName = '{1}'", tableName, columnName);
var tblSchema = m_Execute.ExecuteReadTableSchema(sql);
foreach (DataRow dr in tblSchema.Rows)
{
if (dr["ColumnName"].ToString() == columnName)
{
result = true;
break;
}
}
return(result);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ColumnExists Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("ColumnExists Error!", ex);
}
return(false);
}
}
public bool UpdateTables(List <DataTable> tableList, bool setInsertOn = true, bool setModifyOn = true, string additionalMessage = "")
{
try
{
var result = false;
foreach (DataTable tbl in tableList)
{
result = UpdateTable(tbl, setInsertOn, setModifyOn, additionalMessage);
}
return(result);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTables Error!",
AdditionalMessage = additionalMessage,
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTables Error!", ex);
}
return(false);
}
}
public bool InsertRow(string tableName, DataRow row, bool setInsertOn = true)
{
try
{
var colRowDict = new Dictionary <string, string>();
foreach (DataColumn dc in row.Table.Columns)
{
colRowDict.Add(dc.ColumnName, row[dc.ColumnName].ToString());
}
return(InsertValue(tableName, colRowDict, setInsertOn));
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "InsertRow Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("InsertRow Error!", ex);
}
return(false);
}
}
public DataTable GetTable(string tableName, string where = null, string orderBy = null)
{
var currentSql = string.Empty;
var dt = new DataTable(tableName);
try
{
var whereCond = ConvertionHelper.GetWhere(where);
var orderCond = ConvertionHelper.GetOrderBy(orderBy);
var sql = currentSql = string.Format(@"SELECT * FROM {0} {1} {2}", tableName, whereCond, orderCond);
dt = GetTable(sql);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetTable Error!",
AdditionalMessage = $"SQL: {currentSql}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetTable Error!", ex);
}
}
return(dt);
}
public bool CreateTable(string tableName, Dictionary <string, string> columns)
{
try
{
var colList = new List <ColumnData>();
foreach (var col in columns)
{
colList.Add(new ColumnData
{
Name = col.Key,
Type = col.Value
});
}
return(CreateTable(tableName, colList));
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "CreateTable Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("CreateTable Error!", ex);
}
return(false);
}
}
public DataTable GetTable(string sql)
{
var dt = new DataTable();
try
{
dt = m_Execute.ExecuteReadTable(sql);
SLLog.WriteInfo("GetTable", "Getting Table successfully!", true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "GetTable Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("GetTable Error!", ex);
}
}
return(dt);
}
public bool CreateDatabase(string databaseName)
{
var result = false;
try
{
var cmdResult = m_Execute.ExecuteNonQuery(string.Format(@"CREATE DATABASE {0};", databaseName));
result = cmdResult != -2;
return(result);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "CreateDatabase Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("CreateDatabase Error!", ex);
}
return(false);
}
}
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);
}
}