/// <summary>
/// format insert fields
/// </summary>
/// <param name="fields">fields</param>
/// <param name="originParameters">origin parameters</param>
/// <returns>first:fields,second:parameter fields,third:parameters</returns>
Tuple <List <string>, List <string>, CmdParameters> FormatInsertFields(List <EntityField> fields, object parameters, int parameterSequence)
{
if (fields.IsNullOrEmpty())
{
return(null);
}
List <string> formatFields = new List <string>(fields.Count);
List <string> parameterFields = new List <string>(fields.Count);
CmdParameters cmdParameters = ParseParameters(parameters);
string key = ((int)ServerType.MySQL).ToString();
foreach (var field in fields)
{
//fields
var formatValue = field.GetEditFormat(key);
if (formatValue.IsNullOrEmpty())
{
formatValue = string.Format("`{0}`", field.FieldName);
field.SetEditFormat(key, formatValue);
}
formatFields.Add(formatValue);
//parameter name
parameterSequence++;
string parameterName = field.PropertyName + parameterSequence;
parameterFields.Add("?" + parameterName);
//parameter value
cmdParameters?.Rename(field.PropertyName, parameterName);
}
return(new Tuple <List <string>, List <string>, CmdParameters>(formatFields, parameterFields, cmdParameters));
}
/// <summary>
/// get insert execute DbCommand
/// </summary>
/// <param name="translator">translator</param>
/// <param name="cmd">cmd</param>
/// <returns></returns>
DbExecuteCommand GetInsertExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
{
string cmdText = string.Empty;
CmdParameters parameters = null;
CommandType commandType = GetCommandType(cmd);
if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
{
cmdText = cmd.CommandText;
parameters = ParseParameters(cmd.Parameters);
}
else
{
string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
var fields = DataManager.GetEditFields(ServerType.SQLServer, cmd.EntityType);
var insertFormatResult = FormatInsertFields(fields, cmd.Parameters, translator.ParameterSequence);
if (insertFormatResult == null)
{
return(null);
}
cmdText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});"
, objectName
, string.Join(",", insertFormatResult.Item1)
, string.Join(",", insertFormatResult.Item2));
parameters = insertFormatResult.Item3;
translator.ParameterSequence += fields.Count;
}
return(new DbExecuteCommand()
{
CommandText = cmdText,
CommandType = commandType,
ForceReturnValue = cmd.MustReturnValueOnSuccess,
Parameters = parameters
});
}
/// <summary>
/// get delete execute command
/// </summary>
/// <param name="translator">translator</param>
/// <param name="cmd">cmd</param>
/// <returns></returns>
DbExecuteCommand GetDeleteExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
{
#region query translate
var tranResult = translator.Translate(cmd.Query);
string conditionString = string.Empty;
if (!tranResult.ConditionString.IsNullOrEmpty())
{
conditionString += "WHERE " + tranResult.ConditionString;
}
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
string cmdText = string.Empty;
CmdParameters parameters = ParseParameters(cmd.Parameters);
if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
{
cmdText = cmd.CommandText;
}
else
{
string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
cmdText = string.Format("{3}DELETE {0} FROM `{1}` AS {0}{4} {2};"
, translator.ObjectPetName
, objectName
, conditionString
, preScript
, joinScript);
}
//combine parameters
if (tranResult.Parameters != null)
{
var queryParameters = ParseParameters(tranResult.Parameters);
if (parameters != null)
{
parameters.Union(queryParameters);
}
else
{
parameters = queryParameters;
}
}
CommandType commandType = GetCommandType(cmd);
return(new DbExecuteCommand()
{
CommandText = cmdText,
CommandType = commandType,
ForceReturnValue = cmd.MustReturnValueOnSuccess,
Parameters = parameters,
HasPreScript = !string.IsNullOrWhiteSpace(preScript)
});
}
/// <summary>
/// convert cmd parameters
/// </summary>
/// <param name="cmdParameters">cmd parameters</param>
/// <returns></returns>
DynamicParameters ConvertCmdParameters(CmdParameters cmdParameters)
{
if (cmdParameters?.Parameters.IsNullOrEmpty() ?? true)
{
return(null);
}
DynamicParameters dynamicParameters = new DynamicParameters();
foreach (var item in cmdParameters.Parameters)
{
var parameter = item.Value;
dynamicParameters.Add(parameter.Name, parameter.Value
, parameter.DbType, parameter.ParameterDirection
, parameter.Size, parameter.Precision
, parameter.Scale);
}
return(dynamicParameters);
}
/// <summary>
/// parse parameter
/// </summary>
/// <param name="originParameters">origin parameter</param>
/// <returns></returns>
CmdParameters ParseParameters(object originParameters)
{
if (originParameters == null)
{
return(null);
}
CmdParameters parameters = originParameters as CmdParameters;
if (parameters != null)
{
return(parameters);
}
parameters = new CmdParameters();
if (originParameters is IEnumerable <KeyValuePair <string, string> > )
{
var stringParametersDict = originParameters as IEnumerable <KeyValuePair <string, string> >;
parameters.Add(stringParametersDict);
}
else if (originParameters is IEnumerable <KeyValuePair <string, dynamic> > )
{
var dynamicParametersDict = originParameters as IEnumerable <KeyValuePair <string, dynamic> >;
parameters.Add(dynamicParametersDict);
}
else if (originParameters is IEnumerable <KeyValuePair <string, object> > )
{
var objectParametersDict = originParameters as IEnumerable <KeyValuePair <string, object> >;
parameters.Add(objectParametersDict);
}
else if (originParameters is IEnumerable <KeyValuePair <string, IModifyValue> > )
{
var modifyParametersDict = originParameters as IEnumerable <KeyValuePair <string, IModifyValue> >;
parameters.Add(modifyParametersDict);
}
else
{
var objectParametersDict = originParameters.ObjectToDcitionary();
parameters.Add(objectParametersDict);
}
return(parameters);
}
/// <summary>
/// execute command
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">server</param>
/// <param name="executeOption">execute option</param>
/// <param name="cmds">command</param>
/// <returns>data numbers</returns>
public async Task <int> ExecuteAsync(ServerInfo server, CommandExecuteOption executeOption, params ICommand[] cmds)
{
#region group execute commands
IQueryTranslator translator = QueryTranslator.GetTranslator(server);
List <DbExecuteCommand> executeCommands = new List <DbExecuteCommand>();
var batchExecuteConfig = DataManager.GetBatchExecuteConfig(server.ServerType) ?? BatchExecuteConfig.Default;
var groupStatementsCount = batchExecuteConfig.GroupStatementsCount;
groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount;
var groupParameterCount = batchExecuteConfig.GroupParametersCount;
groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount;
StringBuilder commandTextBuilder = new StringBuilder();
CmdParameters parameters = null;
int statementsCount = 0;
bool forceReturnValue = false;
foreach (var cmd in cmds)
{
DbExecuteCommand executeCommand = GetExecuteDbCommand(translator, cmd as RdbCommand);
if (executeCommand == null)
{
continue;
}
if (executeCommand.PerformAlone)
{
if (statementsCount > 0)
{
executeCommands.Add(new DbExecuteCommand()
{
CommandText = commandTextBuilder.ToString(),
CommandType = CommandType.Text,
ForceReturnValue = true,
Parameters = parameters
});
statementsCount = 0;
translator.ParameterSequence = 0;
commandTextBuilder.Clear();
parameters = null;
}
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(new DbExecuteCommand()
{
CommandText = commandTextBuilder.ToString(),
CommandType = CommandType.Text,
ForceReturnValue = true,
Parameters = parameters
});
statementsCount = 0;
translator.ParameterSequence = 0;
commandTextBuilder.Clear();
parameters = null;
}
}
if (statementsCount > 0)
{
executeCommands.Add(new DbExecuteCommand()
{
CommandText = commandTextBuilder.ToString(),
CommandType = CommandType.Text,
ForceReturnValue = true,
Parameters = parameters
});
}
#endregion
return(await ExecuteCommandAsync(server, executeOption, executeCommands, executeOption?.ExecuteByTransaction ?? cmds.Length > 1).ConfigureAwait(false));
}
/// <summary>
/// get update execute command
/// </summary>
/// <param name="translator">translator</param>
/// <param name="cmd">cmd</param>
/// <returns></returns>
DbExecuteCommand GetUpdateExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
{
#region query translate
var tranResult = translator.Translate(cmd.Query);
string conditionString = string.Empty;
if (!tranResult.ConditionString.IsNullOrEmpty())
{
conditionString += "WHERE " + tranResult.ConditionString;
}
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
string cmdText = string.Empty;
CmdParameters parameters = ParseParameters(cmd.Parameters);
if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
{
cmdText = cmd.CommandText;
}
else
{
parameters = parameters ?? new CmdParameters();
string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
var fields = GetFields(cmd.EntityType, cmd.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 = 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 = GetCalculateChar(calculateModifyValue.Operator);
newValueExpression = string.Format("{0}.`{1}`{2}?{3}"
, translator.ObjectPetName
, field.FieldName
, calChar
, parameterName);
}
}
}
if (string.IsNullOrWhiteSpace(newValueExpression))
{
newValueExpression = "?" + parameterName;
}
updateSetArray.Add(string.Format("{0}.`{1}`={2}"
, translator.ObjectPetName
, field.FieldName
, newValueExpression));
}
cmdText = string.Format("{4}UPDATE `{2}` AS {0}{5} SET {1} {3};"
, translator.ObjectPetName
, string.Join(",", updateSetArray.ToArray())
, objectName
, conditionString
, preScript
, joinScript);
translator.ParameterSequence = parameterSequence;
}
//combine parameters
if (tranResult.Parameters != null)
{
var queryParameters = ParseParameters(tranResult.Parameters);
if (parameters != null)
{
parameters.Union(queryParameters);
}
else
{
parameters = queryParameters;
}
}
CommandType commandType = GetCommandType(cmd);
return(new DbExecuteCommand()
{
CommandText = cmdText,
CommandType = commandType,
ForceReturnValue = cmd.MustReturnValueOnSuccess,
Parameters = parameters,
HasPreScript = !string.IsNullOrWhiteSpace(preScript)
});
}
/// <summary>
/// execute single command
/// </summary>
/// <param name="conn">db connection</param>
/// <param name="cmd">command</param>
/// <param name="tran">transaction</param>
/// <returns></returns>
int ExecuteSingleCommand(IQueryTranslator queryTranslator, IDbConnection conn, RdbCommand cmd, IDbTransaction tran = null)
{
int resultValue = 0;
#region query object translate
IDictionary <string, object> queryParameters = null;
var tranResult = queryTranslator.Translate(cmd.Query);
string conditionString = string.Empty;
if (!tranResult.ConditionString.IsNullOrEmpty())
{
conditionString += "WHERE " + tranResult.ConditionString;
}
if (tranResult.Parameters != null)
{
if (tranResult.Parameters is IDictionary <string, object> )
{
queryParameters = tranResult.Parameters as IDictionary <string, object>;
}
else
{
queryParameters = tranResult.Parameters.ObjectToDcitionary();
}
}
#endregion
#region execute
StringBuilder cmdText = new StringBuilder();
object newParameters = cmd.Parameters;
ExecuteCommandResult executeCmdResult = cmd.CommandResultType;
if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
{
cmdText.Append(cmd.CommandText);
}
else
{
switch (cmd.Operate)
{
case OperateType.Insert:
cmdText.AppendFormat("INSERT INTO `{0}` ({1}) VALUES ({2})", cmd.ObjectName, string.Join(",", FormatFields(cmd.Fields)), string.Join(",", cmd.Fields.Select(c => "?" + c)));
break;
case OperateType.Update:
IDictionary <string, dynamic> oldCmdParameterDic = null;
IDictionary <string, dynamic> newCmdParameterDic = new Dictionary <string, dynamic>();
if (cmd.Parameters is IDictionary <string, dynamic> )
{
oldCmdParameterDic = cmd.Parameters as IDictionary <string, dynamic>;
}
else
{
oldCmdParameterDic = cmd.Parameters.ObjectToDcitionary();
}
List <string> updateSetArray = new List <string>();
foreach (var field in cmd.Fields)
{
if (oldCmdParameterDic == null || !oldCmdParameterDic.ContainsKey(field) || !(oldCmdParameterDic[field] is CalculateModify))
{
updateSetArray.Add(string.Format("{0}.`{1}`=?{2}", queryTranslator.ObjectPetName, field, field));
if (oldCmdParameterDic != null && oldCmdParameterDic.ContainsKey(field))
{
newCmdParameterDic.Add(field, oldCmdParameterDic[field]);
}
}
else if (oldCmdParameterDic[field] is CalculateModify)
{
CalculateModify calModify = oldCmdParameterDic[field] as CalculateModify;
newCmdParameterDic.Add(field, calModify.Value);
string calChar = GetCalculateChar(calModify.Calculate);
updateSetArray.Add(string.Format("{0}.`{1}`={0}.`{1}`{2}?{3}", queryTranslator.ObjectPetName, field, calChar, field));
}
}
newParameters = newCmdParameterDic;
cmdText.AppendFormat("{4}UPDATE `{2}` AS {0} SET {1} {3}", queryTranslator.ObjectPetName, string.Join(",", updateSetArray.ToArray()), cmd.ObjectName, conditionString, tranResult.PreScript);
break;
case OperateType.Delete:
cmdText.AppendFormat("{3}DELETE {0} FROM `{1}` AS {0} {2}", queryTranslator.ObjectPetName, cmd.ObjectName, conditionString, tranResult.PreScript);
break;
case OperateType.Exist:
cmdText.AppendFormat("SELECT EXISTS(SELECT {0}.`{1}` FROM `{2}` AS {0} {3})", queryTranslator.ObjectPetName, cmd.Fields.ElementAt(0), cmd.ObjectName, conditionString);
executeCmdResult = ExecuteCommandResult.ExecuteScalar;
break;
default:
break;
}
}
if (cmdText.Length <= 0)
{
return(0);
}
#endregion
#region parameter opertion
object parameters = queryParameters;
if (newParameters != null)
{
CmdParameters cmdParameters = newParameters as CmdParameters;
if (cmdParameters != null)
{
DynamicParameters dynamicParameters = new DynamicParameters();
foreach (var item in cmdParameters.Parameters)
{
dynamicParameters.Add(item.Key, item.Value.Value, item.Value.DbType, item.Value.ParameterDirection, item.Value.Size, item.Value.Precision, item.Value.Scale);
}
if (queryParameters != null)
{
foreach (var parameter in queryParameters)
{
dynamicParameters.Add(parameter.Key, parameter.Value);
}
}
parameters = dynamicParameters;
}
else if (newParameters is IDictionary <string, dynamic> )
{
IDictionary <string, dynamic> cmdParametersDic = newParameters as IDictionary <string, dynamic>;
if (queryParameters != null)
{
foreach (var queryParaItem in queryParameters)
{
cmdParametersDic.Add(queryParaItem.Key, queryParaItem.Value);
}
}
parameters = cmdParametersDic;
}
else
{
IDictionary <string, object> cmdParametersDic = newParameters.ObjectToDcitionary();
if (queryParameters != null)
{
foreach (var queryParaItem in queryParameters)
{
cmdParametersDic.Add(queryParaItem.Key, queryParaItem.Value);
}
}
parameters = cmdParametersDic;
}
}
#endregion
switch (executeCmdResult)
{
case ExecuteCommandResult.ExecuteScalar:
resultValue = conn.ExecuteScalar <int>(cmdText.ToString(), parameters, transaction: tran, commandType: GetCommandType(cmd));
break;
case ExecuteCommandResult.ExecuteRows:
resultValue = conn.Execute(cmdText.ToString(), parameters, transaction: tran, commandType: GetCommandType(cmd));
break;
}
return(resultValue);
}