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?
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.
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:
I would recommend the second option, because you can set your packages to auto download before build when not present (part of your CVS).
In Visual Studio, you can use integrated Entity Framework's "Code first from database" function to generate POCO classes for use with PetaPoco. Just generate them and remove all unnecessary EF trash like DBContext and attributes. This way, you can generate POCOs not only for C#, but also for VB.NET and its all visual clicking, no messing with T4 Templates.
Project/Add New Item.../Data/ADO.NET Entity Data Model/Code first from database
