/// <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.Empty; string wrapObjectName = SQLiteManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {translator.ObjectPetName} {conditionString};"; } else { var primaryKeyFields = DataManager.GetFields(CurrentDatabaseServerType, command.EntityType, EntityManager.GetPrimaryKeys(command.EntityType)).ToList(); if (primaryKeyFields.IsNullOrEmpty()) { throw new EZNEWException($"{command.EntityType?.FullName} not set primary key"); } string deleteTableShortName = "DTB"; cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {deleteTableShortName} WHERE {string.Join("||", primaryKeyFields.Select(pk => deleteTableShortName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} IN (SELECT {string.Join("||", primaryKeyFields.Select(pk => translator.ObjectPetName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString});"; } #endregion #region parameter CommandParameters parameters = SQLiteManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); var queryParameters = SQLiteManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = SQLiteManager.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 = SQLiteManager.ConvertParameter(command.Parameters), CommandType = SQLiteManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, HasPreScript = true }); } if (command.ExecutionMode == CommandExecutionMode.CommandText) { return(GetTextCommand()); } DatabaseExecutionCommand databaseExecutionCommand; switch (command.OperationType) { case CommandOperationType.Insert: databaseExecutionCommand = GetDatabaseInsertionCommand(queryTranslator, command); break; case CommandOperationType.Update: databaseExecutionCommand = GetDatabaseUpdateCommand(queryTranslator, command); break; case CommandOperationType.Delete: databaseExecutionCommand = GetDatabaseDeletionCommand(queryTranslator, command); break; default: databaseExecutionCommand = GetTextCommand(); break; } return(databaseExecutionCommand); }
/// <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 = SQLiteManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); var fields = SQLiteManager.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 = SQLiteManager.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 = SQLiteManager.GetSystemCalculationOperator(calculateModifyValue.Operator); newValueExpression = $"{translator.ObjectPetName}.{SQLiteManager.WrapKeyword(field.FieldName)}{calChar}{SQLiteManager.ParameterPrefix}{parameterName}"; } } } if (string.IsNullOrWhiteSpace(newValueExpression)) { newValueExpression = $"{SQLiteManager.ParameterPrefix}{parameterName}"; } updateSetArray.Add($"{SQLiteManager.WrapKeyword(field.FieldName)}={newValueExpression}"); } string cmdText = string.Empty; string wrapObjectName = SQLiteManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}UPDATE {wrapObjectName} AS {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} {conditionString};"; } else { var primaryKeyFields = DataManager.GetFields(CurrentDatabaseServerType, command.EntityType, EntityManager.GetPrimaryKeys(command.EntityType)).ToList(); if (primaryKeyFields.IsNullOrEmpty()) { throw new EZNEWException($"{command.EntityType?.FullName} not set primary key"); } string updateTableShortName = "UTB"; cmdText = $"{preScript}UPDATE {wrapObjectName} AS {updateTableShortName} SET {string.Join(",", updateSetArray)} WHERE {string.Join("||", primaryKeyFields.Select(pk => updateTableShortName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} IN (SELECT {string.Join("||", primaryKeyFields.Select(pk => translator.ObjectPetName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString});"; } translator.ParameterSequence = parameterSequence; #endregion #region parameter var queryParameters = SQLiteManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = SQLiteManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }