c# ado.net : best way to map database fields to property

Question

Using VS2005, .net 2.0 , C#

Hi all,

What is the best way to map stored proc columns to the c# object properties with out creating tight coupling.

For example, I dont like to do the following

DataRow row = Getmyrows();
MyObject.MyProperty1 = row["col1"];
MyObject.MyProperty2 = row["col2"];

So, when the column in stored proc gets changed to colxyz then the binary code will break. What is the best practice to address this. A code sample would be helpful and thank you in advance.

Answer 1

I would look into OR mappers. LINQ to SQL, nHibernate, Entity Framework, LLBLGen, etc. These allow you to configure your mapping via XML or some other external configuration source. Most of them also provide a way to completely decouple your entities from the persistence framework, allowing your entities to be POCO (Plain Old CLR Objects). Another benefit of OR mappers is they generate SQL for you on the fly, which allows you to largely eliminate your stored proc layer, which is also a coupling that can cause problems (on both ends...in your code as well as in your DB schema.)

Answer 2

Best solution is to use a proper O/RM like NHibernate, or if you can settle for "less" Linq to SQL or Entity Framework.

However if you must, I suggest using IDataReader/SqlDataReader instead (simplest, best performance), but you won't get away with having to map to column names if you want to do it the "hard way".

Answer 3

Couple approaches:

1. If you're forced to stick w/ ADO.NET proper, use a strongly typed dataset. All that mapping between objects and data structures is down in a schema where it belongs. Then you'd be able to hydrate your objects w/ code like this:

   MyObject.MyProperty1 = dataSet.TableName.PropertyName

2. I noticed everyone else said the same thing I was going to ;-) Go w/ an ORM. I know premature optimization is a slippery slope, but you inevitably will find clear justification for going that route. You won't regret it as your requirements become more complex, and you'll be learning a valuable skillset that's clearly gaining a lot of momentum in the .NET space.

Answer 4

If you have to do it that way, and dont want to use ORM, store the column names mapping in an xml file.

<appSettings>
    <key="prop1" value="col1">
</appSettings>

then in the code do something like:

myObject.Prop1 = ConfigurationManager.AppSettings["prop1"].Value

I know its clunky, and involves reading from xml (or the config file) for each property but it will work.

Answer 5

An approach that coworkers and I used back in the 2.0 days was to create a custom attribute which we used to specify the field name from the data table, and tagged our objects' properties with it. then built a generic entity builder that would take a datareader as a parameter ( EntityBuilder(IDataReader rdr) ); as it worked through the datareader, it would create an empty T, reflect on the class, go through the properties to get the custom attribute information and the type, and set the value based on that.

also had another custom attribute that specified the parameter used in our Insert and Update SPROCs to automatically populate the parameters, too.