visual-studio-2010dappermicro-orm

How to generate model from database using Dapper?


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.


Solution

  • 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;
                }
    
    
    
            }
    #>