.NET Data Secrets, Part 1

Welcome to the first part in .NET Data Secrets!

I'm Karl Moore and today, we'll be discovering a bundle of simple tricks to make your data access work as easily as possible. We'll be looking at:

• Generating GUIDs in a Flash
• Making Your Own OLE DB Connection String Creator
• Cheating with SQL
• Finding the Last Identity Number Added
• Seven Steps to a Quick, Editable Windows Grid

Generating GUIDs in a Flash

GUIDs are globally unique identifiers, 128-bit integers that are automatically generated based on around two zillion frequently varying factors. In brief, they're useful when you need a value you can be assured will not match any other, anywhere. Probably.

The SQL Server data type 'uniqueidentifier' stores a GUID. You can either generate this value within SQL Server, using the NEWID() function (perhaps you could specify the function as the default value for all new rows)—or you can generate the GUID outside of SQL Server and insert it manually.

If you're doing the latter, this tip can help out. Here's a function for instantly generating your own GUID in VB.NET:

Public Function GetGUID() As String
    ' Returns a new GUID
    Return System.Guid.NewGuid.ToString
End Function

The code simply uses the NewGuid function of the System.Guid namespace to return a value. If you've ever done this in VB6, you'll appreciate quite how compact this simple function really is. Finally, here's how you may use this function:

Dim MyGUID As String
MyGUID = GetGUID()

Making Your Own OLE DB Connection String Creator

You're often required to generate OLE DB connection strings for use in your code; however, it's never an easy task. You can either rummage through the documentation and attempt to piece together your own, or use the VS.NET Server Explorer to make a connection and then inspect its properties.

One handy alternative, however, is to type out the following code into Notepad, saving the file with a .VBS extension. Whenever you need a connection string in the future, simply launch the file. It'll run your VBScript, visually prompt you for the database details, and then offer the final connection string for you to copy from an InputBox:

Dim objDataLinks, strRetVal
Set objDataLinks = CreateObject("DataLinks")
On Error Resume Next ' ignore cancel
strRetVal = objDataLinks.PromptNew
On Error Goto 0
If Not IsEmpty(strRetVal) Then
InputBox "Your Connection String is listed below.", _
         "OLE DB Connection String", strRetVal
End If
Set objDataLinks = Nothing

Fill out your databases details, and then copy the OLE DB connection string

Page 1 of 3