/// <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> /// 更新一条数据 /// </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> /// 根据表达式生成sql语句 /// </summary> /// <typeparam name="T">表实体类</typeparam> /// <param name="filter">查询条件:lambda条件过滤表达式</param> /// <param name="dml">数据操作:select * from {0};delete {0} </param> /// <returns></returns> protected static string CreateSqlByExpression <T>(Expression <Func <T, bool> > filter, string dml) { List <SqlParameter> parameters = new List <SqlParameter>(); StringBuilder strSql = new StringBuilder(); string strWhere = string.Empty; strWhere = LinqCompile.GetWhereByLambda(filter, DataBaseType.SqlServer); var model = Activator.CreateInstance <T>(); Type t = model.GetType(); //获得该类的Type string tbName = GetTableName(t); //表名 strSql.AppendFormat(dml, tbName); if (!string.IsNullOrEmpty(strWhere)) { strSql.AppendFormat(" where {0}", strWhere); } return(strSql.ToString()); }