Developer Secrets

Tired of the bare bones? Want to get stuck into the real meat behind databases in .NET? Then check out the following supercool developer secrets:

• Generating GUIDs in a Flash

• Making Your Own Connection String Creator

• Finding the Last Identity Number Added

• Cheating with SQL

• Returning Multiple Tables into a DataSet

• Checking Whether SQL Server Is Available

• Seven Steps to a Quick, Editable Windows Grid

• Nine Steps to a Quick, Editable Web Grid

• How to Use HTML in a Web Grid

• Using Hyperlinks in Your Web Grid

• Dates, Currency, Percentages: Formatting your Web Grid Data

• Looking Good: Color -Coding Your 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

• The Lowdown on Using Dropdown Boxes in a Web Grid

• Speedy, Personalized Web Data Binding

• Quick and Easy Data Paging, with Your Web Grid

• Sorting in Seconds, with Your Web Grid

• Amazingly Simple Method for Exporting Your Web Grid to Excel

• Returning a DataSet from an Excel Spreadsheet

• Get Microsoft to Write Your Code ”Amazing Undocumented SQL Server Tool!

Raring to get started? On your marks, get set .

Generating GUIDs in a Flash

Download supporting files at www.apress.com .

The files for this tip are in the Ch4 ” GUIDs folder.

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

