Developer Docs

Link copied to clipboard!

Custom SQL Scripts with C#Bot

As part of C#Bot, custom SQL scripts can be added by the developers to gain more fine-grain control over the underlying database. These scripts can be used to define the desired database state with both schema and data migration scripts.

By default, C#Bot uses Entity Framework (EF) as a bridge between the application and the database. Sometimes you may require custom database manipulation that is not possible with Entity Framework migrations. SQL scripts can be added to allow better control and performance. You can learn more about Entity Framework from the Microsoft docs.

This article will walk through how to add your custom SQL script and how to elevate them in your project.

Migration Scripts

Migrations scripts can be found inside of the folder /serverside/src/Migrations. If you have not completed the first time setup steps inside the Running C#Bot article, this folder will be empty.

Modifying a Migration Script

Since the migration scripts are not written by C#Bot, but instead by Entity Framework using code first approach on C#Bot written classes, there are no protected regions in these files but you are free to edit them as the bot will not touch them.

Migration scripts can either be edited in their C# form (default output from EF) or SQL form.

C# Migrations

Modifying C# Migration

Create the initial migration by navigating to /serverside/src and running:

dotnet ef migrations add [migration name]

You should now have a migrations file in the /serverside/src/Migrations folder. The name will be in the format [DateTime of creation]_[Migration name].cs.

Open this file up in your editor of choice.

There are two functions inside the migration file Up and Down. Up is executed when updating the database, and Down can be used to remove the updates after they have been applied to the database.

You can add a new table or modify a table inside this migration. Below is an example of a new table being constructed in the C# migration. Adding this to the migration Up method would create a new table called Visitor with attributes -> Id, Name, DateVisited and DidDonate.

migrationBuilder.CreateTable(
    name: "Visitor",
    columns: table => new
    {
        Id = table.Column<Guid>(nullable: false),
        Name = table.Column<string>(maxLength: 256, nullable: true),
        DateVisited = table.Column<DateTime>(nullable: false),
        DidDonate = table.Column<bool>(nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Visitor", x => x.Id);
    });

Executing using Entity Framework

The migration can then be executed after saving the file, navigating to /serverside/src and running:

dotnet ef database update

SQL Migrations

Modifying SQL Migration

SQL migrations output from Entity Framework can also be modified. To generate these SQL migrations, navigate to /serverside/src and run the following command.

dotnet ef migrations script -o Migrations/[DateTime of creation]_[Migration name].sql

You should now have a file called [DateTime of creation]_[Migration name].sql in the /serverside/src/Migrations directory. Open this file in your editor of choice.

You can add tables to this file using SQL. Below is an example of a new table being constructed in the SQL migration. Adding this to the migration would create a new table called Visitor with attributes -> Id, Name, DateVisited and DidDonate.

CREATE TABLE "Visitor" (
    "Id" uuid NOT NULL,
    "Name" character varying(256) NULL,
    "DateVisited" timestamp without time zone NOT NULL,
    "DidDonate" boolean NULL,
    CONSTRAINT "PK_Visitor" PRIMARY KEY ("Id")
);

Executing using Entity Framework

To execute the sql migration using Entity Framework, a .cs migration file is needed. Go to the /serverside/src/Migrations folder and create a new file with the same filename as the sql migration, but with a .cs extension.

migrationfilenames.jpg

Scaffolding code is given below and the comments highlight the modifications needed to this file.

using System.IO;
using Zoo.Models;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Zoo.Migrations
{
    [DbContext(typeof(ZooDBContext))]
    [Migration("RunSqlScripts")] // Change 'RunSqlScripts' to your migration name
    public class RunSqlScripts : Migration // Change 'RunSqlScripts' to your migration name
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Change filename to the name of your custom sql migration file
            migrationBuilder.Sql(File.ReadAllText("Migrations/20191029025018_RunSqlScripts.sql"));
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // custom down code here
        }
    }
}

