Deserialize a database table row straight into a C# object - is there a mechanism for this?

Question

I am new to C# and this may end up being a dumb question but i need to ask anyway.

Is there a mechanism with C# to deserialize a result from an executed SQL statement into a c# object?

I have a C# program that reads a table from an sql server storing the row in an object - i am assigning each column value to an object member manually so i was wondering if there is a way to serialize the row automagically into an object. Or even better, a whole table in a collection of objects of the same type.

My environment is C#, VS2010, .NET4, SQLServer2008. The assumption is that i know the columns i need, it's not a select * query.

A link to a neat example will also be appreciated.

Thanks.

Answer 1

You could use an ORM to do this. ADO.NET Entity Framework (checkout the video tutorials) and NHibernate are popular choices.

Answer 2

If the columns named as per the table names, you can do this with LINQ-to-SQL without any mapping code - just using ExecuteQuery:

using(var dc = new DataContext(connectionString)) {
    var objects = dc.ExecuteQuery<YourType>(sql); // now iterate object
}

Additionally, the sql can be automatically parameterized using string.Format rules:

class Person {
    public int Id {get;set;}
    public string Name {get;set;}
    public string Address {get;set;}
}
using(var dc = new DataContext(connectionString)) {
    List<Person> people = dc.ExecuteQuery(@"
          SELECT Id, Name Address
          FROM [People]
          WHERE [Name] = {0}", name).ToList(); // some LINQ too

}

Of course, you can also use the VS tools to create a typed data-context, allowing things like:

using(var dc = new SpecificDataContext(connectionString)) {
    List<Person> people =
         (from person in dc.People
          where person.Name == name
          select person).ToList();
}