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