Tool to generate C# DTO Class for SQL Table AND DTO Instances for each Row in Table

Question

I'm looking for for a tool or technique that will allow me to point to a table in a SQL Server Database, generate a DTO Class for that table AND then generate DTO Class INSTANCES for each row in that table.

Given a table in SQL Server

CREATE TABLE 
[dbo].[Tickets](
[TicketID] [int] IDENTITY(1,1) NOT NULL,
[TicketName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED ([TicketID] ASC)

Populated with data:

TicketID   TicketName
1          Green
2          Blue

Would produce a class called "Tickets" with properties:

 public int TicketID {get;set;}
 public string TicketName {get;set;}

AND then something like:

public List<Tickets> LoadDataIntoListOfTickets()
{
     List<Tickets> theList = new List<Tickets>;
     theList.Add(new Ticket(){TicketID = 1, TicketName = "Green"});
     theList.Add(new Ticket(){TicketID = 2, TicketName = "Blue"});
     return theList;  
}

I don't want to generate INSERT statements to send to SQL Server. I want to create DTO Class and Instances for the Data.

Clarification: I'm looking to do a code generation of the DTO Class AND the source code for the DTO instances for each row of in the data as it exists AT A SPECIFIC POINT IN TIME. I'm familiar with the various ORM's that can create the classes. What I'm looking for is something that will generate the LoadDataIntoListOfTickets() listing with a line of source code for each ROW in the database. (Hopefully that makes it clear.)

Clarification #2: This is going to be used to replace having SQL INSERT statements in a text file for initializing tables when the user creates a new database for an application. Having a text file with the SQL INSERT statements allowed me to use traditional version control tools to manage our database initialization process. It works really well, but it isn't as human readable (whitespace / alignment) or re-usable as storing the data in DTO instances would be. Serializing to XML would reduce readability. Binary serialization would (by nature) make it NOT human readable and would also make it near impossible to do a meaningful compare/merge with previous versions.

Does anyone know of a tool or library that can do this ?

Have you looked at ORM tools? Entity Framework? LINQ-to-SQL? nHibernate? — Marc Gravell, Apr 08 '12 at 10:03
Also: minor note - normally you wouldn't deliberately read *all* rows - just the ones you need in an appropriately filtered subset — Marc Gravell, Apr 08 '12 at 10:17
Perhaps I need to be more clear in my question. I'll go edit it. — AZDev, Apr 08 '12 at 10:21
Do you mean you want the tool to query the data ahead of time, and generate code to represent the dta *as seen then*, so it can be used in isolation? If so, that is possible - typically via serialization. — Marc Gravell, Apr 08 '12 at 10:26
for the record, I've implemented very similar systems several times, and in each case serialization was by far the most convenient approach. I'll add context in an answer... — Marc Gravell, Apr 08 '12 at 10:38
T4 can do this. Look here for a ballpark answer that would at least get you started: http://stackoverflow.com/questions/2223421/t4-template-to-create-multiple-html-for-example-output-files-per-table-from/3070406#3070406 — Michael Maddox, Apr 08 '12 at 11:53

score 1 · Answer 1 · edited May 23 '17 at 11:49

If what you are really after is a way to get a new database from containing nothing to it containing a database a schema and an initial set of data then I would HIGHLY recommend you looking into database migration tools. they will help you keep things human-readable and in sync on multiple installations / environments.

here you can find a few listed: https://stackoverflow.com/questions/8033/database-migration-library-for-net

Personally I prefer https://github.com/dradovic/MigSharp

Tool to generate C# DTO Class for SQL Table AND DTO Instances for each Row in Table

1 Answers1