C# SqlExecutor类代码示例

 public InteractiveSqlApplicationTests()
 {
     var sqlExecutor = new SqlExecutor(SqlServerConfiguration.ReadAppSettings());
     var hintGenerator = new HintGenerator();
     m_Application = new InteractiveSqlApplication(m_Logger, sqlExecutor, hintGenerator, CreateCourse());
     m_Application.Start();
 }

        public int RunScriptsToUpdate(Server disposableDbServer, string scriptsFolderPath, string dbname)
        {
            SqlExecutor = new SqlExecutor(disposableDbServer);
            if (!scriptsFolderPath.TrimEnd('/').TrimEnd('\\').EndsWith(FolderName))
            {
                var type = GetType();
                var suffixPos = type.Name.IndexOf("Manager");
                if (suffixPos < 1) {
                    throw new InvalidOperationException("Derived classes of UpdatableManager should have the -Manager suffix as well");
                }
                var scriptType = type.Name.Substring(0, suffixPos);
                throw new ArgumentException(String.Format("scriptsFolderPath must contain {0}s", scriptType),
                    "scriptsFolderPath");
            }

            int count = 0;
            IEnumerable<string> scriptPaths = Directory.GetFileSystemEntries(scriptsFolderPath, "*.sql");
            foreach (var scriptPath in scriptPaths)
            {
                var scriptFilename = Path.GetFileName(scriptPath);

                var scriptContents = File.ReadAllText(scriptPath);

                var scriptContentsWithAlter = ReplaceCreateWithAlter(scriptContents);

                try{
                    Run(scriptContentsWithAlter, dbname, scriptFilename);
                } catch {
                    //maybe ALTER failed because the element is still not there...
                    Run(scriptContents, dbname, scriptFilename);
                }
                count++;
            }
            return count;
        }

 public DisposableDbManager(string dbCreationPath, Server disposableDbServer, string dbNameInVcs)
 {
     _dbCreationPath = dbCreationPath;
     _disposableDbServer = disposableDbServer;
     _sqlExecutor = new SqlExecutor(_disposableDbServer);
     _dbNameInVcs = dbNameInVcs;
 }

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

            Log.Info("Listing backups for server '{0}':", dbServer);
            
            using (var sqlConnection = new SqlConnection(masterConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var dbs = dbExecutor.Query<Db>(
                    "SELECT name FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state",
                    new { state = Util.OnlineState });

                foreach(var db in dbs)
                {
                    var timestamp = Util.GetDatabaseNameTimestamp(db);
                    var date = Util.GetDateTimeFromTimestamp(timestamp);

                    Log.Info("{0} ({1})", timestamp, date);
                }
            }
        }

 public static bool BackupIsInProgress(SqlExecutor dbExecutor)
 {
     return dbExecutor.Query<Database>(
         "SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state",
         new { state = CopyingState })
         .Any();
 }

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

            Log.Trace("Deleting old warehouse backups for server '{0}':", dbServer);

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

                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    var dbs = dbExecutor.Query<Db>(
                        "SELECT name FROM sys.databases WHERE name LIKE 'WarehouseBackup_%' AND state = @state",
                        new { state = Util.OnlineState });

                    foreach (var db in dbs)
                    {
                        var timestamp = Util.GetDatabaseNameTimestamp(db);
                        var date = Util.GetDateTimeFromTimestamp(timestamp);
                        if (DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)) > date)
                            DeleteDatabaseBackup(db, dbExecutor);
                    }
                }
            }
        }

        private void CreateReportForVSTask()
        {
            string[] VsQuery = new JavaScriptSerializer().Deserialize<string[]>(ReportHelpers.Load(StorageAccount, "VsVersion.json", ContainerName));
            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();
                    DateTime date = DateTime.UtcNow.AddDays(-LastNDays);
                    List<VsRequest> requests = new List<VsRequest>();
                    foreach (string each in VsQuery)
                    {
                        try
                        {
                            var request = dbExecutor.Query<Int32>(string.Format(sqlQueryForVS, date.ToString("yyyy-MM-dd"), each)).SingleOrDefault();
                            requests.Add(new VsRequest("VS" + each, request.ToString()));
                        }

                        catch
                        {
                            requests.Add(new VsRequest("VS" + each, "0"));
                        }

                    }
                    var json = new JavaScriptSerializer().Serialize(requests);
                    ReportHelpers.CreateBlob(StorageAccount, "VsTrend" + LastNDays.ToString() + "Day.json", ContainerName, "application/json", ReportHelpers.ToStream(json));



                }
            }
        }

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

        public override void ExecuteCommand()
        {
            using (var masterDbConnection = new SqlConnection(Util.GetMasterConnectionString(ConnectionString)))
            using (var masterDbExecutor = new SqlExecutor(masterDbConnection))
            {
                masterDbConnection.Open();

                var restoreDbName = CopyDatabaseForRestore(
                    masterDbExecutor);

                using (var restoreDbConnection = new SqlConnection(Util.GetConnectionString(ConnectionString, restoreDbName)))
                using (var restoreDbExecutor = new SqlExecutor(restoreDbConnection))
                {
                    restoreDbConnection.Open();

                    PrepareDataForRestore(
                        restoreDbExecutor);

                    RenameLiveDatabase(
                        masterDbExecutor);

                    RenameDatabaseBackup(
                        masterDbExecutor,
                        restoreDbName);
                }
            }
        }

        private void CreateWeeklyStatReportFor(string connectionString,string sqlQuery,string reportName)
        {
            startingTime = new DateTime(Year, UnixTimeStampUtility.GetMonthNumber(Month), 01); //initialize to day 01 of the given month.
            DateTime monthEndTime = new DateTime(Year, UnixTimeStampUtility.GetMonthNumber(Month), UnixTimeStampUtility.GetDaysInMonth(Month));
            List<Tuple<string, string>> uploadsDataPoints = new List<Tuple<string, string>>();
            int week = 1;
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();

                    while (startingTime <= monthEndTime)
                    {
                        DateTime endTime = startingTime.AddDays(7);
                        if (endTime > monthEndTime) endTime = monthEndTime;
                        try
                        {
                            var count = dbExecutor.Query<Int32>(string.Format(sqlQuery, startingTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"))).SingleOrDefault();
                            uploadsDataPoints.Add(new Tuple<string, string>("Week" + week++, count.ToString()));
                        }
                        catch (NullReferenceException)
                        {
                            uploadsDataPoints.Add(new Tuple<string, string>("Week" + week++, "0"));
                        }
                        
                        startingTime = startingTime.AddDays(7);
                    }
                }
            }
            JArray reportObject = ReportHelpers.GetJson(uploadsDataPoints);
            ReportHelpers.CreateBlob(ReportStorage, reportName + Month + "MonthlyReport.json", "dashboard", "application/json", ReportHelpers.ToStream(reportObject));
        }

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

 public static bool BackupIsInProgress(SqlExecutor dbExecutor, string backupPrefix)
 {
     return dbExecutor.Query<Db>(
         // Not worried about SQL Injection here :). This is an admin tool.
         "SELECT name, state FROM sys.databases WHERE name LIKE '" + backupPrefix + "%' AND state = @state",
         new { state = CopyingState })
         .Any();
 }

 public LessonModule(InteractiveSqlApplication application, SqlExecutor sqlExecutor)
 {
     m_Application = application;
     m_SqlExecutor = sqlExecutor;
     var firstLesson = m_Application.Course.Lessons.First();
     Get["/"] = parameters => Response.AsRedirect(firstLesson.Url());
     Get["/lesson/{lessonName}"] = parameters => RenderLesson(parameters.lessonName);
 }

 public void EmptyQueriesCauseFailedResult()
 {
     var application = new SqlExecutor(m_SqlServerConfiguration);
     application.SetUpDatabase(" ");
     var result = application.ExecuteQuery("");
     
     Assert.Equal("Query is empty", ExpectError(result));
 }