Monday, March 13, 2006

Getting Ctrl-Shift-F2 back in Visual Studio .Net

One feature I used a lot while working in Visual Basic 6 was the ability to press Shift-F2 on the name of a routine and instantly jump to that routine's code to investigate something about that routine. Once finished, I could press Ctrl-Shift-F2 and jump back to the calling program where I left off and continue on with my previous task. Very productive.

In Visual Studio.Net, under the default keyboard mappings, Shift-F2 is replaced by F12. This is an improvement since I don't have to press the Shift key to go to a code routine's definition. But the equivalent for the Ctrl-Shift-F2 to jump back to the original source code location is nowhere to be found.

In Visual Studio.Net terms, the equivalent for the VB6 Shift-F2 functionality is Edit.GoToDefinition and the equivalent for Ctrl-Shift-F2 is View.NavigateBackward. I used the Visual Studio.Net keyboard customization options to assign these functions to F12 and Shift-F12 respectively. Now I once-again have the ability to investigate a routine, jump back to where I left off, and the keystrokes are more convenient and intuitive than before. I'm diggin' it.


Hope you found this helpful.

Joe.

Tuesday, March 07, 2006

Dealing with Concurrency issues in ASP.Net

When working on ASP.Net systems where multiple users could potentially change the same information in a database such as SQL Server, there is a real concern that one user could make a change and then another user could erase that change by updating the record with a slightly older copy that they had been working on for a while.

Handling this issue efficiently is essential to writing larger systems in .Net so I have done some research and my findings for at least a partial solution appear below.

While there are many reasons why ADO.Net is a huge improvement, it does introduce one very major problem that we must solve before we can use .Net to write larger database systems. The issue is in managing concurrency, i.e., preventing multiple users from overwriting each others' changes when sharing a table in the database. The email explains how the problem can occur and how I have arrived at what I believe is the best possible solution based on extensive reading and testing of many suggested solutions.


The Problem
ADO.Net uses a disconnected data access model, i.e, a connection is made to the database only long enough to perform a specific action and then the connection is immediately closed. This approach is the opposite of how Visual Basic 6 typically worked, which opened a connection to the database and kept it open as long as the data would be needed.

ADO.Net's disconnected data model provides superior performance and scalability since "expensive" database connections are used very sparingly and can be easily shared among applications. From an application developer's perspective, the biggest disadvantage is the inability to lock a record for updates. This means that ADO.Net developers must explicitly manage concurrency issues. Reading a record, allowing the user to change it, then updating the record without any further checking can easily overwrite someone else's changes.


Possible Solutions
Two possible solutions are generally discussed on managing database concurrency; Pessimistic Locking vs. Optimistic Locking. Pessimistic locking means locking the record from the time that the data is read until the time that the update is completed. Optimistic Locking means re-establishing the connection to the database to perform the update and checking to make sure that the record has not changed since it was read.

While Pessimistic Locking is possible in ADO.Net, it is not recommended. It prevents any other access to the record while it is locked and maintains lengthy database connections, resulting in reduced performance in the database.

Optimistic Locking is the generally recommended solution but a reliable method must be determined for ensuring that the record has not changed prior to applying the update or deleting the record.


Implementing Optimistic Locking
The challenge in Optimistic Locking is to have a reliable method to ensure that the table row has not changed before being updated or deleted, even when there is heavy usage of the table. This challenge is even greater when there is a Master-Detail relationship that must ensure integrity across multiple tables.

The first requirement is to have a field in the table row that automatically changes when the record is created or updated.

If you need to know when the change was made and by whom, then you must add a varchar field for the username, a datetime field for the update time, and write a database trigger for the table that automatically updates these fields at each SQL Insert or Update. This introduces the possibility of application errors due to an incorrect or missing trigger. This is not enough reason to avoid this triggers, but care must be taken to make sure the triggers are properly implemented and maintained.

