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