/// <summary> /// determine whether data has existed /// </summary> /// <param name="server">server</param> /// <param name="cmd">command</param> /// <returns>data has existed</returns> public async Task <bool> QueryAsync(ServerInfo server, ICommand cmd) { var translator = QueryTranslator.GetTranslator(server); #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 var field = DataManager.GetDefaultField(ServerType.SQLServer, cmd.EntityType); string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName); string cmdText = string.Format("{5}SELECT 1 WHERE EXISTS(SELECT {0}.[{1}] FROM [{2}] AS {0}{4} {3})" , translator.ObjectPetName , field.FieldName , objectName , conditionString , joinScript , preScript); using (var conn = DbServerFactory.GetConnection(server)) { int value = await conn.ExecuteScalarAsync <int>(cmdText, tranResult.Parameters).ConfigureAwait(false); return(value > 0); } }
/// <summary> /// query data list offset the specified numbers /// </summary> /// <typeparam name="T">data type</typeparam> /// <param name="server">database server</param> /// <param name="cmd">command</param> /// <param name="offsetNum">offset num</param> /// <param name="size">query size</param> /// <returns></returns> public async Task <IEnumerable <T> > QueryOffsetAsync <T>(ServerInfo server, ICommand cmd, int offsetNum = 0, int size = int.MaxValue) { if (cmd.Query == null) { throw new EZNEWException("ICommand.Query is null"); } #region query object translate IQueryTranslator translator = QueryTranslator.GetTranslator(server); var tranResult = translator.Translate(cmd.Query); #endregion #region execute string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; StringBuilder cmdText = new StringBuilder(); switch (cmd.Query.QueryType) { case QueryCommandType.Text: cmdText.Append(tranResult.ConditionString); break; case QueryCommandType.QueryObject: default: string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName); string defaultFieldName = string.Empty; List <string> formatQueryFields = FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out defaultFieldName); cmdText.AppendFormat("{4}SELECT COUNT({3}.[{0}]) OVER() AS QueryDataTotalCount,{1} FROM [{2}] AS {3}{5}" , defaultFieldName , string.Join(",", formatQueryFields) , objectName , translator.ObjectPetName , tranResult.PreScript , joinScript); if (!tranResult.ConditionString.IsNullOrEmpty()) { cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString); } if (!tranResult.OrderString.IsNullOrEmpty()) { cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString); } else { cmdText.AppendFormat(" ORDER BY {0}.[{1}] DESC", translator.ObjectPetName, defaultFieldName); } cmdText.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offsetNum, size); break; } #endregion using (var conn = DbServerFactory.GetConnection(server)) { var tran = GetQueryTransaction(conn, cmd.Query); return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false)); } }
/// <summary> /// Aggregate Function /// </summary> /// <typeparam name="T">data type</typeparam> /// <param name="server">database server</param> /// <param name="cmd">command</param> /// <returns></returns> async Task <T> AggregateFunctionAsync <T>(ServerInfo server, ICommand cmd) { cmd.Query = cmd.Query ?? QueryFactory.Create(); #region query object translate IQueryTranslator translator = QueryTranslator.GetTranslator(server); var tranResult = translator.Translate(cmd.Query); #endregion #region execute StringBuilder cmdText = new StringBuilder(); string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; switch (cmd.Query.QueryType) { case QueryCommandType.Text: cmdText.Append(tranResult.ConditionString); break; case QueryCommandType.QueryObject: default: string funcName = GetAggregateFunctionName(cmd.Operate); if (funcName.IsNullOrEmpty() || cmd.Fields.IsNullOrEmpty()) { return(default(T)); } string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName); if (cmd.Query.QueryFields.IsNullOrEmpty()) { throw new EZNEWException("must set one field"); } var field = DataManager.GetField(ServerType.SQLServer, cmd.EntityType, cmd.Query.QueryFields[0]); cmdText.AppendFormat("{4}SELECT {0}({3}.[{1}]) FROM [{2}] AS {3}{5}" , funcName , field.FieldName , objectName , translator.ObjectPetName , tranResult.PreScript , joinScript); if (!tranResult.ConditionString.IsNullOrEmpty()) { cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString); } if (!tranResult.OrderString.IsNullOrEmpty()) { cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString); } break; } #endregion using (var conn = DbServerFactory.GetConnection(server)) { return(await conn.ExecuteScalarAsync <T>(cmdText.ToString(), tranResult.Parameters, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false)); } }
/// <summary> /// query data list /// </summary> /// <typeparam name="T">data type</typeparam> /// <param name="server">database server</param> /// <param name="cmd">command</param> /// <returns>data list</returns> public async Task <IEnumerable <T> > QueryAsync <T>(ServerInfo server, ICommand cmd) { if (cmd.Query == null) { throw new EZNEWException("ICommand.Query is null"); } #region query object translate IQueryTranslator translator = QueryTranslator.GetTranslator(server); var tranResult = translator.Translate(cmd.Query); string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region execute StringBuilder cmdText = new StringBuilder(); switch (cmd.Query.QueryType) { case QueryCommandType.Text: cmdText.Append(tranResult.ConditionString); break; case QueryCommandType.QueryObject: default: int size = cmd.Query.QuerySize; string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName); cmdText.AppendFormat("{4}SELECT {0} {1} FROM [{2}] AS {3}{5}" , size > 0 ? "TOP " + size : string.Empty , string.Join(",", FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out var defaultFieldName)) , objectName , translator.ObjectPetName , preScript , joinScript); if (!tranResult.ConditionString.IsNullOrEmpty()) { cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString); } if (!tranResult.OrderString.IsNullOrEmpty()) { cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString); } break; } #endregion using (var conn = DbServerFactory.GetConnection(server)) { var tran = GetQueryTransaction(conn, cmd.Query); var data = await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false); return(data); } }
/// <summary> /// execute commands /// </summary> /// <param name="server">db server</param> /// <param name="executeCommands">execute commands</param> /// <param name="useTransaction">use transaction</param> /// <returns></returns> async Task <int> ExecuteCommandAsync(ServerInfo server, IEnumerable <DbExecuteCommand> executeCommands, bool useTransaction) { int resultValue = 0; bool success = true; using (var conn = DbServerFactory.GetConnection(server)) { conn.Open(); IDbTransaction transaction = null; if (useTransaction) { transaction = conn.BeginTransaction(); } try { foreach (var cmd in executeCommands) { var executeResultValue = await conn.ExecuteAsync(cmd.CommandText, ConvertCmdParameters(cmd.Parameters), transaction, commandType : cmd.CommandType).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> /// query data /// </summary> /// <param name="server">database server</param> /// <param name="cmd">query cmd</param> /// <returns>data</returns> public async Task <DataSet> QueryMultipleAsync(ServerInfo server, ICommand cmd) { using (var conn = DbServerFactory.GetConnection(server)) { var tran = GetQueryTransaction(conn, cmd.Query); DynamicParameters parameters = ConvertCmdParameters(ParseParameters(cmd.Parameters)); using (var reader = await conn.ExecuteReaderAsync(cmd.CommandText, parameters, transaction: tran, commandType: GetCommandType(cmd as RdbCommand)).ConfigureAwait(false)) { DataSet dataSet = new DataSet(); while (!reader.IsClosed && reader.Read()) { DataTable dataTable = new DataTable(); dataTable.Load(reader); dataSet.Tables.Add(dataTable); } return(dataSet); } } }
/// <summary> /// query single value /// </summary> /// <typeparam name="T">data type</typeparam> /// <param name="server">database server</param> /// <param name="cmd">command</param> /// <returns>query data</returns> public async Task <T> AggregateValueAsync <T>(ServerInfo server, ICommand cmd) { if (cmd.Query == null) { throw new EZNEWException("ICommand.Query is null"); } #region query object translate IQueryTranslator translator = QueryTranslator.GetTranslator(server); var tranResult = translator.Translate(cmd.Query); #endregion #region execute StringBuilder cmdText = new StringBuilder(); string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; switch (cmd.Query.QueryType) { case QueryCommandType.Text: cmdText.Append(tranResult.ConditionString); break; case QueryCommandType.QueryObject: default: string funcName = GetAggregateFunctionName(cmd.Operate); if (funcName.IsNullOrEmpty()) { return(default(T)); } #region field EntityField field = null; if (AggregateOperateMustNeedField(cmd.Operate)) { if (cmd.Query?.QueryFields.IsNullOrEmpty() ?? true) { throw new EZNEWException(string.Format("You must specify the field to perform for the {0} operation", funcName)); } else { field = DataManager.GetField(ServerType.SQLServer, cmd.EntityType, cmd.Query.QueryFields[0]); } } else { field = DataManager.GetDefaultField(ServerType.SQLServer, cmd.EntityType); } #endregion string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName); cmdText.AppendFormat("{4}SELECT {0}({1}) FROM [{2}] AS {3}{5}" , funcName , FormatField(translator.ObjectPetName, field) , objectName , translator.ObjectPetName , tranResult.PreScript , joinScript); if (!tranResult.ConditionString.IsNullOrEmpty()) { cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString); } if (!tranResult.OrderString.IsNullOrEmpty()) { cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString); } break; } #endregion using (var conn = DbServerFactory.GetConnection(server)) { var tran = GetQueryTransaction(conn, cmd.Query); return(await conn.ExecuteScalarAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false)); } }