Tips for returning IDENTITY values from INSERT

There’s a good article by Scott Guthrie here that describes the basics. Just scroll down to Tutorial 5 for the INSERT specific bit – it is pretty straightforward and there is no point my repeating it here.

One point to note, however, and why I wrote this particular post. The key step in getting this working is changing the query type from NonQuery to Scalar. But this can get reset by Visual Studio if you choose the Configure option on the ObjectDataSource’s smart tags to modify the query in any way. If you don’t notice this, then suddenly the identity value stops getting returned and your code breaks.

The second tip is that by default the type returned by the insert query is decimal. This can cause some extra casting in your code if you were expecting it to be int. To fix this, just modify the SQL in Scott’s original article to be this:

SELECT CAST (SCOPE_IDENTITY() AS INT)

Determining row counts for all tables in a database

Here is a usful snippet if you want a way to get the number of rows in each table in your database. I use this in an admin-only page of the web application to provide some at-a-glance statistics. It is also really useful in unit tests for checking the correctness of business logic that may create new entries in several tables in one transaction.

Continue reading

Don’t believe NUnit

Just a quick note about a little problem I found using Nunit (2.2.9, but affects earlier revisions too).

After a bunch of edits to my project, I suddenly started getting messages from NUnit GUI “could not load file or assembly nunit.core”. Try as I could, this would not go away. This included everything from reinstalling NUnit to tracking DLL loading via SysInternal’s Process Explorer tool.
My project is non trivial – a top level solution containing an ASP2 web project, a couple of library DLL’s for the business logic, a unit test executable, and a testing helper library to help cross reference test results against direct SQL calls. As I’ll be writing about in another post shortly, incorporating unit testing into an ASP project is a pain in the rear and so I had been restructuring things. My natural assumption based on the error message was I had messed up something in the linking/referencing.

But it turned out to be an error in the application config file – for some reason NUnit reports this as a DLL missing, not that you have mistyped a bit of config text. I simply had a closing tag in the wrong place – still syntactically correct, however.
This link is by someone else who hit the exact same problem, and he has written about in more detail.

So remember folks: never assume a system generates the correct error message for all circumstances.

Adding ASP Role tables to your own database

This is still work in progress, but I want to incorporate the user role management tables within my own database, as the concept of users and their distinct roles ties right in with the core business model of the new site.
The following link explains the standalone tool to create the tables:
ASP.NET SQL Server Registration Tool

Invoking the exe runs a wizard requesting the database service – it needs to match the connection string as mentioned in the gotcha above. Since no parameters are specified, it then creates a database “aspnetdb” with all the role tables. This is not quite what I want, as I need these added to my application database.

The answer is to use the command line:
aspnet_regsql -S -E -A all -d

If you run SQL Management Server Studio Express before and after – not forgetting to hit ‘refresh’ – then a bunch of new tables are added: aspnet_Applications, aspnet_Roles, aspnet_SchemaVersions, aspnet_Users, aspnet_UsersInRoles etc. Now it is just a matter of hooking those up with the application tables.

Since I wanted membership and roles as well as basic authentication I used the “-A all” option to add everything in. Of course I get profile and web-part support too with this, but I may choose to exploit such features in the future.

Gotcha on setting up a data connection

This one gets me every time. When you set up a new data connection to a database running on a local copy of Sql Server Express, in the “Add Connection” dialog you need to prefix your machine name to the defaulted value of “SQLEXPRESS”. E.g. if the network name of your PC is called “devpc”, then the connection string is:

  • DEVPC\SQLEXPRESS

While this is the obvious choice and works for many circumstances, a subtle variation is to use the following instead:

  • localhost\SQLEXPRESS

This now points the connection to an instance of SQL Server Express running on your local machine. If working in a team where each developer needs their own local copy of a database, then setting this in the web.config file can be a real help.