Example #1
0
        /// <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;
        }
        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;
                    }
                }
            }
        }
Example #3
0
        /// <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;
            }
        }
Example #4
0
        /// <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);
            }
        }