1

Is there a way to automap a query into a class in C# in order to strong type the query results? I don't want to access to query result with a DataTable, specifying manually the Column Name anymore.

CRK
  • 337
  • 2
  • 10

4 Answers4

3

There is couple of options:

EntityFramework - powerful and heavyweight. https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).aspx

Dapper - lightweight - mostly mapper https://www.codeproject.com/Articles/212274/A-Look-at-Dapper-NET

If you need only mapping of stored procedures, queries etc, Dapper is good way to go, as it's basically adding extension methods to connection object. It cannot create database etc as Entity Framework but it all really depends on needs.

madoxdev
  • 3,770
  • 1
  • 24
  • 39
  • How to use Dapper to automatically create a class from query results? – CRK Dec 29 '16 at 11:10
  • Nothing can do it for you. As it's not anyhow good practice. You can generate classes bye EntityFramework based on existing Database (but that is not on runtime). These days you should be creating classes and using EF to create database out of them not other way round. – madoxdev Dec 29 '16 at 11:12
  • The fact that is "not on runtime" is good. In this way I can strong type my DataTable. If it create class at runtime I can't do this. I'm right? – CRK Dec 29 '16 at 11:17
  • in ORM there is not more DataTable, it's Classes to Database objects relation. DataTable is already a moment where you skip ORM. – madoxdev Dec 29 '16 at 11:40
  • Why `DataTable's are so important for you? – madoxdev Dec 29 '16 at 11:42
  • I make it simple: I want to automatically create a class from a query at design time in order to use this class in my code. I don't want to do this at runtime. – CRK Dec 29 '16 at 11:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131793/discussion-between-madox-and-crk). – madoxdev Dec 29 '16 at 11:44
  • @MadOX - "Nothing can do it for you". QueryFirst can do it for you, and I would say it's very good practice! Not everyone is happy with EF. See my answer. – bbsimonbb Jan 03 '17 at 00:34
2

You should take a look at AutoMapper. It's an easy and user-friendly way to map a DataTable to a List<object> of your specification.

You can find an article which talks about it (including sample code) here.

If you want to automize the process, you can use dynamic objects which don't require you to create specific classes for every DataTable that you're using.

Hope this helped!

Nathangrad
  • 1,426
  • 10
  • 25
  • hi, thank you for the answer. I want to automatize the process, in the artcile you posted he tells to create a Class that matches Column of DataTable but I want to do it automatically (not manually create the class). – CRK Dec 29 '16 at 11:09
  • Good, but this do all at runtime , I'm right? So I can't strong type the DataTable. – CRK Dec 29 '16 at 11:16
  • You would do something like: `List objects = AutoMapper.Mapper.DynamicMap>(dataTable.CreateDataReader());` The DataTable has to be strongly typed, but the objects mapped don't need to be. So, yes it'll be done at runtime. – Nathangrad Dec 29 '16 at 11:18
  • Maybe my bad English don't help. If I do what you tell, how can I have the autocompletion to access at a specific Column Name if this will evaluate at runtime? – CRK Dec 29 '16 at 11:21
  • Autocomplete doesn't work for dynamic objects. You'll need to make sure you're not making any errors whilst programming manually. e.g. `objects[0].testVariable = 5;` will be evaluated at runtime as it's an anonymous type. – Nathangrad Dec 29 '16 at 11:24
  • Is there a way to do this at design time? Automatically create class for the query (I don't know how to right explain that) like EF do when you connect to a DataBase and he automatically create classes in order to use them with the autocompletion, strong typing all? @Nathangrad – CRK Dec 29 '16 at 11:30
  • Not that I'm aware of, but if you want to use Entity Framework for that, then that would probably be the best option for you. See MadOX's answer for that solution. – Nathangrad Dec 29 '16 at 11:33
  • EF create class at design-time. I want this but not from DataBase, but only from query results – CRK Dec 29 '16 at 11:36
2

My favourite question. Use QueryFirst. Write your SQL in the SQL editor window, syntax validated as you type, then QueryFirst will generate the C# wrapper to execute it, with strongly typed inputs and outputs. And there are numerous other benefits :-) SqlServer, MySql and Postgres support built in. Others easy to add. Disclaimer : I wrote QueryFirst.

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110
  • the tool seems fit perfect to my needs but why all classes generated are empty even if tables are full of query results? – CRK Jan 02 '17 at 12:30
  • 1
    How do you mean empty classes? You've installed the tool? You've created your design time and runtime data sources? You've written a query, that runs, and so you can inspect the generated wrapper and poco? You call Execute() and get empty instances ? How many ? – bbsimonbb Jan 02 '17 at 13:23
  • I'm using Microsoft SQL Server – CRK Jan 02 '17 at 14:23
  • Ok then make sure your QfDefaultConnection has a provider="System.Data.SqlClient". – bbsimonbb Jan 02 '17 at 14:26
  • this is my QfRuntimeConnection class QfRuntimeConnection { public static string GetConnectionString() { return ConfigurationSettings.AppSettings["QfRuntimeConnection"]; } } – CRK Jan 02 '17 at 14:32
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132111/discussion-between-crk-and-user1585345). – CRK Jan 02 '17 at 14:33
0

There is a helpful library on LINQ to DB

"LINQ to DB is the fastest LINQ database access library offering a simple, light, fast, and type-safe layer between your POCO objects and your database."

https://github.com/linq2db/linq2db

Ggalla1779
  • 476
  • 7
  • 18
  • Does it create classes at design time? – CRK Dec 29 '16 at 11:40
  • No it does not... yry here is talks about what your looking for http://stackoverflow.com/questions/2464909/generate-poco-classes-in-different-project-to-the-project-with-entity-framework – Ggalla1779 Dec 29 '16 at 11:56