C# SqlExecutor.Query方法代码示例

        private void CreateReportForDataBaseEvents()
        {           
            var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
            var currentDbName = Util.GetDbName(ConnectionString.ConnectionString);
            using (var sqlConnection = new SqlConnection(masterConnectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();                
                       
                        var usageSeconds = dbExecutor.Query<DatabaseEvent>(string.Format("select start_time, end_time,event_type,event_count,description from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and severity = 2", DateTime.UtcNow.AddHours(-LastNHours).ToString("yyyy-MM-dd hh:mm:ss"),DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
                        var json = new JavaScriptSerializer().Serialize(usageSeconds);
                        ReportHelpers.CreateBlob(StorageAccount, "DBDetailed" + LastNHours.ToString() +  "Hour.json", ContainerName, "application/json", ReportHelpers.ToStream(json));

                        var throttlingEventCount = dbExecutor.Query<Int32>(string.Format("select count(*) from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"),DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"),currentDbName)).SingleOrDefault();
                        var additional_data = dbExecutor.Query<string>(string.Format("select additional_data from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
                        AlertThresholds thresholdValues = new JavaScriptSerializer().Deserialize<AlertThresholds>(ReportHelpers.Load(StorageAccount, "Configuration.AlertThresholds.json", ContainerName));

                        StringBuilder sb = new StringBuilder();
                        foreach (string data in additional_data)
                        {
                            if (data != null) sb.Append(data+"\n");
                        }
                        if(throttlingEventCount > thresholdValues.DatabaseThrottlingEventErrorThreshold && LastNHours == 1)
                        {
                            new SendAlertMailTask
                            {
                                AlertSubject = "Error: SQL Azure DB alert activated for throttling/deadlock event",
                                Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Error Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventErrorThreshold, throttlingEventCount,sb.ToString()),                               
                                AlertName = "Error: SQL Azure DB throttling/deadlock event",
                                Component = "SQL Azure Database",
                                Level = "Error"
                            }.ExecuteCommand();
                        }
                        else if (throttlingEventCount > thresholdValues.DatabaseThrottlingEventWarningThreshold && LastNHours == 1)
                        {
                            new SendAlertMailTask
                            {
                                AlertSubject = "Warning: SQL Azure DB alert activated for throttling/deadlock event",
                                Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Warning Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventWarningThreshold, throttlingEventCount,sb.ToString()),
                                AlertName = "Warning: SQL Azure DB throttling/deadlock event",
                                Component = "SQL Azure Database",
                                Level = "Warning"
                            }.ExecuteCommand();
                        }
                }               
               
            }
        }

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

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

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

        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 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 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 override void ExecuteCommand()
        {
            var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
            using (var connection = new SqlConnection(cstr))
            using (var db = new SqlExecutor(connection))
            {
                connection.Open();

                // Snap the current date just in case we are running right on the cusp
                var today = DateTime.UtcNow;

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

                // Grab end-of-day backups from days before today
                var dailyBackups = backups
                    .GroupBy(b => b.Timestamp.Value.Date)
                    .Where(g => g.Key < today.Date)
                    .Select(g => g.OrderByDescending(b => b.Timestamp.Value).Last())
                    .ToList();
                Log.Info("Found {0} daily backups to export", dailyBackups.Count);

                // Start exporting them
                foreach (var dailyBackup in dailyBackups)
                {
                    if (dailyBackup.State != Util.OnlineState)
                    {
                        Log.Info("Skipping '{0}', it is still being copied", dailyBackup.DatabaseName);
                    }
                    else
                    {
                        if (dailyBackup.Timestamp.Value.TimeOfDay < new TimeSpan(23, 30, 00))
                        {
                            Log.Warn("Somehow, '{0}' is the only backup from {1}. Exporting it to be paranoid",
                                dailyBackup.DatabaseName,
                                dailyBackup.Timestamp.Value.Date.ToShortDateString());
                        }
                        Log.Info("Exporting '{0}'...", dailyBackup.DatabaseName);
                        (new ExportDatabaseTask()
                        {
                            ConnectionString = new SqlConnectionStringBuilder(ConnectionString.ConnectionString)
                            {
                                InitialCatalog = dailyBackup.DatabaseName
                            },
                            DestinationStorage = StorageAccount,
                            DestinationContainer = "database-backups",
                            SqlDacEndpoint = SqlDacEndpoint,
                            WhatIf = WhatIf
                        }).Execute();
                    }
                }
            }
        }

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

                var externalPackages = dbExecutor.Query<Package>(@"
                    SELECT pr.Id, p.Version, p.ExternalPackageUrl
                    FROM Packages p 
                        JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey
                    WHERE p.ExternalPackageUrl IS NOT NULL
                    ORDER BY Id, Version");

                foreach (Package pkg in externalPackages)
                {
                    Console.WriteLine();
                    HttpClient client = new HttpClient();
                    var responseTask = client.GetAsync(pkg.ExternalPackageUrl);
                    var response = responseTask.Result;
                    if (!response.IsSuccessStatusCode)
                    {
                        Console.WriteLine("Found broken package: " + response.StatusCode + "  " + pkg.ExternalPackageUrl);
                        Console.WriteLine("You should ask the package owner to unlist the package " + pkg.Id + " " + pkg.Version);
                    }

                    var bytesTask = response.Content.ReadAsByteArrayAsync();
                    byte[] bytes = bytesTask.Result;
                    var blobClient = CreateBlobClient();
                    var packagesBlobContainer = Util.GetPackagesBlobContainer(blobClient);
                    var packageFileBlob = Util.GetPackageFileBlob(
                        packagesBlobContainer,
                        pkg.Id,
                        pkg.Version);
                    var fileName = Util.GetPackageFileName(
                        pkg.Id,
                        pkg.Version);
                    if (packageFileBlob.Exists())
                    {
                        Console.WriteLine("SKIPPED! Package file blob " + fileName + " already exists");
                    }
                    else
                    {
                        Console.WriteLine("Saving the package file " + pkg.ExternalPackageUrl + " to blob storage as " + fileName);
                        if (!WhatIf)
                        {
                            packageFileBlob.UploadFromStream(
                                new MemoryStream(bytes),
                                AccessCondition.GenerateIfNoneMatchCondition("*"));
                        }
                    }
                }
            }
        }

        private DatabaseSize GetDataSize(string connectionString,int error, int warning)
        {
            
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();
                    int sizeInMb = dbExecutor.Query<int>(SqlQueryForDbSize).SingleOrDefault();
                    Int64 maxSizeInMb = dbExecutor.Query<Int64>(SqlQueryForMaxSize).SingleOrDefault();
                    double percentUsed = (sizeInMb/maxSizeInMb)*100;                    
                    string edition = dbExecutor.Query<string>(SqlQueryForEdition).SingleOrDefault();
                    string dbName = Util.GetDbName(connectionString);

                    if (percentUsed > error)
                    {
                        new SendAlertMailTask
                        {
                            AlertSubject = string.Format("Error: SQL Azure database size alert activated for {0}",dbName),
                            Details = string.Format("DB Size excced the Error threshold percent.Current Used % {0}, Threshold % : {1}", percentUsed, error ),
                            AlertName = "Error: SQL Azure DB alert for database size limit",
                            Component = string.Format("SQL Azure database-{0}",dbName),
                            Level = "Error"
                        }.ExecuteCommand();
                    }
                    else if (percentUsed > warning)
                    {
                        new SendAlertMailTask
                        {
                            AlertSubject = string.Format("Warning: SQL Azure database size alert activated for {0}", dbName),
                            Details = string.Format("DB Size excced the Warning threshold percent.Current Used % {0}, Threshold % : {1}", percentUsed, warning),
                            AlertName = "Warning: SQL Azure DB alert for database size limit",
                            Component = string.Format("SQL Azure database-{0}", dbName),
                            Level = "Warning"
                        }.ExecuteCommand();
                    }

                    return new DatabaseSize(dbName, sizeInMb, maxSizeInMb, edition);
                }
            }
        }

 public List<DatabaseRequest> RefreshDatebaseRequest() 
 {
     List<Tuple<string, string>> connectionCountDataPoints = new List<Tuple<string, string>>();
     using (var sqlConnection = new SqlConnection(ConnectionString))
     {
         using (var dbExecutor = new SqlExecutor(sqlConnection))
         {
             sqlConnection.Open();
             var requests = dbExecutor.Query<DatabaseRequest>("SELECT t.text, r.start_time, r.status, r.command, r.wait_type, r.wait_time FROM sys.dm_exec_requests r OUTER APPLY sys.dm_exec_sql_text(sql_handle) t​");
             return requests.ToList();
         }
     }   
 }

        public static Db GetLastBackup(SqlExecutor dbExecutor, string backupNamePrefix)
        {
            var allBackups = dbExecutor.Query<Db>(
                "SELECT name, state FROM sys.databases WHERE name LIKE '" + backupNamePrefix + "%' AND state = @state",
                new { state = OnlineState });        
            var orderedBackups = from db in allBackups
                                 let t = ParseNewTimestamp(BackupNameFormat.Match(db.Name).Groups["timestamp"].Value)
                                 where t != null
                                 orderby t descending
                                 select db;

            return orderedBackups.FirstOrDefault();
        }

        private void CreateReportForIndexFragmentation()
        {        
            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();
                    AlertThresholds thresholdValues = new JavaScriptSerializer().Deserialize<AlertThresholds>(ReportHelpers.Load(StorageAccount, "Configuration.AlertThresholds.json", ContainerName));
                    var fragmentationDetails = dbExecutor.Query<DatabaseIndex>(string.Format(sqlQueryForIndexFragmentation,thresholdValues.DatabaseIndexFragmentationPercentErrorThreshold));
                    var json = new JavaScriptSerializer().Serialize(fragmentationDetails);
                    ReportHelpers.CreateBlob(StorageAccount, "DBIndexFragmentation.json", ContainerName, "application/json", ReportHelpers.ToStream(json));
                }

            }
        }

        public List<DatabaseEvent> RefreshDatabaseEvent() 
        {
            var masterConnectionString = new SqlConnectionStringBuilder(ConnectionString) { InitialCatalog = "master" }.ToString();
            var currentDbName = new SqlConnectionStringBuilder(ConnectionString).InitialCatalog;
            using (var sqlConnection = new SqlConnection(masterConnectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();

                    var usageSeconds = dbExecutor.Query<DatabaseEvent>(string.Format("select start_time, end_time,event_type,event_count,description from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and severity = 2", DateTime.UtcNow.AddHours(-LastNHours).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
                    return usageSeconds.ToList();
                }

            }
        }

        private void GetCurrentValueAndAlert(string sqlQuery,string blobName,int error, int warning)
        {
            List<Tuple<string, string>> connectionCountDataPoints = new List<Tuple<string, string>>();
            StringBuilder message = new StringBuilder();
            if (blobName.Equals("DBSuspendedRequests")) 
            {
                message.Append("all requests wait_type are");
                foreach (DatabaseRequest rq in GetDBRequest())
                {
                    message.Append(rq.Wait_Type + "\n");
                }
            }

            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();
                    var connectionCount = dbExecutor.Query<Int32>(sqlQuery).SingleOrDefault();
                    if(connectionCount > error)
                    {
                        new SendAlertMailTask
                        {
                            AlertSubject = string.Format("Error: SQL Azure database alert activated for {0}", blobName),
                            Details = string.Format("Number of {0} exceeded the Error threshold value. Threshold value  {1}, Current value : {2}.{3}",blobName,error,connectionCount,message),
                            AlertName = "Error: SQL Azure DB alert for connections/requests count",
                            Component = "SQL Azure database",
                            Level = "Error"
                        }.ExecuteCommand();
                    }
                    else if (connectionCount > warning)
                    {
                        new SendAlertMailTask
                        {
                            AlertSubject = string.Format("Warning: SQL Azure database alert activated for {0}", blobName),
                            Details = string.Format("Number of {0} exceeded the Warning threshold value. Threshold value  {1}, Current value : {2}.{3}", blobName, warning, connectionCount,message),
                            AlertName = "Warning: SQL Azure DB alert for connections/requests count",
                            Component = "SQL Azure database",
                            Level = "Warning"
                        }.ExecuteCommand();
                    }
                   
                    ReportHelpers.AppendDatatoBlob(StorageAccount, blobName + string.Format("{0:MMdd}", DateTime.Now) + ".json", new Tuple<string, string>(String.Format("{0:HH:mm}", DateTime.Now), connectionCount.ToString()), 50, ContainerName);
                }
            }                    
        }