How to generate model from database using Dapper?

Question

I am coming from PetaPoco camp. PetaPoco has a T4 template which generates model from the database. Is anything similar available for Dapper?

I installed Dapper using NuGet and added SqlHelper.cs, but I didn't find anything which generates model from the database.

Gwenny

Answer 1

Dapper itself provides few extension methods (Query, Execute) for the connection object and does not have "model generator." Perhaps some other framework can be used to generate POCO's based on the db schema.

Update:

Database tables to C# POCO classes T4 template

<#@ template language="C#" debug="True" #>

<#@ assembly name="System" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Xml" #>

<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>

<#@ import namespace="System" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>

namespace Namespace
{

<#         
   var databaseName = "testDb";
            var serverConnection = new SqlConnection(
                    @"Data Source=.\SQLEXPRESS; Integrated Security=true; Initial Catalog=" + databaseName);
            var svrConnection = new ServerConnection(serverConnection);

   Server srv = new Server(svrConnection);
            foreach (Table table in srv.Databases[databaseName].Tables)
            {

#>
        class <#= table.Name #>
        {
            <#
                foreach (Column col in table.Columns)
                {
                #>
                    public <#= GetNetDataType(col.DataType.Name) #> <#= col.Name #> { get; set; }
                <#
                }
             #>
        }

<#            }
#>
}



<#+
        public static string GetNetDataType(string sqlDataTypeName)
        {

            switch (sqlDataTypeName.ToLower())
            {
                case "bigint":
                    return "Int64";
                case "binary":
                    return "Byte[]";
                case "bit":
                    return "bool";
                case "char":
                    return "char";
                case "cursor":
                    return string.Empty;
                case "datetime":
                    return "DateTime";
                case "decimal":
                    return "Decimal";
                case "float":
                    return "Double";
                case "int":
                    return "int";
                case "money":
                    return "Decimal";
                case "nchar":
                    return "string";
                case "numeric":
                    return "Decimal";
                case "nvarchar":
                    return "string";
                case "real":
                    return "single";
                case "smallint":
                    return "Int16";
                case "text":
                    return "string";
                case "tinyint":
                    return "Byte";
                case "varbinary":
                    return "Byte[]";
                case "xml":
                    return "string";
                case "varchar":
                    return "string";
                case "smalldatetime":
                    return "DateTime";
                case "image":
                    return "byte[]";

                default:
                    return string.Empty;
            }



        }
#>

Familiarize

I have not used any of the below tools, but you can take a look at: visualstudiogallery.msdn.microsoft.com/… and sites.google.com/site/mrmbookmarks/msg/… – Familiarize

There are also more sophisticated code generators like: codesmithtools.com – Familiarize

I played around with the "Generate POCO from all tables" template a bit and seems like a good starting point; it does basic table-to-class generation. I ran into few issue while testing it which forced me to modify it a bit; here is an updated version: teamyudin.blogspot.com/2012/06/…. – Familiarize

@RPK: The history of Dapper makes it quite clear why there's no T4 template to generate your application model. You can of course write your own T4 template to do the trick for you and it shouldn't be too complicated either. But Dapper was introduced to otherwise LINQ to SQL existing model where they wanted to improve performance. Hence no need to generate anything. Hmmm. LINQ 2 SQL could do the job for you or at least find a T4 on the net related to it and remove unnecessary stuff out of it. – Penholder

@RobertKoritnik I just want classes with Getters and Setters. – Gwenny

@RPK: If that's all that you need, I suggest you simply write the T4 yourself. This is a rather trivial task. – Penholder

for googlers, could take a look at this repo github.com/shps951023/PocoClassGenerator. It helps you generate poco classes from several types of db. Easy to use. – Morley

Answer 2

I've just recently written a sql query to do the job for myself. And updating it with extra types when i need. Just replace the table name where it says @@@@.

To make alot of tables i created a temp stored procedure to call. eg. exec createTablePOCO(@tableName)

SELECT 
    'public ' + a1.NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}'
    ,*
FROM (
    /*using top because i'm putting an order by ordinal_position on it. 
    putting a top on it is the only way for a subquery to be ordered*/
    SELECT TOP 100 PERCENT
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    CASE 
        WHEN DATA_TYPE = 'varchar' THEN 'string'
        WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
        WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
        WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
        WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
        WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
        WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
        WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
        WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
        WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
        WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
        WHEN DATA_TYPE = 'char' THEN 'string'
        WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
        WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
        WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
        WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
        WHEN DATA_TYPE = 'xml' THEN 'string'
    END AS NewType
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = '@@@@'
    ORDER BY ORDINAL_POSITION
) as a1

Yokel

I like this little script. If you want a quick and dirty POCO generated from an existing table it does not get much easier than this. If you need to map more fields use this link to look them up. msdn.microsoft.com/en-us/library/ms131092.aspx – Winkler

Don't forget WHEN DATA_TYPE = 'nvarchar' THEN 'string' – Krein

I added these two lines WHEN DATA_TYPE = 'nvarchar' THEN 'string' WHEN DATA_TYPE = 'uniqueidentifier' THEN 'Guid' – Indira

Might also want to add these: WHEN DATA_TYPE = 'date' AND IS_NULLABLE = 'NO' THEN 'DateTime' WHEN DATA_TYPE = 'date' AND IS_NULLABLE = 'YES' THEN 'DateTime?' WHEN DATA_TYPE = 'smallmoney' AND IS_NULLABLE = 'NO' THEN 'decimal' WHEN DATA_TYPE = 'smallmoney' AND IS_NULLABLE = 'YES' THEN 'decimal?' – Inapprehensive

WHEN DATA_TYPE LIKE '%char%' THEN 'string' – Ply

Sorry I'm not good at SQL. But can you give sample code on how to use it in Dapper for C# lang? – Beliabelial

Answer 3

Calling the stored procedure from a cursor

If you combine the sp mattritchies mentioned (see answer above) and call it from a cursor you can generate the poco class for every table in your database

USE YourDataBaseName
GO 
    DECLARE @field1 nvarchar(400)
    DECLARE cur CURSOR LOCAL for

    SELECT TABLE_NAME FROM information_schema.tables
    OPEN cur
    FETCH NEXT FROM cur INTO @field1 --, @field2
    WHILE @@FETCH_STATUS = 0 BEGIN          
        exec Helper_CreatePocoFromTableName @field1 -- , @field2            
        fetch next from cur into @field1 -- , @field2
    END

close cur
deallocate cur

Stored Procedure mattritchies mentioned

I took the sql from mattritchies answer (see above) and created the stored procedure he mentioned and modified it a bit so that it adds the class name as well. If you put Management Studio into Text-Output-Mode and remove the output of the column names you get copy paste text for all classes:

CREATE PROCEDURE [dbo].[Helper_CreatePocoFromTableName]    
    @tableName varchar(100)
AS
BEGIN
SET NOCOUNT ON;

-- Subquery to return only the copy paste text
Select PropertyColumn from (
    SELECT 1 as rowNr, 'public class ' + @tableName + ' {' as PropertyColumn
    UNION
    SELECT 2 as rowNr, 'public ' + a1.NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}' as PropertyColumn
    -- ,* comment added so that i get copy pasteable output
     FROM 
    (
        /*using top because i'm putting an order by ordinal_position on it. 
        putting a top on it is the only way for a subquery to be ordered*/
        SELECT TOP 100 PERCENT
        COLUMN_NAME,
        DATA_TYPE,
        IS_NULLABLE,
        CASE 
            WHEN DATA_TYPE = 'varchar' THEN 'string'
            WHEN DATA_TYPE = 'nvarchar' THEN 'string' 
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
            WHEN DATA_TYPE = 'char' THEN 'string'                       
            WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
            WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
            WHEN DATA_TYPE = 'xml' THEN 'string'
        END AS NewType
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @tableName
        ORDER BY ORDINAL_POSITION
        ) AS a1 
    UNION 
    SELECT 3 as rowNr,  '} // class ' + @tableName
    ) as t Order By rowNr asc
