public void Delete(object obj, string tableName = null)
{
SQLTableMap map = getMap(obj.GetType());
if (map.PrimaryKey == null)
{
throw new Exception("Cannot delete object that doesn't have a Primary key to delete on.");
}
string query = $@"DELETE FROM [{(tableName != null ? tableName : map.TableName)}] WHERE [{map.PrimaryKey.SQLColName}] = {turnToSQLVal(obj.getProperty(map.PrimaryKey.CSProp.Name))}";
}
private SQLTableMap getMap(Type type)
{
SQLTableMap outMap;
if (!objectSQLmap.ContainsKey(type))
{
outMap = SQLTableMap.GetSQLMap(type);
objectSQLmap[type] = outMap;
}
outMap = objectSQLmap[type];
return(outMap);
}
public IEnumerable <T> select <T>(string tableName = null)
{
SQLTableMap map = getMap(typeof(T));
List <string> lines = new List <string>();
List <SQLTableMap.SQLTableProp> tableProps = new List <SQLTableMap.SQLTableProp>();
foreach (var att in map.Cols)
{
if (att.GetType() != typeof(SQLTableMap.SQLTableProp))
{
lines.Add($"[{att.SQLColName}] {att.CSProp.Name}");
}
else
{
tableProps.Add(att as SQLTableMap.SQLTableProp);
}
}
string query = $@"
SELECT
{ListToString(", ", lines)}
FROM
[{(tableName != null ? tableName : map.TableName)}]";
var result = Connection.Query <T>(query);
//List<IEnumerable<object>> childTables = new List<IEnumerable<object>>();
foreach (var tp in tableProps)
{
Type t = tp.type;
//var childTable = select<object>(tp.Table.TableName);
var prop = tp.CSProp;
foreach (var i in result)
{
//link on what?
//prop.SetValue(i, childTable.Where(x => tp.LinkOn.CSProp.GetValue(x) == tp.Table.p));
//i.Property(tp.CSPropName).SetValue();
}
}
return(result);
}
public void Update(object obj, string tableName = null)
{
SQLTableMap map = getMap(obj.GetType());
List <string> lines = new List <string>();
foreach (var att in map.Cols)
{
lines.Add("[" + att.SQLColName + "] = " + turnToSQLVal(obj.getProperty(att.CSProp.Name)));
}
string query = $@"
UPDATE [{(tableName != null? tableName : map.TableName)}]
SET {ListToString(", ", lines)}
WHERE [{map.PrimaryKey.SQLColName}] = {turnToSQLVal(obj.getProperty(map.PrimaryKey.CSProp.Name))}
";
Connection.Execute(query);
}
public T selectFirst <T>(string tableName = null)
{
SQLTableMap map = getMap(typeof(T));
List <string> lines = new List <string>();
foreach (var att in map.Cols)
{
lines.Add($"[{att.SQLColName}] {att.CSProp.Name}");
}
string query = $@"
SELECT
{ListToString(", ",lines)}
FROM
[{(tableName != null ? tableName : map.TableName)}]";
return(Connection.QueryFirst <T>(query));
}
public void Insert(object obj)
{
SQLTableMap map = getMap(obj.GetType());
List <string> cols = new List <string>();
List <string> vals = new List <string>();
foreach (var att in map.Cols)
{
cols.Add("[" + att.SQLColName + "]");
vals.Add(turnToSQLVal(obj.getProperty(att.CSProp.Name)));
}
string query = $@"
INSERT INTO [{map.TableName}]
({ListToString(", ", cols)})
VALUES ({ListToString(", ", vals)})
";
Connection.Execute(query);
}