It is highly recommended to also implement a Down method for rolling back migrations (e.g. dropping tables added in the Up method). This could be an sql script in a .sql file that is executed in the same manner as the Up in this example, or could be written in C# directly in the Down method. See the C# Migrations sections this article for writing Up and Down migrations in C#

The migration can then be executed after saving the file, navigating to /serverside/src and running:

dotnet ef database update

Creating a Custom Migration Script

C# Migrations

Creating C# Migration

C# migrations can also be created from scratch. To do this, navigate to the Migrations folder /serverside/src/Migrations, and create a new C# (.cs) file with the naming pattern [DateTime of creation]_[Migration name].cs. A template for a migration file is provided below.

using System;
using Zoo.Models;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Zoo.Migrations
{
    [DbContext(typeof(ZooDBContext))]
    [Migration("ExampleMigration")]
    public class ExampleMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // custom up code here
        }
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // custom down code here
        }
    }
}

A migration is a class that extends the Entity Framework Migration class, and makes use of the MigrationBuilder.

As in the example above for modifying a migration, we can add in a new custom table for a Visitor using the MigrationBuilder.

using System;
using Zoo.Models;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Zoo.Migrations
{
    [DbContext(typeof(ZooDBContext))]
    [Migration("ExampleMigration")]
    public class ExampleMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Visitor",
                columns: table => new
                {
                    Id = table.Column<Guid>(nullable: false),
                    Name = table.Column<string>(maxLength: 256, nullable: true),
                    DateVisited = table.Column<DateTime>(nullable: false),
                    DidDonate = table.Column<bool>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Visitor", x => x.Id);
                });
        }
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // custom down code here
        }
    }
}

And we can also add to our down method so the migration can be undone by dropping the Visitor table.

using System;
using Zoo.Models;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Zoo.Migrations
{
    [DbContext(typeof(ZooDBContext))]
    [Migration("ExampleMigration")]
    public class ExampleMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Visitor",
                columns: table => new
                {
                    Id = table.Column<Guid>(nullable: false),
                    Name = table.Column<string>(maxLength: 256, nullable: true),
                    DateVisited = table.Column<DateTime>(nullable: false),
                    DidDonate = table.Column<bool>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Visitor", x => x.Id);
                });
        }
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Visitor");
        }
    }
}

Executing using Entity Framework

The migration can then be executed after saving the file, navigating to /serverside/src and running:

dotnet ef database update

SQL Migrations

Creating SQL Migration

SQL Migrations/Custom Scripts can also be written and executed from scratch. Create a new SQL (.sql) file in the /serverside/src/Migrations directory using the filename pattern [DateTime of creation]_[Migration name].sql, and open it in your editor of choice.

You can write any custom SQL in this file. For this example we will be continuing with the Visitors Table.

CREATE TABLE "Visitor" (
    "Id" uuid NOT NULL,
    "Name" character varying(256) NULL,
    "DateVisited" timestamp without time zone NOT NULL,
    "DidDonate" boolean NULL,
    CONSTRAINT "PK_Visitor" PRIMARY KEY ("Id")
);

We might also want to update an existing table with a new entry. Fist we check if the PoolTable table doesn’t exists, and if not, create the table, run our custom SQL script, and then update the table.

CREATE TABLE IF NOT EXISTS "PoolTable" (
    "Id" uuid NOT NULL DEFAULT (uuid_generate_v4()),
    "PoolTableName" character varying(256) NOT NULL,
    CONSTRAINT "PK___PoolTable2" PRIMARY KEY ("Id")
);

CREATE TABLE "Visitor" (
    "Id" uuid NOT NULL,
    "Name" character varying(256) NULL,
    "DateVisited" timestamp without time zone NOT NULL,
    "DidDonate" boolean NULL,
    CONSTRAINT "PK_Visitor" PRIMARY KEY ("Id")
);

INSERT INTO "PoolTable" ("PoolTableName")
VALUES ('MyNewPoolTable');

Executing using Entity Framework

To execute the sql migration using Entity Framework, a .cs migration file is needed. Go to the /serverside/src/Migrations folder and create a new file with the same filename as the sql migration, but with a .cs extension.