END

P.S.: I would have done it as an edit suggestion to his answers but my experience is that often edit suggestions get rejected.

Update

User chris-w-mclean suggested the following changes (see his suggested-edit) which i have not tried myself:

• Replace SELECT 1 as rowNr, 'public class ' with SELECT 1.0 as rowNr, 'public class '
• Replace SELECT 2 as rowNr, 'public ' with SELECT 2 + a1.ORDINAL_POSITION/1000 as rowNr, 'public '
• Replace SELECT TOP 100 PERCENT COLUMN_NAME, with SELECT COLUMN_NAME,
• add between IS_NULLABLE, CASE this line cast(ORDINAL_POSITION as float) as ORDINAL_POSITION,
• remove ORDER BY ORDINAL_POSITION
• change SELECT 3 as to SELECT 3.0 as

Orfurd

This code doesn't guarantee correct ordering - the ordering is lost in the union itself. The ORDINAL_POSITION should be promoted through the query for a final ORDER BY rowNr, pos. Then the "TOP 100 PERCENT" kludge can also go. – Olson

WHEN DATA_TYPE = 'uniqueidentifier' AND IS_NULLABLE = 'NO' THEN 'Guid' WHEN DATA_TYPE = 'uniqueidentifier' AND IS_NULLABLE = 'YES' THEN 'Guid?' – Caine

Answer 4

Try this version I optimized a bit, so that the result doesn't need to be piped to Text output. Instead, the PRINT statement allows the output to be copy/pasted easily. I've also removed the subquery and added declarations for nvarchar/ntext types.

This is for a single table, but it can be converted to a stored proc to use one of the cursor suggestions above.

SET NOCOUNT ON
DECLARE @tbl as varchar(255)
SET @tbl = '@@@@'

DECLARE @flds as varchar(8000)
SET @flds=''

SELECT -1 as f0, 'public class ' + @tbl + ' {' as f1 into #tmp

INSERT #tmp
SELECT 
    ORDINAL_POSITION, 
    '    public ' + 
    CASE 
        WHEN DATA_TYPE = 'varchar' THEN 'string'
        WHEN DATA_TYPE = 'nvarchar' THEN 'string'
        WHEN DATA_TYPE = 'text' THEN 'string'
        WHEN DATA_TYPE = 'ntext' THEN 'string'
        WHEN DATA_TYPE = 'char' THEN 'string'
        WHEN DATA_TYPE = 'xml' THEN 'string'
        WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
        WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
        WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
        WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
        WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
        WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
        WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
        WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
        WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
        WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
        WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
        WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
        WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
        WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
    END + ' ' + COLUMN_NAME + ' {get;set;}'
FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @tbl

INSERT #tmp SELECT 999, '}'

SELECT @flds=@flds + f1 +'
' from #tmp order by f0

DROP TABLE #tmp

PRINT @flds

Owenism

Answer 5

My approach is to:

1. Use <dynamic> to fetch some rows without type
2. Serialize these rows to JSON
3. Copy the JSON string from the console (or using the debugger)
4. Paste this into a JSON to C# model generator (e.g. https://app.quicktype.io/).

I.e.:

var persons = connection.Query<dynamic>("SELECT * FROM Persons");
var serializedPerson = JsonConvert.Serialize(persons.First());
Console.WriteLine(serializedPerson);

Enrollee

Answer 6

This one is for Oracle. It's probably not complete, but it's worked for me thus far.

SELECT 
'public ' || A.NewType || ' ' || REPLACE(INITCAP(REPLACE(A.COLUMN_NAME, '_', ' ')), ' ', '') || ' {get;set;}' GET_SET
, A.*
 FROM 
(
SELECT
COLUMN_NAME,
DATA_TYPE,
NULLABLE,
CASE 
    WHEN DATA_TYPE = 'VARCHAR2' THEN 'string'
    WHEN DATA_TYPE = 'VARCHAR' THEN 'string'
    WHEN DATA_TYPE = 'DATE' AND NULLABLE = 'N' THEN 'DateTime'
    WHEN DATA_TYPE = 'DATE' AND NULLABLE = 'Y' THEN 'DateTime?'
    WHEN DATA_TYPE = 'INT' AND NULLABLE = 'N' THEN 'int?'
    WHEN DATA_TYPE = 'INT' AND NULLABLE = 'Y' THEN 'int'
    WHEN DATA_TYPE = 'DECIMAL' AND NULLABLE = 'N' THEN 'decimal'
    WHEN DATA_TYPE = 'DECIMAL' AND NULLABLE = 'Y' THEN 'decimal?'
    WHEN DATA_TYPE = 'NUMBER' AND NULLABLE = 'N' THEN 'decimal'
    WHEN DATA_TYPE = 'NUMBER' AND NULLABLE = 'Y' THEN 'decimal?'
    WHEN DATA_TYPE = 'NUMBER2' AND NULLABLE = 'N' THEN 'decimal'
    WHEN DATA_TYPE = 'NUMBER2' AND NULLABLE = 'Y' THEN 'decimal?'
    WHEN DATA_TYPE = 'CHAR' THEN 'string'
    WHEN DATA_TYPE = 'CHAR2' THEN 'string'
    WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
    WHEN DATA_TYPE = 'CLOB' THEN 'byte[]'
    ELSE '??'
END AS NewType
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '<<TABLENAME>>'
ORDER BY COLUMN_ID
) A

Pouncey

Answer 7

I know it is an old topic,but there is another simple option can choose.

You can use PocoClassGenerator: Mini Dapper's POCO Class Generator (Support Dapper Contrib)

• Support current DataBase all tables and views generate POCO class code
• Support Dapper.Contrib
• Support mutiple RDBMS : sqlserver,oracle,mysql,postgresql
• mini and faster (only in 5 seconds generate 100 tables code)
• Use appropriate dialect schema table SQL for each database query

DEMO

• POCOGenerator Generate Class By Dynamic SQL | .NET Fiddle
• POCO Class Generator GenerateAllTables | .NET Fiddle
• DataTable POCO Class Generator | .NET Fiddle

GetStart

👇First : Copy&Paste PocoClassGenerator.cs Code to your project or LINQPad.
or Install from NuGet

PM> install-package PocoClassGenerator

👇Second : Use Connection to call GenerateAllTables and then print it.

using (var connection = Connection)
{
    Console.WriteLine(connection.GenerateAllTables());
}

Support Dapper Contrib POCO Class

• Just call method with GeneratorBehavior.DapperContrib

using (var conn = GetConnection())
{
    var result = conn.GenerateAllTables(GeneratorBehavior.DapperContrib);
    Console.WriteLine(result);
}

The Online Demo : POCO Dapper Contrib Class Generator GenerateAllTables | .NET Fiddle

Generate Comment

using (var conn = GetConnection())
{
    var result = conn.GenerateAllTables(GeneratorBehavior.Comment);
    Console.WriteLine(result);
}

Generate View

using (var conn = GetConnection())
{
    var result = conn.GenerateAllTables(GeneratorBehavior.View);
    Console.WriteLine(result);
}

Generate View and Comment and Dapper.Contrib

using (var conn = GetConnection())
{
    var result = conn.GenerateAllTables(GeneratorBehavior.View | GeneratorBehavior.Comment | GeneratorBehavior.DapperContrib);
    Console.WriteLine(result);
}

Generate one class by sql

1. Generate one class

using (var connection = Connection)
{
    var classCode = connection.GenerateClass("select * from Table");
    Console.WriteLine(classCode);
}

2. Specify class name

using (var connection = Connection)
{
    var classCode = connection.GenerateClass("with EMP as (select 1 ID,'WeiHan' Name,25 Age) select * from EMP", className: "EMP");
    Console.WriteLine(classCode);
}

DataTablePocoClass

Code at DataTablePocoClassGenerator.cs

var dt = new DataTable();
dt.TableName = "TestTable";
dt.Columns.Add(new DataColumn() { ColumnName = "ID", DataType = typeof(string) });

