public OleDbDataReader sql(string sql) { if (!connected) { connect(); } timing.start(); cmd = new OleDbCommand(sql, conn); OleDbDataReader reader = null; string errMsg = ""; try { reader = cmd.ExecuteReader(); }catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); return(reader); }
public void error(Exception e) { App.error($@"*** SQL Query Reader : {e.Message} {sql}"); errorMsg = e.Message; // allow retry? state = states.Blocked; result = results.Error; // need to save this object }
public bool read() { bool more = false; try { more = _reader.Read(); } catch (Exception e) { App.error("Something wrong with SQL Reader : " + e.Message); } return(more); }
public void insert(string tableName, string[] fields, object[] values) { string _fields = String.Join(",", fields); string _values = ""; for (int i = 0; i < values.Length; i++) { if (i > 0) { _values += ","; } _values += $"@" + fields[i]; // param placeholders } string sql = $"INSERT INTO [{tableName}] ({_fields}) VALUES ({_values})"; if (!connected) { connect(); } timing.start(); cmd = new OleDbCommand(sql, conn); // add the params int _i = 0; foreach (object value in values) { cmd.Parameters.AddWithValue($"@" + fields[_i], values[_i]); _i++; } string errMsg = ""; try { App.log("exec insert query " + sql); rowsAffected = cmd.ExecuteNonQuery(); App.log("done"); } catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); }
public void delete(string tableName, string where = "", bool confirmAll = false) { string sql = $"DELETE * FROM {tableName} "; if (where.Length == 0) { if (!confirmAll) { Console.WriteLine($"*** SQL Delete table {tableName} : You must confirm All if no where clause specified"); return; } else { Console.WriteLine($"*** SQL Delete : Warning - deleting all records table : {tableName}"); } } else { sql += $" WHERE {where} ;"; } if (!connected) { connect(); } timing.start(); cmd = new OleDbCommand(sql, conn); string errMsg = ""; try { App.log("exec delete query " + sql); rowsAffected = cmd.ExecuteNonQuery(); App.log("done"); } catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); }
public OleDbDataReader join(string fields, string tables, string on, string where = "", string orderBy = "") { string[] _tables = tables.Split(','); string sql = $"SELECT {fields} FROM {_tables[0]} "; sql += $" INNER JOIN {_tables[1]} "; sql += $" ON ( {on} )"; if (where.Length > 0) { sql += $" WHERE {where} "; } if (orderBy.Length > 0) { sql += $" ORDER BY {orderBy} "; } sql += ";"; if (!connected) { connect(); } timing.start(); cmd = new OleDbCommand(sql, conn); OleDbDataReader reader = null; string errMsg = ""; try { reader = cmd.ExecuteReader(); } catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); return(reader); }
public void update(string tableName, string[] fields, string[] values, string where) { string sql = $"UPDATE [{tableName}] SET "; int i = 0; foreach (string field in fields) { if (i > 0) { sql += ","; } string value = values[i]; i++; sql += $"[{field}] = {value}"; } sql += $" WHERE {where} ;"; if (!connected) { connect(); } timing.start(); cmd = new OleDbCommand(sql, conn); string errMsg = ""; try { App.log("exec update query " + sql); rowsAffected = cmd.ExecuteNonQuery(); App.log("done"); } catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); }
public OleDbDataReader query(string table, string where = "", string orderBy = "") { string sql = $"SELECT * FROM {table} "; if (where.Length > 0) { sql += $" WHERE {where} "; } if (orderBy.Length > 0) { sql += $" ORDER BY {orderBy} "; } sql += ";"; if (!connected) { connect(); } timing.start(); cmd = new OleDbCommand(sql, conn); OleDbDataReader reader = null; string errMsg = ""; try { reader = cmd.ExecuteReader(); } catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); return(reader); }
public void insert(string tableName, string fields, string values) { string[] _fields = fields.Split(','); fields = String.Join(",", _fields.Select(x => $"[{x}]").ToArray()); // another kludge for sql strings - // I'll assume 1 delimited by a special pair of chars - like sql dates // ~sql~ // split string BEFORE break into commas var hasSql = false; var sqlQuery = ""; if (values.Contains(App.sqlStringValueDelim)) { hasSql = true; string[] temp = values.Split(App.sqlStringValueDelim); // assuming 1 sql strung only, so 3 parts values = temp[0] + "@sql" + temp[2]; sqlQuery = temp[1]; } // this will f**k with function calls and expressions char sep = ','; if (values[0] == '|') { sep = '|'; values = values.Substring(1); } // now we can split the values on , without worrying about 's in sql strings // damn - I coulda used the | hack I wrote earlier!! string[] _values = values.Split(sep); // temp array for params string[] _params = new string[10]; int offset = 0; // keep track of param int i = 0; foreach (string value in _values) { if (value[0] == App.sqlStringValueMarker) { _params[offset] = value.Substring(1); // remove marker _values[i] = $"@{offset}"; offset++; // only increment when sql found } i++; // loop counter } int maxOffset = offset; // oh balls would need to convert them back to their original data types so sql can convert them back // I'm only worried about strings, so I'll use placeholders only for strings // in most cases, I'm trying to encode SQL queries!! // maybe use a special syntax such as ^ to denote an sql query string // now use the values with params - use ordinals as placeholders // need to rejoin them!! this is simply for compat with update values = String.Join(",", _values); // includes placeholders //values = values.Replace("'", "\""); // escape string sql = $"INSERT INTO [{tableName}] ({fields}) VALUES ({values})"; if (!connected) { connect(); } timing.start(); // escaping single quotes //sql = sql.Replace("'", "'''"); // 3 ' cmd = new OleDbCommand(sql, conn); offset = 0; for (offset = 0; offset < maxOffset; offset++) { cmd.Parameters.AddWithValue($"@{offset}", _params[offset]); } if (hasSql) { cmd.Parameters.AddWithValue($"@sql", sqlQuery); } string errMsg = ""; try { App.log("exec insert query " + sql); rowsAffected = cmd.ExecuteNonQuery(); App.log("done"); } catch (Exception e) { errMsg = e.Message; App.error($@"Db.sql {e.Message} {sql} "); } timing.stop(); timing.log($@"{errMsg} : {sql}"); }