Tuesday, November 28, 2006

Project must be startup project for Create Instance to be available for Object Test Bench in Visual Studio 2005

I am trying to do a better job of testing (and fixing) my code as I write it, and a big part of that is using a great new feature of Visual Studio 2005 called Object Test Bench which allows you to easily create and call objects on the fly and essentially test them immediately after writing them, without having to create any test projects or forms or classes as drivers.

When you open up the Object Test Bench window, it tells you to right click the class in the Class View window and choose "Create Instance" to test it. Except that option was not on the right click menu for any of my classes. Everything looked right, and I was at a loss to explain it. Finally I found a statement on MSDN that the project has to be the startup project in order for the Create Instance option to be available.

I set the project as Startup Project and it worked perfectly. Once again, the answer is simple and obvious once you know it.

Hope you find this information useful.

Joe Kunk

Tuesday, August 01, 2006

Awesome presentation by Joe Stagner

On July 20th, the Greater Lansing User Group for .Net (www.glugnet.org) was honored to host Joe Stagner as speaker. Joe is a program manager for ASP.Net at Microsoft. Joe spent 90 minutes demonstrating the capabilities of Microsoft's Atlas, their implementation of Ajax. It was very impressive to see what this technology can do in terms of providing a much richer and more responsive user experience in ASP.Net web pages.

After the meeting, I heard several user group members say that it was among the best presentation that they had ever atttended at GLUGnet. Another member told me that he went back to the office the next day and implemented Atlas in his current ASP.Net project and was able to demonstrate a much nicer interface to his supervisor by the end of the same day. Great stuff!

I want to extend a very sincere "Thank You!" to Drew Robbins, the Microsoft Developer Evangelist that was instrumental in making Joe Stagner available to GLUGnet and to other Michigan user groups that week.

Needless to say, if you ever get the chance to hear Joe Stagner speak at a user group or conference, don't miss it!

Joe Kunk

Sunday, July 16, 2006

How to create a single-user ACT! 2006 database with the ACT! SDK

Below is the code that I developed to create a new ACT! 2006 Premium database on a Windows XP machine via the ACT! 2006 SDK.

There can be numerous situations where you will want to create a blank new ACT! database with just the standard ACT! fields.

Please note that you must have at least one CLY layout file in the Layouts folder in order to open the database from ACT! 2006.

This routine has only been certified for non-shared, i.e., single-user databases. Parameter bShared=False.

I hope you find this useful, please let me know if you do.

ACT! is a registered trademark of Sage Software SB, Inc. See www.act.com for more information.

Joe Kunk
Listen IT Solutions, LLC
jkunk at listenit dot com

Public Function CreateNewACTDatabase(ByVal DBName As String, ByVal dbLocation As String, ByVal UserName As String, ByVal Password As String, ByVal bShared As Boolean, ByVal SourceLayoutFolder As String) As String

'Returns PAD filename if successful, otherwise null
'Return an empty string if an error in the parameters

If DBName.Trim.Length = 0 Then Return String.Empty
If dbLocation.Trim.Length = 0 Then Return String.Empty
If UserName.Trim.Length = 0 Then Return String.Empty
If SourceLayoutFolder.Trim.Length = 0 Then Return String.Empty
If Not IO.Directory.Exists(dbLocation) Then Return String.Empty
If Not IO.Directory.Exists(SourceLayoutFolder) Then Return String.Empty

'Sample standalone database PAD File
'<?xml version="1.0" standalone="no"?>
'<!DOCTYPE ACTDatabasePADFile>
'<!--This file represents a Pointer to an Act Database or [PAD]-->
'<ACTDatabase name="ACT_Standalone_Test_2006" host="." location="C:\Test" type="Sql" />

'Sample shared database PAD file
'<?xml version="1.0" standalone="no"?>
'<!DOCTYPE ACTDatabasePADFile>
'<!--This file represents a Pointer to an Act Database or [PAD]-->
'<ACTDatabase name="ACT_Shared_Test_2006" host="MYPCNAME" location="C:\Test" type="Sql" />

'Declare the ACT! Framework object
Dim cAFF As New Act.Framework.ActFramework

'Declare GeneratePADFile=True as a constant
Const GeneratePADFile As Boolean = True

'Now actually create the database, calling routine should have warned user that could take a couple minutes
Dim strPadFileName As String = _
cAFF.CreateActDatabase(DBName, dbLocation, bShared, UserName, Password, GeneratePADFile)

'Exit with an empty string if CreateACTDatabase function failed
If strPadFileName.Trim.Length = 0 Then Return String.Empty

'Set up the Path variables for both the Source and Destination CLY files
Dim CLY_Source_Path As String = SourceLayoutFolder
If Not CLY_Source_Path.EndsWith("\") Then CLY_Source_Path &= "\"
Dim CLY_Dest_Path As String = dbLocation & "\" & DBName & "-database files\Layouts\"

'Get the list of all CLY files in the Source folder and copy each one to the Dest folder
Dim dir As New System.IO.DirectoryInfo(SourceLayoutFolder)
Dim f As System.IO.FileInfo
For Each f In dir.GetFiles("*.cly")
f.CopyTo(CLY_Dest_Path & f.Name, True)
Next f

Return strPadFileName

End Function

Wednesday, May 24, 2006

Create a quick report in Word

Sometimes I just want to create a very quick report for a client but I don't want to deal with the complexity of setting up a report in a tool like Crystal Reports or SQL Reporting Services.

What I have found works well is to create a Word template with the report headers and footers that I want, and then simply create all the report lines as a large string and then just have the application paste that text into a blank instance of that template.

Sample code follows:

Dim sb as new System.Text.StringBuilder

... place here all the statements needed to create the report data as a large string in sb ...

Dim oWord As Object
oWord = CreateObject("Word.Application")
oWord.Visible = True

Note that this is only for very simple reports because it does not allow for any formatting within the text or within the document header or footer. Note that a tab is chr(9) and a form-feed (new page) is chr(12).

Often time clients just want the data and don't mind doing the formatting themselves in MS Word when it is finished.

Hope you find this tip useful...
Joe Kunk

SQL Reporting Services and Word are registered trademarks of Microsoft Corporation.
Crystal Reports is a registered trademark of Business Objects SA.

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.


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.


.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.