June 2005 - Posts

Thursday, June 30, 2005 10:12 PM

Wait a Minute -- Where is All the Code and Sql -- I Can't Find It

I get an interesting question sometimes when someone downloads my WilsonORMapper demo -- they want to know "Where is all the code and sql?".  Now this is exactly one of the main points of using an O/R Mapper -- that being that there really is very little code, and even less sql!  Another common statement that I hear is that "I'm doing just fine right now" or "I don't have any problems when it comes to data access".  Of course you don't have any "problems" -- you just have lots of repetitious code and boring sql, and new features require a lot more of it.  Yea, yea -- you've heard it all before -- but once again I ask if you have ever REALLY looked at the small amount of code needed with O/R Mapping?  So let's look at a real example -- lets consider a MS Sql table named Categories with fields (CategoryId INT, CategoryName VARCHAR(50)).

Here's the bare minimum amount of sql you'll have to create/generate/maintain (you'll need more procs for filtering, sorting, and paging):
CREATE PROCEDURE RetrieveCategoryList () AS
    SELECT CategoryId, CategoryName FROM Categories;

CREATE PROCEDURE RetrieveCategoryById (@CategoryId INT) AS
    SELECT CategoryId, CategoryName FROM Categories WHERE CategoryId = @CategoryId;

CREATE PROCEDURE InsertCategory (@CategoryName VARCHAR(50)) AS
    INSERT INTO Categories (CategoryName) VALUES (@CategoryName);
    SELECT CategoryId = SCOPE_IDENTITY();

CREATE PROCEDURE UpdateCategory (@CategoryId INT, @CategoryName VARCHAR(50)) AS
    UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryId = @CategoryId;

CREATE PROCEDURE DeleteCategory (@CategoryId INT) AS
    DELETE FROM Categories WHERE CategoryId = @CategoryId;
Here's the total amount of "sql" that I'll have to create/generate/maintain for my O/R Mapper (includes filtering, sorting, and paging):
<entity type="Demo.Category" table="Categories" keyMember="id" keyType="Auto">
    <attribute member="id" field="CategoryId" alias="Id" />
    <attribute member="name" field="CategoryName" alias="Name"/>
</entity>
Now I'll also have to create/generate/maintain my business object, but you will to (and much more) unless you're using DataSets here:
namespace Demo {
    public class Category {
        private int id;
        private string name;

        public int Id {
            get { return this.id; }
        }

        public string Name {
            get { return this.name; }
            set { this.name = value; }
        }
    }
}
By the way, there's nothing inherently wrong with DataSets, but I would like to point out that I still have no more code in that case.  And the more features you need (filtering, sorting, paging) the more you have to continue to create/generate/maintain -- but I am done!  But wait, there's more -- we still need to look at how much code is required to actually use this O/R Mapping business class and mapping.  I'll assume you know what your own ADO.NET code or DAL code will look like, and of course you'll also have a lot of code that fills your business objects with the data your return, and you'll also have a lot of code to set the various parameters for the persistence later.  Oh yea, and don't forget that the more features you've had to implement, the more cases of setting up those stored procs there will be.

In the meantime, here's the code that's required to initialize my O/R Mapper and to get all the Categories (and bind them to your grid):
ObjectSpace manager = new ObjectSpace(mappingFile, connectString, Provider.MsSql);
CategoryGrid.DataSource = manager.GetObjectSet(typeof(Category), String.Empty);
// .NET v2.0: CategoryGrid.DataSource = manager.GetObjectSet<Category>(String.Empty);
Now here's all the code that's required to create a new Category and to insert it into the database (initialization has already happened):
Category category = manager.GetObject(typeof(Category)) as Category;
// .NET v2.0: Category category = manager.GetObject<Category>();
category.Name = "Insert";
manager.PersistChanges(category);
Here's the code that's required to retrieve an existing Category and to update it into the database (it should look very similar I hope):
Category category = manager.GetObject(typeof(Category), id) as Category;
// .NET v2.0: Category category = manager.GetObject<Category>(id);
category.Name = "Update";
manager.PersistChanges(category);
And here's the code that's required to retrieve an existing category and to delete it from the database (still pretty similar I think):
Category category = manager.GetObject(typeof(Category), id) as Category;
// .NET v2.0: Category category = manager.GetObject<Category>(id);
manager.MarkForDeletion(category);
manager.PersistChanges(category);
To make it interesting, what code would I need to write if I decided I wanted to delete all of my categories in one batch statement:
manager.ExecuteDelete(typeof(Category). String.Empty);
Note that this really will be a batch statement -- there will NOT be a bunch of objects loaded and then a bunch of individual deletes!  I should also point out that I have so far only talked about a single type of object -- but what if you need to look at object graphs? Once you starting adding related objects, your sql and business objects and ADO.NET or DAL usage can get an order of complexity greater.  But all I have to do is to do the same type of mapping, and also add an additional xml declaration to describe the relationships needed.  Then my related objects will be loaded (or lazy-loaded) automatically, and I can also easily persist an entire object graph at one time.  So download it and REALLY try the demos to see it for yourself, either with one of the provided demo/example apps, or with your own app.

Note that most everything I've said will also be similar with other O/R Mappers should you prefer -- so find the one that's best for you.
Posted by PaulWilson | 4 comment(s)
Thursday, June 30, 2005 10:00 AM

Sam Smoot discusses DataSets and O/R Mappers (and Fruit)

Sam Smoot discusses DataSets and O/R Mappers -- excellent post -- and it mentions fruit too!
Posted by PaulWilson | 3 comment(s)
Wednesday, June 29, 2005 8:28 AM

Does anyone really know what they are missing until they try it?

A few of my friends were recently talking about their favorite fruits when another friend walked up and said he avoided all fruits.  His rationale was that fruits were bad for you, and don't taste good anyhow, so we would all do well to stop eating these fruits.  Of course we told him he was very wrong and that fruits are not only very tasty, but also very healthy and part of a balanced diet.  It turns out that this fellow had a bad experience once a very long time ago with some fruit that caused him to get a little sick.  Now he is convinced, without a doubt, that all fruit must therefore be very bad for you, not to mention that all fruits taste bad.  I thought it would be easy to point out all the scientific evidence that claims to prove fruit is actually very healthy for you, but his personal "proof" was that he was very healthy without fruit, so even if it wasn't unhealthy it also wasn't of any value.

Now if you knew this fellow you would get a good laugh since he's extremely overweight and out of shape from eating lots of junk, but so far he hasn't had to go to the doctor or hospital with any major problems -- but we know its probably just matter of time.  So we next tried to encourage him to just try some fruits for himself, since this would prove quite well how tasty they really are, and then given time he would also come to realize that they were quite good for him too -- but he's convinced he knows far better.  Its easy to just leave him be, but we also really believe this fellow is in very bad shape, even if he doesn't realize it himself -- so what should we do?  We can't make him try some fruit, but I suppose we can continue to give him evidence fruit is healthy, and wouldn't we be very poor friends if we let him continue in his unhealthy ways, especially since he has a family to care for.

P.S.  The subject of this story was changed to protect the innocent -- the real story was about o/R Mappers and business objects.
Posted by PaulWilson | 9 comment(s)
Monday, June 27, 2005 4:05 PM

What do you look for in O/R Mappers?

Jason Mauss is asking for your feedback on what you look for in O/R Mappers since he'll be working on a How To Select Guide very soon.
Posted by PaulWilson | with no comments
Monday, June 20, 2005 3:37 PM

Craig Shoemaker's Polymorphic Podcast

Check out Craig Shoemaker's Polymorphic Podcast.  He's had a lot of really good shows, including several about O/R Mapping.  Yours truly is featured on his most recent one -- and while I sound better this time, I'm also facing reality that I just sound terrible no matter what.  That's hard to accept since I used to do a lot of singing, but I guess speaking somehow isn't the same.  :)
Posted by PaulWilson | 6 comment(s)
Thursday, June 02, 2005 10:08 PM

The Data Mapping Application Block

Lenny Fenster has released a preview version of the Data Mapping Application Block.  It uses the Enterprise Library, although a modified Data Access Application Block.  My understanding is that this will be rolled into the Enterprise Library sometime.  I'd heard rumors of this being worked on for quite some time, so it was no surprise.  I suppose you could think of it as the Enterprise Library's O/R Mapper -- kind of.

What do I mean by "kind of"?  There are no objects here, unless you like typed datasets, and you're required to use stored procs, which is great if it were just an option.  But if you like to use your own objects and/or want to avoid the bloat of datasets and/or enjoy the flexibility and maintainability of runtime generated dynamic sql, then you won't find what you're looking for in the Data Mapping Application Block.

Of couse, if you follow all of Microsoft's "guidance", and are already using the Enterprise Library, then this is probably exactly what you have been dreaming about.
Posted by PaulWilson | 10 comment(s)
More Posts