Last active
August 1, 2024 21:15
-
-
Save JerryNixon/3026509cb2035a86fd5273592b0967ef to your computer and use it in GitHub Desktop.
Database Unit Test
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using Microsoft.Data.SqlClient; | |
| using Microsoft.Extensions.Configuration; | |
| using System.Data; | |
| namespace Database.TestRunner; | |
| public class SqlDatabase : IDisposable, IAsyncDisposable | |
| { | |
| private static string ReadConnectionString() | |
| { | |
| // dotnet user-secrets init | |
| // dotnet user-secrets set "SQL_CONNECTION_STRING" "Data Source=(localdb)\\MSSQLLocalDB;Database=HR;Integrated Security=True;" | |
| // Microsoft.Extensions.Configuration | |
| // Microsoft.Extensions.Configuration.UserSecrets | |
| var configuration = new ConfigurationBuilder().AddUserSecrets<SqlDatabase>().Build(); | |
| var connectionString = configuration["SQL_CONNECTION_STRING"]; | |
| ArgumentNullException.ThrowIfNull(connectionString, "SQL_CONNECTION_STRING not set"); | |
| return connectionString; | |
| } | |
| private readonly SqlConnection _connection; | |
| private bool _disposed = false; | |
| public SqlDatabase() | |
| { | |
| _connection = new SqlConnection(ReadConnectionString()); | |
| _connection.Open(); | |
| } | |
| public async Task ExecuteNonQueryAsync(string sql, CancellationToken token) | |
| { | |
| ArgumentException.ThrowIfNullOrEmpty(sql, nameof(sql)); | |
| using var command = new SqlCommand(sql, _connection); | |
| await command.ExecuteNonQueryAsync(token); | |
| } | |
| public TheoryData<string> AllTests(string schemaName) | |
| { | |
| ArgumentException.ThrowIfNullOrEmpty(schemaName, nameof(schemaName)); | |
| string sql = """ | |
| SELECT CONCAT('EXEC [', s.name, '].[', p.name, '];') as proc_name | |
| FROM sys.procedures AS p | |
| JOIN sys.schemas AS s ON p.schema_id = s.schema_id | |
| WHERE s.name = @SchemaName | |
| """; | |
| using var command = new SqlCommand(sql, _connection); | |
| command.Parameters.AddWithValue("@SchemaName", schemaName); | |
| using var reader = command.ExecuteReader(); | |
| var result = new TheoryData<string>(); | |
| while (reader.Read()) | |
| { | |
| result.Add(reader.GetString(0)); | |
| } | |
| return result; | |
| } | |
| public void Dispose() | |
| { | |
| Dispose(true); | |
| GC.SuppressFinalize(this); | |
| } | |
| public async ValueTask DisposeAsync() | |
| { | |
| await DisposeAsyncCore(); | |
| Dispose(false); | |
| GC.SuppressFinalize(this); | |
| } | |
| protected virtual async ValueTask DisposeAsyncCore() | |
| { | |
| if (_connection is not null | |
| && _connection.State is not ConnectionState.Closed) | |
| { | |
| await _connection.CloseAsync(); | |
| } | |
| } | |
| protected virtual void Dispose(bool disposing) | |
| { | |
| if (_disposed) | |
| { | |
| return; | |
| } | |
| if (disposing) | |
| { | |
| if (_connection != null) | |
| { | |
| _connection.Close(); | |
| _connection.Dispose(); | |
| } | |
| } | |
| _disposed = true; | |
| } | |
| } | |
| public class TestRunner | |
| { | |
| private static readonly SqlDatabase _sql = new(); | |
| public static TheoryData<string> AllTests() => _sql.AllTests("Tests"); | |
| [Theory] | |
| [MemberData(nameof(AllTests))] | |
| public async Task TestObjects(string sql, CancellationToken token) | |
| { | |
| await _sql.ExecuteNonQueryAsync(sql, token); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment