C# SqlConnectionStringBuilder.ConnectTo方法代码示例

        public static async Task<IReadOnlyCollection<string>> ListInactivePackageIdReports(SqlConnectionStringBuilder sourceDatabase, DateTime reportGenerationTime)
        {
            using (var connection = await sourceDatabase.ConnectTo())
            {
                var command = new SqlCommand("[dbo].[DownloadReportListInactive]", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = _commandTimeout;

                command.Parameters.Add("ReportGenerationTime", SqlDbType.DateTime).Value = reportGenerationTime;

                var packageIds = new List<string>();
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        packageIds.Add(reader.GetString(0));
                    }
                }

                return packageIds;
            }
        }

        private async Task<int> GetNumberOfRecordsToPurge(SqlConnectionStringBuilder Source, DateTime minTimestampToKeep)
        {
            var sql = @"
                SELECT		COUNT(*)
                FROM		PackageStatistics WITH (NOLOCK)
                WHERE		[Timestamp] < @MinTimestampToKeep";

            using (var connection = await Source.ConnectTo())
            {
                var command = new SqlCommand(sql, connection);
                command.Parameters.AddWithValue("@MinTimestampToKeep", minTimestampToKeep);

                return await command.ExecuteScalarAsync() as int? ?? 0;
            }
        }

        private async Task<DateTime?> GetMinTimestampToKeep(SqlConnectionStringBuilder Destination)
        {
            // Get the most recent cursor window that is older than the days we want to keep.
            // By getting the MAX(MinTimestamp), we'll delete statistics older than the beginning of the
            // most recent window that has begun processing (but isn't guaranteed to have completed).

            // Note that we made sure to treat DaysToKeep as a NEGATIVE number for the expected behavior
            var sql = @"
                SELECT      MAX(MinTimestamp)
                FROM        CollectorCursor
                WHERE       MinTimestamp <= DATEADD(day, -ABS(@DaysToKeep), convert(date, GETUTCDATE()))";

            using (var connection = await Destination.ConnectTo())
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.AddWithValue("@DaysToKeep", DaysToKeep);

                return await command.ExecuteScalarAsync() as DateTime?;
            }
        }

        public static async Task UpdateDirtyPackageIdCursor(SqlConnectionStringBuilder sourceDatabase, DateTime runToCursor)
        {
            using (var connection = await sourceDatabase.ConnectTo())
            {
                var command = new SqlCommand("[dbo].[UpdateDirtyPackageIdCursor]", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = _commandTimeout;
                command.Parameters.Add("@Position", SqlDbType.DateTime).Value = runToCursor;

                await command.ExecuteNonQueryAsync();
            }
        }

        private static async Task PutDownloadRecords(SqlConnectionStringBuilder target, XDocument batch, ReplicationTargetMarker currentCursor, ReplicationTargetMarker newCursor)
        {
            using (var connection = await target.ConnectTo())
            {
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = new SqlCommand("AddDownloadFacts", connection, transaction))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@facts", batch.ToString());
                        command.Parameters.AddWithValue("@cursorMinTimestamp", currentCursor.MinTimestamp);
                        command.Parameters.AddWithValue("@cursorMaxTimestamp", currentCursor.MaxTimestamp);
                        command.Parameters.AddWithValue("@cursor", newCursor.Cursor);

                        await command.ExecuteNonQueryAsync();
                        transaction.Commit();
                    }
                }
            }
        }

        private static async Task<IReadOnlyCollection<DirtyPackageId>> GetDirtyPackageIdsFromWarehouse(SqlConnectionStringBuilder sourceDatabase, DateTime reportGenerationTime)
        {
            using (var connection = await sourceDatabase.ConnectTo())
            {
                var command = new SqlCommand("[dbo].[GetDirtyPackageIds]", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = _commandTimeout;

                command.Parameters.Add("ReportGenerationTime", SqlDbType.DateTime).Value = reportGenerationTime;

                var packageIds = new List<DirtyPackageId>();
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        packageIds.Add(new DirtyPackageId(reader.GetString(0), reader.GetDateTime(1)));
                    }
                }

                return packageIds;
            }
        }

        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;
                    }
                }
            }
        }

        private static async Task ClearDownloadFacts(SqlConnectionStringBuilder target, DateTime minTimestamp, DateTime maxTimestamp)
        {
            int totalRecordsCleared = 0;
            int recordsClearedInBatch = 0;

            do
            {
                JobEventSourceLog.ClearingDownloadFacts(minTimestamp, maxTimestamp);

                using (var connection = await target.ConnectTo())
                {
                    // This proc will delete 5000 records at a time, so we have to run in a loop until there's nothing more to delete
                    using (var command = new SqlCommand("ClearDownloadFacts", connection) { CommandTimeout = 60 * 30 }) // 30-minute timeout
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@minTimestamp", minTimestamp);
                        command.Parameters.AddWithValue("@maxTimestamp", maxTimestamp);

                        var recordsCleared = new SqlParameter() { Direction = ParameterDirection.ReturnValue };
                        command.Parameters.Add(recordsCleared);

                        await command.ExecuteNonQueryAsync();

                        recordsClearedInBatch = (int)recordsCleared.Value;
                        totalRecordsCleared += recordsClearedInBatch;
                    }
                }

                JobEventSourceLog.ClearedDownloadFacts(recordsClearedInBatch, "Batch Completed.");
            }
            while (recordsClearedInBatch == 5000); // Hard-coded to match the stored proc - allows us to stop when done

            JobEventSourceLog.ClearedDownloadFacts(totalRecordsCleared, "Finished.");
        }

        private static async Task<int?> GetTargetCursor(SqlConnectionStringBuilder target, ReplicationTargetMarker targetMarker)
        {
            using (var connection = await target.ConnectTo())
            {
                using (var command = new SqlCommand("GetCursor", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp);
                    command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp);

                    return await command.ExecuteScalarAsync() as int?;
                }
            }
        }

        private static async Task<ReplicationTargetMarker> GetReplicationTargetMarker(SqlConnectionStringBuilder target, ReplicationSourceMarker sourceMarker)
        {
            using (var connection = await target.ConnectTo())
            {
                using (var command = new SqlCommand("CreateCursor", connection))
                {
                    var minTimestamp = new SqlParameter("@minTimestamp", sourceMarker.MinTimestamp) { Direction = ParameterDirection.InputOutput };
                    var maxTimestamp = new SqlParameter("@maxTimestamp", sourceMarker.MaxTimestamp) { Direction = ParameterDirection.InputOutput };

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(minTimestamp);
                    command.Parameters.Add(maxTimestamp);

                    await command.ExecuteNonQueryAsync();

                    // If the min/max pair is null then that means there are no records missing
                    // from the target. So we use the MaxTimestamp as the null value for BOTH
                    // as that will result in no records to replicate, but we also set the flag.
                    var minTimestampValue = (minTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp;
                    var maxTimestampValue = (maxTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp;

                    return new ReplicationTargetMarker
                    {
                        MinTimestamp = minTimestampValue,
                        MaxTimestamp = maxTimestampValue,
                        TimeWindowNeedsReplication = (minTimestampValue < maxTimestampValue)
                    };
                }
            }
        }

        private static async Task<ReplicationSourceMarker> GetReplicationSourceMarker(SqlConnectionStringBuilder source, DateTime? minTimestamp, DateTime? maxTimestamp)
        {
            string sql = @"
                SELECT		MIN([Key]) AS MinKey
		                ,	MAX([Key]) AS MaxKey
                        ,   MIN([Timestamp]) AS MinTimestamp
                        ,   MAX([Timestamp]) AS MaxTimestamp
                        ,   COUNT(*) AS Records
                FROM		PackageStatistics
                WHERE		[Timestamp] >= @minTimestamp
		                AND	[Timestamp] < @maxTimestamp";

            using (var connection = await source.ConnectTo())
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.AddWithValue("@minTimestamp", minTimestamp ?? System.Data.SqlTypes.SqlDateTime.MinValue);
                command.Parameters.AddWithValue("@maxTimestamp", maxTimestamp ?? System.Data.SqlTypes.SqlDateTime.MaxValue);

                using (var result = await command.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow | CommandBehavior.KeyInfo))
                {
                    if (result.HasRows && result.Read())
                    {
                        if (!result.IsDBNull(result.GetOrdinal("MinKey")) && !result.IsDBNull(result.GetOrdinal("MaxKey")))
                        {
                            return new ReplicationSourceMarker
                            {
                                MinKey = result.GetInt32(result.GetOrdinal("MinKey")),
                                MaxKey = result.GetInt32(result.GetOrdinal("MaxKey")),
                                // Keep the original timestamp min/max values if specified
                                // Otherwise we lose the boundary values and might not process the window (thinking it's incomplete)
                                MinTimestamp = minTimestamp ?? result.GetDateTime(result.GetOrdinal("MinTimestamp")),
                                MaxTimestamp = maxTimestamp ?? result.GetDateTime(result.GetOrdinal("MaxTimestamp")),
                                RecordsToReplicate = result.GetInt32(result.GetOrdinal("Records"))
                            };
                        }
                        else
                        {
                            return new ReplicationSourceMarker { RecordsToReplicate = 0 };
                        }
                    }
                }
            }

            return new ReplicationSourceMarker
            {
                MinKey = 0,
                MaxKey = 0
            };
        }