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
Cheating with SQL
So, you’re developing all those SQL Server applications—and keep forgetting the difference between an inner join and an outer join? Unsure whether you should “Order By”, “Sort By”, or “Group By”? Then maybe it’s about time you started to cheat.
You see, there’s an easy way to write SQL—visually! Simply use the Server Explorer to open up your SQL Server, then right-click on the Views node. Select ‘New View,’ add the tables you wish to use in your query—and begin designing, specifying any sorting or other criteria. Right-click a blank area of the table view and select Property Pages to specify further criteria.
Also, the designer is particularly useful when dealing with date and time fields because it incorporates any required SQL Server functions for you, automatically. Just write the query as you would in, say, Microsoft Access.
Top Tip: The designer may be cool—but don’t trust the relationships it automatically adds for you. Hover your mouse over a relationship to view its details. If it’s incorrect, select with your mouse and press Delete. To create a relationship, drag-and-drop one field onto the other. Right-click and select Property Pages if you want to alter its details.
When you’ve finished, click Query > Run to test that it produces the results you require. When you’re happy, simply take text from the SQL pane and remove all instances of the useless term “dbo.” (this is added by SQL Server for the view and will ruin your statement when used in data access code)—and that’s it!
You now can put your SQL statement to work—and have saved yourself an hour in writing and debugging in the process. Not a bad two minutes’ work.
Putting together our SQL statement the easy way
Finding the Last Identity Number Added
Everyone has this problem: you’ve just added a record to your SQL Server database and need to figure out what the automatically generated identity number was, so you can use the value as a foreign key in some child table.
It took me a good while to figure this one out. You simply need to issue the “select @@identity” command to SQL Server and it’ll return a one-field response that contains the last identity number added during your connection.
Let’s look at a commented example:
' Variable to hold the identity value of our record
Dim MyIdentityValue As Integer
' Set up sample connection and command
Dim objConnection As New SqlClient.SqlConnection( _
"server=NEMEAN;database=MYDATABASE;" & _
"user ID=USERID;password=PWD")
Dim objCommand As New SqlClient.SqlCommand( _
"INSERT INTO author (authorname) " & _
"VALUES('Karl Moore')")
' Open connection and execute INSERT command
objConnection.Open()
objCommand.Connection = objConnection
' Execute and check minimum of one record affected...
If objCommand.ExecuteNonQuery > 0 = True Then
' Set up separate command to retrieve identity value
Dim objIdentifier As New _
SqlClient.SqlCommand("Select @@Identity", objConnection)
Try
' Return value of field
MyIdentityValue = objIdentifier.ExecuteScalar
Catch
MyIdentityValue = 0
End Try
End If
' Close connection
objConnection.Close()
Seven Steps to a Quick, Editable Windows Grid
One of the most common requests when dealing with databases is the ability to display a grid that “binds” to a table or view, displaying information and allowing you to quickly edit data. The good news is that in .NET, it’s a relatively simple process. The following seven steps will guide you through the exact process.
Ready to rumble?
- Open the Server Explorer (View > Server Explorer). If you’re connecting into an SQL Server database, expand the Servers node, locate your target machine (if not available, click the ‘Connect to Server’ icon and specify the machine name), and then drill down to your database. If you’re connecting into another type of database, right-click on the Data Connections node, select Add Connection, and connect into your database.
- Expand the list of tables (or views) and drag the one you want to bind to your grid onto your Windows Form. Two components will be created—a Connection object, which connects into the database, and a DataAdapter object, which acts as the “phone line” between your Connection object and your actual set of data (your “DataSet“).
- Right-click the DataAdapter and choose Generate DataSet. A dialog box will appear, about to create the template upon which your DataSet will be based (a DataSet based on a template like this is called a “typed DataSet,” whilst the template itself is a customizable “XML schema,” sometimes referred to as XSD [XML Schema Definition]). Ensure New is selected; replace the suggested name with something more sensible, such as ‘Customer’. Ensure ‘Add this DataSet to the designer’ is checked. Click OK when finished. Two things will happen: A Customer.xsd (or similar) template will be added to your Solution, and a DataSet component will be added to your form, based on the template. Rename your new DataSet to, say, dsCustomer.
- Drag-and-drop a DataGrid control from the Toolbox onto your Form. Resize as appropriate; then right-click and select Auto Format. Choose a new style, such as “Professional 3” or “Colorful 3”.
- Change the DataSource property of your DataGrid control, selecting your DataSet table from the dropdown list.
- Add the following line of code to respond to your form Load event, or place it behind a button. It tells your DataAdapter to talk through the connection, retrieve data, and then pass it into your DataSet (which is feeding the DataGrid):
MyDataAdapter.Fill(MyDataSet)
- Add the following code to respond to your form Closing event, or place it behind a button. It tells your DataAdapter to talk through the connection, updating the source database with any changes made to the DataSet (which may have been through editing the DataGrid):
MyDataAdapter.Update(MyDataSet)
And that’s it. Now all you have to do is run and test!
If you’ve already created your data objects in code, simply set the DataSource property of your DataGrid to your DataSet table in code; then continue from step six onward.
Top Tip: Don’t want a certain column displayed in your Windows Form DataGrid? Simply configure your DataAdapter so it only pulls back the information you want (right-click the DataAdapter and select ‘Configure Data Adapter’). Be careful when excluding primary keys, however, because it can cause problems when editing and updating. Alternatively, use views or customize the more flexible ASP.NET DataGrid (see the next “Web Grid” tip). The official Microsoft workaround for ‘hiding’ columns is more complex; no properties for the columns are exposed directly. However if you’re interested, check out http://support.microsoft.com/default.aspx?scid=KB;EN-US;q317951.
Another Top Tip: There are plenty of .NET resources on the Web, but few have spent time collating real Windows DataGrid newsgroup questions quite as well as George Shepherd. Check out his FAQ for answers to common puzzlers at www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp—it’s C#-based, but still highly understandable.
My example DataGrid. And it’s quite big
Next Time
Coming up in part two of .NET Data Secrets:
- Nine Steps to a Quick, Editable Web Grid
- Little-Known Technique for Confirming Web Deletes
- Selecting Multiple Web Form Grid Items, Hotmail-Style
- Click Anywhere and Select, with a Web Grid
See you next time!
About the Author
Karl Moore is a technology author living in Yorkshire, England. He runs his own consultancy group, White Cliff Computing Ltd, and is author of two best-selling books exposing the secrets behind Visual Basic .NET. When he’s not writing for magazines, speaking at conferences, or making embarrassing mistakes on live radio, Karl enjoys a complete lack of a social life.
All of these tips are quick and nifty versions of those you’ll find in Karl’s newest book, Ultimate VB .NET and ASP.NET Code Book.
# # #
