C# SqlExecutor.Execute方法代码示例

        public override void ExecuteCommand()
        {
            Log.Info(
                "Deleting package registration and all package versions for '{0}'.",
                PackageId);

            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var packageRegistration = Util.GetPackageRegistration(
                    dbExecutor,
                    PackageId);
                var packages = Util.GetPackages(
                    dbExecutor,
                    packageRegistration.Key);

                foreach(var package in packages)
                {
                    var task = new DeletePackageVersionTask {
                        ConnectionString = ConnectionString,
                        BackupStorage = BackupStorage,
                        StorageAccount = StorageAccount,
                        PackageId = package.Id,
                        PackageVersion = package.Version,
                        Reason = Reason,
                        WhatIf = WhatIf
                    };
                    task.ExecuteCommand();
                }

                Log.Info(
                    "Deleting package registration data for '{0}'",
                    packageRegistration.Id);
                if (!WhatIf)
                {
                    dbExecutor.Execute(
                        "DELETE por FROM PackageOwnerRequests por JOIN PackageRegistrations pr ON pr.[Key] = por.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
                        new { packageRegistrationKey = packageRegistration.Key });
                    dbExecutor.Execute(
                        "DELETE pro FROM PackageRegistrationOwners pro JOIN PackageRegistrations pr ON pr.[Key] = pro.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
                        new { packageRegistrationKey = packageRegistration.Key });
                    dbExecutor.Execute(
                        "DELETE FROM PackageRegistrations WHERE [Key] = @packageRegistrationKey",
                        new { packageRegistrationKey = packageRegistration.Key });
                }
            }

            Log.Info(
                "Deleted package registration and all package versions for '{0}'.",
                PackageId);
        }

        public override void ExecuteCommand()
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var package = Util.GetPackage(
                    dbExecutor,
                    PackageId,
                    PackageVersion);

                if (package == null)
                {
                    Log.Error("Package version does not exist: '{0}.{1}'", PackageId, PackageVersion);
                    return;
                }

                Log.Info(
                    "Deleting package data for '{0}.{1}'",
                    package.Id,
                    package.Version);

                if (!WhatIf)
                {
                    dbExecutor.Execute(
                        "DELETE pa FROM PackageAuthors pa JOIN Packages p ON p.[Key] = pa.PackageKey WHERE p.[Key] = @key",
                        new { key = package.Key });
                    dbExecutor.Execute(
                        "DELETE pd FROM PackageDependencies pd JOIN Packages p ON p.[Key] = pd.PackageKey WHERE p.[Key] = @key",
                        new { key = package.Key });
                    dbExecutor.Execute(
                        "DELETE ps FROM PackageStatistics ps JOIN Packages p ON p.[Key] = ps.PackageKey WHERE p.[Key] = @key",
                        new { key = package.Key });
                    dbExecutor.Execute(
                        "DELETE pf FROM PackageFrameworks pf JOIN Packages p ON p.[Key] = pf.Package_Key WHERE p.[Key] = @key",
                        new { key = package.Key });
                    dbExecutor.Execute(
                        "DELETE p FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE p.[Key] = @key",
                        new { key = package.Key });
                }

                new DeletePackageFileTask {
                    StorageAccount = StorageAccount,
                    PackageId = package.Id,
                    PackageVersion = package.Version,
                    PackageHash = package.Hash,
                    WhatIf = WhatIf
                }.ExecuteCommand();
            }
        }

        public override void ExecuteCommand()
        {
            // Verify the name
            if (!Force && !AllowedPrefixes.Any(p => ConnectionString.InitialCatalog.StartsWith(p, StringComparison.OrdinalIgnoreCase)))
            {
                Log.Error("Cannot sanitize database named '{0}' without -Force argument", ConnectionString.InitialCatalog);
                return;
            }
            Log.Info("Ready to sanitize {0} on {1}", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString));

            // All we need to sanitize is the user table. Package data is public (EVEN unlisted ones) and not PII
            if (WhatIf)
            {
                Log.Trace("Would execute the following SQL:");
                Log.Trace(SanitizeUsersQuery);
                Log.Trace("With @emailDomain = " + EmailDomain);
            }
            else
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
                using (SqlExecutor dbExecutor = new SqlExecutor(connection))
                {
                    connection.Open();
                    try
                    {
                        var count = dbExecutor.Execute(SanitizeUsersQuery, new { emailDomain = EmailDomain });
                        Log.Info("Sanitization complete. {0} Users affected", count);
                    }
                    catch (Exception ex)
                    {
                        Log.Error(ex.ToString());
                    }
                }
            }
        }

 private void DeleteDatabaseBackup(Database db, SqlExecutor dbExecutor)
 {
     if (!WhatIf)
     {
         dbExecutor.Execute(string.Format("DROP DATABASE {0}", db.Name));
     }
     Log.Info("Deleted database {0}.", db.Name);
 }

 private void DeleteDatabaseBackup(Db db, SqlExecutor dbExecutor)
 {
     if (!WhatIf)
     {
         dbExecutor.Execute($"DROP DATABASE {db.Name}");
     }
     Log.Info("Deleted database {0}.", db.Name);
 }

 private string CopyDatabaseForRestore(
     SqlExecutor masterDbExecutor)
 {
     var restoreDbName = string.Format("Restore_{0}", Util.GetTimestamp());
     Log.Info("Copying {0} to {1}.", BackupName, restoreDbName);
     masterDbExecutor.Execute(string.Format("CREATE DATABASE {0} AS COPY OF {1}", restoreDbName, BackupName));
     Log.Info("Waiting for copy to complete.");
     WaitForBackupCopy(
         masterDbExecutor,
         restoreDbName);
     return restoreDbName;
 }

        public override void ExecuteCommand()
        {
            Log.Trace("Connecting to server '{0}' to back up database '{1}'.", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString));

            _startedBackup = false;

            var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
            using(var connection = new SqlConnection(cstr))
            using(var db = new SqlExecutor(connection))
            {
                connection.Open();

                if (!Force)
                {
                    Log.Trace("Checking for a backup in progress.");
                    if (Util.BackupIsInProgress(db, BackupNamePrefix))
                    {
                        Log.Trace("Found a backup in progress; exiting.");
                        return;
                    }

                    Log.Trace("Found no backup in progress.");

                    Log.Trace("Getting last backup time.");
                    var lastBackupTime = Util.GetLastBackupTime(db, BackupNamePrefix);
                    if (lastBackupTime >= DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(IfOlderThan)))
                    {
                        Log.Info("Skipping Backup. Last Backup was less than {0} minutes ago", IfOlderThan);
                        return;
                    }
                    Log.Trace("Last backup time is more than {0} minutes ago. Starting new backup.", IfOlderThan);
                }
                else
                {
                    Log.Trace("Forcing new backup");
                }

                // Generate a backup name
                var timestamp = Util.GetTimestamp();

                _backupName = BackupNamePrefix + timestamp;

                if (!WhatIf)
                {
                    db.Execute(string.Format("CREATE DATABASE {0} AS COPY OF {1}", _backupName, ConnectionString.InitialCatalog));
                    _startedBackup = true;
                }

                Log.Info("Started Copy of '{0}' to '{1}'", ConnectionString.InitialCatalog, _backupName);
            }
        }

 public string Execute(string query, LoginDetails loginDetails)
 {
     string result;
     using (var ms = new MemoryStream(2048))
     using (var tw = new StreamWriter(ms))
     {
         var executor = new SqlExecutor(tw, loginDetails);
         executor.Execute(query);
         tw.Flush();
         ms.Position = 0;
         using (var tr = new StreamReader(ms))
         {
             result = tr.ReadToEnd();
         }
     }
     return result;
 }

        public override void ExecuteCommand()
        {
            var dbServer = Util.GetDbServer(ConnectionString);
            var dbName = Util.GetDbName(ConnectionString);
            var masterConnectionString = Util.GetMasterConnectionString(ConnectionString);

            Log.Trace("Connecting to server '{0}' to back up database '{1}'.", dbServer, dbName);

            SkippingBackup = false;

            using (var sqlConnection = new SqlConnection(masterConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                Log.Trace("Checking for a backup in progress.");
                if (Util.BackupIsInProgress(dbExecutor))
                {
                    Log.Trace("Found a backup in progress; exiting.");
                    return;
                }

                Log.Trace("Found no backup in progress.");

                Log.Trace("Getting last backup time.");
                var lastBackupTime = Util.GetLastBackupTime(dbExecutor);
                if (lastBackupTime >= DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(IfOlderThan)))
                {
                    Log.Info("Skipping Backup. Last Backup was less than {0} minutes ago", IfOlderThan);

                    SkippingBackup = true;

                    return;
                }
                Log.Trace("Last backup time is more than {0} minutes ago. Starting new backup.", IfOlderThan);

                var timestamp = Util.GetTimestamp();

                BackupName = string.Format("Backup_{0}", timestamp);

                dbExecutor.Execute(string.Format("CREATE DATABASE {0} AS COPY OF NuGetGallery", BackupName));

                Log.Info("Starting '{0}'", BackupName);
            }
        }

        public override void ExecuteCommand()
        {
            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var package = Util.GetPackage(
                    dbExecutor,
                    PackageId,
                    PackageVersion);

                if (package == null)
                {
                    Log.Info("Package '{0}.{1}' does not exist; exiting.");
                    return;
                }

                new BackupPackageFileTask {
                    StorageAccount = StorageAccount,
                    PackageId = package.Id,
                    PackageVersion = package.Version,
                    PackageHash = package.Hash
                }.ExecuteCommand();

                var hash = Util.GenerateHash(ReplacementFile.ReadAllBytes());
                Log.Info("Updating hash for package '{0}.{1}' to '{2}'", package.Id, package.Version, hash);
                dbExecutor.Execute(
                    "UPDATE Packages SET Hash = @hash WHERE [Key] = @key",
                    new { @key = package.Key, hash });

                Log.Info("Uploading replacement file for package '{0}.{1}'", package.Id, package.Version);
                ReplacementFile.Position = 0;
                new UploadPackageTask {
                    StorageAccount = StorageAccount,
                    PackageId = package.Id,
                    PackageVersion = package.Version,
                    PackageFile = ReplacementFile
                }.ExecuteCommand();
            }
        }

        public override void ExecuteCommand()
        {
            // todo: move the data access from the website to a common lib and use that instead
            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();
                var package = dbExecutor.Query<Package>(
                    "SELECT p.[Key], pr.Id, p.Version, p.ExternalPackageUrl FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE pr.Id = @id AND p.Version = @version AND p.ExternalPackageUrl IS NOT NULL",
                    new { id = PackageId, version = PackageVersion })
                    .SingleOrDefault();
                if (package == null)
                {
                    Log.Info("Package is stored locally: {0} {1}", PackageId, PackageVersion);
                }
                else
                {
                    using (var httpClient = new HttpClient())
                    using (var packageStream = httpClient.GetStreamAsync(package.ExternalPackageUrl).Result)
                    {
                        new UploadPackageTask
                        {
                            StorageAccount = StorageAccount,
                            PackageId = package.Id,
                            PackageVersion = package.Version,
                            PackageFile = packageStream,
                            WhatIf = WhatIf
                        }.ExecuteCommand();
                    }

                    if (!WhatIf)
                    {
                        dbExecutor.Execute(
                            "UPDATE Packages SET ExternalPackageUrl = NULL WHERE [Key] = @key",
                            new { key = package.Key });
                    }
                }
            }
        }

        public override void ExecuteCommand()
        {
            Log.Info(
                "Delete the user account and all packages for '{0}'.",
                Username);

            using (var sqlConnection = new SqlConnection(ConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var user = Util.GetUser(dbExecutor, Username);

                if (user == null)
                {
                    Log.Error("User was not found");
                    return;
                }

                Log.Info("User found with EmailAddress '{0}' and UnconfirmedEmailAddress '{1}'",
                    user.EmailAddress, user.UnconfirmedEmailAddress);

                var packageCount = user.PackageRegistrationIds.Count();
                var packageNumber = 0;

                foreach (var packageId in user.PackageRegistrationIds)
                {
                    Log.Info("Deleting package '{0}' because '{1}' is the sole owner. ({2}/{3})",
                        packageId, Username, ++packageNumber, packageCount);

                    var deletePackageTask = new DeleteAllPackageVersionsTask
                    {
                        ConnectionString = ConnectionString,
                        StorageAccount = StorageAccount,
                        PackageId = packageId,
                        WhatIf = WhatIf
                    };

                    deletePackageTask.Execute();
                }

                Log.Info("Deleting remaining package ownership records (from shared ownership)");

                if (!WhatIf)
                {
                    dbExecutor.Execute(
                        "DELETE pro FROM PackageRegistrationOwners pro WHERE pro.UserKey = @userKey",
                        new { userKey = user.Key });
                }

                Log.Info("Deleting package ownership requests");

                if (!WhatIf)
                {
                    dbExecutor.Execute(
                        "DELETE por FROM PackageOwnerRequests por WHERE @userKey IN (por.NewOwnerKey, por.RequestingOwnerKey)",
                        new { userKey = user.Key });
                }

                Log.Info("Deleting the user record itself");

                if (!WhatIf)
                {
                    dbExecutor.Execute(
                        "DELETE u FROM Users u WHERE u.[Key] = @userKey",
                        new { userKey = user.Key });
                }

                Log.Info(
                    "Deleted all packages owned solely by '{0}' as well as the user record."
                    , user.Username);
            }
        }

 void AddPackageToCuratedFeed(Package package)
 {
     if (!WhatIf)
     {
         using (var sqlConnection = new SqlConnection(ConnectionString))
         using (var dbExecutor = new SqlExecutor(sqlConnection))
         {
             sqlConnection.Open();
             dbExecutor.Execute(@"
             INSERT INTO CuratedPackages
                 (CuratedFeedKey, PackageRegistrationKey, AutomaticallyCurated, Included)
             VALUES
                 ((SELECT [Key] FROM CuratedFeeds WHERE Name = 'webmatrix'), (SELECT [Key] FROM PackageRegistrations WHERE Id = @id), @automaticallyCurated, @included)",
                 new { id = package.Id, automaticallyCurated = true, included = true });
         }
     }
 }

 void PopulateFrameworks(
     Package package,
     IEnumerable<string> targetFrameworks)
 {
     foreach (var targetFramework in targetFrameworks)
     {
         using (var sqlConnection = new SqlConnection(ConnectionString))
         using (var dbExecutor = new SqlExecutor(sqlConnection))
         {
             sqlConnection.Open();
             dbExecutor.Execute(
                 @"
             INSERT INTO PackageFrameworks
                 (Package_Key, TargetFramework)
             VALUES
                 (@packageKey, @targetFramework)",
                 new { packageKey = package.Key, targetFramework});
         }
     }
 }

        public override void ExecuteCommand()
        {
            // The backup policy:
            //  1. Keep 2 rolling 30 minute backups
            //  2. Keep the last backup (from 23:30 - 23:59) of the past two days
            //  3. Delete any backup from before the past two days
            
            // The result is:
            //  1 Active Database
            //  1 30min-old Backup
            //  1 60min-old Backup
            //  1 24hr-old (at most) Backup
            //  1 48hr-old (at most) Backup

            // TODO: Parameterize the policy (i.e. BackupPeriod, RollingBackupCount, DailyBackupCount, etc.)

            var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
            using (var connection = new SqlConnection(cstr))
            using (var db = new SqlExecutor(connection))
            {
                connection.Open();

                // Get the list of backups
                var backups = db.Query<Db>(
                    "SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%'",
                    new { state = Util.OnlineState })
                    .Select(d => new OnlineDatabaseBackup(Util.GetDatabaseServerName(ConnectionString), d.Name, d.State))
                    .OrderByDescending(b => b.Timestamp)
                    .ToList();

                // Any currently copying are safe
                var keepers = new HashSet<string>();
                keepers.AddRange(backups.Where(b => b.State == Util.CopyingState).Select(b => b.DatabaseName));
                
                // The last 2 online databases are definitely safe
                keepers.AddRange(backups.Where(b => b.State == Util.OnlineState).Take(2).Select(b => b.DatabaseName));
                Log.Info("Selected most recent two backups: {0}", String.Join(", ", keepers));

                // Group by day, and skip any from today
                var days = backups
                    .GroupBy(b => b.Timestamp.Value.Date)
                    .OrderByDescending(g => g.Key)
                    .Where(g => g.Key < DateTime.UtcNow.Date); // .Date gives us the current day at 00:00 hours, so "<" means previous day and earlier
    
                // Keep the last backup from each of the previous two days
                var dailyBackups = days
                    .Take(2) // Grab the last two days
                    .Select(day => day.OrderByDescending(b => b.Timestamp.Value).First()); // The last backup from each day
                Log.Info("Selected most recent two daily backups: {0}", String.Join(", ", dailyBackups.Select(b => b.DatabaseName)));

                // Verify data
                var brokenDays = dailyBackups.Where(b => b.Timestamp.Value.TimeOfDay < new TimeSpan(23, 30, 00));
                if(brokenDays.Any()) {
                    foreach(var brokenDay in brokenDays) {
                        Log.Warn("Daily backups for {0} are from earlier than 23:30 hours?", brokenDay.Timestamp.Value.DateTime.ToShortDateString());
                    }
                }
                var exportedDailyBackups = days.Skip(2).Select(day => day.Last());
                var client = BackupStorage.CreateCloudBlobClient();
                var container = client.GetContainerReference("database-backups");
                foreach (var exportedDaily in exportedDailyBackups)
                {
                    // We should be able to find a backup blob
                    string blobName = exportedDaily.DatabaseName + ".bacpac";
                    var blob = container.GetBlockBlobReference(blobName);
                    if (!blob.Exists())
                    {
                        // Derp?
                        Log.Warn("Expected {0} blob to exist but it hasn't been exported!", blob.Name);
                        keepers.Add(exportedDaily.DatabaseName); // Keep it for now.
                    }
                }

                // Keep those backups!
                keepers.AddRange(dailyBackups.Select(b => b.DatabaseName));

                // Figure out how many we're keeping
                Log.Info("Keeping the following Backups: {0}", String.Join(", ", keepers));

                if (keepers.Count < 2)
                {
                    // Abort!
                    Log.Warn("About to clean too many backups. Aborting until we have enough to be in-policy.");
                }
                else
                {
                    // Delete the rest!
                    foreach (var backup in backups.Where(b => !keepers.Contains(b.DatabaseName)))
                    {
                        if (!WhatIf)
                        {
                            db.Execute("DROP DATABASE " + backup.DatabaseName);
                        }
                        Log.Info("Deleted {0}", backup.DatabaseName);
                    }
                }
            }
        }