236 |
Sunday Morning |
We can further specify the look and feel of the resulting table by using the HeaderStyle,
FooterStyle, ItemStyle, and AlternatingItemStyle properties. These properties specify the look and feel of the final output. Use HeaderStyle to format the header row if you have established headings for any of the output columns. The ItemStyle provides the default formatting for each new row; and AlternatingItemStyle sets up a contrasting pattern when a long series of alternating data is to be displayed. The results of these formatting styles can be seen in Figure 23-1.
Figure 23-1 Results of using various style properties of the DataGrid control
Now that we understand how to control what fields to display and how to format the resulting output, lets move into a very common use for the DataGrid control, displaying master/detail relationships
Master/Detail Relationships with the DataGrid Control
When implementing user interfaces, a very common technique for navigating through sets of data is to utilize Master/Detail controls to facilitate data searching. For example,
Figure 23-2 shows the data relationships that exist between the Authors table and the Titles Table in the Pubs database.
Session 23—Using the DataGrid Control with Bound Data |
237 |
Figure 23-2 Diagram of authors and titles relationship
In Figure 23-2, there is a many-to-many relationship between authors and titles. The titleauthor table maintains these relationships. It is very likely that you may need to provide the user of this database a list of authors and provide a list of titles related to a specific author. You can quickly build such a Master/Detail relationship by using a bound dropdown list and a DataGrid control. You can load the dropdown list with the authors table and then filter the titles table by the selected author.
The remainder of this session will refer to the masterdetail.aspx file located in the session 23 folder of the CD-ROM.
CD-ROM
In the masterdetail.aspx file, you’ll find three key functions that support the generation and binding of all data required to support the master/detail relationship shown in
Listing 23-3.
Listing 23-3 Partial Listing of masterdetail.aspx file illustrating core functions
Sub Page_Load(Src As Object, E as EventArgs)
Dim connection as New OleDBConnection(“provider=sqloledb;Data
Source=localhost;Initial Catalog=pubs;User ID=sa;pwd=”)
If Not IsPostback Then Connection.Open()
Dim command as New OleDbCommand(“SELECT * FROM Authors Order By au_lname, au_fname”, connection)
Dim AuthorDataReader As OleDbDataReader = command.ExecuteReader() Dim sFullName as String
While AuthorDataReader.Read()
sFullName = AuthorDataReader.Item(“au_fname”) & “ “ & AuthorDataReader.Item(“au_lname”)
Continued
238 |
Sunday Morning |
Listing 23-3 |
Continued |
authorlist.items.add(New ListItem(sFullName, AuthorDataReader.Item(“au_id”)))
End While AuthorDataReader.Close() Connection.Close() BindData()
End If
End Sub
Function GetTitles(ByVal au_id As String) As DataView Try
Dim connection as New OleDBConnection(“provider=sqloledb;Data Source=localhost;Initial Catalog=pubs;User ID=sa;pwd=”)
Dim command As New OleDBDataAdapter(“SELECT titles.title_id, titles.title, titles.price, titles.ytd_sales,titleauthor.au_ord,authors.au_id, titles.pub_id FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id”, connection)
Dim dataset As New DataSet() Dim dataView1 as DataView
command.Fill(dataset, “TitleAuthor”)
dataView1 = new DataView(dataset.Tables(“TitleAuthor”)) dataView1.RowFilter = “au_id=’” & au_id &”’”
Return dataview1
Catch myException as Exception
Message.Text = (“Exception: “ + myException.ToString()) End Try
End Function
Function BindData() Try
Dim TitlesDataView As DataView = GetTitles(authorlist.SelectedItem.Value) titleGrid.DataSource = TitlesDataView
titleGrid.DataBind() Catch myException as Exception
Message.Text = (“Exception: “ + myException.ToString()) End Try
End Function
Populating the Master control
In the Page_Load() function we establish a connection to the Pubs database, then proceed to fill an OleDbDataReader by connecting it to a OleDbCommand which has been attached to a SQL select statement. We have decided to use a OleDbataReader object here as opposed to a DataSet object to simply illustrate how it can be used as easily as the DataSet object for this type of operation, forward reading a set of data.
We loop through the retrieved DataReader using the following control loop:
While AuthorDataReader.Read()
sFullName = AuthorDataReader.Item(“au_fname”) & “ “ &
AuthorDataReader.Item(“au_lname”)
authorlist.items.add(New ListItem(sFullName,
AuthorDataReader.Item(“au_id”)))
End While
Session 23—Using the DataGrid Control with Bound Data |
239 |
This allows us to quickly loop through the records and populate the authorlist dropdown list. Finally we close the OleDbDataReader and the Connection Objects with
AuthorDataReader.Close()
Connection.Close()
It is important to remember that you need to close OleDbDataReader objects when you are completed with them, as they do not automatically go out of scope, closing the
OleDbConnection the way that DataSet objects do.
Tip
General Rule on opening connections: If you explicitly open a connection, for instance by calling an Open() method, then you will need to use a Close() method to avoid errors. When using DataSet objects, you never explicitly open a connection, so closing is not an issue.
Now that we have a master list of authors populated, we need to filter the Titles table for the selected author and display the details in a DataGrid control. This is done by calling the BindData() function.
Filtering the detail listing
The BindData() function is the primary function, which initiates grabbing the authorid selected in the authorlist and then filtering the titles table by calling the GetTitles() function and passing the authorid value as shown below:
Function BindData() Try
Dim TitlesDataView As DataView = GetTitles(authorlist.SelectedItem.Value) titleGrid.DataSource = TitlesDataView
titleGrid.DataBind() Catch myException as Exception
Message.Text = (“Exception: “ + myException.ToString()) End Try
End Function
The GetTitles() function accepts the authorid and does a multitable query of the authors, titleauthor, and titles tables to get a list of all titles, the price of each title, and the year to date revenue for the title. The following example illustrates the use of a DataView filter to filter the rows returned by the author id:
Function GetTitles(ByVal au_id As String) As DataView Try
Dim connection as New OleDBConnection(“provider=sqloledb;Data Source=localhost;Initial Catalog=pubs;User ID=sa;pwd=”)
Dim command As New OleDBDataAdapter(“SELECT titles.title_id, titles.title, titles.price, titles.ytd_sales,titleauthor.au_ord,authors.au_id, titles.pub_id FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id”, connection)
Dim dataset As New DataSet() Dim dataView1 as DataView
command.Fill(dataset, “TitleAuthor”)
dataView1 = new DataView(dataset.Tables(“TitleAuthor”)) dataView1.RowFilter = “au_id=’” & au_id &”’”