Query SQL Server dynamically

Question

I am working on a panel. I started reorganizing the application, so the methods associated with querying the database are general or dynamic.

I am new to the concept of generics and still an amateur programmer, but I did a few searches and I tried to find a solution. The problem is not to build the query string dynamically. I'm fine with the concatenation of string literals and variables, I really don't need anything more complicated. The big problem for me is when I create this query, returning the data and assigning them to the right variables in a dynamic way.

Suppose I have a defect table, another for test cases and another for test runs. I want to create a method that looks something like this:

public void QueryDatabase<T>(ref List<T> Entitylist, List<string> Columns, string query) where T: Defect, new() 

Now this is not perfect, but you get the idea. Not everything about defects, test scripts, and test runs is the same, but I'm looking for a way to dynamically assign recovered columns to my “correct” variable.

If you need more information, I can provide it.

Answer 1

You reinvent the wheel, yes that's true. It’s best to use an object-relational map maker from a shelf. But I think you also deserve the answer to your question: in order to dynamically assign query results to the correct properties, you must use reflection. See the documentation for the System.Reflection namespace for more information.

Answer 2

You reinvent the wheel. Use an ORM like Entity Framework or NHibernate. You will find it more flexible, and such tools will continue to evolve over time and add new features and improve performance, while you can focus on more important things.

EDIT :
Although I believe that in general it is important to learn how to use tools for something like this (I personally am a fan of the Entity Framework and have successfully used it in several projects and used LINQ to SQL before), it can still be valuable, because training exercise to understand how to do this. ORMs I have experience using XML to define a data model and using code generation in an XML file to create a class model. LINQ to SQL uses custom attributes for code-generated classes to define the source table and columns for each class and property and reflection at run time to map the data returned from SqlDataReader to the properties of the class object. Entity Framework may behave differently depending on the version you are using, regardless of whether you use the default or POCO templates, but in the end, basically, it's the same thing (using reflection to match the results database with the properties of your class), you can or may not use custom attributes to determine the mapping. I assume NHibernate is doing the same.