Displaying Data from a Table

We saw how easily we can create a page that displays the contents of a database table in the previous chapter. By simply dragging a table from the Data Explorer window and dropping it into an ASP.NET page, Web Matrix builds the page and all the code required automatically. The result is repeated in the next screenshot:

The part to focus on at the moment is the gray box labeled SqlDataSourceControl at the top of the page, above the grid control. While we are all familiar with what a "black box" is (especially if you are what the airlines like to call "a frequent flyer"!), few of us know exactly what's inside it. Likewise with our "gray box" – it's identified in the page as being a SqlDataSourceControl, but what's going on inside it?

Obviously, it is something to do with extracting the data from the database. The following screenshot shows the ASP.NET page running in the browser, and it displays all the data from the table that we dragged onto the ASP.NET page in the first place (the publishers table from the pubs database):

Inside the SqlDataSourceControl "Gray Box"

So, let's look inside the SqlDataSourceControl and see in a bit more detail what it does. This will help you to understand how Web Matrix builds ASP.NET pages that can work with data in a database. The SqlDataSourceControl is listed in the Web Controls section of the Toolbox, and we can add one to our ASP.NET page simply by dragging it onto the page, just like any other control from the Toolbox.

Try It Out—Exploring the SqlDataSourceControl

1. Open a new blank ASP.NET page in Web Matrix and call it SqlDataSourceControl.aspx. Find the SqlDataSourceControl from the Web Controls section of the Toolbox and drag it onto the empty page:

2. The now familiar gray box appears on the page. Click on it to make sure it's selected, and look in the Properties window at the bottom right of the screen. You can see the kind of things that this control expects us to provide values for. The properties are mostly concerned with commands, and whether they are auto generated:

   

   

   We could approach our exploration of this control by explaining all the properties, one by one, until you finally drifted off to sleep! However, instead, click on the SqlDataSoureControl again and press Delete to remove it from the page. We're going to work with this control in a slightly different way.

3. Open a connection to the CAM database in the Data Explorer window, as shown in the previous chapter, by clicking the New Connection icon, entering the name of the database server to connect to, and selecting the database in the drop down list:

   

4. Select the Discs table, and drag it onto the ASP.NET page. Web Matrix places a SqlDataSourceControl and a DataGrid onto the page automatically:

   

5. Click the Start button on the main Web Matrix toolbar, and the browser displays the contents of the Discs table:

   

6. Now close your browser, and click on the SqlDataSourceControl. Look at the Properties window. This time many of the values have been filled in automatically:

   

7. To see the effects of these property settings, we'll modify the one containing the SQL statement that selects the row for display. In the Properties window, change the value of the SelectCommand property to SELECT * FROM [Discs] WHERE DiscID=3:

   

8. Now click the Start button on the main Web Matrix toolbar and you'll see the results appear in the browser. There is just the single row that we specified in the SQL statement used for the SelectCommand:

   

9. Close the browser, but leave the current page open in Web Matrix. Select the SqlDataSourceControl on the page, go to the Properties window, and change the value of the SelectCommand back to its original value by removing WHERE DiscID=3, so that it will display all the rows again. We're going to use this page again in the next Try It Out.

How It Works

By dragging the table onto the page, we caused Web Matrix to create a SqlDataSourceControl with some interesting properties.

Web Matrix has created two SQL statements, as seen in the SelectCommand and UpdateCommand properties. Since it has created the value for the UpdateCommand property, it has also set the AutoGenerateUpdateCommand property value to False to tell the control not to create an UpdateCommand property itself. We'll talk about what these terms mean in more detail later on.

In the meantime, you should recognize the SelectCommand value. This is the same SQL statement that we used in the Query window of the Data Explorer in the previous chapter to extract a list of the rows in our table (the square brackets around the table name are there only to prevent an error should the table name contain a space). The SqlDataSourceControl uses this SQL statement to extract the rows for the grid control in our ASP.NET page.

Web Matrix has also figured out the connection string that's required to connect to our database, and placed it in the ConnectionString property. Again, we'll discuss connection strings in a little more detail later in this chapter.

The SqlDataSourceControl uses the SQL statements we specify (or which Web Matrix creates for us) to access the data in the database. The techniques it uses are actually very similar to those of ADO.NET, upon which all of this stuff depends. (In fact, you'll see this at the start of the next chapter, where we learn more about what ADO.NET is, and how it's used to access data programmatically.) The SqlDataSourceControl is like a wrapper around one of the basic ADO.NET objects – the DataAdapter – which we use to interact with a database when writing code outside Web Matrix.

We'll be looking at the DataAdapter and other ADO.NET objects later. In the meantime, we'll summarize the way that the properties of the SqlDataSourceControl are used in Web Matrix.

The SqlDataSourceControl Properties

We've seen that the SqlDataSourceControl exposes three command properties, and that at least two of them can be specified as SQL statements:

