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