migrationfilenames.jpg

Scaffolding code is given below and the comments highlight the modifications needed to this file.

using System.IO;
using Zoo.Models;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Zoo.Migrations
{
    [DbContext(typeof(ZooDBContext))]
    [Migration("RunSqlScripts")] // Change 'RunSqlScripts' to your migration name
    public class RunSqlScripts : Migration // Change 'RunSqlScripts' to your migration name
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Change filename to the name of your custom sql migration file
            migrationBuilder.Sql(File.ReadAllText("Migrations/20191029025018_RunSqlScripts.sql"));
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // custom down code here
        }
    }
}

It is highly recommended to also implement a Down method for rolling back migrations (e.g. dropping tables added in the Up method). This could be an sql script in a .sql file that is executed in the same manner as the Up in this example, or could be written in C# directly in the Down method. See the C# Migrations sections this article for writing Up and Down migrations in C#

The migration can then be executed after saving the file, navigating to /serverside/src and running:

dotnet ef database update

Seeding Data

Using Migration File

If you would like to insert data / seed data in your database, add a new migration file in the folder /serverside/src/Migrations with the file name pattern [DateTime of creation]_[Migration name].cs.

using System;
using Microsoft.EntityFrameworkCore.Migrations;

namespace [Project Name].Migrations
{
    public partial class [Migration name] : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.InsertData(
                [Table Name],
                new[] 
                {
                    // array of column names here
                },
                new object[] 
                {
                    // values for columns go here
                });
        }
    }
}

Example:

using System;
using Microsoft.EntityFrameworkCore.Migrations;

namespace Zoo.Migrations
{
    public partial class [Migration name] : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.InsertData(
                "Enclosure",
                new[] 
                {
                    "Id", "Owner", "Created", "Modified", "Name", "Capacity"
                },
                new object[] 
                {
                    Guid.NewGuid(), null, DateTime.Now, DateTime.Now, "Penguin Pen", 10
                });
        }
    }
}

You can then run dotnet ef database update from the /serverside/src directory to seed your data.

Using DataSeedHelper

The class DataSeedHelper inside the directory /serverside/src/Helpers/DataSeedHelper.cs is run during startup of the server, and will seed the data provided if it does not already exists. An example is already provided in the class for seeding users in development. There are protected region provided to add further seeding.

private async Task CreateObjects()
        {
            // Create the roles first since we need them to assign users to afterwards
            foreach (var role in Roles)
            {
                await CreateRole(role);
            }

            // Create users for testing in development environments
            if (_environment.IsDevelopment())
            {
                await CreateUser(
                    new User {Email = "super@example.com"},
                    "password",
                    new [] {"Visitors", "Admin", "Fishnatic", "Super Administrators"});
                await CreateUser(
                    new AdminEntity {Email = "adminentity@example.com"},
                    "password",
                    new [] {"Admin"});
                await CreateUser(
                    new FishnaticEntity {Email = "fishnaticentity@example.com"},
                    "password",
                    new [] {"Fishnatic"});
                // % protected region % [Add any extra development seeding here] off begin
                // % protected region % [Add any extra development seeding here] end
            }

            // % protected region % [Add any extra seeding here] off begin
            // % protected region % [Add any extra seeding here] end
        }

Dumping SQL from PgAdmin

It is possible to dump the contents of your database into an SQL file that can later be run to seed other databases.

pg_dump -U [username] -d [database name] | Set-Content [output file name].sql

Example:

pg_dump -U postgres -d zoo | Set-Content out.sql

Solution

by Tessa Mylonas

13 March 2020

Originally written by Mikaela Robertson on 30 October 2019

Was this article helpful?

Thanks for your feedback!

If you would like to tell us more, please click on the link below to send us a message with more details.

Tool:

Generate

Iterate

Bot:

C#Bot

SpringBot

On this page

Back to top

New to Codebots?

We know our software can be complicated, so we are always happy to have a chat if you have any questions.

Have a demo/chat via Zoom