Custom column names in Fluent NHibernate

In working on our web application, I came across an interesting issue with our Fluent NHibernate mapping files. On property mappings where we specified a column name (using the .Column("columnName") property), all of the other attributes that we applied to the property (length, nullability, lazyloading, etc) were ignored. This has been documented in here. I needed to move forward with this, so I wrote a simple extension method to handle our scenario.

This extension method is used in place of the Column("columnName") method call and doesn't replace the other values. Instead, it uses reflection to update the defaultColumn field's name value, just as the Length(), Nullable() and other methods do.

public static PropertyPart ColumnName(this PropertyPart part, string columnName)
        {
            const BindingFlags accessor = BindingFlags.NonPublic | BindingFlags.Instance;

            // retrieve the defaultColumn field property
            var defaultColumnField = 
                typeof(PropertyPart).GetField("defaultColumn", accessor);

            // get the current value of the field
            var defaultColumn = (ColumnMapping)defaultColumnField.GetValue(part);
            
            // set the column name as appropriate
            defaultColumn.Name = columnName;

            // reset the default column with the applied column name
            defaultColumnField.SetValue(part, defaultColumn);

            return part;
        }

I am still not sure why the Fluent guys decided to update the column name property the way that they did, but this seems to do the trick for us. As a result, I have replaced all Column() calls with ColumnName() calls, to prevent these unexpected behaviors in the future.

One risk with this solution, and it is considerable, is that I am binding to the internals of the PropertyPart class, which is subject to change out from under me.

Now, I just need to make sure I investigate the source before deploying a new build to ensure that this won't break my workaround.

What do you think?

The "ResourceKeySource" task failed unexpectedly on build server

I have been working to get one of our projects to build on our CruiseControl.net
server. This is a Winfoms app that uses several third party controls. I had been
unable to complete a build and was getting the following error during the build:

c:\WINDOWS\Microsoft.NET\Framework\v3.5\Microsoft.Common.targets (1805,7):

errorMSB4018: The "ResolveKeySource" task failed unexpectedly.

System.InvalidOperationException: Showing a modal dialog box or form when the application
is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification
or DefaultDesktopOnly style to display a notification from a service application.

at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)

at System.Windows.Forms.Form.ShowDialog()

at Microsoft.Build.Tasks.ResolveKeySource.ResolveManifestKey()

at Microsoft.Build.Tasks.ResolveKeySource.Execute()

at Microsoft.Build.BuildEngine.TaskEngine.ExecuteInstantiatedTask(EngineProxy engineProxy,
ItemBucket bucket, TaskExecutionMode howToExecuteTask, ITask task, Boolean&
taskResult)

I did some research on the message and found that MSBuild was trying to install
a key file for the current user and throwing up a dialog to prompt for a password.

I logged into the console of the build server and ran the install from MSBuild as
myself and got it to run. When prompted for the key file, I entered my password. Everything worked. But, when I tried to kick off a build from CruiseControl.net again, no dice. I knew there was an issue with that key file dialog. When I ran the build from the command line again, it did not prompt me for a password. So, all I needed to do was to get that key installed once and I thought I would be good.

Using the information in the article on run the command-line as the LocalSystem, I ran the following command from the command line

at [current time + 1 min] /interactive cmd.exe

This opened a new command window a minute later that was running as the LocalSystem account. Using this command window, I was able to run msbuild. I was prompted for the key password, which I entered. The build succeeded nicely. Once this was working, I kicked off a build of the project from CruiseControl.net and Bingo!, it worked.

NHibernate one-to-one mapping does an update on the parent when the child is created..

I have been implementing new functionality to an existing system using NHibernate. This application has an existing schema that we need to augment. In the current project I have an existing database that is used for transaction tracking. The main table for tracking transactions is Transaction. This table has an InsteadOf trigger on the table that prevents updates when the transaction has been cancelled or posted. I am adding functionality to the system to show the calculate the transaction sequence number for each transaction for the given account. This sequence is the incrementing count of all non-cancelled transactions, in date order. If a transaction within the sequence is cancelled, all transaction sequences are recalculated and the TransactionSequence table is updated with the results. The cancelled transaction will retain its sequence number, but the next sequence number will be decremented until there is a continuous sequencing of all non-cancelled transactions.

