Archive for June, 2007
I recently ran across this older post about Nine Things Developers Want More Than Money. I can relate to items 3, 4, 5, 7, 8, and 9. I’m fortunate to have most of those things in my work environment, except for #9. I’m working on a project that interfaces new work in VB.NET with existing work that was done in VB6 several years ago. I wrote most of the code on both ends, and I curse myself at least once a week for being so ignorant when I wrote the VB6 code several years ago. 🙂
I recently ran across Kate Gregory’s post on tracepoints. She’s a C# person, but this technique works in Visual Basic.NET, too.
I did have a problem getting the results to print in my Debug window, as Kate shows on her post. VB.NET has an option to send Debugging information to the Immediate Window, rather than to the Output window. Until I knew this, I was looking for the information in the wrong place.
To change this behavior, select Tools -> Options from the menu. Make sure “Show all settings is checked”. The Debugging/General node has an option, “Redirect all Output Window text to the Immediate Window.” I have turned this option off, as I never use the Immediate Window in VB.NET.
With Visual Studio 2005, the SQLParameterCollection got a new method, AddWithValue. This method is very handy when executing stored procedures. You need to be careful about using this with dynamic SQL and parameterized SELECT statements. You could end up with very poor performance on your dynamic SQL queries.
Suppose you have a flexible inquiry form that is generating dynamic SQL to find order lines that match an optional set of critera that the user has filled in on a form. The parameters are used to build a custom WHERE clause based on the criteria the user selected.
If the criteria in the database are dates or numbers, you may end up with a very inefficient query when you use the AddWithValue method to create the parameters. By default, the generated SQL will set these parameters to an nvarchar data type. When the SQL runs, the server is expected to convert the nvarchar parameter to the other data type. For numbers and dates, this will prevent the use of an index seek.
Instead, use the standard Add method, which allows you to define the data type of the parameter. Here’s a VB example:
cmd.Parameters.Add(“@OrderQuantity”, SqlDbType.Decimal).Value = myValue