Generate class from datatable or database table

Question

I have a project where a client can import an excel file, csv, or tab-delimited file. This file is loaded into a datatable which I convert into a SQLite database table. I'd really prefer to work with strongly typed objects vs datatables so is there a way to easily convert the database table created into a strongly typed class using reflection? The kicker is the file imported into the application will always be different, (ie. different columns) so I can't really hardcode any strongly typed objects, they are always going to have to be generated on the fly.

Answer 1

You could use the new dynamic features of C# 4 if that was a possibility. More specifically the ExpandoObject Class.

ExpandoObject represents an object whose members can be dynamically added and removed at run time.

You also have the DynamicObject Class.

Take a look at the following links for more info:

Dynamic class creation

Populating and Using Dynamic Classes in C#/.NET 4.0

How do create a Dynamic class in C# 4?

Answer 2

In theory, you could create an assembly using something like Reflection.Emit, or even by generating code files and calling the compiler to build these into an assembly.

However, I'm not sure how this would be useful. Provided that the schema of these tables is determined at run-time, any usage of these generated classes wouldn't be able to use these strongly-typed properties you've added without using reflection, and at that point you're essentially dealing with something weakly typed with the added performance hit of reflection. Dynamic types in C# 4.0 might eliminate the performance hit, but you're still adding a lot of complexity for no real benefit.

Answer 3

Ryan already asked the right question and i can also only aggree that the only benefit of a strongly typed class is at design or compile time, but not at run-time.

Maybe you want after creating your SQLlite tables built up some strong type classes in another application that uses this table.

In that case you could probably take a look into T4 - Text Template Transformation Toolkit, cause it is able to create strong type classes out of a query, so that you are able to use these classes at compile-time for further applications.

But before you start using it, you should know that T4 is one of the best kept Visual Studio secrets. ;-)