private TResult ExecuteAllFunc <TResult>(MethodCallExpression methodCall)
{
int countOrigin = 0;
int countAll = 0;
var lambda = (methodCall.Arguments[1] as UnaryExpression).Operand as LambdaExpression;
WhereExpressionResolver <T> whereResolver = new WhereExpressionResolver <T>();
whereResolver.Resolve(lambda);
if (methodCall.Arguments[0] is MethodCallExpression)
{
ExpressionResolverManager <T> manager = new ExpressionResolverManager <T>(methodCall.Arguments[0] as MethodCallExpression).Resolve();
var sqlAll = $"SELECT Count(*) FROM {typeof(T).TableName()} WHERE {$"{whereResolver.SQL} AND {manager.WhereResolver.SQL}"} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL}";
var parameters = whereResolver.SqlParameters.Concat(manager.WhereResolver.SqlParameters);
countAll = (int)DatabaseOperator.ExecuteScalar(DatabaseOperator.connection, sqlAll, parameters.ToArray());
var sqlOrigin = $"SELECT Count(*) FROM {typeof(T).TableName()} WHERE {manager.WhereResolver.SQL} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL}";
countOrigin = (int)DatabaseOperator.ExecuteScalar(DatabaseOperator.connection, sqlOrigin, manager.WhereResolver.SqlParameters);
}
else if (methodCall.Arguments[0] is ConstantExpression)
{
var sqlAll = $"SELECT Count(*) FROM {typeof(T).TableName()} WHERE {whereResolver.SQL}";
countAll = (int)DatabaseOperator.ExecuteScalar(DatabaseOperator.connection, sqlAll, whereResolver.SqlParameters);
var sqlOrigin = $"SELECT Count(*) FROM {typeof(T).TableName()}";
countOrigin = (int)DatabaseOperator.ExecuteScalar(DatabaseOperator.connection, sqlOrigin, whereResolver.SqlParameters);
}
return((TResult)(object)(countAll == countOrigin));
}
private static int UpdateByExpression <T>(TempEntry <T> entry)
{
string paramPrefix = "p_";
List <string> properties = entry.ModifiedProperties.Any() ? entry.ModifiedProperties : typeof(T).GetProperties().Select(p => p.Name).ToList();
if (properties == null || !properties.Any())
{
throw new Exception("No update fields.");
}
List <string> columnList = new List <string>();
List <SqlParameter> paramList = new List <SqlParameter>();
FormatSqlParmeter <T>(entry.Data, properties, ref columnList, ref paramList, paramPrefix);
// Where表达式解析器
WhereExpressionResolver <T> whereResolver = new WhereExpressionResolver <T>();
whereResolver.Resolve(entry.UpdateCondition as LambdaExpression);
string sql = $"UPDATE {typeof(T).TableName()} SET {string.Join(",", columnList.Select(c => $"{c} = @{paramPrefix}{c}"))} WHERE {whereResolver.SQL}";
var parameters = paramList.Concat(whereResolver.SqlParameters.ToList());
return(DatabaseOperator.ExecuteNonQuery(DatabaseOperator.connection, sql, parameters.ToArray()));
}
private TResult ExecuteFirstFunc <TResult>(MethodCallExpression methodCall)
{
List <T> data = null;
if (methodCall.Arguments.Count == 2)
{
var lambda = (methodCall.Arguments[1] as UnaryExpression).Operand as LambdaExpression;
SelectExpressionResolver <T> selectResolver = new SelectExpressionResolver <T>();
WhereExpressionResolver <T> whereResolver = new WhereExpressionResolver <T>();
whereResolver.Resolve(lambda);
if (methodCall.Arguments[0] is MethodCallExpression)
{
ExpressionResolverManager <T> manager = new ExpressionResolverManager <T>(methodCall.Arguments[0] as MethodCallExpression).Resolve();
var sql = $"SELECT TOP 1 {selectResolver.SQL} FROM {typeof(T).TableName()} WHERE {$"{whereResolver.SQL} AND {manager.WhereResolver.SQL}"} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL}";
var parameters = whereResolver.SqlParameters.Concat(manager.WhereResolver.SqlParameters);
data = DatabaseOperator.ExecuteReader <T>(DatabaseOperator.connection, sql, parameters.ToArray());
}
else if (methodCall.Arguments[0] is ConstantExpression)
{
var sql = $"SELECT TOP 1 {selectResolver.SQL} FROM {typeof(T).TableName()} WHERE {whereResolver.SQL}";
data = DatabaseOperator.ExecuteReader <T>(DatabaseOperator.connection, sql, whereResolver.SqlParameters);
}
}
else if (methodCall.Arguments.Count == 1)
{
var manager = new ExpressionResolverManager <T>(methodCall.Arguments[0] as MethodCallExpression).Resolve();
var sql = $"SELECT TOP 1 {manager.SelectResolver.SQL} FROM {typeof(T).TableName()} WHERE {manager.WhereResolver.SQL} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL}";
data = DatabaseOperator.ExecuteReader <T>(DatabaseOperator.connection, sql, manager.WhereResolver.SqlParameters);
}
var ret = Convert.ChangeType((dynamic)data[0], typeof(T));
return((TResult)(object)ret);
}
public static int Execute <T>(List <IQueryable <T> > queryables)
{
foreach (var queryable in queryables)
{
var expression = queryable.Expression;
// Where表达式解析器
WhereExpressionResolver <T> whereResolver = new WhereExpressionResolver <T>();
MethodCallExpression methodCall = expression as MethodCallExpression;
while (methodCall != null)
{
var method = methodCall.Arguments[0];
var lambda = methodCall.Arguments[1];
var right = (lambda as UnaryExpression).Operand as LambdaExpression;
whereResolver.Resolve(right);
methodCall = method as MethodCallExpression;
}
string sql = $"DELETE FROM {typeof(T).TableName()} WHERE {whereResolver.SQL}";
return(DatabaseOperator.ExecuteNonQuery(DatabaseOperator.connection, sql, whereResolver.SqlParameters));
}
return(0);
}
private static int BatchInsert <T>(List <T> datas)
{
DataTable dt = new DataTable();
Type type = typeof(T);
string tableName = type.TableName();// 表名（默认取类名）
var tempMap = new Dictionary <string, string>();
Hashtable mappings = new Hashtable();
var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
foreach (var p in properties)
{
var columnName = p.ColumnName();
mappings.Add(columnName, columnName);
dt.Columns.Add(columnName, p.PropertyType);
tempMap.Add(p.Name, columnName);
}
foreach (var data in datas)
{
DataRow dr = dt.NewRow();
foreach (var key in tempMap.Keys)
{
dr[tempMap[key]] = data.GetType().GetProperty(key).GetValue(data);
}
dt.Rows.Add(dr);
}
return(DatabaseOperator.ExecuteBulkCopy(DatabaseOperator.connection, tableName, dt, mappings));
}
private TResult ExecuteNoneFunc <TResult>()
{
SelectExpressionResolver <T> selectResolver = new SelectExpressionResolver <T>();
string sql = $"SELECT {selectResolver.SQL} FROM {typeof(T).TableName()}";
var data = DatabaseOperator.ExecuteReader <T>(DatabaseOperator.connection, sql);
return((TResult)(object)data);
}
private static int SingleInsert <T>(T data)
{
string tableName = typeof(T).TableName(); // 表名（默认取类名）
List <string> columnList = new List <string>(); // 列名列表
List <SqlParameter> paramList = new List <SqlParameter>(); // 参数列表
FormatSqlParmeter <T>(data, ref columnList, ref paramList);
string sqlText = $"INSERT INTO {tableName}({string.Join(",", columnList.Select(c => $"[{c}]"))})VALUES({string.Join(",", columnList.Select(c => $"@{c}"))})";
return(DatabaseOperator.ExecuteNonQuery(DatabaseOperator.connection, sqlText, paramList.ToArray()));
}
private TResult ExecuteCoreQuery <TResult>(MethodCallExpression methodCall)
{
string sql = string.Empty;
var manager = new ExpressionResolverManager <T>(methodCall).Resolve();
if (manager.SkipResolver.Skip > 0 && manager.TakeResolver.Take > 0)
{
sql = $"SELECT {manager.SelectResolver.SQL} FROM {typeof(T).TableName()} WHERE {manager.WhereResolver.SQL} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL} OFFSET {manager.SkipResolver.Skip} ROWS FETCH NEXT {manager.TakeResolver.Take} ROWS ONLY";
}
else if (manager.SkipResolver.Skip > 0 && manager.TakeResolver.Take == 0)
{
sql = $"SELECT {manager.SelectResolver.SQL} FROM {typeof(T).TableName()} WHERE {manager.WhereResolver.SQL} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL} {manager.SkipResolver.SQL}";
}
else if (manager.SkipResolver.Skip == 0 && manager.TakeResolver.Take > 0)
{
sql = $"SELECT {manager.TakeResolver.SQL} {manager.SelectResolver.SQL} FROM {typeof(T).TableName()} WHERE {manager.WhereResolver.SQL} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL}";
}
else
{
sql = $"SELECT {manager.SelectResolver.SQL} FROM {typeof(T).TableName()} WHERE {manager.WhereResolver.SQL} {manager.GroupByResolver.SQL} {manager.OrderByResolver.SQL}";
}
var data = DatabaseOperator.ExecuteReader <T>(DatabaseOperator.connection, sql, manager.WhereResolver.SqlParameters);
// 处理Select语句返回匿名对象
if (manager.SelectResolver.Expression != null)
{
var selctQuery = data.AsQueryable().Provider.CreateQuery(
Expression.Call(typeof(Queryable),
"Select",
new Type[] {
data.AsQueryable().ElementType,
manager.SelectResolver.Expression.Body.Type
},
data.AsQueryable().Expression,
Expression.Quote(manager.SelectResolver.Expression)));
List <object> list = new List <object>();
foreach (var item in selctQuery)
{
list.Add(Convert.ChangeType(item, manager.SelectResolver.Expression.ReturnType));
}
return((TResult)(object)list);
}
return((TResult)(object)data);
}
private static int UpdateByPrimaryKey <T>(TempEntry <T> entry)
{
var primaryKeyProp = entry.Data.PrimaryKey();
if (primaryKeyProp == null)
{
throw new Exception("No primary key.");
}
List <string> properties = typeof(T).GetProperties().Where(p => !string.Equals(p.Name, primaryKeyProp.Name)).Select(p => p.Name).ToList();
if (properties == null || !properties.Any())
{
throw new Exception("No update fields.");
}
List <string> columnList = new List <string>();
List <SqlParameter> paramList = new List <SqlParameter>();
FormatSqlParmeter <T>(entry.Data, properties, ref columnList, ref paramList, "");
string primaryKeyColumn = primaryKeyProp.ColumnName();
string sql = $"UPDATE {typeof(T).TableName()} SET {string.Join(",", columnList.Select(c => $"{c} = @{c}"))} WHERE {primaryKeyColumn} = @{primaryKeyColumn}";
SqlParameter parameter = new SqlParameter($"{primaryKeyColumn}", primaryKeyProp.GetValue(entry.Data) ?? DBNull.Value);
var dataTypeAttr = primaryKeyProp.Attr <DataTypeAttribute>();
parameter.SqlDbType = dataTypeAttr != null ? dataTypeAttr.DbType : Util.GetSqlDataType(primaryKeyProp);
var stringLengthAttr = primaryKeyProp.Attr <StringLengthAttribute>();
if (stringLengthAttr != null)
{
parameter.Size = stringLengthAttr.Length;
}
paramList.Add(parameter);
return(DatabaseOperator.ExecuteNonQuery(DatabaseOperator.connection, sql, paramList.ToArray()));
}