I wonder how common it is to add an extra syllable, like saying “nu-ku-ler” instead of “nu-clear”?
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
In a post from a long time ago, Eric Gunnerson asks about persisting complex objects. I’m working on a project that is using the CSLA.NET framework. One of the early decisions we made was how to get data updates back to the database. We chose to use Eric’s preferred method, which is change tracking. Eric also likes using serialization. That’s built into CSLA.NET (as well as .NET, of course), so we didn’t have to make a deliberate decision to use serialization.
One decision to make with persisting the objects is the actual mechanism for the updates. We ruled out using a dataset because we wanted to avoid the extra overhead that comes with a dataset. The examples from Rocky’s book all use stored procedures. The stored procedures have to have a parameter for every field. This means you are passing every field to the database, even when they did not change. Another option is dynamic SQL.
I had done some research in the past on the debate over stored procedures vs. dynamic SQL. One of the key issues in the debate is controlling access to the database. In some environments, dynamic SQL might mean that you have to give users access to all tables, and that might not be desirable. With an internal business application, the security can be handled through account impersonation in Enterprise Services or ASP.NET. Only one account needs to be given access to the database. In our environment, dynamic SQL seemed like a viable choice.
When profiling updates with “old-fashioned” ADO Recordsets, I observed that the recordsets generate dynamic SQL. The SQL was bloated, but reliable, and worked well with the optimistic concurrency model. I wanted to use this model, but in a more efficient manner.
In its simplest form, here is the format of a SQL statement that updates a record and also provides optimistic concurrency protection:
Set Field1 = NewValue
Where PrimaryKey = KeyValue And Field1 = OriginalValue
With our decision to use dynamic SQL inside the business object, the business object will already have most of the information needed to generate this statement. It has to know what table(s) are used to load the data. It has to know what columns are used to load the data, including the primary key value. It has to know the current value to update the database. The only optional piece is the original value. The original value is usually overwritten when the user makes changes.
One option to manage this is to double the number of variables. For each m_Field1, you would also have an m_orig_Field1, for example. This technique forces the business object developer to write two lines of code when setting each field value.
Since the update statement could be manufactured mechanically with all the necessary information, we felt there should be a way to encapsulate the necessary information. We have created “smart data” classes for the basic data types (string, integer, double, date, and boolean). Each smart data type knows the column name it is associated with, the original database value, and the current value. It turns out this is enough for efficient updates.
These smart data objects share a common interface. The interface defines three read-only properties: Column name, original value, and current value. A DataUpdater object uses this interface to generate the update statement.
Within the business object, the following lines of code are used to update a sample business object:
Dim updater As New DataUpdater(“MyTable”, m_KeyValue)
Within the updater, the SQL statement is generated and run against the database. As an added bonus, the smart data objects which have a concept of an “empty” value (i.e. string and date) also handle translating the empty value into a null value for the database.
The smart data objects are also easy to use when fetching the data. Before the smart data objects, fetch code looked like “m_Field1 = dr.GetString(“Field1”). dr refers to a SafeDataReader, which is part of the CSLA.NET framework. With smart data, the line is written as m_Field1 = New SmartString(dr, “Field1”).
The smart data object handles all the details of retrieving the value from the data reader and setting the original and current values.
We’ve added other capabilities to the smart data objects that help our Windows Forms classes, too. But that is outside of the persistence topic. These smart data objects are working very well for us. They do create more overhead, but nothing like a dataset. If you have a similar environment, perhaps you can benefit from this concept as well.
I just received the July Technet shipment. The shipment included a disc for Windows Server “Longhorn” Beta 2 (x86). The disc number is 0666. Is this a sign of what we can expect from installing the beta? 🙂
Paul Vick writes about an early experience with a legacy computer. He never owned a CoCo, but it inspired him to learn about them. Our family’s first computer was a 16K Coco. If Paul had gotten that computer, it came with a great book on learning Color Basic and Extended Color Basic. I spent hours and hours typing in code listings from those books and the Rainbow magazine.
Now, you can experience a CoCo through a web page (Java required).