How to automatically create model from database using PetaPoco library?

Question

I have a table in my database for which I want to create a model class with getters and setters. For most of the tasks in my project, I am using PetaPoco. I created models manually, but few tables have lots of columns.

Is there any way to create a model from a database using PetaPoco?

Solution 1

PetaPoco has T4 Visual Studio templates that should do this for you. If you don't want to have them all generated, just use the template and then copy those classes from the resulting *.cs file that you want.

You'll find templates on PetaPoco's GitHub code repo.

Adding templates to your project can be done in two ways:

1. copying files manually
2. using NuGet in the package console or using Visual Studio Package Manager. Details on NuGet site

I would recommend the second option, because you can set your packages to auto download before build when not present (part of your CVS).

Solution 2

I highly recommend you use the T4 templates as it will make the process much faster as well as add additional functionality.

The fastest way is to open the "Package Manager Console" (Tools > Library Package Manger > Package Manger Console), make sure that your project is selected in the "Default project" drop down and run:

PM>install-package petapoco

This will create a folder called "Models" and a sub folder called "Generated". In the "Generated" folder, open the "Database.tt" file and setup the information. Refer to http://www.toptensoftware.com/petapoco/ for information about setting this up.

Once you successfully setup the T4 template, it will generate a code file called "Database.cs" with all your objects in your database. What's generated are "partial classes". The key here is that you should NOT modify any object in this file. Instead, you will create a new partial class in the same namespace that you can then implement more logic to. Move/update your existing objects to confirm to the new setup.

Another option is to create some TSQL to generate some code. Here is a very crude example of something I've used in the past to generate interfaces for my PetaPoco tables.

declare @script nvarchar(max);
declare @table nvarchar(256);

set @table = 'YourTableName'

set @script = 'public interface I' + @table + '{' + char(10);
SELECT
    @script = @script + 
        CASE
            WHEN st.Name IN ('int') AND c.is_nullable = 0 THEN 'int'
            WHEN st.name in ('smallint')  AND c.is_nullable = 0 THEN 'short'
            WHEN st.name IN ('bigint') AND c.is_nullable = 0  THEN 'long'
            WHEN st.name IN ('varchar','nvarchar','sysname') THEN 'string'
            WHEN st.Name IN ('datetime') AND c.is_nullable = 0 THEN 'DateTime'
            WHEN st.Name IN ('bit') AND c.is_nullable = 0 THEN 'bool'
            WHEN st.Name IN ('decimal') AND c.is_nullable = 0 THEN 'decimal'
            /* NULLABLE VALUES */
            WHEN st.Name IN ('int') AND c.is_nullable = 1 THEN 'int?'
            WHEN st.name in ('smallint')  AND c.is_nullable = 1 THEN 'short?'
            WHEN st.name IN ('bigint') AND c.is_nullable = 1  THEN 'long?'
            WHEN st.name IN ('varchar','nvarchar','sysname') AND c.is_nullable = 1 THEN 'string?'
            WHEN st.Name IN ('datetime') AND c.is_nullable = 1 THEN 'DateTime?'
            WHEN st.Name IN ('bit') AND c.is_nullable = 1 THEN 'bool?'
            WHEN st.Name IN ('decimal') AND c.is_nullable = 1 THEN 'decimal?'   
            --WHEN st.name IN('sysname') AND c.is_nullable = 1 THEN 'string?'       
            ELSE 'UNKOWN-' + st.name
        END
    + ' ' + c.name + '{get;set;}' + char(10)
FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @table

print @script + '}'

I hope this helps.