Populate SQL tables with test data

Question

I have some unit-tests that must be executed against a real database. This requires populating the database with test-data.

In order to simplify this process I created the SqlTableSeeder. It uses the SqlBulkCopy internally to insert the data from the specified DataTable. Optionally it truncates the target table first.

The reason why I picked the SqlBulkcopy is because certain queries need to be tested for performance and I'll be creating data-tables with a couple of millions of rows.

public static class SqlTableSeeder
{
    public static async Task SeedAsync(this SqlConnection connection, string schema, string table, DataTable data, bool truncate = true)
    {
        if (Transaction.Current == null)
        {
            throw new InvalidOperationException($"{nameof(SeedAsync)} can be executed only within a transaction scope.");
        }

        var identifier = connection.CreateIdentifier(schema, table);

        if (truncate)
        {
            // Using "truncate" because some databases/tables do not allow "delete".
            await connection.ExecuteQueryAsync($"truncate table {identifier}", command => command.ExecuteNonQueryAsync());
        }

        using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null))
        {
            bulkCopy.DestinationTableName = identifier;

            foreach (var column in data.Columns.Cast<DataColumn>().Select(c => c.ColumnName))
            {
                bulkCopy.ColumnMappings.Add(column, column);
            }

            await bulkCopy.WriteToServerAsync(data);
        }
    }

    public static void Seed(this SqlConnection connection, string schema, string table, DataTable data, bool truncate = true)
    {
        SeedAsync(connection, schema, table, data, truncate).GetAwaiter().GetResult();
    }
}

where CreateIdentifier is an extension implemented as follows:

    public static string CreateIdentifier(this SqlConnection connection, params string[] names)
    {
        if (connection == null) throw new ArgumentNullException(nameof(connection));

        using (var commandBuilder = DbProviderFactories.GetFactory(connection).CreateCommandBuilder())
        {
            // ReSharper disable once PossibleNullReferenceException - commandBuilder is never null for SqlConnection.
            return names.Select(commandBuilder.QuoteIdentifier).Join(".");
        }
    }

---

Example

As an example of how I'm using it, I post one of the unit-tests I created for it and the schema of the test table.

I import the test-data from a CSV, I turn into a DataTable and pass this to the seeder which takes just 4 lines of code (not counting the constants).

[TestClass]
public class SqlTableSeederTest
{
    private const string ConnectonString = "Data Source=(local);Initial Catalog=TestDb;Integrated Security=SSPI;";
    private const string Schema = "dbo";
    private const string Table = "SqlTableSeederTest";
    private static readonly ITypeConverter Converter =
        TypeConverter
            .Empty
            .Add<StringToInt32Converter>()
            .Add<StringToDateTimeConverter>();

    [TestMethod]
    public void Seed_WithoutId_Seeded()
    {
        var csvReader = CsvReader.FromFile(@"testdata\SqlTableSeederTest-without-id.csv");
        var sqlColumns = SqlHelper.Execute(ConnectonString, connection => connection.GetColumnFrameworkTypes(Schema, Table));
        var csv = csvReader.AsEnumerable().ToDataTable(sqlColumns, Converter);

        SqlHelper.Execute(ConnectonString, connection => connection.Seed(Schema, Table, csv));

        Assert.AreEqual(3, SqlHelper.Execute(ConnectonString, connection =>
        {
            return connection.ExecuteQuery($"select count(*) from [{Schema}].[{Table}]", command => command.ExecuteScalar());                
        }));
    }
}

CREATE TABLE [dbo].[SqlTableSeederTest](
    [_id] [int] IDENTITY(1,1) NOT NULL,
    [_nvarchar] [nvarchar](50) NULL,
    [_datetime] [datetime2](7) NULL,
 CONSTRAINT [PK_SqlTableSeederTest] PRIMARY KEY CLUSTERED 
(
    [_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

---

So what do you think of this helper? Could it be even shorter and still SOLID and easy to use? Did I miss anything important? I'll be needing it in several unit-test projects so convenience is an important factor too.

Answer 1

So what do you think of this helper?

It looks good, but I am missing the parameter validation for a public method

• DataTable data -> blows very late if it is null.
• string table -> blows at least if truncate == true

Seeing

// Using "truncate" because some databases/tables do not allow "delete".

just makes me happy because too often you see only senseless comments. This is a valid and strong comment explaining why you did this.