To implement this, I created a TransactionSequence table that has a primary key of TransactionId which is also a foreign key to the Transaction table. I then created a one-to-one mapping between these two entities in my mapping files.

The Transaction mapping file looks like this:

<class name="Transaction" dynamic-update="true">
 <id name="Id" column="ID">
     <generator class="native" />
 id>
 <property name="TransactionTypeId" access="field.camelcase-underscore" />
 <property name="TransactionStatusId" column="DebitDebitStatus" access="field.camelcase-underscore" />

 <one-to-one name="Sequence" class="TransactionSequence" fetch="join"
              lazy="false" constrained="false"> 
 </one-to-one>
class>

The TransactionSequence mapping looks like:

<class name="TransactionSequence" table="TransactionSequence" dynamic-update="true">
 <id name="TransactionId" column="TransactionID" type="Int32">
     <generator class="foreign">
         <param name="property">Transactionparam>
     generator>
 id>
 <version name="Version" column="Version" unsaved-value="-1" access="field.camelcase-underscore" />
    <property name="SequenceNumber" not-null="true" />
 <one-to-one name="Transaction"
             class="Transaction"
             constrained="true"
             foreign-key="fk_Transaction_Sequence" />

When I have calculate the transaction sequences, I do a save of the Transaction within the NHibernate session, but it fails when the transaction sequence does not yet exist because it tries to update the Transaction table with a query like 'UPDATE Transaction SET Id = ? WHERE Id = ?' with the same value for parameters 1 and 2.

How can I get NHibernate to create the child of the one-to-one mapping without updating the Transaction record? Has anyone seen this before?

GridView Sorting with the ObjectDataSource

In an attempt to move some of the data access and business objects out of the presentation layer of an inherited web application, I have started working with the ObjectDataSource and custom business objects. The other day when I was testing a grid, I kept hitting exceptions whenever I tried to sort the grid stating that the OnSorting event was not being properly overridden.

The reality was that the OnSorting was being overridden, but I was not canceling the sort event of the gridview. If I cancelled the sort, then nothing happened, but if I did not cancel the sort, then I hit an exception.

I found some information on custom sorting with the ObjectDataSource on from kubben at CodeProject. What I understood was that I needed to create a IComparer for my business object and use that within the select method that does the sorting.

My business object looks like this:

public class StatusLevel
{
private int _statusLevelID;
private string _statusLevelName;
private int _numberOfTransactions = 0;
private int _numberOfPurchaseOrders = 0;
private int _numberOfProducts = 0;

public int StatusLevelID
{
get { return _statusLevelID; }
set { _statusLevelID = value; }
}

public string StatusLevelName
{
get { return _statusLevelName; }
set { _statusLevelName = value; }
}

public int NumberOfTransactions
{
get { return _numberOfTransactions; }
set { _numberOfTransactions = value; }
}

public int NumberOfPurchaseOrders
{
get { return _numberOfPurchaseOrders; }
set { _numberOfPurchaseOrders = value; }
}

public int NumberOfProducts
{
get { return _numberOfProducts; }
set { _numberOfProducts = value; }
}
}

So, I created a comparer class that implements IComparer, like this:

public class StatusLevelComparer : IComparer<StatusLevel>
{
private string _sortColumn;
private bool _reverse;

internal StatusLevelComparer(string sortExpression)
{
_reverse = sortExpression.ToLowerInvariant().EndsWith(" desc");
if (_reverse)
{
_sortColumn = sortExpression.Substring(0, sortExpression.Length - 5);
}
else if (sortExpression.ToLowerInvariant().EndsWith(" asc"))
{
_sortColumn = sortExpression.Substring(0, sortExpression.Length - 4);
}
else
{
_sortColumn = sortExpression;
}
}

public int Compare(StatusLevel x, StatusLevel y)
{
int retVal = 0;
switch (_sortColumn)
{
case "StatusLevelID":
{
retVal = Compare(x.StatusLevelID, y.StatusLevelID);
break;
}
case "NumberOfTransactions":
{
retVal = Compare(x.NumberOfTransactions, y.NumberOfTransactions);
break;
}
case "NumberOfPurchaseOrders":
{
retVal = Compare(x.NumberOfPurchaseOrders, y.NumberOfPurchaseOrders);
break;
}
case "NumberOfProducts":
{
retVal = Compare(x.NumberOfProducts, y.NumberOfProducts);
break;
}
case "StatusLevelName":
{
retVal = string.Compare(x.StatusLevelName, y.StatusLevelName, StringComparison.InvariantCultureIgnoreCase);
break;
}
default:
{
retVal = Compare(x.StatusLevelID, y.StatusLevelID);
break;
}
}

return (retVal * (_reverse ? -1 : 1));
}

private static int Compare(int i1, int i2)
{
if (i1 > i2)
{
return 1;
}
if (i1 < i2)
{
return -1;
}
return 0;
}
}