var result = dt.GenerateClass();
var expect =
@"public class TestTable
{
public string ID { get; set; }
}";
Assert.Equal(expect, result);

Assuming

Answer 8

Here's dapper-pocos I made for generating POCOs for Dapper. The solution uses SQL Server's "sp_HELP" and "sp_describe_first_result_set". Give it the name of a stored procedure, or give it a select statement, and it will generate the related POCOs for use with Dapper. The app just passes the stored procedure or select statement to sp_Help and sp_describe_first_result_set, and maps the results to C# data types.

Sesquicentennial

Answer 9

I'm the author of CodegenCS Code Generator, and there's this sample template SimplePocos which does what you want.

How to use:

• Install dotnet-codegencs with this command: dotnet tool install -g dotnet-codegencs
• Extract your Database Schema (MSSQL or PostgreSQL supported) using this command: dotnet-codegencs model dbschema extract MSSQL "Server=<myHost>;initial catalog=<myDatabase>;persist security info=True;user id=<username>;password=<password>;MultipleActiveResultSets=True;TrustServerCertificate=True" AdventureWorks.json
• Copy the template to your local, which you can do with dotnet-codegencs template clone https://github.com/CodegenCS/Templates/DatabaseSchema/SimplePocos/SimplePocos.cs
• Check template cmdline options: dotnet-codegencs template run SimplePocos.cs /?
• Run like this dotnet-codegencs template run SimplePocos.cs .\Models\CodegenCS.Models.DbSchema.Extractor\MSSQL\AdventureWorksSchema.json "MyNamespace"
• There are many options, like -p:SingleFile=False

Templates are very easy to understand and customize.

Gradation

Answer 10

I had exactly the same requirement to generate objects from a database while handling CRUD reliably and efficiently in Dapper and took a different approach of preparing a replacement for Dapper's own Dapper.Contrib with support of Entity Framework schema definition so that scaffolding a database (models, relations, keys) can be done using Entity Framework tools like described for example here, sample below:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet ef dbcontext scaffold "Server=.\;Database=AdventureWorksLT2012;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model

Above dependencies can be removed from the project after scaffolding.

Currently Dapper.SqlGenerator is successfully working in production. It does not produce any overhead over Dapper in terms of performance, sometimes reducing time to generate a query by other means.

Keep in mind there are 2 separate nuget packages - Dapper.SqlGenerator for purely SQL Code generation from EF (Core) scaffolded models and Dapper.SqlGenerator.Async to run CRUD queries against the database using Dapper.

TLDR; You can use Entity Framework (Core) to scaffold model from database and use Dapper.SqlGenerator to generate CRUD queries on generated objects.

Rapprochement

Answer 11

I've seen where people use a hybrid project, using EF to scaffold the database, but I had to dapperize the output from that. For the recommended tools, I'm sure they are good, but I shy away from installing special software, until I had a stab at writing my own solution.

That said, here's a small CLI program(for my needs) that may be useful. Disclaimer, I'm not a seasoned C# programmer, so forgive anything that may be off kilter.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using Dapper;

