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