/// <summary>
/// Get insert execute DbCommand
/// </summary>
/// <param name="translator">Translator</param>
/// <param name="command">Command</param>
/// <returns>Return insert execute command</returns>
DatabaseExecuteCommand GetInsertExecuteDbCommand(IQueryTranslator translator, RdbCommand command)
{
string objectName = DataManager.GetEntityObjectName(DatabaseServerType.SQLServer, command.EntityType, command.ObjectName);
var fields = DataManager.GetEditFields(DatabaseServerType.SQLServer, command.EntityType);
var fieldCount = fields.GetCount();
var insertFormatResult = SqlServerFactory.FormatInsertFields(fieldCount, fields, command.Parameters, translator.ParameterSequence);
if (insertFormatResult == null)
{
return(null);
}
string cmdText = $"INSERT INTO {SqlServerFactory.WrapKeyword(objectName)} ({string.Join(",", insertFormatResult.Item1)}) VALUES ({string.Join(",", insertFormatResult.Item2)});";
CommandParameters parameters = insertFormatResult.Item3;
translator.ParameterSequence += fieldCount;
return(new DatabaseExecuteCommand()
{
CommandText = cmdText,
CommandType = SqlServerFactory.GetCommandType(command),
ForceReturnValue = command.MustReturnValueOnSuccess,
Parameters = parameters
});
}
/// <summary>
/// Query datas
/// </summary>
/// <typeparam name="T">Data type</typeparam>
/// <param name="server">Database server</param>
/// <param name="command">Command</param>
/// <returns>Return datas</returns>
public async Task <IEnumerable <T> > QueryAsync <T>(DatabaseServer server, ICommand command)
{
if (command.Query == null)
{
throw new EZNEWException("ICommand.Query is null");
}
#region query translate
IQueryTranslator translator = SqlServerFactory.GetQueryTranslator(server);
var tranResult = translator.Translate(command.Query);
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region script
string cmdText;
switch (command.Query.QueryType)
{
case QueryCommandType.Text:
cmdText = tranResult.ConditionString;
break;
case QueryCommandType.QueryObject:
default:
int size = command.Query.QuerySize;
string objectName = DataManager.GetEntityObjectName(DatabaseServerType.SQLServer, command.EntityType, command.ObjectName);
string orderString = string.IsNullOrWhiteSpace(tranResult.OrderString) ? string.Empty : $"ORDER BY {tranResult.OrderString}";
var queryFields = SqlServerFactory.GetQueryFields(command.Query, command.EntityType, true);
string outputFormatedField = string.Join(",", SqlServerFactory.FormatQueryFields(translator.ObjectPetName, queryFields, true));
if (string.IsNullOrWhiteSpace(tranResult.CombineScript))
{
cmdText = $"{preScript}SELECT {(size > 0 ? $"TOP {size}" : string.Empty)} {outputFormatedField} FROM {SqlServerFactory.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {orderString}";
}
/// <summary>
/// Execute command
/// </summary>
/// <param name="server">Server</param>
/// <param name="executeOption">Execute option</param>
/// <param name="commands">Commands</param>
/// <returns>Return effect data numbers</returns>
public async Task <int> ExecuteAsync(DatabaseServer server, CommandExecuteOptions executeOption, IEnumerable <ICommand> commands)
{
#region group execute commands
IQueryTranslator translator = SqlServerFactory.GetQueryTranslator(server);
List <DatabaseExecuteCommand> executeCommands = new List <DatabaseExecuteCommand>();
var batchExecuteConfig = DataManager.GetBatchExecuteConfiguration(server.ServerType) ?? BatchExecuteConfiguration.Default;
var groupStatementsCount = batchExecuteConfig.GroupStatementsCount;
groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount;
var groupParameterCount = batchExecuteConfig.GroupParametersCount;
groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount;
StringBuilder commandTextBuilder = new StringBuilder();
CommandParameters parameters = null;
int statementsCount = 0;
bool forceReturnValue = false;
int cmdCount = 0;
DatabaseExecuteCommand GetGroupExecuteCommand()
{
var executeCommand = new DatabaseExecuteCommand()
{
CommandText = commandTextBuilder.ToString(),
CommandType = CommandType.Text,
ForceReturnValue = forceReturnValue,
Parameters = parameters
};
statementsCount = 0;
translator.ParameterSequence = 0;
commandTextBuilder.Clear();
parameters = null;
forceReturnValue = false;
return(executeCommand);
}
foreach (var cmd in commands)
{
DatabaseExecuteCommand executeCommand = GetExecuteDbCommand(translator, cmd as RdbCommand);
if (executeCommand == null)
{
continue;
}
//Trace log
SqlServerFactory.LogExecuteCommand(executeCommand);
cmdCount++;
if (executeCommand.PerformAlone)
{
if (statementsCount > 0)
{
executeCommands.Add(GetGroupExecuteCommand());
}
executeCommands.Add(executeCommand);
continue;
}
commandTextBuilder.AppendLine(executeCommand.CommandText);
parameters = parameters == null ? executeCommand.Parameters : parameters.Union(executeCommand.Parameters);
forceReturnValue |= executeCommand.ForceReturnValue;
statementsCount++;
if (translator.ParameterSequence >= groupParameterCount || statementsCount >= groupStatementsCount)
{
executeCommands.Add(GetGroupExecuteCommand());
}
}
if (statementsCount > 0)
{
executeCommands.Add(GetGroupExecuteCommand());
}
#endregion
return(await ExecuteCommandAsync(server, executeOption, executeCommands, executeOption?.ExecuteByTransaction ?? cmdCount > 1).ConfigureAwait(false));
}
/// <summary>
/// Get update execute command
/// </summary>
/// <param name="translator">Translator</param>
/// <param name="command">Command</param>
/// <returns>Return update execute command</returns>
DatabaseExecuteCommand GetUpdateExecuteDbCommand(IQueryTranslator translator, RdbCommand command)
{
#region query translate
var tranResult = translator.Translate(command.Query);
string conditionString = string.Empty;
if (!string.IsNullOrWhiteSpace(tranResult.ConditionString))
{
conditionString = $"WHERE {tranResult.ConditionString}";
}
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region script
CommandParameters parameters = SqlServerFactory.ParseParameters(command.Parameters) ?? new CommandParameters();
string objectName = DataManager.GetEntityObjectName(DatabaseServerType.SQLServer, command.EntityType, command.ObjectName);
var fields = SqlServerFactory.GetFields(command.EntityType, command.Fields);
int parameterSequence = translator.ParameterSequence;
List <string> updateSetArray = new List <string>();
foreach (var field in fields)
{
var parameterValue = parameters.GetParameterValue(field.PropertyName);
var parameterName = field.PropertyName;
string newValueExpression = string.Empty;
if (parameterValue != null)
{
parameterSequence++;
parameterName = SqlServerFactory.FormatParameterName(parameterName, parameterSequence);
parameters.Rename(field.PropertyName, parameterName);
if (parameterValue is IModifyValue)
{
var modifyValue = parameterValue as IModifyValue;
parameters.ModifyValue(parameterName, modifyValue.Value);
if (parameterValue is CalculateModifyValue)
{
var calculateModifyValue = parameterValue as CalculateModifyValue;
string calChar = SqlServerFactory.GetCalculateChar(calculateModifyValue.Operator);
newValueExpression = $"{translator.ObjectPetName}.{SqlServerFactory.WrapKeyword(field.FieldName)}{calChar}{SqlServerFactory.ParameterPrefix}{parameterName}";
}
}
}
if (string.IsNullOrWhiteSpace(newValueExpression))
{
newValueExpression = $"{SqlServerFactory.ParameterPrefix}{parameterName}";
}
updateSetArray.Add($"{translator.ObjectPetName}.{SqlServerFactory.WrapKeyword(field.FieldName)}={newValueExpression}");
}
string cmdText = $"{preScript}UPDATE {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} FROM {SqlServerFactory.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {conditionString};";
translator.ParameterSequence = parameterSequence;
#endregion
#region parameter
var queryParameters = SqlServerFactory.ParseParameters(tranResult.Parameters);
parameters.Union(queryParameters);
#endregion
return(new DatabaseExecuteCommand()
{
CommandText = cmdText,
CommandType = SqlServerFactory.GetCommandType(command),
ForceReturnValue = command.MustReturnValueOnSuccess,
Parameters = parameters,
HasPreScript = !string.IsNullOrWhiteSpace(preScript)
});
}
/// <summary>
/// Execute commands
/// </summary>
/// <param name="server">db server</param>
/// <param name="executeCommands">execute commands</param>
/// <param name="useTransaction">use transaction</param>
/// <returns>Return effect data numbers</returns>
async Task <int> ExecuteCommandAsync(DatabaseServer server, CommandExecuteOptions executeOption, IEnumerable <DatabaseExecuteCommand> executeCommands, bool useTransaction)
{
int resultValue = 0;
bool success = true;
using (var conn = SqlServerFactory.GetConnection(server))
{
IDbTransaction transaction = null;
if (useTransaction)
{
transaction = SqlServerFactory.GetExecuteTransaction(conn, executeOption);
}
try
{
foreach (var cmd in executeCommands)
{
var cmdDefinition = new CommandDefinition(cmd.CommandText, SqlServerFactory.ConvertCmdParameters(cmd.Parameters), transaction: transaction, commandType: cmd.CommandType, cancellationToken: executeOption?.CancellationToken ?? default);
var executeResultValue = await conn.ExecuteAsync(cmdDefinition).ConfigureAwait(false);
success = success && (cmd.ForceReturnValue ? executeResultValue > 0 : true);
resultValue += executeResultValue;
if (useTransaction && !success)
{
break;
}
}
if (!useTransaction)
{
return(resultValue);
}
if (success)
{
transaction.Commit();
}
else
{
resultValue = 0;
transaction.Rollback();
}
return(resultValue);
}
catch (Exception ex)
{
resultValue = 0;
transaction?.Rollback();
throw ex;
}
}
}
/// <summary>
/// Execute Translate
/// </summary>
/// <param name="query">Query object</param>
/// <param name="paras">Parameters</param>
/// <param name="objectName">Query object name</param>
/// <returns>Return translate result</returns>
public TranslateResult ExecuteTranslate(IQuery query, CommandParameters paras = null, string objectName = "", bool subQuery = false, bool useOrder = true)
{
if (query == null)
{
return(TranslateResult.Empty);
}
StringBuilder conditionBuilder = new StringBuilder();
if (query.QueryType == QueryCommandType.QueryObject)
{
StringBuilder orderBuilder = new StringBuilder();
CommandParameters parameters = paras ?? new CommandParameters();
objectName = string.IsNullOrWhiteSpace(objectName) ? ObjPetName : objectName;
List <string> withScripts = new List <string>();
string recurveTableName = string.Empty;
string recurveTablePetName = string.Empty;
#region query condition
if (!query.Criterias.IsNullOrEmpty())
{
int index = 0;
foreach (var queryItem in query.Criterias)
{
var queryItemCondition = TranslateCondition(query, queryItem, parameters, objectName);
if (!queryItemCondition.WithScripts.IsNullOrEmpty())
{
withScripts.AddRange(queryItemCondition.WithScripts);
recurveTableName = queryItemCondition.RecurveObjectName;
recurveTablePetName = queryItemCondition.RecurvePetName;
}
conditionBuilder.Append($" {(index > 0 ? queryItem.Item1.ToString() : string.Empty)} {queryItemCondition.ConditionString}");
index++;
}
}
#endregion
#region sort
if (useOrder && !query.Orders.IsNullOrEmpty())
{
foreach (var orderItem in query.Orders)
{
orderBuilder.Append($"{ConvertOrderCriteriaName(query, objectName, orderItem)} {(orderItem.Desc ? DescKeyWord : AscKeyWord)},");
}
}
#endregion
#region combine
StringBuilder combineBuilder = new StringBuilder();
if (!query.CombineItems.IsNullOrEmpty())
{
foreach (var combine in query.CombineItems)
{
if (combine?.CombineQuery == null)
{
continue;
}
var combineObjectPetName = GetNewSubObjectPetName();
string combineObjectName = DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, combine.CombineQuery);
var combineQueryResult = ExecuteTranslate(combine.CombineQuery, parameters, combineObjectPetName, true, true);
string combineConditionString = string.IsNullOrWhiteSpace(combineQueryResult.ConditionString) ? string.Empty : $"WHERE {combineQueryResult.ConditionString}";
combineBuilder.Append($" {GetCombineOperator(combine.CombineType)} SELECT {string.Join(",", SqlServerFactory.FormatQueryFields(combineObjectPetName, query, query.GetEntityType(), true, false))} FROM {SqlServerFactory.WrapKeyword(combineObjectName)} AS {combineObjectPetName} {(combineQueryResult.AllowJoin ? combineQueryResult.JoinScript : string.Empty)} {combineConditionString}");
if (!combineQueryResult.WithScripts.IsNullOrEmpty())
{
withScripts.AddRange(combineQueryResult.WithScripts);
recurveTableName = combineQueryResult.RecurveObjectName;
recurveTablePetName = combineQueryResult.RecurvePetName;
}
}
}
#endregion
#region join
bool allowJoin = true;
StringBuilder joinBuilder = new StringBuilder();
if (!query.JoinItems.IsNullOrEmpty())
{
foreach (var joinItem in query.JoinItems)
{
if (joinItem == null || joinItem.JoinQuery == null)
{
continue;
}
if (joinItem.JoinQuery.GetEntityType() == null)
{
throw new EZNEWException("IQuery object must set entity type if use in join operation");
}
string joinObjName = GetNewSubObjectPetName();
var joinQueryResult = ExecuteTranslate(joinItem.JoinQuery, parameters, joinObjName, true, true);
if (string.IsNullOrWhiteSpace(joinQueryResult.CombineScript))
{
var joinConnection = GetJoinCondition(query, joinItem, objectName, joinObjName);
if (!string.IsNullOrWhiteSpace(joinQueryResult.ConditionString))
{
if (joinQueryResult.AllowJoin && PositionJoinConditionToConnection(joinItem.JoinType))
{
joinConnection += $"{(string.IsNullOrWhiteSpace(joinConnection) ? " ON" : " AND ")}{joinQueryResult.ConditionString}";
}
else
{
conditionBuilder.Append($"{(conditionBuilder.Length == 0 ? string.Empty : " AND ")}{joinQueryResult.ConditionString}");
}
}
joinBuilder.Append($" {GetJoinOperator(joinItem.JoinType)} {SqlServerFactory.WrapKeyword(DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, joinItem.JoinQuery))} AS {joinObjName}{joinConnection}");
if (joinQueryResult.AllowJoin && !string.IsNullOrWhiteSpace(joinQueryResult.JoinScript))
{
joinBuilder.Append($" {joinQueryResult.JoinScript}");
}
}
else
{
var combineJoinObjName = GetNewSubObjectPetName();
var joinConnection = GetJoinCondition(query, joinItem, objectName, combineJoinObjName);
joinBuilder.Append($" {GetJoinOperator(joinItem.JoinType)} (SELECT {string.Join(",", SqlServerFactory.FormatQueryFields(joinObjName, joinItem.JoinQuery, joinItem.JoinQuery.GetEntityType(), true, false))} FROM {SqlServerFactory.WrapKeyword(DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, joinItem.JoinQuery))} AS {joinObjName} {(joinQueryResult.AllowJoin ? joinQueryResult.JoinScript : string.Empty)} {(string.IsNullOrWhiteSpace(joinQueryResult.ConditionString) ? string.Empty : "WHERE " + joinQueryResult.ConditionString)} {joinQueryResult.CombineScript}) AS {combineJoinObjName}{joinConnection}");
}
if (!joinQueryResult.WithScripts.IsNullOrEmpty())
{
withScripts.AddRange(joinQueryResult.WithScripts);
recurveTableName = joinQueryResult.RecurveObjectName;
recurveTablePetName = joinQueryResult.RecurvePetName;
}
}
}
string joinScript = joinBuilder.ToString();
#endregion
#region recurve script
string conditionString = conditionBuilder.ToString();
if (query.RecurveCriteria != null)
{
allowJoin = false;
string nowConditionString = conditionString;
EntityField recurveField = DataManager.GetField(DatabaseServerType.SQLServer, query, query.RecurveCriteria.Key);
EntityField recurveRelationField = DataManager.GetField(DatabaseServerType.SQLServer, query, query.RecurveCriteria.RelationKey);
var recurveTable = GetNewRecurveTableName();
recurveTablePetName = recurveTable.Item1;
recurveTableName = recurveTable.Item2;
conditionString = $"{objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)} IN (SELECT {recurveTablePetName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)} FROM {SqlServerFactory.WrapKeyword(recurveTableName)} AS {recurveTablePetName})";
string queryObjectName = DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, query);
string withScript =
$"{recurveTableName} AS (SELECT {objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)},{objectName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)} FROM {SqlServerFactory.WrapKeyword(queryObjectName)} AS {objectName} {joinScript} {(string.IsNullOrWhiteSpace(nowConditionString) ? string.Empty : $"WHERE {nowConditionString}")} " +
$"UNION ALL SELECT {objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)},{objectName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)} FROM {SqlServerFactory.WrapKeyword(queryObjectName)} AS {objectName},{recurveTableName} AS {recurveTablePetName} " +
$"WHERE {(query.RecurveCriteria.Direction == RecurveDirection.Up ? $"{objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)}={recurveTablePetName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)}" : $"{objectName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)}={recurveTablePetName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)}")})";