namespace Pocos
{
    public class TAB {
        public string TABLE_NAME { get; set; }
    }
    public class COL {
        public string COLUMN_NAME { get; set; }
        public int? ORIDINAL_POSITIONS { set; get; }
        public string DATA_TYPE { get; set; }
        public string CHARACTER_MAXIMUM_LENGTH { get; set; }
        public string NUMERIC_PRECISION { get; set; }
        public string NUMERIC_SCALE { get; set; }
    }
    class Program {
        static void Main(string[] args) {
            string sConnect = "Server=LT6-MARKL;Database=PKDEM815;UID=PKDEM815;Password=PKDEM815";
            IEnumerable tables;
            IEnumerable columns;
            List lines;
            using ( var conn = new SqlConnection(sConnect))
                tables = conn.Query("SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME");
            // Roll through each table of the database and generate an .cs file, as a POCO
            foreach (TAB t in tables.OrderBy(t => t.TABLE_NAME)) {
                lines = new List();
                lines.Add("using System;");
                lines.Add("using System.Collections.Generic;");
                lines.Add("using System.Configuration;");
                lines.Add("using System.Data.SqlClient;");
                lines.Add("using Dapper;"); 
                lines.Add("using Dapper.Contrib.Extensions;");
                lines.Add("");
                lines.Add("namespace PKDataLayer.Models  {");
                lines.Add("");
                lines.Add("\t[Table(\"" + t.TABLE_NAME + "\")]");
                lines.Add("\tpublic class " + t.TABLE_NAME + " {");
                lines.Add("");
                using (var conn2 = new SqlConnection(sConnect)) {
                    columns = conn2.Query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"+ t.TABLE_NAME +"' ORDER BY ORDINAL_POSITION");
                    foreach( COL c in columns) {
                        if (t.TABLE_NAME + "_KEY" == c.COLUMN_NAME || t.TABLE_NAME + "_SEQNUM" == c.COLUMN_NAME)
                            lines.Add("\t\t[Key]");
                        // SELECT DISTINCT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES )
                        if (c.DATA_TYPE == "char" || c.DATA_TYPE == "varchar")
                            lines.Add("\t\tpublic string " + c.COLUMN_NAME + " { get; set; }");
                        if (c.DATA_TYPE == "int")
                            lines.Add("\t\tpublic int " + c.COLUMN_NAME + " { get; set; }");
                        if (c.DATA_TYPE == "datetime")
                            lines.Add("\t\tpublic DateTime? " + c.COLUMN_NAME + " { get; set; }");
                        if (c.DATA_TYPE == "decimal" || c.DATA_TYPE == "numeric")
                            lines.Add("\t\tpublic decimal? " + c.COLUMN_NAME + " { get; set; }");
                    }
                }
                lines.Add("\t}");
                lines.Add("}");
                Console.WriteLine("Creating POCO for " + t.TABLE_NAME);
                using (TextWriter tw = new StreamWriter( t.TABLE_NAME + ".cs" ))
                    foreach (String s in lines)
                        tw.WriteLine(s);
            }
        }
    }
}

Factfinding

Answer 12

This might not work with VS2010, but if you've been updating your Version this should work.

My way of generating Models from a Database is with Ef Core Power Tools, a little Add-On that uses Ef Core 6.

Go to Extensions in Visual Studio and install it. After that, you can right-click on your Project and select Reverse Engineer under EF Core Power Tools.

From there, you connect to the Database, select the Tables to be reverse-engineered and select EntityTypes only You can be as specific as you want, e.g. specify the output path (DbModels in my case). Click on OK.

Then, your Models should pop up and you're free to use these Models in your Dapper-Code.

Summation

Answer 13

So based on some SQL here and there. I've build a "simple" select that generate class(es) for table(s) or a schema(s)! What's nice about it, is that it will:

• Keep the column order
• Adding namespaces
• Add the [Table] Attributes on the class.
• Add the [Key] on the primary key(s) (might not be supported by dapper.contrib if 2+ PK)

The function below can be used like so:

select *
from dbo.TableToClass('schema','null or table name') m
where m.TableName not in('unwantedtablename')
order by m.TableSchema asc
    , m.TableName asc
    , m.ClassOrder asc
    , m.ColumnOrder asc;

When you copy paste from SSMS, it might remove the TAB. Here is the code:

CREATE or alter     function dbo.TableToClass(
      @schema varchar(250)
    , @table varchar(250)
)
returns table
as 
return
/*
 --USE IT LIKE: the order by is necessary on the query
 
select *
from dbo.TableToClass('schemaName', 'null or table name') m
order by
      m.tableSchema asc
    , m.tableName asc
    , m.ClassOrder asc
    , m.columnOrder asc
*/


