/// <summary>
/// The process group.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="result">The result.</param>
/// <param name="queryItem">The query item.</param>
/// <param name="group">The group.</param>
/// <param name="subProgress">The sub progress.</param>
protected virtual void ExecuteQuery(
QueryInfo query,
QueryExecutionParams parameters,
QueryInstanceResultInfo result,
QueryItemInfo queryItem,
GroupDefinition group,
ProgressItem subProgress
)
{
// Log.InfoFormat("146:query:'{0}'",
// query
// );
var qr = this.ExecuteQueryItemFunction.Invoke(
this.Instance,
queryItem,
string.IsNullOrWhiteSpace(group.Name) ? parameters.DefaultDatabase : group.Name,
//group.Name,
group.Id,
query.Parameters,
parameters.Values.ToList(),
subProgress
);
// костыль. Наверное, прибъется вообще после убиения "групп"
qr.Database = group.Name;
result.AddDatabaseResult(qr);
}
/// <summary>
/// The execute query simple.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="version">The version.</param>
/// <param name="progress">The progress.</param>
/// <param name="groups">The groups.</param>
/// <returns>
/// The <see cref="QueryInstanceResultInfo" />.
/// </returns>
public QueryInstanceResultInfo ExecuteQuerySimple(
QueryInfo query,
QueryExecutionParams parameters,
InstanceVersion version,
ProgressItem progress,
params GroupDefinition[] groups
)
{
// Log.InfoFormat("73:query:'{0}'",
// query
// );
QueryInstanceResultInfo result = new QueryInstanceResultInfo(this.Instance);
QueryItemInfo queryItem = query.Items.GetQueryItemForVersion(version);
this.PrepareProgress(progress, groups);
this.ExecuteQuery(
query,
parameters,
progress,
groups,
result,
queryItem
);
return(result);
}
/// <summary>
/// The get groups.
/// </summary>
/// <param name="query">
/// The query information.
/// </param>
/// <param name="parameterValues">
/// The parameter values.
/// </param>
/// <param name="version">
/// The version.
/// </param>
/// <returns>
/// The <see cref="Dictionary{TKey,TValue}"/>.
/// </returns>
public virtual ICollection <GroupDefinition> GetGroups(
QueryInfo query,
IEnumerable <ParameterValue> parameterValues,
InstanceVersion version
)
{
var groups = new List <GroupDefinition>();
// TODO: Create GroupDefinition with childrens
QueryItemInfo selectGroupsSql = this.GetQueryForGroups(query, version);
if (selectGroupsSql != null)
{
try
{
var paramsArray = parameterValues as ParameterValue[] ?? parameterValues.ToArray();
DataTable[] groupsTabels = this.ExecuteSqlFunction(
this.Instance,
selectGroupsSql,
null,
query.Parameters,
paramsArray,
null,
true
);
foreach (DataTable groupTable in groupsTabels)
{
this.AddGroupsFromTable(groupTable, groups);
}
if (selectGroupsSql.ChildGroups.IsNullOrEmpty())
{
return(groups);
}
foreach (GroupDefinition groupDefinition in groups)
{
this.GetChildGroupsDefinitions(selectGroupsSql, groupDefinition, paramsArray, version);
}
}
catch (OperationCanceledException ex)
{
log.Error(ex);
throw;
}
catch (Exception ex)
{
log.Error("instance=" + this.Instance.Name + " query=" + query, ex);
}
}
return(groups);
}
internal DataTable[] ExecuteSql(
InstanceInfo instance,
QueryItemInfo sql,
IEnumerable <QueryParameterInfo> parameters = null,
IEnumerable <ParameterValue> parameterValues = null
)
{
bool shouldExecute = true;
if (sql == null)
{
throw new Exception("There is no sql statement to execute (QueryItemInfo == null).");
}
// Log.InfoFormat("instance:'{0}';sql.Text:'{1}'",
// instance,
// sql.Text
// );
if (sql.ExecuteIfSqlText != null)
{
var clone = sql.Clone();
clone.ExecuteIfSqlText = null;
clone.Text = sql.ExecuteIfSqlText;
DataTable[] tt = ExecuteSql(
instance,
clone,
parameters,
parameterValues
);
if (tt.Length > 0 && tt[0].Rows.Count > 0)
{
shouldExecute = (int)(tt[0].Rows[0][0]) == 1;
}
}
if (shouldExecute)
{
return(this.CurrentStorage.ExecuteSql(
sql.Text,
parameters,
parameterValues,
instance
));
}
return(new DataTable[0]);
}
/// <summary>
/// The get child groups definitions.
/// </summary>
/// <param name="parent">The parent.</param>
/// <param name="parentDefinition">The parent definition.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="version">The version.</param>
private void GetChildGroupsDefinitions(
QueryItemInfo parent,
GroupDefinition parentDefinition,
IEnumerable <ParameterValue> parameterValues,
InstanceVersion version
)
{
QueryItemInfo childGroupSql = parent.ChildGroups.GetQueryItemForVersion(version);
var tempParams = parameterValues.ToList();
tempParams.AddRange(this.GetParametersFromDefinition(parentDefinition));
try
{
DataTable[] groupsTabels = this.ExecuteSqlFunction(
this.Instance,
childGroupSql,
null,
childGroupSql.Parameters,
tempParams,
null,
true
);
foreach (DataTable groupTable in groupsTabels)
{
this.AddGroupsFromTable(groupTable, parentDefinition.ChildGroups);
}
if (childGroupSql.ChildGroups == null || childGroupSql.ChildGroups.Count <= 0)
{
return;
}
foreach (GroupDefinition childGroup in parentDefinition.ChildGroups)
{
this.GetChildGroupsDefinitions(childGroupSql, childGroup, tempParams, version);
}
}
catch (OperationCanceledException ex)
{
log.Error(ex);
throw;
}
catch (Exception ex)
{
log.Error("instance=" + this.Instance.Name + " query=" + childGroupSql, ex);
}
}
/// <summary>
/// The prepare.
/// </summary>
/// <param name="queryItem">
/// The query item.
/// </param>
public void PrepareIfNeeds(QueryItemInfo queryItem)
{
if (!this.PreparationNeeded(queryItem))
{
return;
}
this.logger.WriteToLog("Вставка дополнительного SQL");
var additionalSql = this.settings.AdditionalSql.Replace("\r\n", string.Empty);
if (!additionalSql.EndsWith(";"))
{
additionalSql += ";";
}
queryItem.Text = additionalSql + queryItem.Text;
this.logger.WriteToLog("SQL вставлен");
}
/// <inheritdoc />
protected override void ExecuteQuery(
QueryInfo query,
QueryExecutionParams parameters,
QueryInstanceResultInfo result,
QueryItemInfo queryItem,
GroupDefinition group,
ProgressItem subProgress
)
{
var tempValues = new List <ParameterValue>(parameters.Values);
foreach (var parameter in group.GroupParameters)
{
tempValues.Add(new ParameterValue
{
Name = "@" + parameter.Key,
StringValue = parameter.Value
}
);
}
QueryDatabaseResultInfo databaseResult = this.ExecuteQueryItemFunction.Invoke(
this.Instance,
queryItem,
null,
null,
query.Parameters,
tempValues,
subProgress
);
databaseResult.Database = group.Name;
databaseResult.DatabaseId = group.Id;
result.AddDatabaseResult(databaseResult);
}
/// <inheritdoc />
protected override void ExecuteQuery(
QueryInfo query,
QueryExecutionParams parameters,
QueryInstanceResultInfo result,
QueryItemInfo queryItem,
GroupDefinition group,
ProgressItem subProgress
)
{
var tempValues = new List<ParameterValue>(parameters.Values);
foreach (var parameter in group.GroupParameters)
{
tempValues.Add(new ParameterValue
{
Name = "@" + parameter.Key,
StringValue = parameter.Value
}
);
}
QueryDatabaseResultInfo databaseResult = this.ExecuteQueryItemFunction.Invoke(
this.Instance,
queryItem,
null,
null,
query.Parameters,
tempValues,
subProgress
);
databaseResult.Database = group.Name;
databaseResult.DatabaseId = group.Id;
result.AddDatabaseResult(databaseResult);
}
/// <summary>
/// The preparation nedeed.
/// </summary>
/// <returns>
/// The <see cref="bool" />.
/// </returns>
private bool PreparationNeeded(QueryItemInfo queryItem)
{
return((queryItem.ParentQuery.Source == QuerySource.MSSQL || queryItem.ParentQuery.Source == QuerySource.TDSQL) && // #187
!string.IsNullOrEmpty(this.settings.AdditionalSql));
}
/// <summary>
/// The get child groups definitions.
/// </summary>
/// <param name="parent">The parent.</param>
/// <param name="parentDefinition">The parent definition.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="version">The version.</param>
private void GetChildGroupsDefinitions(
QueryItemInfo parent,
GroupDefinition parentDefinition,
IEnumerable<ParameterValue> parameterValues,
InstanceVersion version
)
{
QueryItemInfo childGroupSql = parent.ChildGroups.GetQueryItemForVersion(version);
var tempParams = parameterValues.ToList();
tempParams.AddRange(this.GetParametersFromDefinition(parentDefinition));
try
{
DataTable[] groupsTabels = this.ExecuteSqlFunction(
this.Instance,
childGroupSql,
null,
childGroupSql.Parameters,
tempParams,
null,
true
);
foreach (DataTable groupTable in groupsTabels)
{
this.AddGroupsFromTable(groupTable, parentDefinition.ChildGroups);
}
if (childGroupSql.ChildGroups == null || childGroupSql.ChildGroups.Count <= 0)
{
return;
}
foreach (GroupDefinition childGroup in parentDefinition.ChildGroups)
{
this.GetChildGroupsDefinitions(childGroupSql, childGroup, tempParams, version);
}
}
catch (OperationCanceledException ex)
{
log.Error(ex);
throw;
}
catch (Exception ex)
{
log.Error("instance=" + this.Instance.Name + " query=" + childGroupSql, ex);
}
}
/// <summary>
/// The execute query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="progress">The progress.</param>
/// <param name="groups">The groups.</param>
/// <param name="result">The result.</param>
/// <param name="queryItem">The query item.</param>
private void ExecuteQuery(
QueryInfo query,
QueryExecutionParams parameters,
ProgressItem progress,
GroupDefinition[] groups,
QueryInstanceResultInfo result,
QueryItemInfo queryItem
)
{
// Log.InfoFormat("query:'{0}'",
// query
// );
Debug.Assert(groups.Length <= 1);
if (groups.Length == 0)
{
this.ExecuteQuery(
query,
parameters,
result,
queryItem,
new GroupDefinition(
this.Instance,
string.Empty,
string.Empty
),
progress
);
ProgressComplete(progress);
}
else
{
foreach (var group in groups)
{
if (group.ChildGroups.Count > 0)
{
var tempParams = parameters.Clone();
tempParams.AddValues(
this.GetGroupsResolver().GetParametersFromDefinition(group)
);
this.ExecuteQuery(
query,
tempParams,
progress,
group.ChildGroups.ToArray(),
result,
queryItem
);
}
ProgressItem subProgress = null;
if (progress != null)
{
try
{
subProgress = progress.GetChild();
}
catch (Exception)
{
}
}
this.ExecuteQuery(
query,
parameters,
result,
queryItem,
@group,
subProgress
);
ProgressComplete(subProgress);
}
}
}
private void addQueryItem(string pageName, string controlID, string controlType, string controlValue)
{
QueryItemInfo queryItem = new QueryItemInfo(Session["UserID"].ToString(), pageName, controlID, controlType, controlValue);
queryItems.Add(queryItem);
}
private void ProcessCodeGuardQuery(
MultyQueryResultInfo result,
TemplateNodeSqlGuardQueryInfo guardQueryInfo)
{
var queryResultInfo = result.List.First(item => item.TemplateNodeQuery.Id == guardQueryInfo.SqlQueryId);
var templateNodeQueryInfo = queryResultInfo.TemplateNodeQuery;
var userParams = new List <ParameterValue>();
if (base.Settings != null)
{
var querySettings = base.Settings.Connection.Activity.Parameters
.Where(i => i.Key == guardQueryInfo.IdsHierarchy && i.Value != null);
foreach (var info in querySettings)
{
switch (info.Type)
{
case ParameterInfoType.Attribute:
guardQueryInfo.GetType().GetProperty("User" + info.Parameter)
.SetValue(guardQueryInfo, info.Value, null);
break;
case ParameterInfoType.Parameter:
var parameter =
templateNodeQueryInfo.ParameterValues.FirstOrDefault(p => p.Name == info.Parameter);
if (parameter != null)
{
parameter.UserValue = info.Value;
}
break;
case ParameterInfoType.EditableParameter:
var editparameter = new ParameterValue
{
Name = info.Parameter,
StringValue = info.Default,
UserValue = info.Value
};
userParams.Add(editparameter);
break;
}
}
}
var guardQueryResult = new QueryResultInfo();
foreach (var instanceResult in queryResultInfo.QueryResult.InstancesResult)
{
var instance = instanceResult.Key;
var queryTable = instanceResult.Value.DatabasesResult.First().Value.DataTables.First();
if (!queryTable.Columns.Contains(guardQueryInfo.QueryCodeColumn))
{
continue;
}
//var meta = ReadMeta(connectionGroup, templateNode, instance, database, templateNodeQueryInfo).FirstOrDefault();
var meta = Storage.ReadLastMeta(
base.TemplateNode,
instance,
templateNodeQueryInfo
);
if (meta == null)
{
continue;
}
QueryInstanceResultInfo guardInstanceResult;
var timestamp = (DateTime)meta.Values[TableDefinition.DateCreated];
result.RefreshTimestamp(timestamp);
if (!string.IsNullOrEmpty(meta.Values[MetaResultTable.ErrorMessageFieldName].ToString()))
{
guardInstanceResult = new QueryInstanceResultInfo(
new ErrorInfo(
meta.Values[MetaResultTable.ErrorIdFieldName].ToString(),
meta.Values[MetaResultTable.ErrorCodeFieldName].ToString(),
meta.Values[MetaResultTable.ErrorMessageFieldName].ToString(),
timestamp
),
instance
);
}
else
{
guardInstanceResult = new QueryInstanceResultInfo(instance);
}
var dataTables = Storage.ReadSqlCodeGuardResult(guardQueryInfo, queryTable, userParams);
QueryItemInfo queryItemInfo = new QueryItemInfo
{
ParentQuery = new QueryInfo {
Name = guardQueryInfo.QueryName
}
};
QueryDatabaseResultInfo databaseResult = new QueryDatabaseResultInfo(
dataTables,
queryItemInfo,
base.GroupDefinition.Name,
base.GroupDefinition.Id
);
guardInstanceResult.AddDatabaseResult(databaseResult);
guardQueryResult.AddInstanceResult(guardInstanceResult);
}
var templateNodeResultItem = new TemplateNodeResultItem(guardQueryInfo, guardQueryResult);
result.Add(templateNodeResultItem);
}
/// <summary>
/// The execute query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="progress">The progress.</param>
/// <param name="groups">The groups.</param>
/// <param name="result">The result.</param>
/// <param name="queryItem">The query item.</param>
private void ExecuteQuery(
QueryInfo query,
QueryExecutionParams parameters,
ProgressItem progress,
GroupDefinition[] groups,
QueryInstanceResultInfo result,
QueryItemInfo queryItem
)
{
// Log.InfoFormat("query:'{0}'",
// query
// );
Debug.Assert(groups.Length <= 1);
if (groups.Length == 0)
{
this.ExecuteQuery(
query,
parameters,
result,
queryItem,
new GroupDefinition(
this.Instance,
string.Empty,
string.Empty
),
progress
);
ProgressComplete(progress);
}
else
{
foreach (var group in groups)
{
if (group.ChildGroups.Count > 0)
{
var tempParams = parameters.Clone();
tempParams.AddValues(
this.GetGroupsResolver().GetParametersFromDefinition(group)
);
this.ExecuteQuery(
query,
tempParams,
progress,
group.ChildGroups.ToArray(),
result,
queryItem
);
}
ProgressItem subProgress = null;
if (progress != null)
{
try
{
subProgress = progress.GetChild();
}
catch (Exception)
{
}
}
this.ExecuteQuery(
query,
parameters,
result,
queryItem,
@group,
subProgress
);
ProgressComplete(subProgress);
}
}
}
/// <summary>
/// The process group.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <param name="result">The result.</param>
/// <param name="queryItem">The query item.</param>
/// <param name="group">The group.</param>
/// <param name="subProgress">The sub progress.</param>
protected virtual void ExecuteQuery(
QueryInfo query,
QueryExecutionParams parameters,
QueryInstanceResultInfo result,
QueryItemInfo queryItem,
GroupDefinition group,
ProgressItem subProgress
)
{
// Log.InfoFormat("146:query:'{0}'",
// query
// );
var qr = this.ExecuteQueryItemFunction.Invoke(
this.Instance,
queryItem,
string.IsNullOrWhiteSpace(group.Name) ? parameters.DefaultDatabase : group.Name,
//group.Name,
group.Id,
query.Parameters,
parameters.Values.ToList(),
subProgress
);
// костыль. Наверное, прибъется вообще после убиения "групп"
qr.Database = group.Name;
result.AddDatabaseResult(qr);
}
private void SaveHistoryData(
QueryInfo query,
InstanceInfo instance,
long queryId,
TemplateNodeQueryInfo templateNodeQuery
)
{
QueryInfo queryInfo = new QueryInfo {
Source = QuerySource.SQLite
};
Regex regex = new Regex(@"\[\$\{(?<QueryName>[\w]+)\}\$_\$\{(?<RecordSetNumber>[\w]+)\}\$\]");
string strQueryName = String.Empty;
Int64 intRecordSetNumber = 0L;
string strReplacedTableName = String.Empty;
if (query.FillStatementList == null)
{
return;
}
List <HistoryFillStatement> list = query.FillStatementList.GetSortedStatements();
List <QueryParameterInfo> parameters = new List <QueryParameterInfo>
{
new QueryParameterInfo
{
Name = _QueryIdParameterName,
Type = SqlDbType.BigInt
}
};
List <ParameterValue> paramterValues = new List <ParameterValue>
{
new ParameterValue
{
Name = _QueryIdParameterName,
StringValue = queryId.ToString()
}
};
// string newTableName = String.Format("[{0}]",
// this.GetTableName(templateNodeQuery, recordSet) ?? "_unknown_table_"
// );
// string oldTableName = "[${" + query.Name + "}$_${" + recordSet + "}$]";
// string oldTableName = String.Format("[${{{0}}}$_${{{1}}}$]",
// query.Name ?? "_unknown_table_",
// recordSet
//);
// Log.DebugFormat("oldTableName:'{0}',newTableName:'{1}'",
// oldTableName,
// newTableName
//);
foreach (HistoryFillStatement statement in list)
{
QueryItemInfo queryItem = new QueryItemInfo();
queryItem.ParentQuery = queryInfo;
queryItem.Text = statement.Text;
// queryItem.Text = statement.Text.Replace(oldTableName, newTableName);
// Regex regex = new Regex("[\$\{([\w]+)\}\$_\$\{([\w]+)\}\$]");
// Log.InfoFormat("regex:'{0}'",
// regex
// );
var results = regex.Matches(statement.Text);
foreach (Match match in results)
{
// Log.InfoFormat("match:'{0}';match.Value:'{1}'",
// match,
// match.Value
// );
strQueryName = match.Groups["QueryName"].Value;
intRecordSetNumber = Int64.Parse(match.Groups["RecordSetNumber"].Value);
// Log.InfoFormat("strQueryName:'{0}';intRecordSetNumber:'{1}'",
// strQueryName,
// intRecordSetNumber
// );
if (String.Equals(strQueryName, query.Name, StringComparison.OrdinalIgnoreCase))
{
// Log.InfoFormat("matches:strQueryName:'{0}';query.Name:'{1}'",
// strQueryName,
// query.Name
// );
strReplacedTableName = string.Format(
"[{0}]",
this.GetTableName(
templateNodeQuery,
intRecordSetNumber
)
);
// Log.InfoFormat("strReplacedTableName:'{0}'",
// strReplacedTableName
// );
// Log.InfoFormat("queryItem.Text:'{0}'",
// queryItem.Text
// );
queryItem.Text = queryItem.Text.Replace(
match.Value,
strReplacedTableName
);
// Log.InfoFormat("queryItem.Text:'{0}'",
// queryItem.Text
// );
}
}
this.ExecuteSql(
instance,
queryItem,
parameters,
paramterValues
);
}
}