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.
Published Thursday, June 30, 2005 10:12 PM by PaulWilson

Comments

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

Ok, from the "my LOC is smaller than your LOC" boasting department, with my O/R tool all I have to write is this in one file:

table demo:category {
fields {
int id int notnull readonly [the ID];
string name nvarchar(30) notnull readwrite [the name];
};
pkey sequenced id;
# your example had a get of all categories so I need to provide such a get:
get multi {called all};
};

I know it's not XML, I wrote this before it was obvious that XML would be THE way to do these kind of things. To this day I haven't been able to find an XML syntax that can express all the same things that isn't painfully verbose in places.

Anyway, equivalents to your C# code:

// #1
CategoryGrid.DataSource = Category.GetByAll();

// #2
Category cat = new Category();
cat.Name = "Insert";
cat.Update();

// #3
Category cat = Category.GetById(id);
cat.Name = "Update";
cat.Update();

(notice just as with your version, once you've done the create or get, the code is identical regardless of whether it's an insert or an update. This is nice in ASP.NET because you can do the get-or-create conditionally in your Page_Load and then just call .Update() in your _Click)

// #4
Category cat = Category.GetById(id);
cat.Delete();

// #5 (this one's less efficient, but easier to understand when reading it; also it's a corner case, at least in the kind of stuff I do)
foreach (Category cat in Category.GetByAll) {
cat.Delete();
}

By the way, I'm guessing from the fact that your XML doesn't contain enough information to create the table itself that you have to do that by hand? You should also include a CREATE TABLE statement in your "necessary lines of code". Mine creates the table (and indexes it based on what gets you specify, and puts in foreign keys etc) for me. And updates it when the definition file changes.

http://savannah.nongnu.org/projects/nrdo :)

Ok, the downside (and it's a fairly big one) is that the total set of users of nrdo is pretty much me and my company. The documentation is limited and naturally it only gets testing in a limited number of configurations. It also has some quirks and its error messages aren't exactly clear most of the time.

I just wish I could figure out how to convince people that it's actually kind of cool and it would be nice if they'd use it and contribute fixes to some of those problems...

Thursday, June 30, 2005 11:48 PM by Stuart Ballard

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

nrdo seems nice, but you loose a lot of apeal at the door steps by choosing GPL license...

Friday, July 01, 2005 2:34 PM by Srdjan

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

Note that the GPL only applies to the tools (in other words, if you fix or enhance the tools, I'd like the fixes back).

The runtime libraries are licensed under the LGPL which allows them to be linked into any application, under any license, without any obligation except to make the source to the library available - which I'm already doing for you. My own company uses nrdo in a proprietary application - there's no obligation to make the source of your app available.

Saturday, July 02, 2005 1:00 PM by Stuart Ballard

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

well, that is nice to know - thanks for the explanation...

p.s. Paul, sorry for digresing

Sunday, July 03, 2005 3:22 PM by Srdjan

Leave a Comment

(required) 
(required) 
(optional)
(required)