• The SQL statement in the UpdateCommand property (although we didn't demonstrate it) is an UPDATE statement, used to change the values of rows in the database table.

• The SQL SELECT statement in the SelectCommand property, as we saw in our example, is used to extract the data from our database table.

• The same logic applies to the DeleteCommand, with can contain a SQL DELETE statement. This will be used to delete rows from the table that the control is linked to.

However, there is one type of SQL statement for which a matching property is not provided – the SQL INSERT statement – so this control can't be used to insert new rows into a database table.

Auto-Generated Commands

The other three properties that we mentioned, but did not expand on earlier, are the three AutoGenerate properties. The value of each one is a Boolean (True or False) value.

If it is set to True, the control will automatically generate SQL commands for deleting, updating, and inserting data. It does this using an ADO.NET object known as a CommandBuilder to create suitable SQL statements based on the value of the SelectCommand property of the SqlDataSourceControl.

Note

This means that we always have to provide a value for the SelectCommand of our SqlDataSourceControl, though the others (DeleteCommand and UpdateCommand) are optional. They are only used when we come to update or delete a row, and not for just displaying data.

However, if we decide to create our own SQL statement for the UpdateCommand and/or DeleteCommand properties (perhaps because we want to perform some specific checks on the values in the existing rows), we set the AutoGenerate property for that command to False. You can see that this is what Web Matrix does when it creates the SqlDataSourceControl – it fills in the UpdateCommand property and sets the AutoGenerateUpdateCommand property to False:

Having said all this, the process of performing updates to a database using a SqlDataSourceControl is not straightforward. It's likely to be an improved process in future releases of Web Matrix, but for now we suggest you use the techniques we describe in the next chapter for updating data. However, it is useful to appreciate the meaning and purpose of these properties, as they are just about identical to those you'll meet when we look at updating a data source through a DataAdapter object in the next chapter.

Connection Strings

Obviously, the controls and code in our ASP.NET pages have to know where to get the data that they're supposed to be working with. This is done using a connection string, which specifies where to get the data from (the name of the server, the security protocols involved, the database to be used, and so on.

When we created a connection in the Data Explorer window using the New Connection icon, we set a lot of these properties. The settings used in the New Connection dialog determine the values used in the connection string. Let's take a look at an example:

The settings shown above are the settings we used in the previous examples, and these settings generated the following connection string in our code:

 server='(local)\netsdk';trusted_connection=true;Database='CAM' 

Note

A "trusted connection" is one that uses the account under which the ASP.NET page is running to access the database, and so this account must have access to the database. If you have IIS installed on your system, the default used for this is called ASPNET. The .NET SDK samples installation routine adds this account to the sample databases in MSDE, though you may have to add it yourself if you are using a different database. If, however, you don't have IIS installed, the currently logged-in user's account is used instead.

If we specify a different server, for example a machine named dingley (yes, it's a Dell machine!), and that the connection should be made using a specific user name and password, the connection string we get contains these new values. The settings in the next screenshot demonstrate this:

The connection string we get this time contains the user ID and password, rather than the special instruction, trusted_connection=true, that we got when we specified Windows authentication in the previous example:

 server='dingley'; user id='sa'; password='secretone'; Database='CAM' 

Note

This means that the account specified will be used to access the database, rather than the account that is used for a trusted connection. The account used for trusted connections is always a local account (it is only valid on the local machine), so when connecting to a database server on another machine you must specify an account that is valid on the target machine. You also have to make sure that the specified account has permission to access the database and its tables and stored procedures. In SQL Server, this is done using Enterprise Manager.

So, while Web Matrix can create the correct connection string for us automatically, it's worth understanding how you can edit it if required. When we come to write our own code that connects to a database and accesses data, rather than just using the controls provided with Web Matrix, we often have to specify the connection string ourselves. This is particularly the case if we use a database other than SQL Server or MSDE.

About the MxDataGrid Control

So far in this chapter, we've talked about the SqlDataSourceControl, and how it connects to the database and extracts the data. However, the presentation of this data is what we're after. The presentation is the responsibility of the other control that Web Matrix automatically adds to the page when we drag a table from the Data Explorer window onto an ASP.NET page – the MxDataGrid control. In the screenshot we showed at the start of the chapter, you can see it lurking below the SqlDataSourceControl:

If you select this control in the page and look at the Properties dialog, you can see how the MxDataGrid is "connected to" the SqlDataSourceControl. The MxDataGrid control has a property named DataSourceControlID, and this is set to the ID of the SqlDataSourceControl on the page, in this case, SqlDataSourceControl1:

Between them, these two controls do all the work required to extract the data and present it as a grid on the page. To prove this, when the page we ended up with at the end of the previous Try It Out is open in Web Matrix, click the Code tab at the bottom of the editor window to switch to Code view. Other than the default line ' Insert page code here, the code section is empty.

In the next Try It Out, we'll explore how we can work with the MxDataGrid control, and modify the output it generates. Along the way, you'll see how it works – and this will come in extremely useful when we come to look at how we can work with the more generic ASP.NET list controls in the next chapter.

Try It Out—Using the MxDataGrid Control

1. Create a new ASP.NET page called MxDataGrid.aspx, connect to the CAM database, and drag the Discs table onto the page. This adds the SqlDataSourceControl and MxDataGrid controls, and sets their properties automatically.

2. Click on the MxDataGrid control in the page, and go to the Properties window. Find the property entry named Fields and select it. A button with an ellipsis (three dots) on it appears:

   

3. Click this button and the MxDataGridField Collection Editor dialog appears (which, for brevity, we'll refer to as the field editor dialog from now on!). This dialog is used to specify the fields that the grid control will display, and how it will present them. You can see that there are six BoundField items shown in the left-hand list (numbered from zero to five). As you select each one, the right hand list shows the properties of each one, and the current values. You can see that Web Matrix has set several of these properties when it created the control:

   

4. Select the DiscID column in the left-hand list and click the Remove button at the foot of this list:

   

5. The first column (shown as column zero) is now our Title column, and this is fine as it is with the default settings. However, we can make the next column, which contains the release date of the disc, look much better by editing its properties. Select this column in the left-hand list, and change the following properties: change the HeaderText from ReleaseDate to Released, and enter the text {0:MMM yyyy} for the DataFormatString property:

   

6. The next two columns, Notes and Label, are fine with default values that Web Matrix applies when it creates the control. However, we could do with trying to sort out the final column that contains the URL of our cover image. It would be nice if it were a hyperlink rather than just text. So, select the last column (column 4) and click the Remove button at the foot of this list. Then click the drop-down button next to the Add button to see a list of column types that we can use in our grid control:

   

7. Select the HyperlinkField, and the field editor dialog shows this column at the end of the list. Specify the HeaderText as Cover, and type in the field name within the source data, CoverImageURL, for the values of the DataNavigateField and DataTextField properties:

   

8. Click OK to save the new field collection settings, and you'll see the grid in the ASP.NET page updated to these settings. The column headings have changed as we specified, and the content of the Released and Cover columns has also been updated to the new format:

   

9. Now click the Start button to run the page in your browser. Again, you can see the effects of the changes we made to the properties of the columns. You can also see that most of the column headings are hyperlinks, and clicking on one sorts the rows on the value of that column. The Cover column header is not a hyperlink, however, because we didn't enter any value for the SortExpression property of this column, but the important point to notice that the values in the Cover column are now hyperlinks:

   

10. One thing we didn't do when changing the column properties was to modify the visual appearance and style of the columns and their content. However, there are plenty of properties that you can play with in the field editor dialog for each individual column, and in the main Properties window (where the settings affect all the columns). The next screenshot shows the same grid after we changed some of these settings for the Title, Label, and Cover columns (in the field editor dialog) and the grid as a whole (the HeaderStyle, ItemStyle, and AlternatingItemStyle). Have a go yourself, and experiment with these settings:

    

How It Works

In the MxDataGridField Collection Editor dialog, we saw that the six columns in the database had been bound to the MxDataGrid. Let's look at how this collection editor works.

The HeaderText property defines what will be shown as the column heading in the grid, and the DataField is the name of the field (data column) in the source data from which the column will extract the values for display. Columns can be set to be read-only using the ReadOnly property, though this only has any effect when we use the control to edit data in the rows. However, the Visible property can be used to show or hide columns if required.

The SortExpression is used to enable the rows in the grid to be sorted, by clicking on the column heading in the browser. It is the name of the column that the rows should be sorted by when the heading for this column is clicked (you can enter more than one column name, separated by commas, to sort the rows based on the values in these columns). The remaining properties (in collapsible explorer-style lists in the Style section) specify the visual appearance and style of the column, and you can experiment with these if you want to see how the appearance of the grid is achieved. We'll be looking at these properties in more detail with the generic ASP.NET list controls in the next chapter (the MxDataGrid is based on the standard DataGrid control, and most of the properties are the same for both).

We didn't need to display the DiscID column in our page. We could just have hidden it by setting the Visible property to False, but instead we removed it altogether (which saves ASP.NET the processing required to create it and then not display it).

We then formatted the ReleaseDate column to be more customized. We first changed the text to be used in the header, which means that the grid will show Released instead of ReleaseDate at the top of this column. Then by adding a value for the DataFormatString property, we controlled how the value in the column is presented (formatted):

{0:MMM yyyy}

This property accepts the standard .NET format strings where the curly brackets contain two values separated by a colon. The first value is always zero, indicating that the first value it encounters in the data source is the one that will be formatted.

The second value, after the colon, is the format string made up of a set of standard characters that have special meaning. MMM means the month in three letter abbreviated form, and yyyy means the year as four digits, so we'll get something like Oct 2002 displayed in the grid. There are many other formats we can use, such as C for currency, or F2 for a number fixed to two decimal places. We look at formatting strings in a little more detail later on in this chapter.

Our next step was to change the CoverImageUrl field to be a hyperlink. We deleted the original column details, and added a new column of a different type, as a hyperlink. As we saw in the example, the HyperlinkField column has a different set of properties from a BoundField column. When displayed, it creates a normal hyperlink element in the column, and uses the property values to set the attributes and content of the element. The DataNavigateUrlField specifies the field in the source data that will provide the information on what to display when the link is clicked, and the DataTextField specifies the field in the source data that will provide the text of the element (the blue underlined text). We'll learn more about linking to files and pages in Chapter 11.