Wait a Minute -- Where is All the Code and Sql -- I Can't Find It
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 () ASHere's the total amount of "sql" that I'll have to create/generate/maintain for my O/R Mapper (includes filtering, sorting, and paging):
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;
<entity type="Demo.Category" table="Categories" keyMember="id" keyType="Auto">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:
<attribute member="id" field="CategoryId" alias="Id" />
<attribute member="name" field="CategoryName" alias="Name"/>
</entity>
namespace Demo {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.
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; }
}
}
}
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);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):
CategoryGrid.DataSource = manager.GetObjectSet(typeof(Category), String.Empty);
// .NET v2.0: CategoryGrid.DataSource = manager.GetObjectSet<Category>(String.Empty);
Category category = manager.GetObject(typeof(Category)) as 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):
// .NET v2.0: Category category = manager.GetObject<Category>();
category.Name = "Insert";
manager.PersistChanges(category);
Category category = manager.GetObject(typeof(Category), id) as 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):
// .NET v2.0: Category category = manager.GetObject<Category>(id);
category.Name = "Update";
manager.PersistChanges(category);
Category category = manager.GetObject(typeof(Category), id) as 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:
// .NET v2.0: Category category = manager.GetObject<Category>(id);
manager.MarkForDeletion(category);
manager.PersistChanges(category);
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.