2
\$\begingroup\$

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.

\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.