Use a Wiki for Documentation

I am currently working with a client that, like most clients, is insisting on complete documentation of whatever I do while on-site.  I find the most-productive way for me to do this is to use desktop Wiki software on my laptop as my documentation tool. 

The desktop Wiki software allows me to quickly enter notes as I work on items, dynamically creating an intuitive structure for the information.  For less formal engagements, the printouts from the Wiki itself are sufficient.  For more formal engagements, the desktop Wiki helps me organize my thoughts and I can then easily transfer my Wiki text into the formal documentation format required by the client.

If the client does not have formal documentation requirements, I have been successful in getting the client to adopt a Wiki as their technical documentation repository.  Once they see how easy it is to enter and maintain the information, find it via the full-text search, and access it as a web-site from anywhere (server version), they are sold on it.  I point out that for maximum benefit, the Wiki should be secured for access only by technical staff and one or more separate Wiki sites should be set up if it is to be used outside of technical staff.

If you are interested in trying out a Wiki, I have been very happy with the ScrewTurn Wiki.  It is written in .Net, has both a Windows desktop version and an IIS version, and is available at no charge under the GPL v2 licence.  It has a nice graphical toolbar in edit mode plus learning the formatting tags is quick and easy.  I find it very intuitive.  Online hosting is available for a fee if desired.

If you have not yet tried using a Wiki to collect and organize your documentation, I recommend that you do so.  You will be amazed at how much documentation you will actually do real-time once it becomes so easy with the Wiki.  Since finding things with the full-text search feature is so easy and fast, you will become self-motivated to keep it updated as you work, just as a future reference. 

I also find it very helpful to use the Wiki to organize my findings as I work through a difficult issue.  This helps me arrive at a solution faster, and the process of how I arrived at the solution is documented as a bonus.

Hope that helps.

Joe Kunk
Okemos, MI

How to easily access the results of a LINQ-to-SQL query

I have introduced several developers to LINQ-to-SQL and each has been very impressed, immediately followed by the same question. I'll share the question and the answer here.

I show a standard LINQ-to-SQL query and assign the results to a DataGridView as the Datasource. This is consistent with most introductory examples. Think of it as the LINQ-to-SQL version of Hello World.

The next question is inevitably some variation of "OK, now how do I use the data that LINQ returned?".

The answer is simply to convert it to a Generic List with the LINQ "ToList" extension method.

A fully functional code sample will help make it clear.

Dim dbConn as string = "Server=(local);Database=AdventureWorks;" & _
"Trusted_Connection=yes;"

Dim dbx As New System.Data.Linq.DataContext(dbConn)

Dim Promo1 = _
From emp In dbx.GetTable(Of HumanResources_Employee)() _
Where emp.Gender = "M" _
Order By emp.Person_Contact.LastName, emp.Person_Contact.FirstName _
Select emp


Me.DataGridView1.DataSource = Promo1


'Promo1 is of type DatabaseQuery1
Dim PromoList = Promo1.ToList


MessageBox.Show(String.Format("Third Person's Name is {0}, {1}", _
PromoList(2).Person_Contact.LastName, _
PromoList(2).Person_Contact.FirstName))

Hope that helps.

Joe Kunk
Okemos, MI

Organize your Linq-To-SQL Schemas in a separate Solution

Once you generate your database schema classes into a single source code file per my previous post Simple Batch File to Generate a Full Database Schema using SQLMetal for LINQ to SQL, you still need a way to utilize these schema files effectively in your projects and easily update the schemas for all applications that use them upon any database schema change.

I have found the easiest way to do this is to create a separate Visual Studio solution that contains one project per database, and then reference the appropriate project(s) from any Linq-to-SQL applications that utilize the project's database.

For example, on my laptop I have instances of the AdventureWorks, NorthWind, and Pubs databases. I created a Solution that has three projects, named for each of these three databases. The sole source code file in each project is the database schema file generated by SQLMetal for that database. These projects are referenced as needed by other applications.

If the schema in a database changes, I simply use my batch file mentioned in the previous post to re-generate the database schema source file, copy it into the appropriate project - replacing the previous source file, and build the project or solution. I've never had a problem with the build and it is fast. Since I'm building and not executing any code, I can even build directly on an untrusted resource such as a network drive.

