public static IEnumerable <IQueryTable> GetPath(this IQueryTable queryTable, IQueryTable mappingContext, GetPathErrorHandling errorHandling = GetPathErrorHandling.ThrowException)
{
var result = GetPathRec(queryTable, mappingContext);
// if no path was found, the context table might be after the
// query table in the JOIN hirearchy. In this case it is ok
// to return a path without the context table
if (result == null)
{
var rs = GetPathRec(mappingContext, queryTable)
?.TakeUntilIncludeLast(x => x == queryTable)
.ToArray();
result = rs != null && rs[rs.Length - 1] == queryTable
? rs
: null;
}
if (result == null && errorHandling == GetPathErrorHandling.ThrowException)
{
throw new InvalidOperationException($"You cannot use {queryTable.Alias} in the context of {mappingContext.Alias}.");
}
return(result);
}
public DbQueueTable(int index, IQueryTable queryProvider)
{
ID = Guid.NewGuid();
Index = index;
_query = queryProvider;
Param = new List <DbParameter>();
}
public JoinTable(IQueryTable joinQueryTable, WhereClip joinWhere, EJoinType joinType, params ExpressionClip[] queryCulumns)
{
this.JoinQueryTable = joinQueryTable;
this.JoinWhere = joinWhere;
this.JoinType = joinType;
this.QueryCulumns = queryCulumns;
}
public static IEnumerable <IQueryTable> GetTablesInPath(this IQueryTable fromTable)
{
return(fromTable.JoinedFrom
.SelectMany(jf => GetTablesInPath(jf))
.Append(fromTable)
.Distinct());
}
public JoinedTable(JoinType Type, IQueryTable OuterTable, string InnerKey, string OuterKey)
{
this.OuterTable = OuterTable;
this.InnerKey = InnerKey;
this.OuterKey = OuterKey;
this.Type = Type;
}
public static void UpdateStatus(int id, string tableName, string fileName,string title, int archiveId, IQueryTable toTable, QueryColumn toStatus, QueryColumn toPrimary)
{
NBearLite.DbProviders.DbProvider provider =
NBearLite.DbProviders.DbProviderFactory.CreateDbProvider(
null,
"System.Data.SqlServerCe",
@"Data Source=enforce.sdf;
Encrypt Database=True;
Password=enforce_123456;
File Mode=shared read;
Persist Security Info=False;");
Database database = new Database(provider);
UpdateSqlSection section = new UpdateSqlSection(database, toTable);
section.AddColumn(toStatus, 2); // 2 代表已归档
section.Where(toPrimary == id);
section.Execute();
InsertSqlSection insert = new InsertSqlSection(database, DataBases.ArchiveExt);
insert.AddColumn(DataBases.ArchiveExt.ArchiveID, archiveId);
insert.AddColumn(DataBases.ArchiveExt.TableName, tableName);
insert.AddColumn(DataBases.ArchiveExt.PrimaryValue, id);
insert.AddColumn(DataBases.ArchiveExt.FileName, fileName);
insert.AddColumn(DataBases.ArchiveExt.Title, title);
insert.AddColumn(DataBases.ArchiveExt.CreateTime, DateTime.Now);
insert.AddColumn(DataBases.ArchiveExt.ExchangeGUID, CommonInvoke.NewGuid);
insert.Execute();
}
///// <summary>
///// 根据主键ID删除
///// </summary>
///// <param name="ID"></param>
///// <returns></returns>
//public bool Delete(IQueryTable table,string id)
//{
// return DBHelper.DeleteByID(this, table, id);
//}
///// <summary>
///// 根据主键ID删除
///// </summary>
///// <param name="ID"></param>
///// <returns></returns>
//public int DeleteDatasByID(IQueryTable table, string ids)
//{
// return DBHelper.DeleteByIDs(this, table, ids);
//}
///// <summary>
///// 根据条件删除
///// </summary>
///// <param name="where"></param>
///// <returns></returns>
//public int Delete(IQueryTable table, WhereClip where)
//{
// return this.Delete(table).Where(where).Execute();
//}
public DataTable GetOneDataByID(IQueryTable table, string id)
{
if (!string.IsNullOrEmpty(id))
{
return(this.Select(table).Where(table.IDColumn == id).ToDataTable());
}
return(null);
}
public static DataTable GetOneDataByID(IQueryTable table, object id)
{
if (id != null)
{
return(DBHelper.DB.Select(table).Where(table.IDColumn == id).ToDataTable());
}
return(null);
}
public static DataTable GetOneDataByID(EDatabase db, IQueryTable table, object id)
{
if (id != null)
{
return(db.Select(table).Where(table.IDColumn == id).ToDataTable());
}
return(null);
}
static IEnumerable <IQueryTable> PathOfFirsts(IQueryTable forTable)
{
if (forTable.JoinedFrom.Length == 0)
{
return(forTable.ToEnumerable());
}
return(PathOfFirsts(forTable.JoinedFrom[0]).Append(forTable));
}
static IEnumerable <IQueryTable> GetPathRec(IQueryTable fromTable, IQueryTable includeTable)
{
if (fromTable == includeTable)
{
return(PathOfFirsts(fromTable));
}
// TODO: is depth first search optimal?
foreach (var tab in fromTable.JoinedFrom)
{
var xs = GetPathRec(tab, includeTable);
if (xs != null)
{
return(xs.Append(fromTable));
}
}
return(null);
}
/// <summary>
/// 添加关联查询
/// </summary>
/// <param name="queryTable">关联表</param>
/// <param name="joinTableAliasName">关联表别名</param>
/// <param name="joinWhere">关联条件</param>
/// <param name="joinType">关联类型</param>
/// <returns>ModalAdapter</returns>
public SearchHelper Join(IQueryTable queryTable, string joinTableAliasName, WhereClip joinWhere, EJoinType joinType)
{
switch (joinType)
{
case EJoinType.Join:
this._SelectSection.Join(queryTable, joinTableAliasName, joinWhere);
break;
case EJoinType.LeftJoin:
this._SelectSection.LeftJoin(queryTable, joinTableAliasName, joinWhere);
break;
case EJoinType.RightJoin:
this._SelectSection.RightJoin(queryTable, joinTableAliasName, joinWhere);
break;
}
this.JoinTables.Add(new JoinTable(queryTable, joinTableAliasName, joinWhere, joinType));
return(this);
}
/// <summary>
/// 添加关联查询
/// </summary>
/// <param name="queryTable">关联表</param>
/// <param name="joinWhere">关联条件</param>
/// <param name="joinType">关联类型</param>
/// <returns>ModalAdapter</returns>
public ModalAdapter <T> Join(IQueryTable queryTable, WhereClip joinWhere, EJoinType joinType)
{
switch (joinType)
{
case EJoinType.Join:
this._SelectSection.Join(queryTable, joinWhere);
break;
case EJoinType.LeftJoin:
this._SelectSection.LeftJoin(queryTable, joinWhere);
break;
case EJoinType.RightJoin:
this._SelectSection.RightJoin(queryTable, joinWhere);
break;
}
this.JoinTables.Add(new JoinTable(queryTable, joinWhere, joinType));
return(this);
}
/// <summary>
/// 获取总记录条数
/// </summary>
/// <param name="column">主键</param>
/// <returns></returns>
public static int GetTotalRecord(EDatabase db, IQueryTable table, WhereClip where, List <JoinTable> joinTables)
{
SelectSqlSection sql = db.Select(table, table.IDColumn.Count());
if (joinTables != null && joinTables.Count > 0)
{
foreach (JoinTable joinTable in joinTables)
{
joinTable.Join(sql);
}
}
object obj = sql.Where(where).ToScalar();
if (obj != null)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
/// 增加一个表
/// </summary>
/// <param name="Table"></param>
/// <param name="fromTable"></param>
/// <param name="joinType"></param>
public void Add(IQueryTable Table,
SelectedTable fromTable = null)
{
if (fromTable == null)
{
this.Ins.SelectedTables = new SelectedTables(new SelectedTable()
{
Table = Table,
SelectedTableName = Table.ShowName
}, this.Facotry);
}
else
{
var count = this.Ins.SelectedTables.Tables.Count(p => p.Table.DBName == Table.DBName);
string selectedName = Table.ShowName + (count == 0?"":count.ToString());
this.Ins.SelectedTables.Add(new SelectedTable()
{
Table = Table,
SelectedTableName = selectedName
}, fromTable);
}
}
/// <summary>
/// 创建队列
/// </summary>
/// <param name="index">索引</param>
/// <param name="query">数据库持久化</param>
public IQueueTable CreateQueue(int index, IQueryTable query)
{
return(new DbQueueTable(index, query));
}
public SearchHelper(IQueryTable table)
{
this._Table = table;
this._DB = DBHelper.DB;
}
public void AddTable(IQueryTable Table)
{
tables.Add(Table.Alias, Table);
}
public JoinTable(IQueryTable joinQueryTable, string aliasName, WhereClip joinWhere, EJoinType joinType, params ExpressionClip[] queryCulumns)
: this(joinQueryTable, joinWhere, joinType, queryCulumns)
{
this.AliasName = aliasName;
}
public SearchHelper(IQueryTable table, EDatabase db)
{
this._Table = table;
this._DB = db;
}
public static IEnumerable <IQueryTable> GetTableChain(this IQueryTable table, IQueryTable context, GetPathErrorHandling errorHandling = GetPathErrorHandling.ThrowException)
{
return(table.GetPath(context, errorHandling));
}
public StrongMappedTable(IQueryTable from, string to, bool tableresultsAreAggregated)
{
From = from;
To = to;
TableResultsAreAggregated = tableresultsAreAggregated;
}
public static IEnumerable <int> GetRowNumberColumnIndexes(this ISqlSelectStatement sqlStatement, ICompositeKey key, IQueryTable context)
{
return(sqlStatement.GetRowNumberColumnIndexes(sqlStatement.SupplimentPrimaryKeyColumns(key, context)));
}
/// <summary>
/// Select from specified table. Supports select with order by, where, group by, inner join, top, skip.
/// </summary>
/// <param name="table"></param>
/// <param name="columns"></param>
/// <returns></returns>
public SelectSqlSection Select(IQueryTable table, params ExpressionClip[] columns)
{
Check.Require(table != null, "table could not be null.");
return new SelectSqlSection(this, table, columns);
}
public QueryTable RightJoin(IQueryTable OuterTable, string InnerKey, string OuterKey)
{
return Join(JoinType.Right, OuterTable, InnerKey, OuterKey);
}
public ModalAdapter(IQueryTable table)
{
this._DB = DBHelper.GetDB(table);
this._tableName = this._DB.QueryTable.GetTableName();
}
public static IEnumerable <ICompositeKey> GetPrimaryKeyColumns(this IQueryTable table, IQueryTable context)
{
return(table.GetTableChain(context).Select(t => t.PrimaryKey));
}
/// <summary>
/// 创建SQL查询
/// </summary>
/// <typeparam name="TEntity">实体类</typeparam>
/// <param name="query">数据库持久化</param>
/// <param name="queue">当前队列</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public ISqlQueryTable <TEntity> CreateSqlQuery <TEntity>(IQueryTable query, IQueueTable queue, string tableName) where TEntity : class, new()
{
return(new SqlQueryTable <TEntity>(query, queue, tableName));
}
public QueryTable Join(JoinType Type, IQueryTable OuterTable, string InnerKey, string OuterKey)
{
joinedTables.Add(new JoinedTable(Type, OuterTable, InnerKey, OuterKey));
return this;
}
public SqlQueryTable(IQueryTable query, IQueueTable queue, string tableName) : base(query, queue, tableName)
{
}
public QueryTable InnerJoin(IQueryTable OuterTable, string InnerKey, string OuterKey)
{
return Join(JoinType.Inner, OuterTable, InnerKey, OuterKey);
}
public static IEnumerable <IQueryTable> GetAllReferencedTables(this IQueryTable table)
{
return(table.GetTablesInPath());
}
/// <summary>
/// Get a list of pk columns which is a chain from the "forKey" value to the "root", containing the "context"
/// <summary>
public static IEnumerable <ICompositeKey> SupplimentPrimaryKeyColumns(this ISqlSelectStatement sqlStatement, ICompositeKey forKey, IQueryTable context)
{
// if the piece is a parameter, Table will be null
if (forKey == null)
{
return(Enumerable.Empty <ICompositeKey>());
}
var path = forKey.Table
.GetPrimaryKeyColumns(context)
.ToArray();
if (path.Contains(context.PrimaryKey))
{
return(path);
}
// in this case a column is being used in the
// context of one of its child properties
return(context.GetPrimaryKeyColumns(forKey.Table));
}
public ModalAdapter(IQueryTable table, EDatabase db)
{
this._DB = db;
this._DB.QueryTable = table;
this._tableName = this._DB.QueryTable.GetTableName();
}
public static IEnumerable <ICompositeKey> GetAllPrimaryKeyColumns(this IQueryTable table)
{
return(table.GetAllReferencedTables().Select(t => t.PrimaryKey));
}
/// <summary>
/// Insert to specified table.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public InsertSqlSection Insert(IQueryTable table)
{
Check.Require(table != null, "table could not be null.");
return new InsertSqlSection(this, table);
}
/// <summary>
/// Get a list of pk columns for the "columnAlias" containing the "context"
/// <summary>
public static IEnumerable <ICompositeKey> GetPrimaryKeyColumns(this ISqlSelectStatement sqlStatement, string columnAlias, IQueryTable context)
{
return(sqlStatement.SupplimentPrimaryKeyColumns(
sqlStatement.SelectColumns[columnAlias].PrimaryKey,
context));
}
/// <summary>
/// Delete from specified table.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public DeleteSqlSection Delete(IQueryTable table)
{
Check.Require(table != null, "table could not be null.");
return new DeleteSqlSection(this, table);
}
public static IEnumerable <int> GetRowNumberColumnIndexes(this ISqlSelectStatement sqlStatement, string columnAlias, IQueryTable context)
{
return(sqlStatement.GetRowNumberColumnIndexes(sqlStatement.GetPrimaryKeyColumns(columnAlias, context)));
}
