This post continues my “NCharlie” post series, in which I take Agile methods/technologies I picked up at a .Net Bootcamp in Austin back to work. My intended audience is my future self (reference) and my coworkers – we write custom business apps using .Net 2.0 webforms backed by SQL Server.
Currently our team maintains lots of SQL stored procedures per application. As our lead developer I would like to lead the team in a new direction that doesn’t include all these sprocs. The benefits of such a move are numerous and well-stated (edited, updated incorrect link) by people much smarter and more experienced than me. But for us, the immediate value would be:
1) Less code to maintain (yes, Transact-SQL is code).
2) Persistence ignorance, resulting in code that is more testable and easier to create, understand later, and fix if needed. An SQL stored procedure is a terrible place for business logic to reside (hard to test, hard to find)… removing the temptation to put it there is a hidden benefit.
3) Faster development cycles. We’ve built this CRUD over and over again so many times. Let’s be pragmatic and stop repeating ourselves. Even with code generators the CRUD procedures and data access layers are a painful part of our projects we shouldn’t be worrying about.
I could probably go on… instead I’ll jump into an example. You can find this type of example code in many other places, I’m not reinventing anything here. Hopefully this side by side format will help someone else out there move on from writing the type of data access code I’ve been writing for too many years now.
First up let’s knock the “C” off the classic ADO.Net CRUD. “C” is for create, and I want to create a record in my database that corresponds to an entity class in my application I’ve populated with data. The entity may have been populated by taking a user’s inputs on a web form and mapping them to the entity’s properties… it doesn’t matter where I got the data – I’m concentrating now on persisting it to the datastore. Here are the steps involved in such a task:
1) Write a stored procedure that takes input parameters corresponding to the entity properties I want to save, executes an INSERT statement against the proper table, and returns an identifier for the new record. (note: we use a code generator against a database table to create this procedure)
CREATE PROCEDURE [dbo].[InsertTask]
@Description varchar(500),
@Submitted datetime,
@SubmittedBy varchar(50),
@Closed datetime,
@ClosedBy varchar(50),
@Priority int,
@AssignedTo varchar(50)
AS
INSERT INTO [dbo].[wdtasks_Tasks] (
[Description],
[Submitted],
[SubmittedBy],
[Closed],
[ClosedBy],
[Priority],
[AssignedTo]
) VALUES (
@Description,
@Submitted,
@SubmittedBy,
@Closed,
@ClosedBy,
@Priority,
@AssignedTo
)
SELECT SCOPE_IDENTITY()
2) We use a data helper class (cleverly named DataHelper) to instantiate a connection object and an ADO.Net command object. Then we build up the input parameters and execute a stored procedure.
string cn = ConfigurationManager.AppSettings.Get("dbConnection_Tasks");SqlParameter[] aParams = new SqlParameter[7];
aParams[0] = new SqlParameter("@Description", SqlDbType.VarChar);
aParams[0].Value = Null.GetNull(oEntity.Description);
aParams[1] = new SqlParameter("@Submitted", SqlDbType.DateTime);
aParams[1].Value = Null.GetNull(oEntity.Submitted);
aParams[2] = new SqlParameter("@SubmittedBy", SqlDbType.VarChar);
aParams[2].Value = Null.GetNull(oEntity.SubmittedBy);
aParams[3] = new SqlParameter("@Closed", SqlDbType.DateTime);
aParams[3].Value = Null.GetNull(oEntity.Closed);
aParams[4] = new SqlParameter("@ClosedBy", SqlDbType.VarChar);
aParams[4].Value = Null.GetNull(oEntity.ClosedBy);
aParams[5] = new SqlParameter("@Priority", SqlDbType.Int);
aParams[5].Value = Null.GetNull(oEntity.Priority);
aParams[6] = new SqlParameter("@AssignedTo", SqlDbType.VarChar);
aParams[6].Value = Null.GetNull(oEntity.AssignedTo);
return int.Parse(DataHelper.ExecuteProcedureScalar(cn, "InsertTask", aParams).ToString());
After executing the command, we receive a scalar value (the new record identifier) in return. Then we dispose of the command object and close the connection. (note: our DataHelper class handles the repetitive code here too.)
Wow, that wasn’t so bad huh? Code generation and that DataHelper really helps! It is a good thing it does too, because those 5 steps have to be repeated over, and over, ad nauseam, for ALL the entities that need to be persisted to the database.
Object Relational Mapping to the rescue… let’s perform that same task (saving our entity to the database) using NHibernate:
1) Create an XML mapping file for the entity.
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NCharlie.Core"
namespace="NCharlie.Core.Domain.Model">
<class name="Task">
<id name="ID" column="ID" type="Guid">
<generator class="guid.comb"/>
</id>
<property name="Description" />
<bag name="Tags" access="field.camelcase-underscore" cascade="all-delete-orphan" >
<key column="Task_ID" />
<one-to-many class="Tag" />
</bag>
</class>
</hibernate-mapping>
2) Call the “SaveOrUpdate” method.
using (ISession session = GetSession())
using (ITransaction tx = session.BeginTransaction())
{
session.SaveOrUpdate(entity);
tx.Commit();
}
Some of you may be looking at that mapping file and thinking I bumped my head… kind of seems like I’m introducing more complexity if you are used to plain old SQL. And I am. But it is a good tradeoff… because once you get that mapping file done, you also get the RUD parts of CRUD for free, as well as a ton of other NHibernate features (including custom queries, paging…) The other methods (to round out the CRUD feature) are “List”, “Get” and “Delete”.
The code in step 2 above, the code using the NHibernate ORM, is actually really nice and simple to use once you get used to it. You can see it in action in the NCharlie example project… and if you work with me, you’ll see it in the near future in the prototype stage of one of our next projects :)
Posts
