Lack of NHibernate HQL support for question mark character in column name

If you’re faced with a database that contains reserved characters in some of its column names, NHibernate allows you to escape a column name in the mapping file by wrapping the column name in backticks, for example:

<class name="Foo">
   <property name="Bar" column="`Bar?`" />
</class>

This convention is used by NHibernate to generate SQL with the column name wrapped appropriately (e.g. [] for SQL Server). This works as intended for ICriteria queries and for HQL queries returning tuples in the following form:

session.CreateQuery("select Bar from Foo").List();

For SQL Server this results in the following SQL:

select foo0_.[Bar?] as col_0_0_
from Foo foo0_

The problem occurs if you use the entity selection form of the HQL query in conjunction with the generic List method:

session.CreateQuery("select foo from Foo foo").List<Foo>();

In this scenario, when constructing the SQL statement NHibernate interprets the question mark as a position parameter placeholder and substitutes the question mark with the parameter, i.e. the query winds up containing this column

foo0_.[Bar@p0]

instead of

foo0_.[Bar?]

Unfortunately this is a fault in NHibernate 3.1:

http://stackoverflow.com/questions/5805617/escaping-a-question-mark-character-in-a-column-name-in-nhibernate

Deploying an MVC .NET 4 application on IIS 7.5

I recently incorporated MVC 3 into an existing web application project.  In the process I upgraded the target framework to .NET 4.0.  I faced a number of issues on deploying the application to IIS 7.5.

The first problem was an Internal Server Error 500 HTTP response from the server.  The solution to this is to check the web.config file and remove the system.web/httpModules and system.web/httpHandlers sections.  For my application these were redundant and I was able to simply remove them.  If any modules and/or handlers are still needed by the application these should be moved to system.webServer/modules and system.webServer/handlers respectively.

The next error I faced was a Internal Server Error 500.21 mysteriously complaining that Handler PageHandlerFactory-Integrated has a bad module ManagedPipelineHandler in its module list.  It seems this was caused by ASP.NET 4 not being correctly registered with IIS 7.5.  The solution is to check that the version of .NET installed on the computer running IIS matches the version targeted by the application (on my computer this is v4.0.30319), and run the aspnet_regiis tool for the correct .NET version, e.g.:

%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i

You'll need to run this as an administrator.  You also need to check in IIS that the application pool used by the app is set to .NET 4.0 and Integrated managed pipeline.

If you're accessing a database and you haven't configured impersonation, you may need to set up a login for the ASP.NET identity. In ASP.NET 4 by default the application pool runs under the Windows account APPPOOL\[application pool name].  If you're using the default settings this will be APPPOOL\DefaultAppPool.  If you've upgraded to .NET 4, you'll have to create a login for each database your application accesses for the application pool Windows account.  (Alternatively if you have an existing login set up for the IIS 7 or 7.5 account you can change the application pool identity to this account.)

Having done these tasks I was able to run the application and log in, but as soon as I tried to access any MVC controller the server complained with a 404 response error.  After much googling I found this was fixed by applying hotfix KB980368.

This should now get you into the controller action.

One final error may crop up if you are restarting Cassini or IIS while testing the above, and this is related to any use of the Html.AntiForgeryToken method in views.  The default configuration is for a machine key to automatically generated: this will be invalidated each time IIS is restarted.  This is not a bug in the application: this is how it is supposed to work in production.  To clear this error you will need to clear out your application's cookies from your browser.  If you want to avoid this while testing - or in production if the ASP.NET process is being recycled (e.g. IIS restarts or if your application is being hosted in a web farm scenario) - you can hardwire a machine key by inserting a machine key section and either using a machine key generator tool to generate the machine key section e.g. aspnetresources.com/tools/machineKey or by manually creating a machine key section in system.web.

How to display a jQuery datepicker control in an ASP.NET MVC partial view

When trying to use the jQuery datepicker in an ASP.NET MVC partial view I found that the calendar didn't display correctly.  On investigating the problen I realized I was calling the datepicker function in the wrong place.  Calling it in the partial view doesn't work - the MVC framework ignores the script when it renders the partial view in the .aspx view.  Calling it in the .aspx view's document ready event doesn't work either because at this stage it doesn't know about the control.

Here's a way to get it working.

Firstly, link the jQuery UI CSS file in the head section of the master file:

<link type="text/css" href="../../Content/jquery-ui-1.8.9.custom.css" rel="stylesheet" />

