DbTool

1 Overview

A small almost cross-database tool (supports SqlServer, MySql, Postgres and SqLite) for working with databases:

- `Create`

- `Drop`

- `Execute scrips` as non query

- `Execute data reader` for reading data from db

- `Execute multiple sql scripts` via one method 

Primary aim of this tool is to prepare unit test infrastructure for help test enterprise application: create database and structure with data insert and any custom settings

For running test it is required to have developer/123 user in Postgres and Mysql database servers.

2 Example of usage

All classes are public therefore all of them could be used separately, but the main class is IDbManager, example of usage could be found in unit tests (TestCommonDbManager) see (https://github.com/EvilLord666/DbTools/blob/master/DbTools/DbTools.Simple.Tests/Managers/TestCommonDbManager.cs):

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.IO;
    using System.Threading;
    using System.Threading.Tasks;
    using DbTools.Core;
    using DbTools.Core.Managers;
    using DbTools.Simple.Factories;
    using DbTools.Simple.Utils;
    using Microsoft.Extensions.Logging;
    using Xunit;

    namespace DbTools.Simple.Tests.Managers
    {
        public class TestCommonDbManager
        {
            [Theory]
            [InlineData(DbEngine.SqlServer, true, "", "")]
            [InlineData(DbEngine.SqLite, true, null, null)]
            [InlineData(DbEngine.MySql, false, "root", "123")]
            [InlineData(DbEngine.PostgresSql, false, "postgres", "123")]
            public void TestCreateAndDropDb(DbEngine dbEngine, bool useIntegratedSecurity, string userName, string password)
            {
                IDbManager dbManager = CreateTestDbManager(dbEngine);
                string connectionString = BuildConnectionString(dbEngine, useIntegratedSecurity, userName, password);
                dbManager.CreateDatabase(connectionString, true);
                CheckDatabaseExists(dbManager, dbEngine, connectionString, true);
                dbManager.DropDatabase(connectionString);
                CheckDatabaseExists(dbManager, dbEngine, connectionString, false);
            }

            /// <summary>
            ///     Test execute non-query (structure creation script : one table for compatibility across databases engines
            ///     do not execute this tests parallel, there are could be a problems with it.
            /// </summary>
            /// <param name="dbEngine"> one of enum value corresponding to proper db engine </param>
            /// <param name="useIntegratedSecurity"> affects only on SQL Server to use Win Authentication </param>
            /// <param name="userName"> user name if non sql server and useIntegratedSecurity is false </param>
            /// <param name="password"> user password (my test databases have password 123) </param>
            /// <param name="isAsync"> indicator to use async IDbManager interface or sync </param>
            [Theory]
            [InlineData(DbEngine.SqlServer, true, "", "", false)]
            [InlineData(DbEngine.SqlServer, true, "", "", true)]
            [InlineData(DbEngine.SqLite, true, null, null, false)]
            [InlineData(DbEngine.SqLite, true, null, null, true)]
            [InlineData(DbEngine.MySql, false, "root", "123", false)]
            [InlineData(DbEngine.MySql, false, "root", "123", true)]
            [InlineData(DbEngine.PostgresSql, false, "postgres", "123", false)] 
            [InlineData(DbEngine.PostgresSql, false, "postgres", "123", true)]
            public void TestExecuteNonQuery(DbEngine dbEngine, bool useIntegratedSecurity, string userName, string password, bool isAsync)
            {
                IDbManager dbManager = CreateTestDbManager(dbEngine);
                string connectionString = BuildConnectionString(dbEngine, useIntegratedSecurity, userName, password);
                dbManager.CreateDatabase(connectionString, true);
                string cmd = File.ReadAllText(Path.GetFullPath(CreateStructureScriptFile));
                ExecuteScriptAndCheck(dbManager, connectionString, cmd, isAsync);
                dbManager.DropDatabase(connectionString);
                CheckDatabaseExists(dbManager, dbEngine, connectionString, false);
            }

            [Theory]
            [InlineData(DbEngine.SqlServer, true, "", "", false)]
            [InlineData(DbEngine.SqlServer, true, "", "", true)]
            [InlineData(DbEngine.SqLite, true, null, null, false)] // temporarily disabled due to some bad resource release
            [InlineData(DbEngine.SqLite, true, null, null, true)]
            [InlineData(DbEngine.MySql, false, "root", "123", false)]
            [InlineData(DbEngine.MySql, false, "root", "123", true)]
            [InlineData(DbEngine.PostgresSql, false, "postgres", "123", false)] 
            [InlineData(DbEngine.PostgresSql, false, "postgres", "123", true)]
            public void TestExecuteReader(DbEngine dbEngine, bool useIntegratedSecurity, string userName, string password, bool isAsync)
            {
                IDbManager dbManager = CreateTestDbManager(dbEngine);
                string connectionString = BuildConnectionString(dbEngine, useIntegratedSecurity, userName, password);
                dbManager.CreateDatabase(connectionString, true);
                string createTablesCmd = File.ReadAllText(Path.GetFullPath(CreateStructureScriptFile));
                string insertDataCmd = File.ReadAllText(Path.GetFullPath(InsertDataScriptFile));
                ExecuteScriptAndCheck(dbManager, connectionString, createTablesCmd, isAsync);
                ExecuteScriptAndCheck(dbManager, connectionString, insertDataCmd, isAsync);
                IList<object[]> actualData = new List<object[]>();
                const int numberOfColumns = 3;
                object[] row = new object[numberOfColumns];
                if (isAsync)
                {
                    Task<Tuple<DbDataReader, DbConnection>> getReaderTask = dbManager.ExecuteDbReaderAsync(connectionString, SelectCitiesQuery);
                    getReaderTask.Wait();
                    DbDataReader asyncReader = getReaderTask.Result.Item1;
                    
                    Task<bool> readTask = asyncReader.ReadAsync();
                    readTask.Wait();
                    
                    while (readTask.Result)
                    {
                        for (int i = 0; i < numberOfColumns; i++)
                            row[i] = asyncReader.GetValue(i);
                        actualData.Add(row);
                        readTask = asyncReader.ReadAsync();
                        readTask.Wait();
                    }
                    asyncReader.Close();
                    asyncReader.Dispose();
                    getReaderTask.Result.Item2.Close();
                }
                else
                {
                    Tuple<IDataReader, IDbConnection> reader = dbManager.ExecuteDbReader(connectionString, SelectCitiesQuery);
                    while (reader.Item1.Read())
                    {
                        for (int i = 0; i < numberOfColumns; i++)
                            row[i] = reader.Item1.GetValue(i);
                        actualData.Add(row);
                    }
                    reader.Item1.Close();
                    reader.Item1.Dispose();
                    reader.Item2.Close();
                }
                Assert.Equal(8, actualData.Count);  // indicator tests
                dbManager.DropDatabase(connectionString);
                CheckDatabaseExists(dbManager, dbEngine, connectionString, false);
            }

            private void ExecuteScriptAndCheck(IDbManager dbManager, string connectionString, string cmd, bool isAsync)
            {
                bool result = false;
                if (isAsync)
                {
                    Task<bool> executionTask = dbManager.ExecuteNonQueryAsync(connectionString, cmd);
                    executionTask.Wait();
                    result = executionTask.Result;
                }
                else result = dbManager.ExecuteNonQuery(connectionString, cmd);
                Assert.True(result);
            }

            private IDbManager CreateTestDbManager(DbEngine dbEngine)
            {
                return DbManagerFactory.Create(dbEngine, _loggerFactory);
            }

            private string BuildConnectionString(DbEngine dbEngine, bool useIntegratedSecurity, string userName, string password)
            {
                Tuple<string, string> hostAndDatabase = _hostAndDatabaseOptions[dbEngine];
                IDictionary<string, string> options = new Dictionary<string, string>();
                options.Add(DbParametersKeys.HostKey, hostAndDatabase.Item1);
                options.Add(DbParametersKeys.DatabaseKey, hostAndDatabase.Item2);
                options.Add(DbParametersKeys.UseIntegratedSecurityKey, useIntegratedSecurity.ToString());
                options.Add(DbParametersKeys.LoginKey, userName);
                options.Add(DbParametersKeys.PasswordKey, password);
                return ConnectionStringBuilder.Build(dbEngine, options);
            }

            private void CheckDatabaseExists(IDbManager dbManager, DbEngine dbEngine, string connectionString, bool expected)
            {
                string cmd = null;
                string sysConnectionString = null;
                string dbName = _hostAndDatabaseOptions[dbEngine].Item2;
                if (dbEngine == DbEngine.SqlServer)
                {
                    sysConnectionString = ConnectionStringHelper.GetSqlServerMasterConnectionString(connectionString);
                    cmd = string.Format(SelectDatabaseTemplate, "name", "master.dbo.sysdatabases", $"N'{dbName}'");
                }