According to all the documentation I read, all I would have to do now was the create a select method that took at parameter for the sortExpression and set the SortParameterName to the name of the parameter within the GridView. However, it still would not work.

After more research and testing, I realized that my Select method was a static method and I thought, well let's try. So, I changed my static select method to an instance method and suddenly everything worked. Apparently for the sort functionality to work, the select method that takes the sortExpression must be an instance method, and not a static method. Go figure.

ExecuteReader and the missing output parameters

Most of the time, I like to get some indicator of success back from the stored procedures that I write. To do this, I will add return parameters and sometimes, if I am doing parameter validation, will use output parameters to provide individual values that do not belong to the result set as a whole.

Recently, when building a business layer for an application I began testing the output returned from the output parameters and return value parameters. Since I was simply populating the objects from data in the database and caching the results within the application, I wanted to use the DataReader to retrieve results from the database. I did this because I know that the DataSet carries a lot of unnecessary weight around that wasn't needed in this solution.

So, with DataReader in hand, I began testing some of the data access code. I was rewarded with DBNull results when I tried to access the ReturnValue parameter value or any of my output parameter values.

I began by defining the parameters for the stored procedure (Using the Enterprise Library Data Access Block):

db.AddOutParameter(cmd, "Output", DbType.String, 10);
db.AddParameter(cmd, "Return_Value", DbType.Int32,
ParameterDirection.ReturnValue, string.Empty,
DataRowVersion.Current, 0);

This created an output parameter named "Output" and a return parameter named "Return_Value". Then, I ran the following code to retrieve the results and access the parameter values .

DbDataReader reader = null;
try
{
reader = db.ExecuteReader(cmd);
string output = (string)db.GetParameterValue(cmd, "Output");
int iResult = (int)db.GetParameterValue(cmd, "Return_Value");
if (iResult != 0)
{
// handle error in executing stored procedure 
}
else
{
while (reader.Read())
{
// do something with results rows
}
}
}
catch ( DbException ex )
{
// handle errors in executing stored procedure
}
catch ( Exception ex )
{
// handle errors in getting return values
}
finally
{
if ( reader != null ) reader.Close();
}

However, when I attempted to access the parameter values after executing the data reader, I would hit an InvalidCastException. I first looked at the stored procedure and made sure that the values were getting passed in correctly and ran SQL Profiler to assure that the stored procedure was executing properly. After validating, I checked the parameter values and found that they were not invalid types, but DBNull, which cannot be cast to any other value.

Trying to determine what was happening, I changed the default values of the parameters when I defined them, but would always get the default value after execution. Frustrated with this process, I decided to try using a DataSet to execute the query. When I did so, my parameter values "magically" appeared and everything worked as excepted.

I have not had time to delve into the System.Data.SqlClient code to see why those values are not set, but have determined that if I want to use output parameters and return value parameters, then I will have to pay the piper and use the DataSet, with all of its weight and ungainliness. What a pain. Has anyone else been able to get output parameters to work using the ExecuteReader method? Let me know, as I would love to drop the DataSet, since I don't need it.

Welcome

This is the start of something I have thought of doing for quite a while, as a resource for myself and for others pursuing this interesting art called development. This will primarily be related to web development using asp.net.

The posts that you will find here will run the gamut of technical issues, based on what I see and learn through experience. It will provide an archival location for myself so I can remember all of interesting methods and caveats on the development path.

It will likely include interesting tidbits about XML, JavaScript, AJAX, SQL Server, pretty much the entire range of products and technologies that I touch on a daily basis.

Some of the items I mention will be well documented elsewhere, but most will be things that were discovered through trial and experience.

By any means, I hope that this proves an interesting read and a good resource for those who visit. I hope that you enjoy the ride.