If it is not critical that you know when the change was made or by whom, but are just interested in managing the concurrency issues, then using a Timestamp column on the table is the preferred method. The Timestamp data type is a bit mis-named since it does not contain any date or time, but instead is just a 64-bit integer that is incremented each time that any change is made to any table in the database. Thus it serves as a reliable unique indicator that a record has not changed, but the number itself has no meaning.

The Timestamp column can appear in a Select query like any other column but cannot be directly updated. When displayed by SQL Query Analyzer, it may show a value like Ox000023 where the "23" is actually a hex number, i.e., decimal value 35. A row can be selected by its Timestamp value if you use the decimal value, for example, "Select * from MyTable where MyTimestampField = 35" is a valid query.

To implement Optimistic Locking, the Timestamp value must be returned with the data (as data type UInt64) for each row in the query. That Timestamp can then be used as the single condition in the WHERE condition of the UPDATE statement to ensure that the unchanged row is being updated. To use a UInt64 value as a string in a WHERE condition, it must be converted with a custom function such as GetSQLTimestamp function (shown at the end of this posting).

If the UPDATE command returns 0 rows affected, then the record was changed by someone else during the time that the application had it and the update is invalid. Typically you would notify the user that the data had changed, re-query and re-display the data, and ask the user to make the change again with the new data and re-attempt the update. This process is repeated until an update succeeds or the user declines to try again.

For single table updates, using the GetSQLTimestamp function is the easiest way to manage optimistic concurrency as long as you ensure that your UPDATE SQL command always uses a "WHERE tsTimestamp = " condition and you verify that one and only one row changed.

To enforce optimistic concurrency on updates against multiple tables, use of a stored procedure is required so that a transaction can span the updates against all the tables, and succeed in making any change only if ALL the timestamp values are a match. I'm still working on the easiest solution for that, I'll post more info when I finish. Comments/Suggestions are welcome.


Here is the function that gives you the string value of a timestamp field that can used in the WHERE condition. Note that you can pass the datarow field in directly, no CAST or CTYPE is needed first.



Public Function GetSQLTimestamp(ByVal tsTimestamp As Object) As String
Dim oTimestamp() As Byte = CType(tsTimestamp, System.Byte())
Dim strTimestamp As String = "&H"
For i As Integer = 0 To oTimestamp.GetUpperBound(0)
Dim strHex2Dig As String = Hex(oTimestamp(i)).PadLeft(2, "0"c)
strTimestamp += strHex2Dig
Next i
Return Val(strTimestamp).ToString
End Function


I hope you find this helpful.

Joe

.Net Certification Study Group

Our local user group, GLUGnet (www.glugnet.org) is looking to set up a certification study group. If you, or anyone you know, has been involved in setting up or participating in a certification study group, I would love to hear any tips or tricks that you can share to help make it sucessful. Just comment this post so everyone can see it. Thanks.

Great free DHTML pop-up calendar for ASP.NET

A current project requires the ASP.Net web page to prompt for a Start Date and an End Date.

To make data entry easier for the user, I wanted to provide a small calendar icon that pops-up a monthly calendar which can be used to fill the ASP:textbox field that will be used to hold date. I found many commercial solutions but I was looking for something for free.

The requirements of the calendar are:
1. A small pop-up that does not overwhelm the screen
2. Ability to move prev/next year as well as prev/next month
3. Does not shift around the contents of the page when it appears
4. Honors the date in the text field, i.e., changing the date field then changes the default date for which the calendar appears.
5. Available at no charge.

I have found just such a control and it works GREAT!. The link to this great work of Javascript is Dynamic HTML Lab Pop-up Calendar 1.2.

The only change I made was to modify the javascript to default to Sunday as the first day of the week, to default the date format to mm/dd/yyyy, and to allow javascript and all its images to exist in a sub-folder to avoid cluttering up the application's main folder. Feel free to email me at joekunk at gmail.com if you wish to get a copy of the sample Calendar.aspx page that I used to ensure it was all working properly.

I hope you find this helpful.

Joe