/// <summary> /// Initiates the asynchronous execution of the Transact-SQL statement or stored procedure and returns results as an /// <see cref="XmlReader"/> object contained by an observable sequence. /// </summary> /// <param name="command">The <see cref="SqlCommand"/> to be executed.</param> /// <returns>A singleton observable sequence containing an <see cref="XmlReader"/> object that provides access to /// the results of the specified <paramref name="command"/>.</returns> public static IObservable <XmlReader> ExecuteXmlReaderObservable(this SqlCommand command) { Contract.Requires(command != null); Contract.Ensures(Contract.Result <IObservable <XmlReader> >() != null); return(Observable.StartAsync(cancel => command.ExecuteXmlReaderAsync(cancel))); }
/// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReaderAsync(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public async static Task<XmlReader> ExecuteXmlReaderAsync(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution var cmd = new SqlCommand(); await PrepareCommandAsync(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters).ConfigureAwait(false); // Create the DataAdapter & DataSet var retval = await cmd.ExecuteXmlReaderAsync().ConfigureAwait(false); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; }
/// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReaderAsync(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public async static Task<XmlReader> ExecuteXmlReaderAsync(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); bool mustCloseConnection = false; // Create a command and prepare it for execution var cmd = new SqlCommand(); try { mustCloseConnection = await PrepareCommandAsync(cmd, connection, null, commandType, commandText, commandParameters).ConfigureAwait(false); // Create the DataAdapter & DataSet var retval = await cmd.ExecuteXmlReaderAsync().ConfigureAwait(false); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } catch { if (mustCloseConnection) connection.Close(); throw; } }
private static async Task<XDocument> GetDownloadRecords(SqlConnectionStringBuilder source, ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize) { using (var connection = await source.ConnectTo()) { using (var command = new SqlCommand(@" SELECT TOP(@batchSize) PackageStatistics.[Key] 'originalKey', PackageRegistrations.[Id] 'packageId', Packages.[Version] 'packageVersion', Packages.[Listed] 'packageListed', Packages.[Title] 'packageTitle', Packages.[Description] 'packageDescription', Packages.[IconUrl] 'packageIconUrl', ISNULL(PackageStatistics.[UserAgent], '') 'downloadUserAgent', ISNULL(PackageStatistics.[Operation], '') 'downloadOperation', PackageStatistics.[Timestamp] 'downloadTimestamp', PackageStatistics.[ProjectGuids] 'downloadProjectTypes', PackageStatistics.[DependentPackage] 'downloadDependentPackageId' FROM PackageStatistics INNER JOIN Packages ON PackageStatistics.PackageKey = Packages.[Key] INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey WHERE PackageStatistics.[Key] >= @minSourceKey AND PackageStatistics.[Key] <= @maxSourceKey AND PackageStatistics.[Timestamp] >= @minTimestamp AND PackageStatistics.[Timestamp] < @maxTimestamp AND PackageStatistics.[Key] > @cursor ORDER BY PackageStatistics.[Key] FOR XML RAW('fact'), ELEMENTS, ROOT('facts') ", connection)) { command.Parameters.AddWithValue("@batchSize", batchSize); command.Parameters.AddWithValue("@minSourceKey", sourceMarker.MinKey); command.Parameters.AddWithValue("@maxSourceKey", sourceMarker.MaxKey); command.Parameters.AddWithValue("@cursor", targetMarker.Cursor ?? 0); command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp); command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp); var factsReader = await command.ExecuteXmlReaderAsync(); var nodeType = factsReader.MoveToContent(); if (nodeType != XmlNodeType.None) { var factsDocument = XDocument.Load(factsReader); return factsDocument; } else { // No data returned return null; } } } }
/// <summary> /// Utility function used by async tests /// </summary> /// <param name="com">SqlCommand to be executed.</param> /// <param name="query">Indicates if data is being queried</param> /// <param name="xml">Indicates if the query should be executed as an Xml</param> /// <param name="useBeginAPI"></param> /// <param name="cts">The Cancellation Token Source</param> /// <returns>The result of beginning of Async execution.</returns> private IAsyncResult SqlCommandBeginExecute(SqlCommand com, bool query, bool xml, bool useBeginAPI, CancellationTokenSource cts = null) { DataStressErrors.Assert(!(useBeginAPI && cts != null), "Cannot use begin api with CancellationTokenSource"); CancellationToken token = (cts != null) ? cts.Token : CancellationToken.None; if (xml) { com.CommandText = com.CommandText + " FOR XML AUTO"; return useBeginAPI ? null : com.ExecuteXmlReaderAsync(token); } else if (query) { return useBeginAPI ? null : com.ExecuteReaderAsync(token); } else { return useBeginAPI ? null : com.ExecuteNonQueryAsync(token); } }