/// <summary> /// Get database deletion command /// </summary> /// <param name="translator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database deletion command</returns> DatabaseExecutionCommand GetDatabaseDeletionCommand(IQueryTranslator translator, DefaultCommand command) { translator.DataAccessContext.SetCommand(command); #region query translation 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 string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); string cmdText; string wrapObjectName = PostgreSqlManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {translator.ObjectPetName} {conditionString};"; } else { string deleteTableShortName = "DTB"; string deleteJoinTableShortName = "DJTB"; var primaryKeyFormatedResult = FormatWrapJoinPrimaryKeys(command.EntityType, translator.ObjectPetName, deleteTableShortName, deleteJoinTableShortName); cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {deleteTableShortName} USING (SELECT {string.Join(",", primaryKeyFormatedResult.Item1)} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString}) AS {deleteJoinTableShortName} WHERE {string.Join(" AND ", primaryKeyFormatedResult.Item2)};"; } #endregion #region parameter CommandParameters parameters = PostgreSqlManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); var queryParameters = PostgreSqlManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = PostgreSqlManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }
/// <summary> /// Get database execution command /// </summary> /// <param name="queryTranslator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database execution command</returns> DatabaseExecutionCommand GetDatabaseExecutionCommand(IQueryTranslator queryTranslator, DefaultCommand command) { DatabaseExecutionCommand GetTextCommand() { return(new DatabaseExecutionCommand() { CommandText = command.Text, Parameters = PostgreSqlManager.ConvertParameter(command.Parameters), CommandType = PostgreSqlManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, HasPreScript = true }); } if (command.ExecutionMode == CommandExecutionMode.CommandText) { return(GetTextCommand()); } DatabaseExecutionCommand executionCommand; switch (command.OperationType) { case CommandOperationType.Insert: executionCommand = GetDatabaseInsertionCommand(queryTranslator, command); break; case CommandOperationType.Update: executionCommand = GetDatabaseUpdateCommand(queryTranslator, command); break; case CommandOperationType.Delete: executionCommand = GetDatabaseDeletionCommand(queryTranslator, command); break; default: executionCommand = GetTextCommand(); break; } return(executionCommand); }
/// <summary> /// Get database insertion execution command /// </summary> /// <param name="translator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database insertion command</returns> DatabaseExecutionCommand GetDatabaseInsertionCommand(IQueryTranslator translator, DefaultCommand command) { translator.DataAccessContext.SetCommand(command); string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); var fields = DataManager.GetEditFields(CurrentDatabaseServerType, command.EntityType); var fieldCount = fields.GetCount(); var insertFormatResult = PostgreSqlManager.FormatInsertionFields(command.EntityType, fieldCount, fields, command.Parameters, translator.ParameterSequence); if (insertFormatResult == null) { return(null); } string cmdText = $"INSERT INTO {PostgreSqlManager.WrapKeyword(objectName)} ({string.Join(",", insertFormatResult.Item1)}) VALUES ({string.Join(",", insertFormatResult.Item2)});"; CommandParameters parameters = insertFormatResult.Item3; translator.ParameterSequence += fieldCount; return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = PostgreSqlManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters }); }
/// <summary> /// Get database update command /// </summary> /// <param name="translator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database update command</returns> DatabaseExecutionCommand GetDatabaseUpdateCommand(IQueryTranslator translator, DefaultCommand command) { if (command?.Fields.IsNullOrEmpty() ?? true) { throw new EZNEWException($"No fields are set to update"); } #region query translation translator.DataAccessContext.SetCommand(command); 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 = PostgreSqlManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); var fields = PostgreSqlManager.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 = PostgreSqlManager.FormatParameterName(parameterName, parameterSequence); parameters.Rename(field.PropertyName, parameterName); if (parameterValue is IModificationValue) { var modifyValue = parameterValue as IModificationValue; parameters.ModifyValue(parameterName, modifyValue.Value); if (parameterValue is CalculationModificationValue) { var calculateModifyValue = parameterValue as CalculationModificationValue; string calChar = PostgreSqlManager.GetSystemCalculationOperator(calculateModifyValue.Operator); newValueExpression = $"{translator.ObjectPetName}.{PostgreSqlManager.WrapKeyword(field.FieldName)}{calChar}{PostgreSqlManager.ParameterPrefix}{parameterName}"; } } } if (string.IsNullOrWhiteSpace(newValueExpression)) { newValueExpression = $"{PostgreSqlManager.ParameterPrefix}{parameterName}"; } updateSetArray.Add($"{PostgreSqlManager.WrapKeyword(field.FieldName)}={newValueExpression}"); } string cmdText; string wrapObjName = PostgreSqlManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}UPDATE {wrapObjName} AS {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} {conditionString};"; } else { string updateTableShortName = translator.ObjectPetName; string updateJoinTableShortName = "UJTB"; var primaryKeyFormatedResult = FormatWrapJoinPrimaryKeys(command.EntityType, translator.ObjectPetName, updateTableShortName, updateJoinTableShortName); cmdText = $"{preScript}UPDATE {wrapObjName} AS {updateTableShortName} SET {string.Join(",", updateSetArray)} FROM (SELECT {string.Join(",", primaryKeyFormatedResult.Item1)} FROM {wrapObjName} AS {translator.ObjectPetName} {joinScript} {conditionString}) AS {updateJoinTableShortName} WHERE {string.Join(" AND ", primaryKeyFormatedResult.Item2)};"; } translator.ParameterSequence = parameterSequence; #endregion #region parameter var queryParameters = PostgreSqlManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = PostgreSqlManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }