Creating Dapper models

Posted on: Sunday, Jan 16, 2022

Generating SQL Server table schema

To work effectively, Dapper needs a C# model of the corresponding SQL table.

For example, for this SQL table:

CREATE TABLE [dbo].[Song] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ArtistId] [int] NULL,
    [Title] [nvarchar](200) NOT NULL,
    [Added] [datetime] NULL,
    [Updated] [datetime] NULL
)    

Figure 1. SQL CREATE TABLE for Song table.

It needs a class like this:

public class SongModel
{
    public int Id {get;set;}
    public int ArtistId {get;set;}
    public string Title {get;set;}
    public System.DateTime Added {get;set;}
    public System.DateTime Updated {get;set;}
}

Figure 2. C# model for Song table (for Dapper, for example)

Creating models by hand gets old very quickly. To solve this problem GenerateJsonTableSchema (at this repo) generates a Json schema of all of the tables and views in a SQL Server database.

GenerateJsonTableSchema creates this Json schema for the Song table above:

{
  "DatabaseName": "Sugarfoot",
  "TableName": "Song",
  "columns": [
    {
      "ColumnName": "Id",
      "Type": "int",
      "DDLType": "int",
      "CSType": "int",
      "NETType": "Int32",
      "MaxLength": 4,
      "Precision": 10,
      "Scale": 0,
      "Nullable": "False",
      "PrimaryKey": "True",
      "Identity": "True"
    },
    {
      "ColumnName": "ArtistId",
      "Type": "int",
      "DDLType": "int",
      "CSType": "int",
      "NETType": "Int32",
      "MaxLength": 4,
      "Precision": 10,
      "Scale": 0,
      "Nullable": "True",
      "PrimaryKey": "False",
      "Identity": "False"
    },
    {
      "ColumnName": "Title",
      "Type": "nvarchar",
      "DDLType": "nvarchar(200)",
      "CSType": "string",
      "NETType": "String",
      "MaxLength": 400,
      "Precision": 0,
      "Scale": 0,
      "Nullable": "False",
      "PrimaryKey": "False",
      "Identity": "False"
    },
    {
      "ColumnName": "Added",
      "Type": "datetime",
      "DDLType": "datetime",
      "CSType": "System.DateTime",
      "NETType": "DateTime",
      "MaxLength": 8,
      "Precision": 23,
      "Scale": 3,
      "Nullable": "True",
      "PrimaryKey": "False",
      "Identity": "False"
    },
    {
      "ColumnName": "Updated",
      "Type": "datetime",
      "DDLType": "datetime",
      "CSType": "System.DateTime",
      "NETType": "DateTime",
      "MaxLength": 8,
      "Precision": 23,
      "Scale": 3,
      "Nullable": "True",
      "PrimaryKey": "False",
      "Identity": "False"
    }
  ],
  "primaryKeyCSDeclaration": "int Id",
  "primaryKeyCSAssignment": "Id = Id",
  "primaryKeySqlDeclaration": "@Id int",
  "primaryKeySqlAssignment": "[Id] = @Id",
  "columnSqlDeclarations": "@Id int,\n@ArtistId int,\n@Title nvarchar(200),\n@Added datetime,\n@Updated datetime",
  "columnSqlDeclarationsNoIdentity": "@ArtistId int,\n@Title nvarchar(200),\n@Added datetime,\n@Updated datetime",
  "columnNamesSqlList": "[ArtistId],\n[Title],\n[Added],\n[Updated]",
  "columnValuesSqlList": "@ArtistId,\n@Title,\n@Added,\n@Updated",
  "columnValuesAssignmentSqlList": "[ArtistId] = @ArtistId,\n[Title] = @Title,\n[Added] = @Added,\n[Updated] = @Updated",
  "modelColumnNames": "model.ArtistId, \nmodel.Title, \nmodel.Added, \nmodel.Updated",
  "modelKeyName": "song.Id",
  "csKeyName": "Id",
  "csKeyType": "int"
}

Figure 3. Json schema for Songs table.

What can you do with the Json schema?

This Json schema file is intended to be used with a templating engine to create C# models and other code for the corresponding table. The schema file schema includes column-level details for each column and then several special-case table-level values.

For example, Librettox (which is a Python program that uses the Jinja2 templating engine), the Json schema from figure 3, and this template:

public class {{TableName}}Model
{
    {% for column in columns %}
    public {{column.CSType}} {{column.ColumnName}} {get;set;}
    {% endfor %}
}

Figure 4a. A Lilbrettox template to create a CS model.

produces this output:

public class SongModel
{
    public int Id {get;set;}
    public int ArtistId {get;set;}
    public string Title {get;set;}
    public System.DateTime Added {get;set;}
    public System.DateTime Updated {get;set;}
}

Figure 4b. The output from Figure 4a.




Add your comment
You email is never shared with anyone else.

© Copyright 2017 by Roger Pence. All rights reserved.