How to Seed Database with Dapper

Dapper can be used to seed database with some initial set of data. This includes creating tables in the database, inserting records to the tables and so on. In this tutorial we will be seeding a database with Dapper. So let’s start with the seeding process.

Learn Dapper from the complete series”

1. Dapper Execute Method – Execute SQL and Stored Procedure
2. Dapper Query Method – Execute SQL and Stored Procedure
3. Dapper One-to-One Relationship
4. Dapper One-to-Many Relationship
5. How to Seed Database with Dapper
6. How to perform CRUD Operations in Dapper

Dapper Table-Valued Parameters

Dapper use SQL Server’s feature called Table-Value Parameters which are user-defined table types. These are used to send multiple rows of data to a SQL statement (like a stored procedure or a function), without any need to create a temporary table. We will be using Table-Value Parameters to send a DataTable from our code. This DataTable will contain multiple records which will be inserted to the database.

In Dapper we can create a Table-Value Parameter with be Execute method as shown below.

connection.Execute(@"
    CREATE TYPE TVP_Customer AS TABLE
    (
        [Name] [VARCHAR](20) NULL,
        [Email] [VARCHAR](20) NULL
    )");

Dapper Seed Database

In the below code example we are creating a Customer table in the database and then inserting 5 dummy records to it by using Table-Valued Parameter. See below code:

//connection string
string myCS = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DapperStart;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

using (var connection = new SqlConnection(myCS))
{
    // CREATE the Table
    connection.Execute(@"
        CREATE TABLE [Customer]
        (
            [Id] [INT] IDENTITY(1,1) NOT NULL,
            [Name] [VARCHAR](20) NULL,
            [Email] [VARCHAR](20) NULL,

            CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
            (
                [Id] ASC
            )
        )
                ");

    // CREATE the TVP type
    connection.Execute(@"
        CREATE TYPE TVP_Customer AS TABLE
        (
            [Name] [VARCHAR](20) NULL,
            [Email] [VARCHAR](20) NULL
        )
                ");

    // CREATE the Stored Procedure			   
    connection.Execute(@"
        CREATE PROCEDURE Customer_Seed
            @Customers TVP_Customer READONLY
        AS
        BEGIN
            INSERT INTO Customer (Name,Email)
            SELECT Name, Email
            FROM @Customers
        END
                ");

    var dt = new DataTable();
    dt.Columns.Add("Name");
    dt.Columns.Add("Email");

    for (int i = 0; i < 5; i++)
    {
        dt.Rows.Add("Name_" + i, "Email_" + i);
    }
    connection.Execute("Customer_Seed", new { Customers = dt.AsTableValuedParameter("TVP_Customer") }, commandType: CommandType.StoredProcedure);
}

Code Explanation: First we are creating Customer table using Execute method as:

connection.Execute(@"
    CREATE TABLE [Customer]
    (
        [Id] [INT] IDENTITY(1,1) NOT NULL,
        [Name] [VARCHAR](20) NULL,
        [Email] [VARCHAR](20) NULL,

        CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
        (
            [Id] ASC
        )
    )
");

Next we are creating a SQL Table-Value Parameter. It contains 2 parameters Name and Email which are the columns of the Customer table.

connection.Execute(@"
    CREATE TYPE TVP_Customer AS TABLE
    (
        [Name] [VARCHAR](20) NULL,
        [Email] [VARCHAR](20) NULL
    )
");

SQL Table-Value Parameters are defined inside the Programmability > Types > User-Defined Table Types section:

Next, we are creating a stored procedure which takes the Table-Value Parameter, which we created earlier, as it’s parameter and then uses it to insert customer records to the Customer table.

connection.Execute(@"
    CREATE PROCEDURE Customer_Seed
        @Customers TVP_Customer READONLY
    AS
    BEGIN
        INSERT INTO Customer (Name,Email)
        SELECT Name, Email
        FROM @Customers
    END
");

Finally, at the end we are using a DataTable object to first add 5 dummy customer records. Then executing the stored procedure and passing the DataTable object as Dapper Table-Value parameter to it.

var dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Email");

for (int i = 0; i < 5; i++)
{
    dt.Rows.Add("Name_" + i, "Email_" + i);
}
connection.Execute("Customer_Seed", new { Customers = dt.AsTableValuedParameter("TVP_Customer") }, commandType: CommandType.StoredProcedure);

Run the code and you will find the Customer table is created with 5 records in it. It is shown in the below image:

Download the source codes:

Download