Dapper needs a C# model of the corresponding SQL table for it to work most effectively.
For example, for this SQL table:
1 2 3 4 5 6 7 |
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:
1 2 3 4 5 6 7 8 |
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)
Generating SQL Server table schema
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
{ "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:
1 2 3 4 5 6 |
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:
1 2 3 4 5 6 7 8 |
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.