The SQL Server data type uniqueidentifier stores a GUID. You can either generate this value within SQL Server, using the NEWID() function (perhaps specifying 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 

Here, our 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 how very compact this simple code block really is. Finally, here s how you may use this function:

 Dim MyGUID As String  MyGUID = GetGUID() 

Making Your Own Connection String Creator

Download supporting files at www.apress.com .

The files for this tip are in the Ch4 ” OLE DB Conn String folder.

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

You can also use this method to generate a SQL Server connection string: simply select the Microsoft OLE DB Provider for SQL Server driver and enter your details as normal. (See Figure 4-1.) When you get the returned connection string, simply remove the Provider portion. Easy!

Figure 4-1: Fill out your databases details, then copy the OLE DB connection string.

Finding the Last Identity Number Added

Download supporting files at www.apress.com .

The files for this tip are in the Ch4 ” Select @@Identity folder.

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 before I figured 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  ' Setup 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     ' Setup 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() 

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 ”then begin designing, specifying any sorting or criteria. Right-click on a blank area of the table view and select Property Pages to specify further options.

The designer is also particularly useful when dealing with date and time fields, as it automatically incorporates any required SQL Server functions for you. Just design 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 to alter its details.

When you ve finished, click on Query Run to test that it produces your required results. When you re happy, simply take text from the SQL pane and remove all instances of the useless dbo term (SQL Server adds this for the view and it will ruin your statement when used in data access code) ”and that s it! (See Figure 4-2.)

Figure 4-2: Putting together our SQL statement the easy way

You can now put your SQL statement to work, and you ve saved an hour in writing and debugging in the process. Not a bad two minutes work.

Returning Multiple Tables into a DataSet

Download supporting files at www.apress.com .

The files for this tip are in the Ch4 ” Multiple Tables in DataSet folder.

One of the big new features of the DataSet over the old Recordset is its ability to hold multiple tables of data at a time. But how many developers take advantage of this feature? How many still use multiple DataSet objects to hold multiple tables?

Don t answer that one.

If you find yourself victim, then you might want to add the following code snippet to your basket . It accepts a connection string and string array of table names . It returns a DataSet containing all the tables requested (not, of course, accounting for errors) ”ready for you to add relationships, apply rules, schemas, and all the other whizzy techniques you may wish to embrace.

Here s the code you ll need:

 Public Function GetDataSet(ByVal ConnectionString As String, _      ByRef Tables() As String) As System.Data.DataSet      ' Create connection, command object and empty DataSet      Dim objConn As New System.Data.SqlClient.SqlConnection(ConnectionString)      Dim objCmd As New System.Data.SqlClient.SqlCommand()      objCmd.Connection = objConn      objCmd.CommandType = System.Data.CommandType.Text      Dim objDS As New System.Data.DataSet()      Try          ' Create new DataAdapter          Dim objDA As New System.Data.SqlClient.SqlDataAdapter(objCmd)          objDA.SelectCommand = objCmd          ' Open connection          objConn.Open()          ' Populate the DataSet with specified tables          Dim intCount As Integer          For intCount = 0 To Tables.GetUpperBound(0)              objCmd.CommandText = "SELECT * FROM " & Tables(intCount)              objDA.Fill(objDS, Tables(intCount))          Next      Catch e As Exception          ' Calling code must check for thrown errors          Throw e      Finally          ' Clean up          objConn.Close()      End Try      Return objDS  End Function 

And here s how you might call this function:

 Dim MyDataSet As New DataSet()  Dim Tables(2) As String  Tables(0) = "authors" : Tables(1) = "sales" : Tables(2) = "titles"  MyDataSet = GetDataSet("data source=localhost;initial catalog=pubs;" & _      "persist security info=False;user id=sa;pwd= ", Tables) 

Checking Whether SQL Server Is Available

Download supporting files at www.apress.com .

The files for this tip are in the Ch4 ” SQL Server Online Check folder.

When you re designing applications that work with databases over the Internet, perhaps the largest problem you face is ensuring that everything can actually connect to everything else. You need to ensure that your users have a valid connection to the Net, and you need to ensure that your database server is up and running.

It s not a typically easy process ”and it s a topic that attracts an unusually large amount of error handling code.

However, why not take the simple route and avoid potential big errors later on by checking whether your SQL Server is available to the user before starting your database code?

The following function does just that for you. Simply call IsSQLServerAvailable , passing in your server or IP address. It attempts a connection and returns a True if everything seems fine. Here s the code:

 Public Function IsSQLServerAvailable(ByVal ServerAddress As String) As Boolean      ' Tests an SQL Server connection by name or IP address      Try          ' Attempt to get server address          Dim objIPHost As New System.Net.IPHostEntry()          objIPHost = System.Net.Dns.Resolve(ServerAddress)          Dim objAddress As System.Net.IPAddress          objAddress = objIPHost.AddressList(0)          ' Connect to port 1433, most common SQL Server          ' port. If your target is different, change here          Dim objTCP As System.Net.Sockets.TcpClient = _              New System.Net.Sockets.TcpClient()          objTCP.Connect(objAddress, 1433)          ' No problems (hurrah!)          ' Close and cleanup          objTCP.Close()          objTCP = Nothing          objAddress = Nothing          objIPHost = Nothing          ' Return success          Return True      Catch ex As Exception          ' Server unavailable, return fail value          Return False      End Try  End Function 

And here s how you might call this function:

 Dim blnCanConnect As Boolean  blnCanConnect = IsSQLServerOnline("maxsql001.maximumasp.com") 

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 it s a relatively simple process in .NET ”and the following seven steps will guide you through the exact process.

Ready to rumble?

1. Open the Server Explorer (View Server Explorer). If you re connecting into a SQL Server database, expand the Servers node, locate your target machine (if not available, click on 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.

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

3. Right-click on 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 , whereas the template itself is a customizable XML schema , sometimes referred to as an XSD file [XML Schema Definition].) Ensure that New is selected and replace the suggested name with something more sensible , such as Customer . Ensure that the Add this DataSet to the designer option is checked. Click on 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 .

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

5. Change the DataSource property of your DataGrid control, selecting your DataSet table from the dropdown list.

6. 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, then pass it into your DataSet (which is feeding the DataGrid):

    MyDataAdapter.Fill(MyDataSet) 

7. 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! (See Figure 4-3.)

Figure 4-3: My example DataGrid. It s quite big.

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

TOP TIP  

Don t want a certain column displayed in your Windows form DataGrid? Simply configure your DataAdapter so it pulls back only the information you want. (Right-click on the DataAdapter and select Configure Data Adapter.) Be careful when excluding primary keys, however, as this can cause problems when editing and updating. Alternatively, use views or customize the more flexible ASP.NET DataGrid. (See the Nine Steps to a Quick, Editable Web Grid tip next.) The official Microsoft workaround for hiding columns is more complex, as 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  

Although the Web has plenty of .NET resources, 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. (See Chapter 9 if you feel confused !)

Nine Steps to a Quick, Editable Web Grid

Download supporting files at www.apress.com .

The files for this tip are in the Ch4 ” Web Grid folder.

Setting up your own editable Windows DataGrid may be an absolute doddle, but creating the equivalent grid for the Web is a little more complicated. Most books simply show you how to display data ”and conveniently manage to skip the editing, deleting, adding, and updating stages. But not this one.

Here, we re going to create a template page that will allow you to display data from a table. You ll be able to add new records. Delete records. Edit existing records. Update the backend database. It ll handle most of your simple table operations. I ve written all the code for you ”and if there s any piece of functionality you don t want, just don t add it.

Let s get started.

1. Open the Server Explorer (View Server Explorer). If you re connecting into a SQL Server database, expand the Servers node, locate your target machine (if not available, click on the Connect to Server icon and specify the machine name), 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.

2. Drag the table you want the grid to be based upon onto your Web form. Two components will be added ”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).

   TOP TIP  

   To preview the data coming back from your DataAdapter, right-click on your DataAdapter object and select Preview Data. To change what is returned (for example, to remove certain columns or add calculated fields), right-click and select Configure Data Adapter, and then use the designer to prepare a customized SQL statement. Do not remove primary keys: instead, make them invisible. (See the next Top Tip.)

