ObjectWriter<T>
A recent project I worked on required moving CSLA business objects to and from a sql server database through stored procs (no adhoc queries were allowed). Overtime, and after a few refactoring sessions trying to normalize some of the CSLA property/field to stored proc parameter mapping code, a nice little reusable component emerged that allowed us to write code as follows:
/// <summary> /// /// </summary> public void Insert() { using (SqlConnection connection = new SqlConnection( ConfigurationManager.ConnectionStrings["simple_orm_unit_test"].ConnectionString)) { using (SqlCommand command = new SqlCommand()) { // init the command command.Connection = connection; command.CommandText = "dbo.uspCustomer_Insert"; command.CommandType = CommandType.StoredProcedure; // open the connection command.Connection.Open(); using (ObjectWriter<Customer> writer = new ObjectWriter<Customer>( new SqlServer.SqlServerFieldInfoMap<Customer>(), new SqlServer.SqlServerSprocDataParamBuilder<Customer>())) { writer.Insert(this, command); } } } } |
The ObjectWriter<Customer>.Insert method handles the binding between the parameters in the stored process and the fields of the Customer instance. The stored proc parameters are populated before the sproc is executed. After execution has completed, the values from any output parameters are moved back to the Customer instance. To facilitate this, two helper components are required, one (SqlServerFieldInfoMap) for identifying which field maps to which corresponding sproc parameter. The other component (SqlServerSprocDataParamBuilder) is responsible for building the IDBParameters and initializing them with the correct metadata (i.e. ParameterName, Direction, SqlDBType, Precision, Scale, etc…). The algorithms for these 2 components will vary from project to project, that is why I pulled them out to a separate interface. For the project I am currently working on, the FieldInfo can easily be resolved by peeling the ‘@’ from sproc parameter name and looking up the field. And that is precisely what the SqlServerFieldInfoMap below does. Additionally, since the database is SqlSever, the DeriveParameters method can be used to populate the Parameter metadata (disclaimer: there are performance implications of doing this). See the listing below for this class as well.
/// <summary> /// /// </summary> public class SqlServerFieldInfoMap<T> : IFieldInfoMap where T : class { #region IFieldInfoMap Members /// <summary> /// /// </summary> public SqlServerFieldInfoMap() { } /// <summary> /// /// </summary> /// <param name="alias"></param> /// <returns></returns> public FieldInfo this[string alias] { get { // trim off the parameter (this is sql sever specific) string normalizedAlias = alias.Replace("@", string.Empty); return typeof(T).GetField( normalizedAlias, BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase); } } #endregion } /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> public class SqlServerSprocDataParamBuilder<T> : IDataParamBuilder<T> { /// <summary> /// /// </summary> public SqlServerSprocDataParamBuilder() { } #region IDataParamBuilder<T> Members /// <summary> /// /// </summary> /// <param name="o"></param> /// <param name="command"></param> public void BuildParameters(T o, IDbCommand command) { SqlCommand sqlCommand = command as SqlCommand; if (sqlCommand == null) { throw new ArgumentException("Parameter must be a SqlCommand", "command"); } if (sqlCommand.CommandType != CommandType.StoredProcedure) { throw new ArgumentException("SqlCommand must by of CommandType.StoredProcedure", "command"); } SqlCommandBuilder.DeriveParameters(sqlCommand); } #endregion } |
Caveats
- If you system uses custom .Net class/structs for data types (i.e. PhoneNumber) you will have to add custom logic that converts the strings to PhoneNumbers (or have your data time implement IConvertable)
Here is the complete code listing for ObjectWriter<T>. You can download the code here to view the complete listing and a sample Customer implementation that hits northwind.
/// <summary> /// /// </summary> public class ObjectWriter<T> : IDisposable where T : class { /// <summary> /// /// </summary> private IDataParamBuilder<T> _parameterBuilder; /// <summary> /// /// </summary> private IFieldInfoMap _fieldMap; /// <summary> /// /// </summary> /// <param name="fieldInfoMap"></param> public ObjectWriter(IFieldInfoMap fieldMap, IDataParamBuilder<T> parameterBuilder) { this._fieldMap = fieldMap; this._parameterBuilder = parameterBuilder; } /// <summary> /// /// </summary> private IDataParamBuilder<T> ParamBuilder { get { return this._parameterBuilder; } } /// <summary> /// /// </summary> private IFieldInfoMap FieldInfoMap { get { return this._fieldMap; } } /// <summary> /// /// </summary> /// <param name="o"></param> /// <param name="command"></param> public void Insert(T o, IDbCommand command) { this.Execute(o, command); } /// <summary> /// /// </summary> /// <param name="o"></param> /// <param name="command"></param> public void Update(T o, IDbCommand command) { this.Execute(o, command); } /// <summary> /// /// </summary> /// <param name="o"></param> /// <param name="command"></param> /// <returns></returns> private void Execute(T o, IDbCommand command) { // populate any parameters of the command using the fields from the object this.ParamBuilder.BuildParameters(o, command); // move the values from the fields to the parameters foreach (IDataParameter parameter in command.Parameters) { if (parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput) { // find the field that maps to this parameter FieldInfo finfo = this.FieldInfoMap[parameter.ParameterName]; // set the parameters value to what the field currently contains // this is where you could include logic for // converting from your custom type parameter.Value = finfo.GetValue(o); } } // make sure we have an open connection if (command.Connection.State == ConnectionState.Closed) { command.Connection.Open(); } // execute the command command.ExecuteNonQuery(); // refresh any fields in the object from any output parameters // move the values from the fields to the parameters foreach (IDataParameter parameter in command.Parameters) { if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput) { // find the field that maps to this parameter FieldInfo finfo = this.FieldInfoMap[parameter.ParameterName]; // set the fields value to what the parameter currently contains // this is where you could include logic for // converting from your custom type finfo.SetValue(o, parameter.Value); } } } #region IDisposable Members /// <summary> /// /// </summary> public void Dispose() { } #endregion } |