static void Main()
{
ILl = new EfLoadL();
DataTable dt = DataTableHelper.QueryableToTable(ILl.LoadLevellingTable);
dt.TableName = "LoadLevelling"; // il nome della tabella nel db
DataTable schema = DataTableHelper.QueryableToTable(ILl.SchemaTable);
schema.TableName = "Schema";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables.Add(schema);
CalcEXlogicClass.Execute(ds, "loadl", "LoadLevelling");
DataTable returndt = ds.Tables[0];
// converte il datatable
var retlist = DataTableHelper.TableToList <LoadLevelling>(returndt).ToList();
ILl.MassiveAddData(retlist);
ILl.MassiveSaveData();
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="query">The query to consider.</param>
public static DbField FieldAsQuery(
string name,
DbTable table,
IDbQuery query)
{
return(DbFluent.Field(name, table).AsQuery(query));
}
public virtual int RowCountByQuery(IStructureSchema structureSchema, IDbQuery query)
{
Ensure.That(structureSchema, "structureSchema").IsNotNull();
Ensure.That(query, "query").IsNotNull();
return(ExecuteScalar <int>(query.Sql, query.Parameters));
}
// ------------------------------------------
// ACCESSORS
// ------------------------------------------
#region Accessors
// SQL commands
/// <summary>
/// Gets the SQL text of the specified query.
/// </summary>
/// <param name="query">The query to consider.</param>
/// <param name="parameterMode">Indicates whether parameters are replaced.</param>
/// <param name="parameterSet">The parameter set to consider.</param>
/// <param name="scriptVariableSet">The script variable set to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>Returns the SQL text of the specified query.</returns>
public string CreateCommandText(
IDbQuery query,
DbQueryParameterMode parameterMode = DbQueryParameterMode.ValueInjected,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string sqlText = "";
if (QueryBuilder == null)
{
log?.AddError("Data builder missing");
}
else
{
var subLog = new BdoLog();
sqlText = QueryBuilder.BuildQuery(query, parameterMode, parameterSet, scriptVariableSet, subLog);
log?.AddEvents(subLog);
if (subLog.HasErrorsOrExceptions())
{
return(StringHelper.__NoneString);
}
}
return(sqlText);
}
/// <summary>
/// Extendable method: 过滤数据源中属性包含(或不包含)某些值的实体(生成Sql IN表达式)
/// </summary>
/// <typeparam name="TEntity">对象类型</typeparam>
/// <typeparam name="TProperty">对象属性类型</typeparam>
/// <param name="source">数据源</param>
/// <param name="selector">指定对象某属性的表达式</param>
/// <param name="values">对象属性所包含的值</param>
/// <param name="isIn">IN 或 NOT IN</param>
/// <returns>新的数据源</returns>
public static IDbQuery <TEntity> In <TEntity, TProperty>(this IDbQuery <TEntity> source, Expression <Func <TEntity, TProperty> > selector, TProperty[] values, bool isIn = true)
where TEntity : class
{
//非空验证
Check.ArgumentNull(source, nameof(source));
Check.ArgumentNull(selector, nameof(selector));
Check.ArgumentNull(values, nameof(values));
//获取sql表达式模板
MemberExpression memberExpression = selector.Body.GetMemberExpression();
StringBuilder sqlTemplate = new StringBuilder();
sqlTemplate.AppendLine(isIn ? "IN " : "NOT IN ");
for (int i = 0; i < values.Length; i++)
{
sqlTemplate.AppendFormat("\t{0}", i == 0 ? '(' : ' ');
sqlTemplate.AppendFormat("${0}", memberExpression.Member.Name);
sqlTemplate.AppendFormat("{0}{1}", i, (i + 1 == values.Length) ? ")" : ",\n");
}
//获取原始参数集合
IDbDataParameter[] parameters = new IDbDataParameter[values.Length];
for (int i = 0; i < values.Length; i++)
{
parameters[i] = source.ParameterFactory.Parameter(string.Concat(memberExpression.Member.Name, i), values[i]);
}
//过滤获取新数据源
return(source.Factory.CreateQuery(source, memberExpression, sqlTemplate.ToString(), parameters.ToArray()));
}
public SurveyAnswerServices(IRepository <SurveyAnswer> repository,
IRepository <AnswerRadio> repositoryAnswerRadio,
IRepository <AnswerCheckbox> repositoryAnswerCheckbox,
IRepository <AnswerFillblank> repositoryAnswerFillblank,
IRepository <AnswerScore> repositoryAnswerScore,
IRepository <AnswerOrder> repositoryAnswerOrder,
IRepository <AnswerMultiFillblank> repositoryAnswerMultiFillblank,
IRepository <AnswerChenRadio> repositoryAnswerChenRadio,
IRepository <AnswerChenCheckbox> repositoryAnswerChenCheckbox,
IRepository <AnswerChenScore> repositoryAnswerChenScore,
ISurveyDirectoryServices repositorySurveyDirectory,
IRepository <AnswerChenFillblank> repositoryAnswerChenFillblank,
IQuestionServices questionServices,
IDbQuery dbQuery)
{
_repository = repository;
_dbQuery = dbQuery;
_repositoryAnswerRadio = repositoryAnswerRadio;
_repositoryAnswerCheckbox = repositoryAnswerCheckbox;
_repositoryAnswerFillblank = repositoryAnswerFillblank;
_repositoryAnswerScore = repositoryAnswerScore;
_repositoryAnswerOrder = repositoryAnswerOrder;
_repositoryAnswerMultiFillblank = repositoryAnswerMultiFillblank;
_repositoryAnswerChenRadio = repositoryAnswerChenRadio;
_repositoryAnswerChenCheckbox = repositoryAnswerChenCheckbox;
_repositoryAnswerChenScore = repositoryAnswerChenScore;
_repositoryAnswerChenFillblank = repositoryAnswerChenFillblank;
_questionServices = questionServices;
_repositorySurveyDirectory = repositorySurveyDirectory;
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="query">The query to consider.</param>
public static DbField FieldAsQuery <T>(
Expression <Func <T, object> > expr,
DbTable table,
IDbQuery query) where T : class
{
return(DbFluent.Field <T>(expr, table).AsQuery(query));
}
public virtual string Generate(IDbQuery query)
{
Ensure.That(query, "query").IsNotNull();
if (query.IsEmpty)
{
return(null);
}
var sb = new StringBuilder();
sb.AppendLine(query.Sql);
foreach (var parameter in query.Parameters)
{
if (parameter is ArrayDacParameter)
{
OnVisitParam((ArrayDacParameter)parameter, sb);
continue;
}
if (parameter is DacParameter)
{
OnVisitParam(parameter, sb);
continue;
}
throw new SisoDbNotSupportedException(ExceptionMessages.DbQueryChecksumGenerator_UnsupportedDacParam);
}
return(HashService.GenerateHash(sb.ToString()));
}
/// <summary>
/// Adds the specified sub query.
/// </summary>
/// <param name="subQuery">The sub query to consider.</param>
/// <returns>Return this added parameter.</returns>
public DataExpression UseSubQuery(IDbQuery subQuery)
{
if (SubQueries == null) SubQueries = new List<DbQuery>();
SubQueries.Add((DbQuery)subQuery);
return BdoScript.Function("sqlQuery", SubQueries.Count.ToString()).CreateExp();
}
protected override void InsertToCollection(int minPro, int maxPro, string[] paths)
{
if (JoinDb)
{
using (IDbQuery query = DbIns.GetQuery("Cq"))
{
query.Open();
List <Record> cqs = query.SelectToList <Record>(null);
SetProgress(0, 40);
Tag = "读数据库";
double single = 40d / cqs.Count;
foreach (Record cq in cqs)
{
CqHelper.InsertToCollection(cq, Group);
Progress += single;
}
}
base.InsertToCollection(40, maxPro, paths);
}
else
{
base.InsertToCollection(0, maxPro, paths);
}
}
// Stored --------------------------------
/// <summary>
/// Creates a new Stored advanced database query.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="query">The query to consider.</param>
/// <returns>Returns a new Stored advanced database query</returns>
public static IDbStoredQuery StoredQuery(string name, IDbQuery query)
{
return(new DbStoredQuery(name)
{
Query = query
});
}
public virtual string Generate(IDbQuery query)
{
Ensure.That(query, "query").IsNotNull();
if (query.IsEmpty)
return null;
var sb = new StringBuilder();
sb.AppendLine(query.Sql);
foreach (var parameter in query.Parameters)
{
if(parameter is ArrayDacParameter)
{
OnVisitParam((ArrayDacParameter)parameter, sb);
continue;
}
if(parameter is DacParameter)
{
OnVisitParam(parameter, sb);
continue;
}
throw new SisoDbNotSupportedException(ExceptionMessages.DbQueryChecksumGenerator_UnsupportedDacParam);
}
return HashService.GenerateHash(sb.ToString());
}
public GetInvoiceByIdQueryHandler(
IDbQuery <Invoice> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public UserLookupQueryHandler(
IDbQuery <User> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public RoleLookupQueryHandler(
IDbQuery <Role> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public async Task <T> QueryFirstOrDefaultAsync <T>(IDbQuery query)
{
var connection = this.GetDbConnection();
var transaction = this.context.Database.CurrentTransaction?.GetDbTransaction();
this.CancellationToken.ThrowIfCancellationRequested();
return(await connection.QueryFirstOrDefaultAsync <T>(query.ToString(), query.GetDapperParameters(), transaction));
}
public GetInvoiceAuditlogQueryHandler(
IDbQuery <Auditlog> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public SearchCustomersQueryHandler(
IDbQuery <Customer> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public SearchUsersQueryHandler(
IDbQuery <User> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public GetCustomerByIdQueryHandler(
IDbQuery <Customer> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
/// <summary>
/// Extendable method: 获取按某属性降序排序的分页查询数据源
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <param name="source">数据源</param>
/// <param name="orderBySelector">指定排序属性</param>
/// <param name="pageSize">每页的元素数量</param>
/// <param name="pageIndex">当前第几页</param>
/// <returns>TEntity类型的元素迭代器</returns>
public static IDbPagedQuery <TEntity> PagingByDescending <TEntity>(this IDbQuery <TEntity> source, Expression <Func <TEntity, object> > orderBySelector, int pageSize, int pageIndex = 1)
where TEntity : class
{
//非空检查
Check.ArgumentNull(source, nameof(source));
//执行分页查询
return(source.Factory.CreatePagedQuery(source, orderBySelector, pageSize, pageIndex, true));
}
public GetUserByIdQueryHandler(
IDbQuery <User> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public GetApplicationSettingsAuditlogQueryHandler(
IDbQuery <Auditlog> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public GetCustomerAuditlogQueryHandler(
IDbQuery <Auditlog> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public UserIdProvider(
IDistributedCache cache,
IDbQuery <User> query
)
{
_cache = cache;
_query = query;
}
public SearchInvoicesQueryHandler(
IDbQuery <Invoice> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public GetRoleByIdQueryHandler(
IDbQuery <Role> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public SearchRolesQueryHandler(
IDbQuery <Role> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
/// <inheritdoc cref="QueryAsync{T}(IDbQuery, ulong, Func{IQueryBuilder, IQueryBuilderWithFrom}, IDbTransaction)"/>
public static async Task <Maybe <T> > QuerySingleAsync <T>(
this IDbQuery @this,
Func <IQueryBuilder, IQueryBuilderWithFrom> builder
)
{
using var w = @this.UnitOfWork;
return(await QuerySingleAsync <T>(@this, builder, w.Transaction).ConfigureAwait(false));
}
public GetFeatureFlagSettingsAuditlogQueryHandler(
IDbQuery <Auditlog> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
public CustomerLookupQueryHandler(
IDbQuery <Customer> query,
IMapper mapper
)
{
_query = query;
_mapper = mapper;
}
/// <summary>
/// 创建一个 DbTracing 实例
/// </summary>
/// <param name="query">即将执行的查询对象</param>
/// <param name="complateCallback">当查询执行完成之后需要回调的方法</param>
public DbTracing( IDbQuery query, Action<DbTracing> complateCallback )
{
QueryObject = query;
ExecutionTime = TimeSpan.Zero;
QueryTime = TimeSpan.Zero;
callback = complateCallback;
}
/// <summary>
/// 创建一个 DbTracing 实例
/// </summary>
/// <param name="query">即将执行的查询对象</param>
public DbTracing( IDbQuery query ) : this( query, null ) { }
