/// <summary>
/// Create entity - used for tables don't have primary key
/// </summary>
public static bool Add <T>(IDbConnection db, T entity)
{
List <string> rootFields = new List <string>();
PropertyInfo[] pinfos = entity.GetType().GetProperties();
foreach (PropertyInfo prop in pinfos)
{
object[] attrs = prop.GetCustomAttributes(true);
bool isExternal = attrs.Any(a => (a as External) != null);
if (isExternal)
{
continue;
}
rootFields.Add(prop.Name);
}
List <string> dapperFields = new List <string>();
rootFields.ForEach(f => { dapperFields.Add("@" + f); });
string tableName = EXTable.GetTableName <T>();
StringBuilder query = new StringBuilder();
query.AppendLine("INSERT INTO " + tableName + " (");
query.AppendLine(string.Join(",", rootFields) + ")");
query.AppendLine("VALUES(" + string.Join(",", dapperFields) + ")");
string sqlQuery = query.ToString();
return(1 == db.Execute(sqlQuery, entity));
}
//Get all related object
//public static decimal? Min(this IEnumerable<decimal?> source);
//public static decimal? Sum<TSource>(this IEnumerable<TSource> source, Func<TSource, int> selector);
//public static decimal? Max<TSource>(this IEnumerable<TSource> source, Func<TSource, decimal?> selector);
#endregion
#region Update by Id //TODO: Later, can use expression to get conditon for updating
/// <summary>
/// Update entity
/// </summary>
public static bool Update <T>(IDbConnection db, T entity)
{
List <string> rootFields = new List <string>();
PropertyInfo[] pinfos = entity.GetType().GetProperties();
foreach (PropertyInfo prop in pinfos)
{
object[] attrs = prop.GetCustomAttributes(true);
if (attrs.Any(a => (a as External) != null || (a as PrimaryKey) != null))
{
continue;
}
rootFields.Add(prop.Name + " = @" + prop.Name);
}
StringBuilder query = new StringBuilder();
query.AppendLine("UPDATE " + EXTable.GetTableName <T>());
query.AppendLine("SET");
query.AppendLine(string.Join(",", rootFields));
query.AppendLine("WHERE");
string primaryKey = GetPrimaryIdColumn(typeof(T));
query.AppendLine(primaryKey + " = @" + primaryKey);
string sqlQuery = query.ToString();
return(1 == db.Execute(sqlQuery, entity));
}
/// <summary>
/// Create entity - used for tables have primary key
/// </summary>
public static int Add <T>(T entity, IDbConnection db)
{
List <string> rootFields = new List <string>();
PropertyInfo[] pinfos = entity.GetType().GetProperties();
foreach (PropertyInfo prop in pinfos)
{
object[] attrs = prop.GetCustomAttributes(true);
bool isIgnored = attrs.Any(a => (a as External) != null || (a as PrimaryKey) != null);
if (isIgnored)
{
continue;
}
rootFields.Add(prop.Name);
}
List <string> dapperFields = new List <string>();
rootFields.ForEach(f => { dapperFields.Add("@" + f); });
string tableName = EXTable.GetTableName <T>();
StringBuilder query = new StringBuilder();
query.AppendLine("INSERT INTO " + tableName + " (");
query.AppendLine(string.Join(",", rootFields) + ")");
query.AppendLine("Output Inserted." + GetPrimaryIdColumn(typeof(T)));
query.AppendLine("VALUES(" + string.Join(",", dapperFields) + ")");
string sqlQuery = query.ToString();
return(db.Query <int>(sqlQuery, entity).FirstOrDefault());
}
/// <summary>
/// "Any" keyword is used without expression
/// Way to use:
/// Condition[Table].Any()
/// </summary>
public static bool Any <T>(IDbConnection db)
{
SQlQuery sql = new SQlQuery(EXTable.GetTableName <T>());
string newSql = @"SELECT CASE WHEN ( EXISTS ( " + sql.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
var result = db.Query <bool>(newSql).FirstOrDefault();
return(result);
}
/// <summary>
/// "Count" keyword is used without expression --
/// Way to use: [Table].Count()
/// </summary>
public static int Count <T>(IDbConnection db)
{
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
var result = db.Query <int>(newSql).FirstOrDefault();
return(result);
}
public static QueryResult <T> Get <T>(Expression <Func <T, bool> > expression)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
WalkTree <T>(expression, ref query);
return(new QueryResult <T>(query));
}
/// <summary>
/// "Any" keyword is used with expression
/// Way to use:
/// Any[Table](x=> x.id = 10);
/// Find[Table](x=> x.id = 10).Any(x=>x.id = 10);
/// </summary>
public static bool Any <T>(IDbConnection db, Expression <Func <T, bool> > expression)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
WalkTree <T>(expression, ref query);
string newSql = @"SELECT CASE WHEN ( EXISTS ( " + query.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
var result = db.Query <bool>(newSql, (object)query.Param).FirstOrDefault();
return(result);
}
/// <summary>
/// "Count" keyword is used without expression --
/// Way to use: [Table].Count(expression)
/// </summary>
public static int Count <T>(Expression <Func <T, bool> > expression, IDbConnection db)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
WalkTree <T>(expression, ref query);
string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
var result = db.Query <int>(newSql, (object)query.Param).FirstOrDefault();
return(result);
}
/// <summary>
/// Delete entity
/// </summary>
public static bool Delete <T>(int id, IDbConnection db)
{
StringBuilder query = new StringBuilder();
query.AppendLine("DELETE FROM ");
query.AppendLine(EXTable.GetTableName <T>());
query.AppendLine(" WHERE ");
string primaryKey = GetPrimaryIdColumn(typeof(T));
query.AppendLine(primaryKey + " = @" + primaryKey);
string sqlQuery = query.ToString();
return(1 == db.Execute(sqlQuery, new { primaryKey = id }));
}
/// <summary>
/// result[0] = result[1]
/// </summary>
private static string[] GetForeignKeyColumn(Type table1, Type table2)
{
string[] result = new string[2];
PropertyInfo[] table1PInfos = table1.GetProperties();
string table1NameInDb = EXTable.GetTableName(table1);
string table2NameInDb = EXTable.GetTableName(table2);
PropertyInfo pForeignKeyInfosTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true)
.Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table2NameInDb));
if (pForeignKeyInfosTable1 != null)
{
result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name;
result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name;
return(result);
}
else
{
PropertyInfo[] table2PInfos = table2.GetProperties();
PropertyInfo pForeignKeyInfosTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true)
.Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table1NameInDb));
if (pForeignKeyInfosTable2 != null)
{
result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name;
result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name;
return(result);
}
//External fields of table 1
//search id of table 2 in table 1
PropertyInfo primaryKeyTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
if (primaryKeyTable2 == null)
{
primaryKeyTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
}
if (primaryKeyTable2 == null)
{
throw new Exception("Could not found the primary key in entity: " + table1.FullName);
}
PropertyInfo allPExternalTable2InTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable2.Name, true) == 0);
if (allPExternalTable2InTable1 != null) // primary key of table 2 is existed in table 1
{
result[0] = table1NameInDb + '.' + primaryKeyTable2.Name;
result[1] = table2NameInDb + '.' + primaryKeyTable2.Name;
return(result);
}
else
{
//search id of table 1 in table 2
PropertyInfo primaryKeyTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
if (primaryKeyTable1 == null)
{
primaryKeyTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
}
if (primaryKeyTable1 == null)
{
throw new Exception("Could not found the primary key in entity: " + table1.FullName);
}
PropertyInfo pExternalTable1InTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable1.Name, true) == 0);
if (pExternalTable1InTable2 != null)
{
result[0] = table1NameInDb + '.' + primaryKeyTable1.Name;
result[1] = table2NameInDb + '.' + primaryKeyTable1.Name;
return(result);
}
else
{
throw new Exception("Could not found the foreign key between two entities: " + table1.FullName + " and " + table2.FullName);
}
}
}
}
private static void GetJoinOperation(Type rootTableName, string propertyName, ref List <string> joins)
{
string[] elements = propertyName.Split(new char[] { '.' });
string tableName = elements[0];
PropertyInfo[] table2PInfos = rootTableName.GetProperties();
PropertyInfo primaryKeyTable2 = table2PInfos.FirstOrDefault(p => string.Compare(EXTable.GetTableName(p.PropertyType), tableName, true) == 0);
string[] keyJoin = GetForeignKeyColumn(rootTableName, primaryKeyTable2.PropertyType);
StringBuilder joinComment = new StringBuilder();
joinComment.Append("JOIN ");
joinComment.Append(tableName);
joinComment.Append(" ON " + keyJoin[0] + " = " + keyJoin[1]);
joins.Add(joinComment.ToString());
if (elements.Count() > 2)
{
string newPropertyName = elements[1] + "." + elements[2];
Type destinationType = rootTableName.GetProperty(tableName).PropertyType;
GetJoinOperation(destinationType, newPropertyName, ref joins);
}
}
public static QueryResult <T> Get <T>()
{
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
return(new QueryResult <T>(query));
}