//过滤器
public static List <T> Filter <T>(EntityFilters filters, bool getForign, bool getChild) where T : IBaseEntity
{
if (filters.Count == 0)
{
return(GetAllEntities <T>());
}
else
{
var comm = GetFilterCommand <T>(filters);
if (comm == null)
{
return(null);
}
var list = new List <T>();
using (var conn = new SqlConnection(ConnectionString))
{
comm.Connection = conn;
conn.Open();
using (SqlDataReader dr = comm.ExecuteReader())
{
while (dr.Read())
{
list.Add(GetEntityFromDataReader <T>(dr, getForign, getChild));
}
}
}
return(list);
}
}
//过滤器
public static List <T> Filter <T>(EntityFilters filters) where T : IBaseEntity
{
return(Filter <T>(filters, false, false));
}
//SQL命令 获取过滤命令
public static SqlCommand GetFilterCommand <T>(EntityFilters filters) where T : IBaseEntity
{
var tp = typeof(T);
string tbName;
var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false);
if (attrs.Length > 0)
{
tbName = ((EntityTableNameAttribute)attrs[0]).TableName;
}
else
{
return(null);
}
var sb = new StringBuilder();
sb.AppendFormat("SELECT * FROM [{0}] WHERE {1} ", tbName, filters.And ? "1=1" : "1=2");
var comm = new SqlCommand();
foreach (PropertyInfo prop in tp.GetProperties())
{
var filter = filters.Get(prop.Name);
if (filter != null && filter.Value != null)
{
var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute;
var paramName = string.Format("@{0}", prop.Name);
string op = " = " + paramName;
switch (filter.Operator)
{
case FilterOperator.Equal:
op = " = " + paramName;
break;
case FilterOperator.EqualOrLess:
op = " <= " + paramName;
break;
case FilterOperator.EqualOrGreater:
op = " >= " + paramName;
break;
case FilterOperator.Like:
op = string.Format("LIKE '%'+{0}+'%'", paramName);
break;
case FilterOperator.StartWith:
op = string.Format("LIKE {0}+'%'", paramName);
break;
case FilterOperator.EndWith:
op = string.Format("LIKE '%'+{0}", paramName);
break;
}
if (eca != null && !string.IsNullOrEmpty(eca.Field))
{
sb.AppendFormat(" {0} [{1}] {2}", filters.And ? "AND" : "OR", eca.Field, op);
var param = new SqlParameter(paramName, eca.DataType)
{
Value = filter.Value
};
comm.Parameters.Add(param);
}
}
}
comm.CommandText = sb.ToString();
comm.CommandType = CommandType.Text;
return(comm);
}
