public static IEnumerable <T> GetModelFromQuery <T>(
DbContext context,
string sql,
SqlParameter[] parameters)
where T : new()
{
DatabaseFacade databaseFacade = new DatabaseFacade(context);
using (DbDataReader dr = databaseFacade.ExecuteSqlQuery(sql, parameters).DbDataReader)
{
List <T> lst = new List <T>();
PropertyInfo[] props = typeof(T).GetProperties();
while (dr.Read())
{
T t = new T();
IEnumerable <string> actualNames = dr.GetColumnSchema().Select(o => o.ColumnName);
for (int i = 0; i < props.Length; ++i)
{
PropertyInfo pi = props[i];
if (!pi.CanWrite)
{
continue;
}
System.ComponentModel.DataAnnotations.Schema.ColumnAttribute ca = pi.GetCustomAttribute(typeof(System.ComponentModel.DataAnnotations.Schema.ColumnAttribute)) as System.ComponentModel.DataAnnotations.Schema.ColumnAttribute;
string name = ca?.Name ?? pi.Name;
if (pi == null)
{
continue;
}
if (!actualNames.Contains(name))
{
continue;
}
object value = dr[name];
Type pt = pi.DeclaringType;
bool nullable = pt.GetTypeInfo().IsGenericType&& pt.GetGenericTypeDefinition() == typeof(Nullable <>);
if (value == DBNull.Value)
{
value = null;
}
if (value == null && pt.GetTypeInfo().IsValueType&& !nullable)
{
value = Activator.CreateInstance(pt);
}
pi.SetValue(t, value);
} //for i
lst.Add(t);
} //while
return(lst);
} //using dr
}
/// <summary>
/// 根据实体类型获取所有列的查询字符串
/// </summary>
/// <param name="this">实体Type类型</param>
/// <param name="format">是否启用格式化</param>
/// <param name="databaseType">数据库类型</param>
/// <returns></returns>
public static string ToColumns(this Type @this, bool format, DatabaseType databaseType)
{
var properties = @this.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
if (properties.IsNullOrEmpty())
{
return("*");
}
var columns = new StringBuilder();
//遍历属性
foreach (var property in properties)
{
var select = true;
var columnFormat = false;
var propertyName = property.Name;
var columnName = string.Empty;
//获取特性
var attributes = property.GetAttributes(
typeof(KeyAttribute),
typeof(ColumnAttribute),
typeof(SysColumnAttribute));
if (attributes.IsNotNullOrEmpty())
{
foreach (var attribute in attributes)
{
var(name, res, colFormat) = attribute switch
{
KeyAttribute key => (key.Name, true, key.Format),
ColumnAttribute column => (column.Name, !(!column.Update && !column.Insert), column.Format),
SysColumnAttribute sys => (sys.Name, true, false),
_ => (null, true, false)
};
//判断是否要进行查询
if (!res)
{
select = res;
continue;
}
//只匹配第一个name不为空的特性
if (columnName.IsNullOrEmpty() && name.IsNotNullOrEmpty())
{
columnName = name;
}
//判断是否单独启用格式化
if (colFormat)
{
columnFormat = colFormat;
}
}
}
//判断是否要进行查询
if (!select)
{
continue;
}
//全局格式化、单独格式化
if (format || columnFormat)
{
//格式化模板
var template = databaseType switch
{
DatabaseType.Sqlite => "\"{0}\"",
DatabaseType.SqlServer => "[{0}]",
DatabaseType.MySql => "`{0}`",
DatabaseType.Oracle => "\"{0}\"",
DatabaseType.PostgreSql => "\"{0}\"",
_ => "{0}",
};
columns.Append(columnName.IsNullOrEmpty()
? string.Format(template, propertyName)
: (columnName.EqualIgnoreCase(propertyName)
? string.Format(template, columnName)
: $"{string.Format(template, columnName)} AS {string.Format(template, propertyName)}"));
}
//非格式化
else
{
columns.Append(columnName.IsNullOrEmpty()
? propertyName
: (columnName.EqualIgnoreCase(propertyName)
? columnName
: $"{columnName} AS {propertyName}"));
}
columns.Append(",");
}
columns.Remove(columns.Length - 1, 1);
return(columns.ToString());
}
private void ShowAttribute(PropertyInfo attributeTarget, JQGridColumn col)
{
object[] attributes = attributeTarget.GetCustomAttributes(false);
foreach (object attribute in attributes)
{
if (attribute is System.ComponentModel.DataAnnotations.KeyAttribute)
{
col.PrimaryKey = true;
}
if (attribute is System.ComponentModel.DataAnnotations.Schema.ColumnAttribute)
{
System.ComponentModel.DataAnnotations.Schema.ColumnAttribute ca = attribute as System.ComponentModel.DataAnnotations.Schema.ColumnAttribute;
col.Order = ca.Order;
}
if (attribute is System.ComponentModel.DisplayNameAttribute)
{
System.ComponentModel.DisplayNameAttribute dn = attribute as System.ComponentModel.DisplayNameAttribute;
col.HeaderText = dn.DisplayName;
}
if (attribute is System.ComponentModel.DataAnnotations.DisplayFormatAttribute)
{
System.ComponentModel.DataAnnotations.DisplayFormatAttribute dfa = attribute as System.ComponentModel.DataAnnotations.DisplayFormatAttribute;
col.DataFormatString = dfa.DataFormatString;
}
if (attribute is System.ComponentModel.DataAnnotations.EditableAttribute)
{
System.ComponentModel.DataAnnotations.EditableAttribute ea = attribute as System.ComponentModel.DataAnnotations.EditableAttribute;
col.Editable = ea.AllowEdit;
}
if (attribute is System.ComponentModel.DataAnnotations.RequiredAttribute)
{
col.EditDialogFieldSuffix = "(*)";
col.EditClientSideValidators.Add(new RequiredValidator());
}
if (attribute is SearchableAttribute)
{
SearchableAttribute sa = attribute as SearchableAttribute;
col.Searchable = sa.Searchable;
}
if (attribute is SearchRequiredAttribute)
{
SearchRequiredAttribute sa = attribute as SearchRequiredAttribute;
if (sa.SearchRequired)
{
col.EditClientSideValidators.Add(new SearchRequiredValidator());
}
}
if (attribute is VisiableAttribute)
{
VisiableAttribute va = attribute as VisiableAttribute;
col.Visible = va.Visiable;
}
if (attribute is EditTypeAttribute)
{
EditTypeAttribute ea = attribute as EditTypeAttribute;
col.EditType = ea.EditType;
switch (col.EditType)
{
case EditType.DatePicker:
col.EditorControlID = "DatePicker";
break;
case EditType.DateTimePicker:
col.EditorControlID = "DateTimePicker";
break;
case EditType.TimePicker:
col.EditorControlID = "TimePicker";
break;
case EditType.AutoComplete:
col.EditorControlID = "AutoComplete_" + col.DataField;
break;
}
}
if (attribute is SearchTypeAttribute)
{
SearchTypeAttribute sa = attribute as SearchTypeAttribute;
if (sa.SearchType == SearchType.CheckBox)
{
col.SearchType = SearchType.DropDown;
List <SelectListItem> lsli = new List <SelectListItem>();
lsli.Add(new SelectListItem()
{
Text = "所有", Value = ""
});
lsli.Add(new SelectListItem()
{
Text = "是", Value = "true"
});
lsli.Add(new SelectListItem()
{
Text = "否", Value = "false"
});
col.SearchList = lsli;
}
else
{
col.SearchType = sa.SearchType;
}
switch (col.SearchType)
{
case SearchType.DatePicker:
col.SearchControlID = "DatePicker";
break;
case SearchType.DateTimePicker:
col.SearchControlID = "DateTimePicker";
break;
case SearchType.TimePicker:
col.SearchControlID = "TimePicker";
break;
case SearchType.AutoComplete:
col.SearchControlID = "AutoComplete_" + col.DataField;
break;
}
}
if (attribute is FormatterAttribute)
{
FormatterAttribute fa = attribute as FormatterAttribute;
switch (fa.FormatterType)
{
case FormatterType.CheckBox:
col.Formatter = new CheckBoxFormatter();
break;
}
}
}
}