trjhtr

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(@"testdataSqlTableSeederTest-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.

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.