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 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 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 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 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 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 DataTable GetTable(string tableName, string where = null, string orderBy = null)
{
var dt = new DataTable(tableName);
try
{
var whereCond = ConvertionHelper.GetWhere(where);
var orderCond = ConvertionHelper.GetOrderBy(orderBy);
var sql = 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!",
Ex = ex,
});
}
return(dt);
}