3. Right-click on 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 , whereas the template itself is a customizable XML schema , sometimes referred to as an XSD file.) Ensure New is selected and replace the suggested name with something more sensible, such as Customer . Ensure that the Add this DataSet to the designer option is checked. Click on OK when finished. Two things will happen: a Customer.xsd (or similar) template will be added to your Solution, and an invisible DataSet object will be added to your form, based on the template. Rename the DataSet template to, say, dsCustomer .

4. Drag and drop a DataGrid control from the toolbox onto your form. Resize as appropriate, then right-click on and select Auto Format. Choose a new style, such as Professional 3 or Colorful 3 .

5. Add the following code template to respond to the page Load event. This retrieves your table data from the database and binds it to your DataGrid. Be sure to replace MyDataAdapter and MyDataGrid with names of your own DataAdapter and DataGrid objects:

    If Not IsPostBack Then      MyDataAdapter.Fill(MyDataSet)      MyDataGrid.DataSource = MyDataSet      MyDataGrid.DataBind()      DataSave(MyDataSet)      ' The DataSave function will be added later.      ' Remove this line and stop here if you want      ' a read-only DataGrid  End If 

6. Right-click on your DataGrid and select Property Builder. Select the Columns property sheet and ensure that Create columns automatically at run time is checked. This means that the columns are dynamically created from your table. Next, we re going to add one button to allow you to select a record, perhaps for editing or deleting. Under the Available columns list, expand Button Column, highlight Select, and use the > button to move it across to the Selected columns . The properties of this new column button will appear below. Change its Text property to a square bracket and its Button type to a PushButton. Click on OK when finished. You should be able to see the new record select button in your grid.

   TOP TIP  

   If you want to display only certain columns in the DataGrid, you can selectively choose those required through the Property Builder. Firstly, in the General property sheet, select your data by choosing your DataSet and table for the DataSource and DataMember properties. Next, in the Columns property sheet, uncheck Create columns automatically at run time , and then move individual fields from the list of available columns (under Data Fields) over to the selected columns list. Click on OK when finished and continue the instructions.

7. Add the following code to respond when the SelectedIndexChanged event of your DataGrid occurs. This event fires when a record is selected. This code simply highlights the row, making your selection more obvious:

    Dim intCount As Integer  For intCount = 1 To MyDataGrid.Items.Count     MyDataGrid.Items(intCount - 1).BorderStyle = BorderStyle.Groove  Next  MyDataGrid.SelectedItem.BorderStyle = BorderStyle.Dashed 

8. Add the following functions behind your Web form. They provide a clean and easy way of saving and retrieving the DataSet containing our table data. Here our application uses the page ViewState (encrypted HTML sent back and forth between posts), but you could easily change it to use the Session or Application object if you re dealing with large tables:

    Public Sub DataSave(ByVal DataSet As DataSet)      If DataExists() Then          ViewState.Item("__Data") = DataSet      Else          ViewState.Add("__Data", DataSet)      End If  End Sub  Public Function DataRetrieve() As DataSet      Return CType(ViewState.Item("__Data"), DataSet)  End Function  Public Function DataExists() As Boolean      If Not ViewState.Item("__Data") Is Nothing Then Return True  End Function 

9. Add six buttons to your Web Form, above the grid: Add, Delete, Edit, OK, Cancel, and Update. These will be action buttons. You will click on Add to add a new record, Delete to remove a record, Edit to edit an existing record, OK to accept an edit, Cancel to cancel an edit, and Update to save all changes to the backend database. If you don t want to implement any one of these features, simply leave it out. Behind each of those buttons , add the relevant snippet of code (you may wish to incorporate your own error handling code, too):

    ' Code to respond to the Click event of the ADD button: