Why Use an Object Relational Mapper

I occasionally (still!) get in heated discussions with fellow developers on the finer points of writing -- or generating -- data access code versus using an O/RM. Usually this discussion is happening at a restaurant or some social gathering place (yes we socialize) where I'm not at a computer so, it's hard to answer the call of "Talk is cheap; show me the code!".

The last couple days I've been doing an initial refactoring of a wad of old code that needs some bugs fixed but is suffering from a serious case of code-rot. This code is a pretty straight forward -- if bare-bones -- version of a shopping cart, so everyone in e-commerce should be familiar with the concept. Well, if a picture is worth a thousand words, then this code should be worth at least that much.

This was found in the catalog ASPX code-behind where it needed, in order to display them, all of the items in a chosen category:

string qrySubCategory =
	"select distinct categoryId, subCategoryId, subCategory " +
	"from dbo.vw_fcCategoryItems " +
	"where categoryId = " + categoryId + " " +
	"order by subCategory, subCategoryId";
string qryItem =
	"select itm.itemId, itm.categoryId, itm.subCategoryId, itm.subCategory, itm.itemName, itm.itemShortDescription," +
	" itm.itemType, dbo.fn_fcItemIcon(itm.itemId) as iconUrl, itm.created, itm.updated " +
	"from dbo.vw_fcCategoryItems itm " +
	"where itm.categoryId = " + categoryId + " and deleted is null " +
	"order by itm.subCategory, itm.subCategoryId, itm.itemName";
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnStoreX"].ToString());
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try {
	cn.Open();
	da.SelectCommand = new SqlCommand(qrySubCategory, cn);
	da.Fill(ds, "subCategory");
	da.SelectCommand = new SqlCommand(qryItem, cn);
	da.Fill(ds, "item");
	ds.Relations.Add(new DataRelation("relSubCategoryId", ds.Tables["subCategory"].Columns["subCategoryId"], ds.Tables["item"].Columns["subCategoryId"]));
	gvSubCategory.DataSource = ds.Tables["subCategory"].DefaultView;
	gvSubCategory.DataBind();
}
finally 
{
    if (cn.State != ConnectionState.Closed) 
    { 
        cn.Close();
    }
}

For the sake of our sanity, we'll disregard the anti-patterns that even my adversaries in these debates wouldn't use. In fact, the code that I'm about to show you -- that was used to replace this -- will indeed look very similar to what their hand-crafted or generated code would look like.

So, why? Why write this code? Why generate and compile this code? We use generalized libraries every day so that we don't have to duplicate work. Why is this solved problem of persisting data to a relational store such a polarizing subject?

I'll leave these questions as a rhetorical giant pink elephant in the room as I bestow on you the code that replaced this mess:

Category cat = CategoryRepository.GetById(categoryId);
gvItem.DataSource = cat.Items;