with typeConversion as(
    Select 
         typeConversion.sqlType
        ,typeConversion.isNullable
        ,typeConversion.cSharpType
    from 
    ( values
             ('nvarchar',   'YES', 'string'),   ('nvarchar','NO', 'string') 
            ,('varchar',    'YES', 'string'),   ('varchar', 'NO', 'string')
            ,('char',       'YES', 'string'),   ('char',    'NO', 'string')
                
            ,('datetime',       'YES', 'DateTime?'),        ('datetime',        'NO', 'DateTime')
            ,('datetime2',      'YES', 'DateTime?'),        ('datetime2',       'NO', 'DateTime')
            ,('date',           'YES', 'DateTime?'),        ('date',            'NO', 'DateTime')
            ,('datetimeoffset', 'YES', 'DateTimeOffset?'),  ('datetimeoffset',  'NO', 'DateTimeOffset') 
            ,('time',           'YES', 'TimeSpan?'),        ('timestamp',       'NO', 'TimeSpan')
            
            ,('bigint',   'YES', 'long?'),  ('bigint',  'NO', 'long')   
            ,('int',      'YES', 'int?'),   ('int',     'NO', 'int')  
            ,('smallint', 'YES', 'Int16?'), ('smallint','NO', 'Int16')
            
            ,('decimal',    'YES', 'decimal?'), ('decimal',     'NO', 'decimal')
            ,('numeric',    'YES', 'decimal?'), ('numeric',     'NO', 'decimal')  
            ,('money',      'YES', 'decimal?'), ('money',       'NO', 'decimal') 
            
            ,('tinyint',    'YES', 'byte?'),    ('tinyint',     'NO', 'byte')   
            ,('varbinary',  'YES', 'byte[]'),   ('varbinary',   'NO', 'byte[]?')        
            ,('bit',        'YES', 'bool?'),    ('bit',         'NO', 'bool')       
            ,('xml',        'YES', 'string'),   ('xml',         'NO', 'string')
    ) typeConversion(sqlType, isNullable, cSharpType)
), columnInfo as (
    select 
          colInfo.TABLE_SCHEMA
        , colInfo.TABLE_NAME 
        , concat(colInfo.TABLE_SCHEMA, '.', colInfo.TABLE_NAME) FullTableName
        , colInfo.COLUMN_NAME 
        , colInfo.ORDINAL_POSITION
        , typeConversion.sqlType
        , typeConversion.csharpType
        ,case 
             (
                Select top 1 pk.CONSTRAINT_TYPE
                from INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS columnUsage 
                    join INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS pk
                        --on pk.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                        on pk.TABLE_SCHEMA = colInfo.TABLE_SCHEMA 
                        and pk.TABLE_NAME = colInfo.TABLE_NAME 
                        and pk.CONSTRAINT_NAME = columnUsage.CONSTRAINT_NAME 
                where 
                    columnUsage.TABLE_SCHEMA = colInfo.TABLE_SCHEMA 
                    and columnUsage.TABLE_NAME = colInfo.TABLE_NAME
                    and columnUsage.COLUMN_NAME = colInfo.COLUMN_NAME
            ) 
            when 'PRIMARY KEY' then  (
                case  (select COLUMNPROPERTY(OBJECT_ID(concat(colInfo.TABLE_SCHEMA ,'.',colInfo.TABLE_NAME)),colInfo.COLUMN_NAME,'isidentity'))
                    when 1 then 'PK IDENTITY'
                    else 'PK'
                end
                )
            when 'FOREIGN KEY' then 'FK'
            else 'COL'
        end as ColumnType
    from INFORMATION_SCHEMA.COLUMNS as colInfo
    left join typeConversion on typeConversion.sqlType = colInfo.DATA_TYPE and typeConversion.isNullable = colInfo.IS_NULLABLE
    where 
        /************ SET PARAMETER / CONDITION HERE  **************/
        (   --SCHEMA
            'True' = ( 
                case 
                    --when @schema is null then return           'True'
                    when colInfo.TABLE_SCHEMA = coalesce(@schema, 'dbo') then 'True'
                    else 'False'
                end
            ) 
            And          -- SET SCHEMA NAME HERE (might be dbo for default) 
            'True' = ( --Table
                case 
                    when @table is null then 'True'
                    when colInfo.TABLE_NAME = @table then 'True'
                    else 'False'
                end
            )
        )
),  classBuilder2_StartFile as (
    select top 1  
        concat(
             'using System;', char(10)
            ,'using Dapper;', char(10)
            ,'using Dapper.Contrib;', char(10)
            ,'using Dapper.Contrib.Extensions;', char(10)
            , char(10) 
            ,'namespace MYPROJECTNAMESPACE.',c.TABLE_SCHEMA, '.Models.Db; ', char(10)
        ) as txt
        , 'Using & Namespace' as ClassPart
        , 5 as ClassOrder
        , c.TABLE_SCHEMA as tableSchema
    from columnInfo c
), classBuilder2_StartClass as(
    select distinct 
        concat(
              char(10)
            , '[Table("',c.FullTableName,'")]', char(10)
            , 'public partial class ', c.TABLE_NAME, char(10)
            , '{'
        ) as txt
        , 'Class name' as ClassPart
        , 17 as ClassOrder
        , c.TABLE_NAME as tableName
        , c.TABLE_SCHEMA as tableSchema
    from columnInfo c
), classBuilder2_Properties as(
    select  
        concat(
            case c.ColumnType --Column Attribute for dapper.
                when 'PK' then          
                    concat(Char(9),'[ExplicitKey]', char(10)) --Dapper: After insert return 0
                when 'PK IDENTITY'then      
                    concat(Char(9),'[Key]', char(10)) -- Dapper: After inser return actual PK
                else ''
            end
            , Char(9), char(9), 'public ', c.csharpType,' ', c.COLUMN_NAME, ' { get; set; }'            
        ) as txt
        , ORDINAL_POSITION as columnOrder
        , 'Property' as ClassPart
        , 30 as ClassOrder
        , c.COLUMN_NAME as columnName
        , c.TABLE_NAME as tableName
        , c.TABLE_SCHEMA as tableSchema
    from columnInfo c
), classBuilder2_EndClass as( 
    select distinct
         concat(
              char(9), '}'
        ) as txt
        , 'End of C# Class' as ClassPart
        , 111 as ClassOrder
        , c.TABLE_NAME as tableName
        , c.TABLE_SCHEMA as tableSchema
    from columnInfo c
),  classBuilder2_EndFile as( 
    select top 1
         concat(
            char(10),char(10)
        ) as txt
        , 'End of C# Class' as ClassPart
        , 120 as ClassOrder
        , 'ZZZZZ' as tableName
        , 'ZZZZZ' as tableSchema
    from columnInfo c
), classBuilder_merge as(
    select txt, ClassPart, ClassOrder, 'AAA_SCHEMA' as tableName, tableSchema, 'N/A' as columnName, 0 as columnOrder
    from classBuilder2_StartFile
    union all
    select txt, ClassPart, ClassOrder, tableName, tableSchema, 'N/A' as columnName, 0 as columnOrder
    from classBuilder2_StartClass 
    union all
    select txt, ClassPart, ClassOrder, tableName, tableSchema, columnName, columnOrder  
    from classBuilder2_Properties
    union all 
    select txt, ClassPart, ClassOrder, tableName, tableSchema, 'N/A'as columnNam, 0 as columnOrder
    from classBuilder2_EndClass
    union all 
    select txt, ClassPart, ClassOrder, tableName, tableSchema, 'N/A'as columnNam, 0 as columnOrder
    from classBuilder2_EndFile
), finalSelect as(
--AFTER SQL Server 2016 (13.x) and later. If before that, remove  CROSS APPLY STRING_SPLIT
select top 100 percent 
    lines.*
    , m.tableSchema
    , m.tableName
    , m.ClassOrder
    , m.columnOrder
from classBuilder_merge m
CROSS APPLY STRING_SPLIT(m.txt, char(10))  lines 
order by -- 
      m.tableSchema asc
    , m.tableName asc
    , m.ClassOrder asc
    , m.columnOrder asc
)
select * from finalSelect;

Neoterize