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