public bool Exists(ECTable _table)
{
Type t = _table.GetType();
command.CommandText = "SELECT * FROM sqlite_master where type = 'table' AND name='" + t.Name + "'";
return(command.ExecuteScalar() != null);
}
public int Insert(ECTable _table)
{
Type t = _table.GetType();
PropertyInfo[] properties = t.GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute), false)).ToArray();
string sql = "INSERT INTO `" + t.Name + "` (";
foreach (PropertyInfo p in properties.Where(x => !x.IsDefined(typeof(ECAutoIncrementAttribute), false)))
{
sql += "`" + p.Name + "`,";
}
sql = sql.Substring(0, sql.Length - 1); //delete last comma
sql += ") VALUES (";
foreach (PropertyInfo p in properties.Where(x => !x.IsDefined(typeof(ECAutoIncrementAttribute), false)))
{
sql += _table.GetValueInSqlFormat(p) + ",";
}
sql = sql.Substring(0, sql.Length - 1); //delete last comma
sql += ");";
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
cmd.ExecuteNonQuery();
sql = "SELECT MAX(RecId) AS RecId FROM " + _table.GetType().Name;
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
using (MySqlDataReader res = cmd.ExecuteReader())
{
res.Read();
return(res.GetInt32(nameof(_table.RecId)));
}
}
virtual protected string MakeSelectFrom(ECTable _table, bool _isRootTable = false)
{
string ret;
if (_isRootTable)
{
ret = "SELECT ";
}
else
{
ret = "";
}
foreach (PropertyInfo p in _table.GetType().GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute))))
{
ret += GetSqlTableName(_table) + "." + p.Name + " AS '" + _table.TableName + "." + p.Name + "',";
}
foreach (ECJoin j in _table.Joins)
{
ret += MakeSelectFrom((ECTable)j.Table) + ",";
}
ret = ret.Substring(0, ret.Length - 1);
if (_isRootTable)
{
ret += $" FROM {GetSqlTableName(_table)}";
}
return(ret);
}
virtual protected string ProcessFromToOperator(ECTable _table, int id, string currVal, string lastVal, char lastChar, ref Dictionary <string, string> _parameter)
{
string clause = "";
if (lastVal == "" || currVal == "")
{
if (lastChar == '.') //case: "1.."
{
clause += ">=";
_parameter.Add("F" + _table.TableName + id, lastVal);
}
else //case: "..5"
{
clause += "<=";
_parameter.Add("F" + _table.TableName + id, currVal);
}
clause += "@F" + id;
}
else //case: "1..5"
{
clause += " BETWEEN ";
clause += "@F" + _table.TableName + (id - 1);
clause += " AND ";
clause += "@F" + _table.TableName + id;
_parameter.Add("F" + _table.TableName + (id - 1), lastVal);
_parameter.Add("F" + _table.TableName + id, currVal);
}
return(clause);
}
virtual protected string MakeJoins(ECTable _table)
{
string ret = "";
foreach (ECJoin j in _table.Joins)
{
ECTable joinTable = ((ECTable)j.Table);
switch (j.JoinType)
{
case ECJoinType.Inner:
ret += " INNER JOIN ";
break;
case ECJoinType.LeftOuter:
ret += " LEFT OUTER JOIN ";
break;
case ECJoinType.RightOuter:
ret += " RIGHT OUTER JOIN ";
break;
}
if (j.OnTargetField != null)
{
ret += $"{joinTable.SqlTableName} ON {joinTable.SqlTableName}.{j.OnTargetField} = {_table.SqlTableName}.{j.OnSourceField}";
}
else
{
ret += $"{joinTable.SqlTableName} ON {joinTable.SqlTableName}.{nameof(joinTable.RecId)} = {_table.SqlTableName}.{j.OnSourceField}";
}
ret += MakeJoins(joinTable);
}
return(ret);
}
virtual protected string CreateOrderByClause(ECTable _table, bool _isRootTable = false)
{
string order = "";
string orderJ = "";
foreach (string s in _table.Order)
{
order += $"{GetSqlTableName(_table)}.{s},";
}
foreach (ECJoin j in _table.Joins)
{
ECTable joinTable = (ECTable)j.Table;
orderJ = CreateOrderByClause(joinTable);
if (orderJ != "")
{
order += orderJ + ",";
}
}
if (_isRootTable && order.Length > 1)
{
return(" ORDER BY " + order.Substring(0, order.Length - 1) + " " + _table.OrderType.ToString() + ";");
}
else
{
return(_isRootTable ? ";" : "");
}
}
public void AlterTableFields(ECTable _table)
{
Type t = _table.GetType();
Dictionary <string, string> fieldsToAdd = new Dictionary <string, string>();
Dictionary <string, string> fieldsToDelete = new Dictionary <string, string>();
Dictionary <string, string> databaseFields = new Dictionary <string, string>();
GetFieldsToChange(_table, ref fieldsToAdd, ref fieldsToDelete, ref databaseFields);
//Add Fields
foreach (KeyValuePair <string, string> kv in fieldsToAdd)
{
PropertyInfo p = t.GetProperty(kv.Key);
command.CommandText = "ALTER TABLE '" + t.Name + "' ADD '" + kv.Key + "' " + kv.Value;
if (p.GetCustomAttribute(typeof(ECPrimaryKeyAttribute)) != null)
{
command.CommandText += " NOT NULL PRIMARY KEY";
}
else if (p.GetCustomAttribute(typeof(ECNotNullAttribute)) != null)
{
command.CommandText += " NOT NULL";
}
else
{
command.CommandText += " NULL";
}
if (p.GetCustomAttribute(typeof(ECAutoIncrementAttribute)) != null)
{
command.CommandText += " AUTOINCREMENT";
}
command.CommandText += ";";
command.ExecuteNonQuery();
}
//Delete Fields
if (fieldsToDelete.Count > 0)
{
command.CommandText = "ALTER TABLE '" + t.Name + "' RENAME TO '" + t.Name + "_OLD'";
command.ExecuteNonQuery();
CreateTableIfNotExist(_table);
string fields = "";
foreach (PropertyInfo p in t.GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute))))
{
fields += p.Name + ",";
}
fields = fields.Substring(0, fields.Length - 1);
command.CommandText = "INSERT INTO '" + t.Name + "' (" + fields + ") SELECT " + fields + " FROM '" + t.Name + "_OLD';";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE '" + t.Name + "_OLD" + "'";
command.ExecuteNonQuery();
}
}
public void Delete(ECTable _table)
{
Type t = _table.GetType();
string sql = "DELETE FROM `" + t.Name + "` WHERE RecId=" + _table.RecId.ToString() + ";";
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
cmd.ExecuteNonQuery();
}
public void Delete(ECTable _table)
{
Type t = _table.GetType();
string sql = "DELETE FROM `" + t.Name + "` WHERE RecId=@RecId;";
command.CommandText = sql;
command.Parameters.Clear();
command.Parameters.AddWithValue("RecId", _table.RecId.ToString());
command.ExecuteNonQuery();
}
public void CreateTableIfNotExist(ECTable _table)
{
Type t = _table.GetType();
PropertyInfo[] pi = t.GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute), false)).ToArray();
ECTableFieldAttribute tfa;
if (Exists(_table))
{
string primKey = "";
string createStmt = "CREATE TABLE `" + currentDatabase + "`.`" + t.Name + "` (";
foreach (PropertyInfo p in pi)
{
tfa = p.GetCustomAttribute <ECTableFieldAttribute>();
createStmt += "`" + p.Name + "` " + tfa.type.ToString();
if (tfa.type == FieldType.VARCHAR)
{
createStmt += "(" + tfa.length.ToString() + ") ";
}
if (p.GetCustomAttribute(typeof(ECPrimaryKeyAttribute)) != null)
{
createStmt += " NOT NULL";
primKey += "`" + p.Name + "`,";
}
else if (p.GetCustomAttribute(typeof(ECNotNullAttribute)) != null)
{
createStmt += " NOT NULL";
}
else
{
createStmt += " NULL";
}
if (p.GetCustomAttribute(typeof(ECAutoIncrementAttribute)) != null)
{
createStmt += " AUTO_INCREMENT";
}
createStmt += ",";
}
if (primKey != "")
{
createStmt += "PRIMARY KEY (" + primKey.Substring(0, primKey.Length - 1) + "));";
}
else
{
createStmt = createStmt.Substring(0, createStmt.Length - 1) + ");"; //Delete the last ,
}
using (MySqlCommand cmd = new MySqlCommand(createStmt, connection))
cmd.ExecuteNonQuery();
}
}
internal void GetFieldsToChange(ECTable _table, ref Dictionary <string, string> _fieldsToAdd,
ref Dictionary <string, string> _fieldsToDelete,
ref Dictionary <string, string> _databaseFields)
{
Type t = _table.GetType();
Dictionary <string, string> tableProperties = new Dictionary <string, string>();
foreach (PropertyInfo p in _table.GetType().GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute))))
{
ECTableFieldAttribute tfa = (ECTableFieldAttribute)p.GetCustomAttribute(typeof(ECTableFieldAttribute));
if (tfa.type == FieldType.VARCHAR)
{
tableProperties.Add(p.Name, FieldTypeToSqlType(tfa.type) + "(" + tfa.length.ToString() + ")");
}
else
{
tableProperties.Add(p.Name, FieldTypeToSqlType(tfa.type));
}
}
string sql = "SHOW COLUMNS FROM `" + t.Name + "`;";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader res = cmd.ExecuteReader();
while (res.Read())
{
string type = res["Type"].ToString().ToUpper();
if (type.Substring(0, type.IndexOf('(')) != "VARCHAR")
{
type = type.Substring(0, type.IndexOf('('));
}
_databaseFields.Add(res["Field"].ToString(), type);
}
foreach (KeyValuePair <string, string> kv in tableProperties)
{
if (!_databaseFields.Contains(kv))
{
_fieldsToAdd.Add(kv.Key, kv.Value);
}
}
foreach (KeyValuePair <string, string> kv in _databaseFields)
{
if (!tableProperties.Contains(kv))
{
_fieldsToDelete.Add(kv.Key, kv.Value);
}
}
res.Close();
}
public string GenerateSqlForECTableWithPreparedStatements(ECTable _table,
ref Dictionary <string, string> _parameters)
{
string sql = MakeSelectFrom(_table, true);
sql += MakeJoins(_table);
sql += CreateParameterizedWhereClause(_table, ref _parameters);
sql += CreateOrderByClause(_table, true);
return(sql);
}
public bool Exists(ECTable _table)
{
Type t = _table.GetType();
string sql = "SELECT Count(*) AS c FROM information_schema.TABLES where " +
"TABLE_SCHEMA = '" + currentDatabase + "' AND TABLE_NAME = '" + t.Name + "'; ";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader res = cmd.ExecuteReader();
res.Read();
bool ret = (res.GetInt16("c") == 0);
res.Close();
return(ret);
}
public void CreateTableIfNotExist(ECTable _table)
{
Type t = _table.GetType();
PropertyInfo[] pi = t.GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute), false)).ToArray();
ECTableFieldAttribute tfa;
if (!Exists(_table))
{
string createStmt = "CREATE TABLE " + t.Name + " (";
int i = 0;
foreach (PropertyInfo p in pi)
{
tfa = p.GetCustomAttribute <ECTableFieldAttribute>();
createStmt += "`" + p.Name + "` " + FieldTypeToSqlType(tfa.type);
if (tfa.type == FieldType.VARCHAR)
{
createStmt += "(" + tfa.length.ToString() + ") ";
}
if (p.GetCustomAttribute(typeof(ECPrimaryKeyAttribute)) != null)
{
createStmt += " NOT NULL PRIMARY KEY";
}
else if (p.GetCustomAttribute(typeof(ECNotNullAttribute)) != null)
{
createStmt += " NOT NULL";
}
else
{
createStmt += " NULL";
}
if (p.GetCustomAttribute(typeof(ECAutoIncrementAttribute)) != null)
{
createStmt += " AUTOINCREMENT";
}
createStmt += ",";
i++;
}
createStmt = createStmt.Substring(0, createStmt.Length - 1) + ");"; //Delete the last ,
command.CommandText = createStmt;
command.ExecuteNonQuery();
}
}
public void Modify(ECTable _table)
{
Type t = _table.GetType();
PropertyInfo[] properties = t.GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute), false)).ToArray();
string sql = "UPDATE `" + t.Name + "` SET ";
foreach (PropertyInfo p in properties)
{
sql += "`" + p.Name + "`=" + _table.GetValueInSqlFormat(p) + ",";
}
sql = sql.Substring(0, sql.Length - 1) + " WHERE RecId=" + _table.RecId + ";";
command.CommandText = sql;
command.ExecuteNonQuery();
}
internal void GetFieldsToChange(ECTable _table, ref Dictionary <string, string> _fieldsToAdd,
ref Dictionary <string, string> _fieldsToDelete,
ref Dictionary <string, string> _databaseFields)
{
Type t = _table.GetType();
Dictionary <string, string> tableProperties = new Dictionary <string, string>();
foreach (PropertyInfo p in _table.GetType().GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute))))
{
ECTableFieldAttribute tfa = (ECTableFieldAttribute)p.GetCustomAttribute(typeof(ECTableFieldAttribute));
if (tfa.type == FieldType.VARCHAR)
{
tableProperties.Add(p.Name, FieldTypeToSqlType(tfa.type) + "(" + tfa.length.ToString() + ")");
}
else
{
tableProperties.Add(p.Name, FieldTypeToSqlType(tfa.type));
}
}
command.Parameters.Clear();
command.CommandText = "PRAGMA table_info(" + t.Name + ");";
SQLiteDataReader res = command.ExecuteReader();
while (res.Read())
{
_databaseFields.Add(res["name"].ToString(), res["type"].ToString().Replace(" ", ""));
}
foreach (KeyValuePair <string, string> kv in tableProperties)
{
if (!_databaseFields.Contains(kv))
{
_fieldsToAdd.Add(kv.Key, kv.Value);
}
}
foreach (KeyValuePair <string, string> kv in _databaseFields)
{
if (!tableProperties.Contains(kv))
{
_fieldsToDelete.Add(kv.Key, kv.Value);
}
}
res.Close();
}
public void AlterTableFields(ECTable _table)
{
Type t = _table.GetType();
Dictionary <string, string> fieldsToAdd = new Dictionary <string, string>();
Dictionary <string, string> fieldsToDelete = new Dictionary <string, string>();
Dictionary <string, string> databaseFields = new Dictionary <string, string>();
GetFieldsToChange(_table, ref fieldsToAdd, ref fieldsToDelete, ref databaseFields);
//Add Fields
foreach (KeyValuePair <string, string> kv in fieldsToAdd)
{
PropertyInfo p = t.GetProperty(kv.Key);
string sql = "ALTER TABLE `" + t.Name + "` ADD `" + kv.Key + "` " + kv.Value;
if (p.GetCustomAttribute(typeof(ECPrimaryKeyAttribute)) != null)
{
sql += " NOT NULL PRIMARY KEY";
}
else if (p.GetCustomAttribute(typeof(ECNotNullAttribute)) != null)
{
sql += " NOT NULL";
}
else
{
sql += " NULL";
}
if (p.GetCustomAttribute(typeof(ECAutoIncrementAttribute)) != null)
{
sql += " AUTOINCREMENT";
}
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
cmd.ExecuteNonQuery();
}
//Delete Fields
foreach (KeyValuePair <string, string> kv in fieldsToDelete)
{
string sql = "ALTER TABLE `" + t.Name + "` DROP COLUMN `" + kv.Key + "`";
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
cmd.ExecuteNonQuery();
}
}
public List <Dictionary <string, string> > GetData(ECTable _table, Dictionary <string, string> _filter, Dictionary <string, KeyValuePair <string, string> > _ranges, List <string> _order)
{
Type t = _table.GetType();
Dictionary <string, string> parms = new Dictionary <string, string>();
command = new SQLiteCommand(connection);
string sql = SqlBuilder.GenerateSqlForECTableWithPreparedStatements(_table, ref parms);
command.Parameters.Clear();
foreach (KeyValuePair <string, string> kv in parms)
{
command.Parameters.AddWithValue(kv.Key, kv.Value);
}
command.CommandText = sql;
command.Prepare();
return(ExecuteSql());
}
virtual protected string CreateParameterizedWhereClause(ECTable _table, ref Dictionary <string, string> _parameters)
{
string sql = "";
List <string> where = new List <string>();
CreateParameterizedWhereClause(_table, ref where, ref _parameters);
if (where.Count != 0)
{
sql += " WHERE ";
foreach (string s in where)
{
sql += "(" + s + ") AND";
}
sql = sql.Substring(0, sql.Length - 4);
}
return(sql);
}
virtual protected void CreateParameterizedWhereClause(ECTable _table,
ref List <string> _where,
ref Dictionary <string, string> _parameters)
{
foreach (KeyValuePair <string, KeyValuePair <string, string> > kp in _table.Ranges)
{
if (kp.Value.Value.Equals(""))
{
string keyParm = _table.TableName + kp.Key;
_parameters.Add(keyParm, kp.Value.Key);
_where.Add(String.Format("{0}.{1}=@{2}",
GetSqlTableName(_table),
kp.Key,
keyParm));
}
else
{
string keyParm = _table.TableName + kp.Key;
_parameters.Add($"K{keyParm}", kp.Value.Key);
_parameters.Add($"V{keyParm}", kp.Value.Value);
_where.Add(String.Format("({0}.{1} BETWEEN @K{2} AND @V{3}",
GetSqlTableName(_table),
kp.Key,
keyParm,
keyParm));
}
}
foreach (KeyValuePair <string, string> kp in _table.Filter)
{
_where.Add(ParseFilterString(_table, kp.Key, kp.Value, ref _parameters));
}
foreach (ECJoin j in _table.Joins)
{
ECTable joinTable = (ECTable)j.Table;
CreateParameterizedWhereClause(joinTable, ref _where, ref _parameters);
}
}
public int Insert(ECTable _table)
{
Type t = _table.GetType();
PropertyInfo[] properties = t.GetProperties().Where(x => x.IsDefined(typeof(ECTableFieldAttribute), false)).ToArray();
command.Parameters.Clear();
string sql = "INSERT INTO `" + t.Name + "` (";
foreach (PropertyInfo p in properties.Where(x => !x.IsDefined(typeof(ECAutoIncrementAttribute), false)))
{
sql += "`" + p.Name + "`,";
}
sql = sql.Substring(0, sql.Length - 1); //delete last comma
sql += ") VALUES (";
foreach (PropertyInfo p in properties.Where(x => !x.IsDefined(typeof(ECAutoIncrementAttribute), false)))
{
command.Parameters.AddWithValue(p.Name, p.GetValue(_table));
sql += "@" + p.Name + ",";
}
sql = sql.Substring(0, sql.Length - 1); //delete last comma
sql += ");";
command.CommandText = sql;
command.Prepare();
command.ExecuteNonQuery();
sql = "SELECT MAX(RecId) AS RecId FROM " + _table.GetType().Name;
command.CommandText = sql;
using (SQLiteDataReader r = command.ExecuteReader())
{
r.Read();
return(r.GetInt32(r.GetOrdinal(nameof(_table.RecId))));
}
}
public static void SynchronizeSchema(ECTable _table)
{
Connection.CreateTableIfNotExist(_table);
Connection.AlterTableFields(_table);
}
virtual protected string ParseFilterString(ECTable _table,
string _fieldName,
string _filter,
ref Dictionary <string, string> _parameter)
{
string[] val = { "", "" };
int valId = 0;
bool foundPoint = false;
string clause = $"( {GetSqlTableName(_table)}.{_fieldName}";
string operators = "<>=";
for (int i = 0; i < _filter.Length; i++)
{
switch (_filter[i])
{
case '<':
if (!foundPoint)
{
clause += '<';
}
else
{
clause += ProcessFromToOperator(_table, i, val[valId % 2], val[valId + 1 % 2],
_filter[i - 1], ref _parameter);
foundPoint = false;
val[valId + 1 % 2] = "";
}
break;
case '>':
if (!foundPoint)
{
clause += '>';
}
else
{
clause += ProcessFromToOperator(_table, i, val[valId % 2], val[valId + 1 % 2],
_filter[i - 1], ref _parameter);
foundPoint = false;
val[valId + 1 % 2] = "";
}
break;
case '=':
if (!foundPoint)
{
clause += '=';
}
else
{
clause += ProcessFromToOperator(_table, i, val[valId % 2], val[valId + 1 % 2],
_filter[i - 1], ref _parameter);
foundPoint = false;
val[valId + 1 % 2] = "";
}
break;
case '|':
if (!foundPoint)
{
if (!operators.Contains(clause.Last()))
{
clause += "=";
}
clause += String.Format("@F{0}{1} OR {2}.{3}",
_table.TableName,
i,
GetSqlTableName(_table),
_fieldName);
_parameter.Add("F" + _table.TableName + i, val[valId % 2]);
}
else
{
clause += ProcessFromToOperator(_table, i, val[valId % 2], val[valId + 1 % 2],
_filter[i - 1], ref _parameter);
foundPoint = false;
val[valId + 1 % 2] = "";
}
val[valId % 2] = "";
break;
case '&':
if (!foundPoint)
{
if (!operators.Contains(clause.Last()))
{
clause += "=";
}
clause += String.Format("@F{0}{1} AND {2}",
_table.TableName,
i,
_fieldName);
_parameter.Add($"F{i}", val[valId % 2]);
}
else
{
clause += ProcessFromToOperator(_table, i, val[valId % 2], val[valId + 1 % 2],
_filter[i - 1], ref _parameter);
foundPoint = false;
val[valId + 1 % 2] = "";
}
val[valId % 2] = "";
break;
case '.':
if (foundPoint) //found second . => switch to second value storage
{
valId++;
}
else //found first . => remember for next loop (we're now in another State)
{
foundPoint = true;
}
break;
default:
val[valId % 2] += _filter[i];
break;
}
}
if (foundPoint) // we're at the end of the line and still havent processed the .'s. That Means we have sth. like "1..5" or "1.." or "..5"
{
clause += ProcessFromToOperator(_table, _filter.Length, val[valId % 2], val[(valId + 1) % 2],
_filter[_filter.Length - 1], ref _parameter);
}
else
{
if (!operators.Contains(clause.Last()))
{
clause += "=";
}
clause += "@F" + _table.TableName + _filter.Length;
_parameter.Add("F" + _table.TableName + _filter.Length, val[valId % 2]);
}
return(clause + ")");
}
virtual protected string GetSqlTableName(ECTable _table)
{
return(_table.SqlTableName);
}