In case that is confusing, here is a graphic that illustrates the solution in Visual Studio.

Hope that helps.

Joe Kunk
Okemos, Mi

Simple Batch File to Generate a Full Database Schema using SQLMetal for LINQ to SQL

Applications that use LINQ-to-SQL need to instantiate a System.Data.Linq.DataContext for the database that they want to use. The utility SQLMetal is provided to generate the schema classes needed to instantiate the DataContext. I created the following batch file to easily generate a complete Visual Basic or C# schema after database updates.

I find that generating the schema utilizing SQLMetal is a great error check on the logical consistency of the database Programmabilty objects. The warning messages generated during the process often identify previously unknown errors in stored procedures, user defined functions, views, etc.

The example assumes SQL authentication using the 'sa' username and the (local) server, and a SQL2005 database.

The batch file has three parameters.
Parameter 1 is the Database name.
Parameter 2 is the Password for the 'sa' server login.
Parameter 3 is the language file extension for the output. Values are 'vb' or 'cs'.

The batch file is named DBSchema.bat and is placed in the default folder opened with the Visual Studio 2008 Command Prompt, C:\Program Files\Microsoft Visual Studio 9.0\VC\ to make it easy to access. You can create the batch file by copying the following code into Notepad and saving it in the folder listed above as DBSchema.bat. Use the command "Type DBSchema.bat" to review the needed parameters before using the batch file.

Example usage:
DBSchema AdventureWorks password vb

The resulting .vb file will reside in the C:\Program Files\Microsoft Visual Studio 9.0\VC\ folder and can be copied as needed for use in your .Net projects.

REM Requires Parameter 1: Database name
REM Requires Parameter 2: Password
REM Requires Parameter 3: Language file extension

SqlMetal /server:(local) /database:%1 /user:sa /password:%2 /namespace:%1Schema /functions /sprocs /views /code:%1Schema.%3 /Provider:SQL2005

The batch file requires that the SqlMetal command be on a single line. But for readability purposes, it is repeated here in multiple lines:

SqlMetal /server:(local) /database:%1
/user:sa /password:%2 /namespace:%1Schema
/functions /sprocs /views
/code:%1Schema.%3 /Provider:SQL2005

Hope that helps,

Joe Kunk
Okemos, MI

How to quickly get the HTML source of an unsecured web page in Visual Basic .Net

There are a LOT of useful applications that can be built on the premise of just retrieving the raw HTML of a publicly available web page and then parsing and analyzing the page contents. That should not be a surprise to anyone, but what may be surprising is just how simple it is to do in .Net.

Here is an example that shows just how easy it is:

Dim myURI as new System.URI("http://www.microsoft.com")

Dim myHTML as string = GetURIText(myURI)


Protected Function GetURIText(ByVal myURI As System.Uri) As String

    Dim result As String = String.Empty

    If Not IsNothing(myURI) Then

        Dim wb As New System.Net.WebClient

        result = wb.DownloadString(myURI)

    End If

    Return result

End Function

Hope that helps.

Joe Kunk
Okemos, MI

A good reminder - use computed columns in SQL Server 2005 / 2008

I have been doing a lot of work in SQL Server 2005 over the last 6 months and I have found it very helpful to use computed columns to hold any totals or other figures that can be derived from other standard columns within the same table.

As a simple example, in the LaborDetail table I have Regular hours, OverTime hours, and DoubleTime hours. It is very helpful to have the TotalHours column present in the table as well. A computed column provides this number and ensures that it is correct, providing the column's formula is correct.

Computed columns can be added without breaking existing stored procedures or external programs that do INSERTs on the table since computed columns do not need to be included in the field list.

If performance could be considered an issue, you have the option to mark the column attribute "Is Persisted" to true. The column value will be physically carried in the table and will be re-computed only when one of its dependent columns changes. Stored Procedures and external programs are still not affected, but table storage size is increased.

This post might come under the heading of stating the obvious, but I believe it is a good reminder and it certainly has been helpful for me to remember this.

Hope it helps.

Joe Kunk
Okemos, MI