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 async Task <IBookingIncidentData> BuildAux(IBookingIncidentData result) { var auxQueryStore = QueryStoreFactory.GetQueryStore(); #region KarveCode Generator for query multiple // Code Generated that concatains multiple queries to be executed by QueryMultipleAsync. auxQueryStore.AddParamCount(QueryType.QueryOffice, dto.OFICINA); auxQueryStore.AddParamCount(QueryType.QuerySupplierSummary, dto.PROVEE); auxQueryStore.AddParamCount(QueryType.QueryVehicleSummary, dto.VEHI); auxQueryStore.AddParamCount(QueryType.ClientSummaryExtended, dto.CLIENTE); auxQueryStore.AddParamCount(QueryType.IncidentType, dto.COINRE); #endregion var query = auxQueryStore.BuildQuery(); using (var connection = SqlExecutor.OpenNewDbConnection()) { if (connection != null) { var multipleResult = await connection.QueryMultipleAsync(query).ConfigureAwait(false); result.Valid = ParseResult(result, multipleResult); } } return(result); }
public async Task <IEnumerable <DtoTransfer> > GetPagedAsyncHelper <DtoTransfer, T>(string query, int startIndex, int pageSize) where DtoTransfer : class where T : class { IEnumerable <DtoTransfer> result = new List <DtoTransfer>(); var splittedQuery = query.Trim().Split(' '); var outstring = "SELECT TOP {0} START AT {1} "; var stringBuilder = new StringBuilder(); stringBuilder.Append(outstring); for (int i = 1; i < splittedQuery.Length; ++i) { stringBuilder.Append(query[i]); stringBuilder.Append(" "); } outstring = string.Format(stringBuilder.ToString(), startIndex, pageSize); using (var dbConnection = SqlExecutor.OpenNewDbConnection()) { try { var values = await dbConnection.QueryAsync <T>(query).ConfigureAwait(false); result = _mapper.Map <IEnumerable <DtoTransfer> >(values); } catch (System.Exception e) { throw new DataLayerException("Error during mapping an entity", e); } } return(result); }
/// <summary> /// Get the helper dataset for the async layer. /// </summary> /// <param name="assistQuery">Assist query.</param> /// <param name="assitTableName">Assist table</param> /// <returns></returns> public async Task <DataSet> GetAsyncHelper(string assistQuery, string assitTableName) { DataSet set = await SqlExecutor.AsyncDataSetLoad(assistQuery); set.Tables[0].TableName = assitTableName; return(set); }
/// <summary> /// This returns the way of data layer. /// </summary> /// <param name="assistQuery">Assist query</param> /// <returns></returns> public async Task <IEnumerable <T> > GetAsyncHelper <T>(string assistQuery) { IDbConnection connection = SqlExecutor.Connection; IEnumerable <T> result = null; bool isOpen = false; if (connection == null) { isOpen = SqlExecutor.Open(); } else { if (connection.State != ConnectionState.Open) { isOpen = SqlExecutor.Open(); } } if (isOpen) { connection = SqlExecutor.Connection; try { result = await connection.QueryAsync <T>(assistQuery); } finally { connection.Close(); } } return(result); }
/// <summary> /// GetUniqueMappedId /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public async Task <string> GetMappedUniqueId <DtoTransfer, T>(DtoTransfer entity) where T : class { string uniqueId; if (entity == null) { return(String.Empty); } var entityValue = _mapper.Map <DtoTransfer, T>(entity); if (SqlExecutor.Connection.State != ConnectionState.Open) { using (var connection = SqlExecutor.OpenNewDbConnection()) { uniqueId = await GetScopedUniqueId <T>(connection, entityValue); } } else { using (IDbConnection connection = SqlExecutor.OpenNewDbConnection()) { uniqueId = await GetScopedUniqueId <T>(connection, entityValue); } } return(uniqueId); }
/// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public async Task <string> GetUniqueId <T>(T entity) where T : class { string uniqueId = string.Empty; if (SqlExecutor.Connection.State != ConnectionState.Open) { using (var connection = SqlExecutor.OpenNewDbConnection()) { if (connection != null) { uniqueId = await GetScopedUniqueId <T>(connection, entity); } } } else { using (IDbConnection connection = SqlExecutor.OpenNewDbConnection()) { uniqueId = await GetScopedUniqueId <T>(connection, entity); } } if (uniqueId == string.Empty) { throw new DataAccessLayerException("Not possible to generate an unique identifer"); } return(uniqueId); }
IList <Package> GetPackagesToBackUp(long?lastBackupId, bool forcedRecheck) { using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString)) using (var dbExecutor = new SqlExecutor(sqlConnection)) { sqlConnection.Open(); Log.Info("Getting {1} packages to back up (since Package #{0})...", lastBackupId.HasValue ? lastBackupId.Value.ToString() : "?", forcedRecheck ? "all" : "1000"); StringBuilder uglySqlInjectionyStringBuilder = new StringBuilder(); // We trust our own code so it's not so SQL Injectiony... uglySqlInjectionyStringBuilder.Append("SELECT "); if (!forcedRecheck) { // Back up in 1000 package chunks uglySqlInjectionyStringBuilder.Append("TOP 1000 "); } uglySqlInjectionyStringBuilder.Append("p.[Key], pr.Id, p.Version, p.Hash "); uglySqlInjectionyStringBuilder.Append("FROM Packages p "); uglySqlInjectionyStringBuilder.Append("JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey "); uglySqlInjectionyStringBuilder.Append("WHERE p.ExternalPackageUrl IS NULL "); if (lastBackupId != null) { uglySqlInjectionyStringBuilder.Append("AND p.[Key] > " + lastBackupId.Value + " "); } uglySqlInjectionyStringBuilder.Append("ORDER BY Id, Version, Hash"); var list = dbExecutor.Query <Package>(uglySqlInjectionyStringBuilder.ToString()).ToList(); Log.Info("Got {0} packages.", list.Count); return(list); } }
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; }
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() { 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); } } }
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 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 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() { // 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 async Task Should_Load_AValidReservation() { var codigo = string.Empty; using (var dbConnection = SqlExecutor.OpenNewDbConnection()) { var connection = await dbConnection.GetPagedAsync <RESERVAS1>(1, 2).ConfigureAwait(false); var item = connection.FirstOrDefault <RESERVAS1>(); codigo = item.NUMERO_RES; } IBookingData booking = null; Stopwatch start = new Stopwatch(); start.Start(); booking = await _bookingDataServices.GetDoAsync(codigo).ConfigureAwait(false); //Assert.DoesNotThrowAsync(async () => booking = await _bookingDataServices.GetDoAsync(codigo).ConfigureAwait(false)); var elapse = start.ElapsedMilliseconds; start.Stop(); TestContext.Out.WriteLine("Elapsed booking retrieval time " + elapse); Assert.NotNull(booking); Assert.IsTrue(booking.Valid); Assert.NotNull(booking.Value); Assert.AreEqual(booking.Value.NUMERO_RES, codigo); }
public static TicketCounts[] GetData() { List <TicketCounts> result = new List <TicketCounts>(); SqlCommand command = new SqlCommand(@"select p.name as ProductName, (select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt where t.productid = p2.productid and t.organizationid = @OrganizationID and t.ticketstatusid = ts.ticketstatusid and ts.isclosed = 0 and t.tickettypeid = tt.tickettypeid and tt.name = 'issues' and p.productid = p2.productid) as NumIssues, (select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt where t.productid = p2.productid and t.organizationid = @OrganizationID and t.ticketstatusid = ts.ticketstatusid and ts.isclosed = 0 and t.tickettypeid = tt.tickettypeid and tt.name = 'tasks' and p.productid = p2.productid) as NumTasks, (select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt where t.productid = p2.productid and t.organizationid = @OrganizationID and t.ticketstatusid = ts.ticketstatusid and ts.isclosed = 0 and t.tickettypeid = tt.tickettypeid and tt.name = 'bugs' and p.productid = p2.productid) as NumBugs, (select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt where t.productid = p2.productid and t.organizationid = @OrganizationID and t.ticketstatusid = ts.ticketstatusid and ts.isclosed = 0 and t.tickettypeid = tt.tickettypeid and tt.name = 'features' and p.productid = p2.productid) as NumFeatures From Products as p where p.organizationid = @OrganizationID order by p.name"); command.Parameters.AddWithValue("@OrganizationID", UserSession.LoginUser.OrganizationID); DataTable productTickets = SqlExecutor.ExecuteQuery(UserSession.LoginUser, command); foreach (DataRow thisRow in productTickets.Rows) { TicketCounts counts = new TicketCounts(); counts.Product = (string)thisRow["ProductName"]; counts.Issues = (int)thisRow["NumIssues"]; counts.Tasks = (int)thisRow["NumTasks"]; counts.Bugs = (int)thisRow["NumBugs"]; counts.Features = (int)thisRow["NumFeatures"]; result.Add(counts); } return(result.ToArray()); }
protected void WithConnection(Action <SqlConnection, SqlExecutor> act) { using (var c = OpenConnection()) using (var e = new SqlExecutor(c)) { act(c, e); } }
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); }
protected virtual void ExecuteStatementsWithoutTransaction( [NotNull] IEnumerable <SqlBatch> sqlStatements, [NotNull] RelationalConnection connection) { Check.NotNull(sqlStatements, "sqlStatements"); Check.NotNull(connection, "connection"); SqlExecutor.ExecuteNonQuery(connection, null, sqlStatements); }
public async Task ThenTheNameOfTheSelectedAssociatedServiceIsDisplayedOnTheAssociatedServiceEditForm() { var itemId = (string)Context[ContextKeys.ChosenItemId]; var query = "Select Name FROM [dbo].[CatalogueItem] where CatalogueItemId=@itemId"; var expectedSolutionName = (await SqlExecutor.ExecuteAsync <string>(Test.BapiConnectionString, query, new { itemId })).Single(); Test.Pages.OrderForm.TextDisplayedInPageTitle(expectedSolutionName).Should().BeTrue(); }
public QueryMapper(SqlExecutor <TArgs, TResult> query, Expression <Func <TResult, TArgs, TMapped> > mapper) { Query = query ?? throw new ArgumentNullException(nameof(query)); var(rpu, m) = EnsureComplexMap(mapper ?? throw new ArgumentNullException(nameof(mapper))); RequiresPropertyUnwrap = rpu; Mapper = m; }
public async Task Delete(string connectionString) { var relatedQuery = @"DELETE FROM dbo.RelatedOrganisations WHERE OrganisationId=@organisationId OR RelatedOrganisationId=@organisationId;"; await SqlExecutor.ExecuteAsync <Organisation>(connectionString, relatedQuery, this); var query = @"DELETE FROM dbo.Organisations WHERE OrganisationId=@organisationId OR OdsCode=@odsCode"; await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, this); }
public static async Task DeleteContactsForOrdersNoLongerInDb(string connectionString) { var query = "DELETE FROM dbo.Contact " + "WHERE ContactId NOT IN (SELECT OrganisationContactId FROM dbo.[Order])" + "OR ContactId NOT IN (SELECT SupplierContactId FROM dbo.[Order];"; await SqlExecutor.ExecuteAsync <string>(connectionString, query, null); }
public static async Task <OrderingParty> GetByOdsCode(this OrderingParty orderingParty, string connectionString) { var query = "SELECT * FROM OrderingParty WHERE OdsCode=@OdsCode"; var results = await SqlExecutor.ExecuteAsync <OrderingParty>(connectionString, query, orderingParty); return(results.SingleOrDefault()); }
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); }
protected bool WithMasterConnection(Func <SqlConnection, SqlExecutor, bool> act) { using (var c = OpenMasterConnection()) using (var e = new SqlExecutor(c)) { return(act(c, e)); } }
public async Task TestStringEqualsIgnoreCaseIsNullEqualsNull() { var result = await SqlExecutor.Execute("SELECT * FROM nulltest WHERE isnull is null"); var expected = TestData.GetNullTestData().Where(x => x.IsNull == null).Select(x => new { x.IsNull }).AsQueryable(); AssertAreEqual(expected, result.Result); }
public static async Task <SupplierDetails> GetSupplierWithId(string supplierId, string connectionString) { var query = "SELECT [Id] as 'SupplierId', [Name], [Address] FROM Supplier WHERE Id = @supplierId"; var result = await SqlExecutor.ExecuteAsync <SupplierDetails>(connectionString, query, new { supplierId }); return(result.Single()); }
public async Task TestSumGroupBySingle() { var result = await SqlExecutor.Execute("select sum(Acctbal) from customer group by name"); var expected = TpchData.Customers.GroupBy(x => x.Name).Select(x => new { sum = x.Sum(y => y.Acctbal) }).AsQueryable(); AssertAreEqual(expected, result.Result); }
public async Task TestExecuteScalar() { var result = await SqlExecutor.ExecuteScalar($"SELECT count(*) FROM customer"); var expected = TpchData.Customers.Count; Assert.That(result, Is.EqualTo(expected)); }
public async Task TestSelectCustomerName() { var result = await SqlExecutor.Execute("select name from customer"); var expected = TpchData.Customers.Select(x => new { x.Name }).AsQueryable(); AssertAreEqual(expected, result.Result); }
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 void TestParameterNotFound() { Assert.That(async() => { SqlParameters sqlParameters = new SqlParameters(); await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE orderkey > @P0", sqlParameters); }, Throws.InstanceOf <SqlErrorException>().With.Message.EqualTo("The parameter @P0 could not be found, did you have include @ before the parameter name?")); }
public async Task TestSumDividedBySum() { var result = await SqlExecutor.Execute("select sum(c.Acctbal) / sum(c.Custkey) from customer c"); var expected = TpchData.Customers.GroupBy(x => 1).Select(x => new { sum = x.Sum(y => y.Acctbal) / x.Sum(y => y.Custkey) }).AsQueryable(); AssertAreEqual(expected, result.Result); }
public void EmptyQueriesCauseFailedResult() { var application = new SqlExecutor(m_SqlServerConfiguration); application.SetUpDatabase(" "); var result = application.ExecuteQuery(""); Assert.Equal("Query is empty", ExpectError(result)); }
public void BadlyFormedQueriesCauseFailedResult() { var application = new SqlExecutor(m_SqlServerConfiguration); application.SetUpDatabase(" "); var result = application.ExecuteQuery("SELECTEROO"); Assert.Equal("Could not find stored procedure 'SELECTEROO'.", ExpectError(result)); }
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 async Task TestSumWithOuterAddition() { var result = await SqlExecutor.Execute("select sum(c.Acctbal) + 1 from customer c group by c.name"); var expected = TpchData.Customers.GroupBy(x => x.Name).Select(x => new { sum = x.Sum(y => y.Acctbal) + 1 }).AsQueryable(); AssertAreEqual(expected, result.Result); }
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 async Task TestSubQueryAdditionInOuter() { var result = await SqlExecutor.Execute($"select c.sum + 1 from (select sum(Acctbal) AS sum from customer) c"); var expected = TpchData.Customers .GroupBy(x => 1).Select(x => new { sum = x.Sum(y => y.Acctbal) + 1 }).AsQueryable(); AssertAreEqual(expected, result.Result); }
public void TestSearchFunctionWithParameter() { Assert.That(async() => { SqlParameters sqlParameters = new SqlParameters() .Add(SqlParameter.Create("P0", "test")); await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE CONTAINS(*, @P0)", sqlParameters); }, Throws.InstanceOf <SqlErrorException>().With.Message.EqualTo("Search is not implemented for this table")); }
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 static DateTime GetLastBackupTime(SqlExecutor dbExecutor, string backupNamePrefix) { var lastBackup = GetLastBackup(dbExecutor, backupNamePrefix); if (lastBackup == null) return DateTime.MinValue; var timestamp = lastBackup.Name.Substring(backupNamePrefix.Length); return GetDateTimeFromTimestamp(timestamp); }
private static void RunScriptsInForDatabase(Server server, DbParams dbParams, IEnumerable<string> sqlFiles) { var executor = new SqlExecutor(server); var sqlFilesSortedAlphabetically = Order(sqlFiles); foreach(var sqlFile in sqlFilesSortedAlphabetically) { Console.WriteLine("Going to run " + sqlFile); executor.ExecuteNonQuery(File.ReadAllText(sqlFile), dbParams.DbName); } }
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("*")); } } } } }
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); }
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 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(); }
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 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 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(); } }
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 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 void SetUpRunsCreationSql() { var creationSql = @" CREATE TABLE JustTesting ( Id INT IDENTITY NOT NULL, Answer INT NOT null ) INSERT INTO JustTesting (Answer) VALUES (42) "; var application = new SqlExecutor(m_SqlServerConfiguration); application.SetUpDatabase(creationSql); var result = application.ExecuteQuery("SELECT Answer FROM JustTesting").ExpectSuccess(); Assert.Equal(42, result.Rows[0].ValueAtIndex<int>(0)); }
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(); } } }