Add the jQuery script references to the head section of the master file (or in the appropriate asp:Content section in the .aspx view in which you're posting the partial view):

<script type="text/javascript" src="../../Scripts/jquery-1.4.1.js"></script>
<script type="text/javascript" src="../../Scripts/jquery-ui-1.8.9.custom.min.js"></script>

Create a Javascript function that calls the datepicker function:

<script type="text/javascript">
    function showDatePicker() {
        $('#date').datepicker();
    }
</script>

Declare the input element in the partial view, making sure to give it the same id you used in the call to datepicker:

<input type="text" name="date" id="date" />

Finally (this is the crucial bit), you need to call this function in the OnSuccess parameter of the Ajax call in the .aspx view, e.g.:

<li><%= Ajax.ActionLink("Some text to click on", "action_name", new { Id = Model.Id }, new AjaxOptions { OnSuccess = "showDatePicker", UpdateTargetId = "div_name" })%></li>

Note that this doesn't work if you use the OnComplete parameter.

How to use a CTE to create a crosstab query

You can use a CTE with the ROW_NUMBER function to create a crosstab query. For example, say your data structure stores various types of event in a single table, but you need to report the various event dates in separate columns for each type of event. Here is how you might do this using a CTE joining on the output produced by ROW_NUMBER:

WITH eventTypeX AS
(
SELECT e.EventDate, e.JobID,
ROW_NUMBER() OVER(PARTITION BY e.JobID ORDER BY e.EventDate ASC) AS rn
FROM dbo.tblEvents
WHERE...
),
eventTypeY AS
(
SELECT e.EventDate, e.JobID,
ROW_NUMBER() OVER(PARTITION BY e.JobID ORDER BY e.EventDate ASC) AS rn
FROM dbo.tblEvents
WHERE...
)
SELECT j.JobID, j.JobDescription, e1x.EventDate AS FirstEventTypeX,
e2x.EventDate AS SecondEventType1, e1y.EventDate AS FirstEventTypeY,
e2y.EventDate AS SecondEventTypeY
FROM tblJobs j
LEFT OUTER JOIN eventTypeX e1x ON j.JobID = e1x.JobID AND e1x.rn = 1
LEFT OUTER JOIN eventTypeX e2x ON j.JobID = e2x.JobID AND e2x.rn = 2
LEFT OUTER JOIN eventTypeY e1y ON j.JobID = e1y.JobID AND e1y.rn = 1
LEFT OUTER JOIN eventTypeY e2y ON j.JobID = e2y.JobID AND e2y.rn = 2

How to schedule a program using Windows Task Scheduler

If a program is required to be run on a recurring schedule, a simple way of achieving this is to provide an executable than runs the command from its Main method. The task schedule can be managed using the Windows Task Scheduler.

To create a scheduled task on Windows Vista:

1. Open the Task Scheduler (Control Panel > Administrative Tools > Task Scheduler).
2. Click Create Task.
3. In the General tab, set the name of the task, and optionally provide a description. Click Change User or Group and select the user account under which the program should run. (This account should have the appropriate privileges.) Select Run Whether User Is Logged On or Not.
4. On the Triggers tab, click New. In the Settings section set the schedule. Optionally select Stop Task if It Runs Longer than. Click OK.
5. On the Actions tab, click New. Click Browse and select the .exe in the location selected in Setup.
6. Optionally, if the program requires network access, on the Conditions tab, select Start Only if the Following Network Connection Is Available and select the network connection to the required network connection. This will prevent the program from running in the event of the network connection being unavailable.
7. On the Settings tab, select Run Task as Soon as Possible After a Scheduled Start Is Missed and If the Task Fails Restart Every options.
8. Click OK.

How to use a CTE to select the first n rows of a referenced table for every row

The Common Table Expression (CTE) feature introduced in SQL Server 2005 provides some very useful querying extensions (albeit these are obviously not ANSI-92 compliant). A CTE takes the following form:

WITH cte AS
(
SELECT col1
FROM dbo.tbl1
WHERE...
)
SELECT col1
FROM dbo.cte
WHERE...
GO

One useful adjunct to the CTE is the ROW_NUMBER function in combination with the PARTITION BY clause. This allows you to write a query returning one or more rows of a subquery without resorting to complicated correlated subqueries and performance killing TOP n predicates. For example, say you want to pull for every row in a parent table the first row in a child table. You can write this query using a CTE as follows:

WITH firstChild AS
(
SELECT ParentID, ChildID,
ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY ChildID ASC) AS rn
FROM dbo.tblChildren c
)
SELECT p.ParentID, fc.ChildID
FROM dbo.tblParents p
INNER JOIN firstChild fc ON p.ParentID = fc.ParentID AND fc.rn = 1;
GO

The performance of this query is blindingly fast because ROW_NUMBER is being performed in memory and has no disk IO cost. You can also use this technique to generate high performance crosstab queries.

ADO/ODBC connection string for SQL Server 2008

If upgrading SQL Server from 2005 to 2008, one thing to note is that the provider name has changed for SQL Server 2008. The provider portion of the string in both ADO and ODBC connection strings should be either

Provider=SQLNCLI10

or

Provider=SQLNCLI10.1

depending on which version of SQL Native Client is installed.

http://blogs.msdn.com/b/mattm/archive/2007/06/07/sql-native-client-10-provider-name-change.aspx

http://connectionstringexamples.com/staticpages/index.php?page=20081229061112484

.NET applications will be unaffected as you do not need to provide the provider name in the connection string when using the .NET provider.