/// <summary> /// 更新一条数据 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="model">数据实体</param> /// <param name="filter">过滤条件</param> /// <param name="fields">更新字段</param> /// <returns></returns> public static int Update <T>(T model, Expression <Func <T, bool> > filter, params Expression <Func <T, object> >[] fields) { string strWhere = string.Empty; List <string> strFields = new List <string>(); if (filter != null) { strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer); } if (fields != null) { foreach (var f in fields) { var fieldName = ExpressionField.GetFieldName <T>(f); strFields.Add(fieldName); } } List <SqlParameter> parameters = new List <SqlParameter>(); Type t = model.GetType(); //获得该类的Type StringBuilder strSql = new StringBuilder(); string columns = string.Empty; //字段名 string tbName = GetTableName(t); //表名 strSql.AppendFormat("update {0} set ", tbName); foreach (PropertyInfo pi in t.GetProperties()) { ModelAttribute attr = (ModelAttribute)Attribute.GetCustomAttribute(pi, typeof(ModelAttribute)); // 属性值 if (attr != null && !attr.IsIdentity) //跳过更新主键字段 { if (strFields.Count == 0 || strFields.Contains(attr.ColumnName)) { object value = pi.GetValue(model, null); if (attr.ColumnType == ColumnType.datetimeType && (null == value || (System.DateTime)value == default(System.DateTime))) { //空时间类型或空值,不写入数据库 continue; } //else if(attr.ColumnType == ColumnType.datetimeType) //{ // value = ((System.DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fff"); //} columns = columns + "," + attr.ColumnName + "= @" + attr.ColumnName; parameters.Add(CreateSqlParameter(attr.ColumnName, value, attr.ColumnType, attr.Size)); } } } strSql.Append(columns.Trim(new char[] { ',' })); strSql.AppendFormat(" where {0}", strWhere); int result = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters.ToArray()); return(result); }
/// <summary> /// 根据条件查询 /// </summary> /// <typeparam name="T">表实体类</typeparam> /// <param name="filter">查询条件:lambda条件过滤表达式</param> /// <param name="order">排序表达式</param> /// <param name="fields">查询字段:lambda字段表达式【可多组】</param> /// <returns>列表实体</returns> public static List <T> Select <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, params Expression <Func <T, object> >[] fields) { string strWhere = string.Empty; string strFields = string.Empty; string orderBy = string.Empty; if (filter != null) { strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer); } if (order != null) { orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList()); } foreach (var f in fields) { var fieldName = ExpressionField.GetFieldName <T>(f); strFields = strFields + "," + fieldName; } if (string.IsNullOrEmpty(strFields)) { strFields = "*"; } else { strFields = strFields.Trim(','); } List <T> list = new List <T>(); StringBuilder strSql = new StringBuilder(); var model = Activator.CreateInstance <T>(); Type t = model.GetType(); //获得该类的Type string tbName = GetTableName(t); //表名 strSql.AppendFormat("select {0} ", strFields); strSql.AppendFormat(" from {0} ", tbName); if (!string.IsNullOrEmpty(strWhere)) { strSql.AppendFormat(" where {0} ", strWhere); } if (!string.IsNullOrEmpty(orderBy)) { strSql.Append(orderBy); } var dt = DbHelperSQL.Query(strSql.ToString(), null).Tables[0]; list = ModelUtil.DataTableParse <T>(dt); return(list); }
/// <summary> /// 多表联合查询(left join) /// </summary> /// <typeparam name="T">返回的数据实体类型</typeparam> /// <typeparam name="T1">表1</typeparam> /// <typeparam name="T2">表2</typeparam> /// <param name="joinExpression">联接条件</param> /// <param name="filter">查询条件</param> /// <param name="order">排序</param> /// <param name="fields">查询字段</param> /// <returns>数据实体列表</returns> protected static string JoinScript <T, T1, T2>(Expression <Func <T1, T2, bool> > joinExpression, Expression <Func <T1, T2, bool> > filter = null, OrderBy <T> order = null, params Expression <Func <T1, T2, object> >[] fields) { string join = string.Empty; string strWhere = string.Empty; string strFields = string.Empty; string orderBy = string.Empty; join = LinqCompileExt.GetJoinByLambda(joinExpression, DataBaseType.SqlServer); if (filter != null) { strWhere = LinqCompileExt.GetJoinByLambda(filter, DataBaseType.SqlServer); } if (order != null) { orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList()); } foreach (var f in fields) { var fieldName = ExpressionField.GetFieldName <T1, T2>(f); strFields = strFields + "," + fieldName; } if (string.IsNullOrEmpty(strFields)) { strFields = "*"; } else { strFields = strFields.Trim(','); } StringBuilder strSql = new StringBuilder(); var model = Activator.CreateInstance <T>(); Type[] agrs = new Type[] { typeof(T1), typeof(T2) }; //获得该类的Type string tbName = JoinTable(joinExpression.Parameters, agrs); //表名 strSql.AppendFormat("select {0} ", strFields); strSql.AppendFormat(" from {0} ", tbName); strSql.AppendFormat(" on {0}", join); if (!string.IsNullOrEmpty(strWhere)) { strSql.AppendFormat(" where {0} ", strWhere); } if (!string.IsNullOrEmpty(orderBy)) { strSql.Append(orderBy); } return(strSql.ToString()); }
/// <summary> /// 更新一条数据 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="model">数据实体</param> /// <param name="filter">过滤条件</param> /// <param name="fields">更新字段</param> /// <returns></returns> public static int Update <T>(T model, Expression <Func <T, bool> > filter, params Expression <Func <T, object> >[] fields) { string strWhere = string.Empty; List <string> strFields = new List <string>(); if (filter != null) { strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer); } foreach (var f in fields) { var fieldName = ExpressionField.GetFieldName <T>(f); strFields.Add(fieldName); } List <SqlParameter> parameters = new List <SqlParameter>(); Type t = model.GetType(); //获得该类的Type StringBuilder strSql = new StringBuilder(); string columns = string.Empty; //字段名 string tbName = GetTableName(t); //表名 strSql.AppendFormat("update {0} set ", tbName); foreach (PropertyInfo pi in t.GetProperties()) { ModelAttribute attr = (ModelAttribute)Attribute.GetCustomAttribute(pi, typeof(ModelAttribute));// 属性值 if (attr != null) { if (strFields.Contains(attr.ColumnName)) { object value = pi.GetValue(model, null); columns = columns + "," + attr.ColumnName + "= @" + attr.ColumnName; parameters.Add(CreateSqlParameter(attr.ColumnName, value, attr.ColumnType, attr.Size)); } } } strSql.Append(columns.Trim(new char[] { ',' })); strSql.AppendFormat(" where {0}", strWhere); int result = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters.ToArray()); return(result); }
/// <summary> /// 查询当前指定字段的值 /// </summary> /// <typeparam name="T">表实体类</typeparam> /// <param name="filter">查询条件:lambda条件过滤表达式</param> /// <param name="order">排序表达式</param> /// <param name="field">查询字段:lambda字段表达式</param> /// <returns>返回当前行的查询字段值</returns> public static object GetValue <T>(Expression <Func <T, bool> > filter = null, OrderBy <T> order = null, Expression <Func <T, object> > field = null) { string strWhere = string.Empty; string strFields = string.Empty; string orderBy = string.Empty; if (field == null) { throw new Exception("请指定查询字段"); //return null; } if (filter != null) { strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer); } if (order != null) { orderBy = OrderByUtil.GetOrderBy <T>(order.GetOrderByList()); } strFields = ExpressionField.GetFieldName <T>(field); List <T> list = new List <T>(); StringBuilder strSql = new StringBuilder(); var model = Activator.CreateInstance <T>(); Type t = model.GetType(); //获得该类的Type string tbName = GetTableName(t); //表名 strSql.AppendFormat("select {0} ", strFields); strSql.AppendFormat(" from {0} ", tbName); if (!string.IsNullOrEmpty(strWhere)) { strSql.AppendFormat(" where {0} ", strWhere); } if (!string.IsNullOrEmpty(orderBy)) { strSql.Append(orderBy); } return(DbHelperSQL.GetSingle(strSql.ToString())); }
/// <summary> /// 根据表达式获取排序规则 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="orderList">表达式</param> /// <returns></returns> public static string GetOrderBy <T>(Dictionary <Expression <Func <T, object> >, OrderByEnum> orderList) { StringBuilder orderBy = new StringBuilder(); if (orderList != null) { foreach (KeyValuePair <Expression <Func <T, object> >, OrderByEnum> keyValue in orderList) { if (orderBy.Length == 0) { orderBy.Append(" order by "); } string fieldName = ExpressionField.GetFieldName <T>(keyValue.Key); orderBy.AppendFormat(" {0} {1}", fieldName, keyValue.Value.ToString()); orderBy.Append(","); } } return(orderBy.ToString().TrimEnd(',')); }