| | | 1 | | namespace ZhEaIsNsAaBn.IntegrationTestingBases; |
| | | 2 | | |
| | | 3 | | /// <summary>Database helpers for integration tests: raw SQL, table checks, seeding and clearing data.</summary> |
| | | 4 | | public static class DatabaseTestUtilities |
| | | 5 | | { |
| | | 6 | | /// <summary>Runs a raw SQL command. Pass values as <paramref name="parameters"/> (<c>{0}</c>, <c>{1}</c> …), never |
| | | 7 | | /// <typeparam name="TDbContext">The context type.</typeparam> |
| | | 8 | | /// <param name="context">The context to run on.</param> |
| | | 9 | | /// <param name="sql">The SQL command with <c>{0}</c>-style placeholders.</param> |
| | | 10 | | /// <param name="parameters">The values for the placeholders.</param> |
| | | 11 | | /// <returns>The number of affected rows.</returns> |
| | | 12 | | public static Task<int> ExecuteSqlAsync< TDbContext >( TDbContext context, string sql, params object[] parameters ) |
| | | 13 | | where TDbContext : DbContext |
| | 3 | 14 | | => context.Database.ExecuteSqlRawAsync( sql, parameters ); |
| | | 15 | | |
| | | 16 | | /// <summary>Checks whether a table exists (any schema).</summary> |
| | | 17 | | /// <typeparam name="TDbContext">The context type.</typeparam> |
| | | 18 | | /// <param name="context">The context to query.</param> |
| | | 19 | | /// <param name="tableName">The table name, without schema.</param> |
| | | 20 | | /// <param name="cancellationToken">Token to cancel the operation.</param> |
| | | 21 | | /// <returns><see langword="true"/> when the table exists.</returns> |
| | | 22 | | public static async Task<bool> TableExistsAsync< TDbContext >( TDbContext context, string tableName, CancellationTok |
| | | 23 | | where TDbContext : DbContext |
| | | 24 | | { |
| | | 25 | | const string sql = "SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = {0}"; |
| | | 26 | | |
| | | 27 | | |
| | 6 | 28 | | var count = await context.Database |
| | 6 | 29 | | .SqlQueryRaw<int>( sql, tableName ) |
| | 6 | 30 | | .FirstOrDefaultAsync( cancellationToken ) |
| | 6 | 31 | | .ConfigureAwait( false ); |
| | | 32 | | |
| | 6 | 33 | | return count > 0; |
| | 6 | 34 | | } |
| | | 35 | | |
| | | 36 | | /// <summary>Counts the rows in a table.</summary> |
| | | 37 | | /// <typeparam name="TDbContext">The context type.</typeparam> |
| | | 38 | | /// <param name="context">The context to query.</param> |
| | | 39 | | /// <param name="tableName">The table name, without schema. It is quoted as an identifier.</param> |
| | | 40 | | /// <param name="cancellationToken">Token to cancel the operation.</param> |
| | | 41 | | /// <returns>The row count.</returns> |
| | | 42 | | public static Task<int> GetTableCountAsync< TDbContext >( TDbContext context, string tableName, CancellationToken ca |
| | | 43 | | where TDbContext : DbContext |
| | | 44 | | { |
| | 12 | 45 | | var sql = $"SELECT COUNT(*) AS [Value] FROM {QuoteName( tableName )}"; |
| | | 46 | | |
| | | 47 | | |
| | 12 | 48 | | return context.Database |
| | 12 | 49 | | .SqlQueryRaw<int>( sql ) |
| | 12 | 50 | | .FirstOrDefaultAsync( cancellationToken ); |
| | | 51 | | } |
| | | 52 | | |
| | | 53 | | /// <summary>Removes all rows from a table with <c>TRUNCATE TABLE</c>.</summary> |
| | | 54 | | /// <typeparam name="TDbContext">The context type.</typeparam> |
| | | 55 | | /// <param name="context">The context to run on.</param> |
| | | 56 | | /// <param name="tableName">The table name, without schema. It is quoted as an identifier.</param> |
| | | 57 | | /// <param name="cancellationToken">Token to cancel the operation.</param> |
| | | 58 | | /// <returns>A task that completes when the table is empty.</returns> |
| | | 59 | | public static Task TruncateTableAsync< TDbContext >( TDbContext context, string tableName, CancellationToken cancell |
| | | 60 | | where TDbContext : DbContext |
| | | 61 | | { |
| | 3 | 62 | | var sql = $"TRUNCATE TABLE {QuoteName( tableName )}"; |
| | | 63 | | |
| | | 64 | | |
| | 3 | 65 | | return context.Database.ExecuteSqlRawAsync( sql, cancellationToken ); |
| | | 66 | | } |
| | | 67 | | |
| | | 68 | | /// <summary>Adds the entities and saves them.</summary> |
| | | 69 | | /// <typeparam name="TDbContext">The context type.</typeparam> |
| | | 70 | | /// <typeparam name="TEntity">The entity type.</typeparam> |
| | | 71 | | /// <param name="context">The context to save with.</param> |
| | | 72 | | /// <param name="entities">The entities to add.</param> |
| | | 73 | | /// <param name="cancellationToken">Token to cancel the operation.</param> |
| | | 74 | | /// <returns>A task that completes when the entities are saved.</returns> |
| | | 75 | | public static async Task SeedTestDataAsync< TDbContext, TEntity >( TDbContext context, IEnumerable<TEntity> entities |
| | | 76 | | where TDbContext : DbContext |
| | | 77 | | where TEntity : class |
| | | 78 | | { |
| | 12 | 79 | | await context.Set<TEntity>().AddRangeAsync( entities, cancellationToken ).ConfigureAwait( false ); |
| | | 80 | | |
| | | 81 | | |
| | 12 | 82 | | await context.SaveChangesAsync( cancellationToken ).ConfigureAwait( false ); |
| | 12 | 83 | | } |
| | | 84 | | |
| | | 85 | | /// <summary>Loads and deletes every <typeparamref name="TEntity"/>, then saves.</summary> |
| | | 86 | | /// <typeparam name="TDbContext">The context type.</typeparam> |
| | | 87 | | /// <typeparam name="TEntity">The entity type.</typeparam> |
| | | 88 | | /// <param name="context">The context to delete with.</param> |
| | | 89 | | /// <param name="cancellationToken">Token to cancel the operation.</param> |
| | | 90 | | /// <returns>A task that completes when the entities are deleted.</returns> |
| | | 91 | | public static async Task ClearEntityDataAsync< TDbContext, TEntity >( TDbContext context, CancellationToken cancella |
| | | 92 | | where TDbContext : DbContext |
| | | 93 | | where TEntity : class |
| | | 94 | | { |
| | 3 | 95 | | var entities = await context.Set<TEntity>().ToListAsync( cancellationToken ).ConfigureAwait( false ); |
| | | 96 | | |
| | | 97 | | |
| | 3 | 98 | | context.Set<TEntity>().RemoveRange( entities ); |
| | | 99 | | |
| | 3 | 100 | | await context.SaveChangesAsync( cancellationToken ).ConfigureAwait( false ); |
| | 3 | 101 | | } |
| | | 102 | | |
| | 15 | 103 | | private static string QuoteName( string name ) => $"[{name.Replace( "]", "]]", StringComparison.Ordinal )}]"; |
| | | 104 | | } |