public virtual void ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct); return;
}
var map = TableMapCache.GetMap <TEntity>();
var strSelectSql = Visit.Select(Queue.ExpSelect);
var strWhereSql = Visit.Where(Queue.ExpWhere);
var strOrderBySql = Visit.OrderBy(Queue.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct" : string.Empty;
Queue.Sql = new StringBuilder();
if (string.IsNullOrWhiteSpace(strSelectSql))
{
strSelectSql = "*";
}
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
Queue.Sql.AppendFormat("SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT {0} {1} FROM {4} {5} {6}) A WHERE ROWNUM <= {3} ) WHERE RN > {2};", strDistinctSql, strSelectSql, pageSize * (pageIndex - 1), pageSize * pageIndex, Query.DbProvider.KeywordAegis(TableName), strWhereSql, strOrderBySql);
}
/// <summary>
/// 赋值解析器
/// </summary>
/// <param name="entity">实体类</param>
public string Assign(TEntity entity)
{
Clear();
var map = TableMapCache.GetMap(entity);
var sb = new StringBuilder();
// 迭代实体赋值情况
foreach (var kic in map.ModelList.Where(o => o.Value.IsDbField))
{
// 如果主键有值，则取消修改主键的SQL
if (kic.Value.Column.IsDbGenerated)
{
continue;
}
var obj = kic.Key.GetValue(entity, null);
if (obj == null || obj is TableSet <TEntity> )
{
continue;
}
// 查找组中是否存在已有的参数，有则直接取出
var newParam = _query.DbProvider.CreateDbParam(_queue.Index + "_" + kic.Value.Column.Name, obj, _query.Param, _queue.Param);
// 添加参数到列表
sb.AppendFormat("{0} = {1} ,", _query.DbProvider.KeywordAegis(kic.Key.Name), newParam.ParameterName);
}
return(sb.Length > 0 ? sb.Remove(sb.Length - 1, 1).ToString() : sb.ToString());
}
/// <summary>
/// DataTable转换为实体类
/// </summary>
/// <param name="dt">源DataTable</param>
/// <typeparam name="T">实体类</typeparam>
public static List <TResult> ToList <TResult>(this DataTable dt) where TResult : class, new()
{
var list = new List <TResult>();
var map = TableMapCache.GetMap <TResult>();
TResult t;
foreach (DataRow dr in dt.Rows)
{
t = new TResult();
//赋值字段
foreach (var kic in map.ModelList)
{
if (!kic.Key.CanWrite)
{
continue;
}
string filedName;
if (!DbFactory.GetDbProvider <TResult>().IsField(kic.Value.Column.Name))
{
filedName = kic.Key.Name;
}
else
{
filedName = kic.Value.Column.Name;
}
if (dr.Table.Columns.Contains(filedName))
{
kic.Key.SetValue(t, dr[filedName].ConvertType(kic.Key.PropertyType), null);
}
}
list.Add(t);
}
return(list);
}
/// <summary>
/// 数据填充
/// </summary>
/// <param name="reader">源IDataReader</param>
/// <typeparam name="T">实体类</typeparam>
public static T ToInfo <T>(this IDataReader reader) where T : class, new()
{
var map = TableMapCache.GetMap <T>();
var t = (T)Activator.CreateInstance(typeof(T));
var isHaveValue = false;
if (reader.Read())
{
//赋值字段
foreach (var kic in map.ModelList)
{
if (reader.HaveName(kic.Value.Column.Name))
{
if (!kic.Key.CanWrite)
{
continue;
}
kic.Key.SetValue(t, reader[kic.Value.Column.Name].ConvertType(kic.Key.PropertyType), null);
isHaveValue = true;
}
}
}
reader.Close();
reader.Dispose();
return(isHaveValue ? t : null);
}
public virtual void ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct); return;
}
var map = TableMapCache.GetMap <TEntity>();
var strSelectSql = Visit.Select(Queue.ExpSelect);
var strWhereSql = Visit.Where(Queue.ExpWhere);
var strOrderBySql = Visit.OrderBy(Queue.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct" : string.Empty;
Queue.Sql = new StringBuilder();
if (string.IsNullOrWhiteSpace(strSelectSql))
{
strSelectSql = "*";
}
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (string.IsNullOrWhiteSpace(strOrderBySql) && string.IsNullOrWhiteSpace(map.IndexName))
{
throw new Exception("当未指定排序方式时，必须要指定 主键字段");
}
strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", map.IndexName) : strOrderBySql);
Queue.Sql.AppendFormat("SELECT {1} FROM (SELECT {0} {1},ROW_NUMBER() OVER({2}) as Row FROM {3} {4}) a WHERE Row BETWEEN {5} AND {6};", strDistinctSql, strSelectSql, strOrderBySql, TableName, strWhereSql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
}
/// <summary>
/// 插入字段解析器
/// </summary>
/// <param name="entity">实体类</param>
public string Insert(TEntity entity)
{
Clear();
var map = TableMapCache.GetMap(entity);
// 字段
var strFields = new StringBuilder();
// 值
var strValues = new StringBuilder();
//var lstParam = QueryQueue.Param;
// 迭代实体赋值情况
foreach (var kic in map.ModelList.Where(o => o.Value.IsDbField))
{
var obj = kic.Key.GetValue(entity, null);
if (obj == null || obj is TableSet <TEntity> )
{
continue;
}
// 查找组中是否存在已有的参数，有则直接取出
var newParam = _query.DbProvider.CreateDbParam(_queue.Index + "_" + kic.Value.Column.Name, obj, _query.Param, _queue.Param);
// 添加参数到列表
strFields.AppendFormat("{0},", _query.DbProvider.KeywordAegis(kic.Key.Name));
strValues.AppendFormat("{0},", newParam.ParameterName);
}
//QueryQueue.Param = lstParam;
return("(" + strFields.Remove(strFields.Length - 1, 1) + ") VALUES (" + strValues.Remove(strValues.Length - 1, 1) + ")");
}
/// <summary>
/// IDataReader转换为实体类
/// </summary>
/// <param name="reader">源IDataReader</param>
/// <typeparam name="T">实体类</typeparam>
public static List <T> ToList <T>(this IDataReader reader) where T : class, new()
{
var list = new List <T>();
var Map = TableMapCache.GetMap <T>();
T t;
while (reader.Read())
{
t = (T)Activator.CreateInstance(typeof(T));
//赋值字段
foreach (var kic in Map.ModelList)
{
if (reader.HaveName(kic.Value.Column.Name))
{
if (!kic.Key.CanWrite)
{
continue;
}
kic.Key.SetValue(t, reader[kic.Value.Column.Name].ConvertType(kic.Key.PropertyType), null);
}
}
list.Add(t);
}
reader.Close();
reader.Dispose();
return(list);
}
public override void ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct); return;
}
var map = TableMapCache.GetMap <TEntity>();
var strSelectSql = Visit.Select(Queue.ExpSelect);
var strWhereSql = Visit.Where(Queue.ExpWhere);
var strOrderBySql = Visit.OrderBy(Queue.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct" : string.Empty;
Queue.Sql = new StringBuilder();
strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", map.IndexName) : strOrderBySql);
var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC");
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (string.IsNullOrWhiteSpace(strSelectSql))
{
strSelectSql = "*";
}
Queue.Sql.AppendFormat("SELECT {0} TOP {2} {1} FROM (SELECT TOP {3} {1} FROM {4} {5} {6}) a {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, TableName, strWhereSql, strOrderBySql, strOrderBySqlReverse);
}
/// <summary>
/// 事务
/// </summary>
/// <param name="database">数据库执行</param>
/// <param name="tableName">表名称</param>
public ViewContext(DbExecutor database, string tableName = null) : base(database, tableName)
{
if (string.IsNullOrWhiteSpace(tableName))
{
Name = TableMapCache.GetMap <TEntity>().ClassInfo.Name;
}
ViewSet = new ViewSet <TEntity>(this);
Query = DbFactory.CreateQueryView(this);
}
/// <summary>
/// 事务
/// </summary>
/// <param name="database">数据库执行</param>
/// <param name="tableName">表名称</param>
public TableContext(DbExecutor database, string tableName = null) : base(database, tableName)
{
if (string.IsNullOrWhiteSpace(tableName))
{
Name = TableMapCache.GetMap <TEntity>().ClassInfo.Name;
}
TableSet = new TableSet <TEntity>(this);
IsMergeCommand = true;
Query = DbFactory.CreateQueryTable(this);
}
public override ISqlQueryView <TEntity> CreateSqlQuery <TEntity>(IQueryView query, IQueueView queue, string tableName)
{
var map = TableMapCache.GetMap <TEntity>();
switch (map.ClassInfo.DataVer)
{
case "2000": return(new SqlQueryView2000 <TEntity>(query, queue, tableName));
}
return(new SqlQueryView <TEntity>(query, queue, tableName));
}
/// <summary>
/// 将OutPut参数赋值到实体
/// </summary>
/// <typeparam name="TEntity">实体类</typeparam>
/// <param name="entity">实体类</param>
private void SetParamToEntity <TEntity>(TEntity entity) where TEntity : class, new()
{
if (entity == null)
{
return;
}
var map = TableMapCache.GetMap(entity);
foreach (var kic in map.ModelList.Where(o => o.Value.IsOutParam))
{
kic.Key.SetValue(entity, Param.Find(o => o.ParameterName == _query.DbProvider.ParamsPrefix + kic.Value.Column.Name).Value.ConvertType(kic.Key.PropertyType), null);
}
}
/// <summary>
/// 返回数据库类型名称
/// </summary>
public static DbProvider GetDbProvider <TEntity>() where TEntity : class, new()
{
var dbType = TableMapCache.GetMap <TEntity>().ClassInfo.DataType;
switch (dbType)
{
case DataBaseType.OleDb: return(new OleDbProvider());
case DataBaseType.MySql: return(new MySqlProvider());
case DataBaseType.SQLite: return(new SqLiteProvider());
case DataBaseType.Oracle: return(new OracleProvider());
}
return(new SqlServerProvider());
}
public void CreateParam(TEntity entity)
{
if (entity == null)
{
return;
}
_queue.Param = new List <System.Data.Common.DbParameter>();
var map = TableMapCache.GetMap(entity);
foreach (var kic in map.ModelList.Where(o => o.Value.IsInParam || o.Value.IsOutParam))
{
var obj = kic.Key.GetValue(entity, null);
_queue.Param.Add(_query.DbProvider.CreateDbParam(kic.Value.Column.Name, obj, kic.Key.PropertyType, kic.Value.IsOutParam));
}
}
/// <summary>
/// 获取该实体类的参数
/// </summary>
/// <param name="entity">实体类</param>
public IList <DbParameter> GetParameter <TEntity>(TEntity entity) where TEntity : class, new()
{
var map = TableMapCache.GetMap(entity);
var lst = new List <DbParameter>();
foreach (var kic in map.ModelList.Where(o => o.Value.IsDbField))
{
var obj = kic.Key.GetValue(entity, null);
if (obj == null || obj is TableSet <TEntity> )
{
continue;
}
// 添加参数到列表
lst.Add(CreateDbParam(kic.Value.Column.Name, obj));
}
return(lst);
}
/// <summary>
/// 将DataRow转成实体类
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dr">源DataRow</param>
public static T ToInfo <T>(this DataRow dr) where T : ModelInfo, new()
{
var map = TableMapCache.GetMap <T>();
var t = (T)Activator.CreateInstance(typeof(T));
//赋值字段
foreach (var kic in map.ModelList)
{
if (dr.Table.Columns.Contains(kic.Value.Column.Name))
{
if (!kic.Key.CanWrite)
{
continue;
}
kic.Key.SetValue(t, dr[kic.Value.Column.Name].ConvertType(kic.Key.PropertyType), null);
}
}
return(t ?? new T());
}
/// <summary>
/// 将集合类转换成DataTable
/// </summary>
/// <param name="lst">集合</param>
/// <returns></returns>
public static DataTable ToTable <TInfo>(this List <TInfo> lst) where TInfo : ModelInfo, new()
{
var dt = new DataTable();
if (lst.Count == 0)
{
return(dt);
}
var map = TableMapCache.GetMap(lst[0].GetType());
var lstFields = map.ModelList.Where(o => o.Value.IsDbField);
foreach (var field in lstFields)
{
var type = field.Key.PropertyType;
// 对 List 类型处理
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable <>))
{
type = type.GetGenericArguments()[0];
}
dt.Columns.Add(field.Value.Column.Name, type);
}
foreach (var info in lst)
{
dt.Rows.Add(dt.NewRow());
foreach (var field in lstFields)
{
var value = info.GetValue(field.Key.Name, (object)null);
if (value == null)
{
continue;
}
if (!dt.Columns.Contains(field.Value.Column.Name))
{
dt.Columns.Add(field.Value.Column.Name);
}
dt.Rows[dt.Rows.Count - 1][field.Value.Column.Name] = value;
}
}
return(dt);
}
/// <summary>
/// 将XML转成实体
/// </summary>
public static List <T> ToList <T>(this XElement element) where T : ModelInfo
{
var orm = TableMapCache.GetMap <T>();
var list = new List <T>();
Type type;
T t;
foreach (var el in element.Elements())
{
t = (T)Activator.CreateInstance(typeof(T));
//赋值字段
foreach (var kic in orm.ModelList)
{
type = kic.Key.PropertyType;
if (!kic.Key.CanWrite)
{
continue;
}
if (kic.Value.PropertyExtend == eumPropertyExtend.Attribute)
{
if (el.Attribute(kic.Value.Column.Name) == null)
{
continue;
}
kic.Key.SetValue(t, el.Attribute(kic.Value.Column.Name).Value.ConvertType(type), null);
}
else if (kic.Value.PropertyExtend == eumPropertyExtend.Element)
{
if (el.Element(kic.Value.Column.Name) == null)
{
continue;
}
kic.Key.SetValue(t, el.Element(kic.Value.Column.Name).Value.ConvertType(type), null);
}
}
list.Add(t);
}
return(list);
}
public void InsertIdentity(TEntity entity)
{
Queue.Sql = new StringBuilder();
var strinsertAssemble = Visit.Insert(entity);
var map = TableMapCache.GetMap(entity);
var indexHaveValue = map.GetModelInfo().Key != null && map.GetModelInfo().Key.GetValue(entity, null) != null;
if (!string.IsNullOrWhiteSpace(map.IndexName) && indexHaveValue)
{
Queue.Sql.AppendFormat("SET IDENTITY_INSERT {0} ON ; ", TableName);
}
Queue.Sql.AppendFormat("INSERT INTO {0} {1};SELECT @@IDENTITY;", TableName, strinsertAssemble);
if (!string.IsNullOrWhiteSpace(map.IndexName) && indexHaveValue)
{
Queue.Sql.AppendFormat("; SET IDENTITY_INSERT {0} OFF; ", TableName);
}
}
/// <summary>
/// 获取字段名称
/// </summary>
/// <param name="select">字段名称</param>
/// <returns></returns>
public static string GetUsedName <T1, T2>(this Expression <Func <T1, T2> > select) where T1 : ModelInfo
{
MemberExpression memberExpression;
var unary = select.Body as UnaryExpression;
if (unary != null)
{
memberExpression = unary.Operand as MemberExpression;
}
else if (select.Body.NodeType == ExpressionType.Call)
{
memberExpression = (MemberExpression)((MethodCallExpression)select.Body).Object;
}
else
{
memberExpression = select.Body as MemberExpression;
}
var map = TableMapCache.GetMap <T1>();
var modelInfo = map.GetModelInfo((memberExpression.Member).Name);
return(modelInfo.Value.Column.Name);
}
/// <summary>
/// 通过TEntity的特性，获取数据库配置
/// </summary>
public ProcContext(string tableName = null) : this(TableMapCache.GetMap(typeof(ProcContext <TEntity>)).ClassInfo.ConnStr, TableMapCache.GetMap(typeof(ProcContext <TEntity>)).ClassInfo.DataType, TableMapCache.GetMap(typeof(ProcContext <TEntity>)).ClassInfo.CommandTimeout, tableName)
{
}
/// <summary>
/// 将实体类填充到控件中
/// </summary>
/// <param name="controls">页面控件集合</param>
/// <param name="infoValue">所属实体类的值</param>
/// <param name="prefix">前缀</param>
private static void Fill <TInfo>(ControlCollection controls, TInfo infoValue, string prefix = "hl") where TInfo : ModelInfo, new()
{
if (infoValue == null || controls == null)
{
return;
}
var map = TableMapCache.GetMap(infoValue);
foreach (var kic in map.ModelList)
{
// 当前成员的值
var value = kic.Key.GetValue(infoValue, null);
if (value == null)
{
continue;
}
var type = value.GetType();
// 当前成员，是一个类
if (value is ModelInfo)
{
foreach (var item in type.GetProperties())
{
Fill(controls, (ModelInfo)value, prefix);
}
continue;
}
foreach (Control item in controls)
{
var control = item.FindControl(prefix + kic.Key.Name);
if (control == null)
{
continue;
}
if (control is HiddenField)
{
((HiddenField)control).Value = value.ToString();
break;
}
if (control is CheckBox)
{
((CheckBox)control).Checked = value.ConvertType(false); break;
}
if (control is CheckBoxList)
{
// 数据保存的是数字以逗号分隔的数据，并且是ListControl的控件，则可以直接填充数据
if (value is string)
{
var lstIDs = value.ToString().ToList(0);
((CheckBoxList)control).SetValue(lstIDs);
break;
}
// 枚举为二进制时
var types = kic.Key.PropertyType.GetGenericArguments();
if (types != null && types.Length > 0 && types[0].IsEnum)
{
var att = types[0].GetCustomAttributes(typeof(FlagsAttribute), false);
if (att != null && att.Length > 0)
{
foreach (ListItem listItem in ((CheckBoxList)control).Items)
{
var itemValue = listItem.Value.ConvertType(0);
listItem.Selected = (value.ConvertType(0) & itemValue) == itemValue;
}
break;
}
}
}
if (control is ListControl)
{
((ListControl)control).SelectedItems(value);
break;
}
if (value is Enum)
{
value = ((Enum)value).GetName();
}
if (value is IList)
{
value = ((IList)value).ToString(",");
}
if (value is bool)
{
value = ((bool)value) ? "是" : "否";
}
if (control is TextBox)
{
((TextBox)control).Text = value.ToString(); break;
}
if (control is Label)
{
((Label)control).Text = value.ToString(); break;
}
if (control is Literal)
{
((Literal)control).Text = value.ToString(); break;
}
if (control is Image)
{
((Image)control).ImageUrl = value.ToString(); break;
}
if (control is HyperLink)
{
((HyperLink)control).NavigateUrl = value.ToString(); break;
}
}
}
}