Thursday, March 02, 2006

Tip: SqlHelper Debugging using IISReset

This is one of those things you always forget because you do it so infrequently. If you use Microsoft's SqlHelper (original not EntLib) to connect to SQL Server and call stored procedures, you should probably be aware that SqlHelper gets a list of your stored procedures parameters from SQL Server ultimately by calling SqlCommandBuilder.DeriveParameters(..). These parameter definitions get cached until your AppDomain is reloaded. The sticky bit here is that if you are changing the number of parameters on a stored procedure where its parameter definitions have already been cached, then you will see exceptions from SqlHelper that the parameter count doensn't the data you are passing in because your business tier code has already been updated to reflect the new parameter count. If you are in ASP.NET, just do an IISReset and save yourself time debugging whether you are connecting to the right database, if you really had updated the sproc, if SQL is finding a different sproc becauase its not qualified by the user that created it (e.g. SomeProc instead of dbo.SomeProc) like I just did. :)