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

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开发者_如何学Go to a neat example will also be appreciated.

Thanks.

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

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();
}