Displaying Data on Your Web Page

Questions That Should Be Asked More Frequently

1. Q. How can I determine whether my query returned data before I bind my DataReader to the bound control?

2. A. This is a very common question for Web developers. Unfortunately, there's no simple answer. If you call the DataReader object's Read method to determine whether the query returned rows and then bind controls to your DataReader, the controls will not bind to that first row of data.

   If you're simply binding the DataReader to a DataGrid, you could check the Count property of the DataGrid's Items collection to determine how many rows the query returned. However, there are times when you want to know whether the query returned rows before you bind controls to the DataReader.

   Let's say you want to retrieve the orders for a particular customer using the following query:

   SELECT OrderID, CustomerID, OrderDate FROM Orders     WHERE CustomerID = ?

   If you're working with a database that supports batch queries, you could execute a batch query that first returns the number of rows that satisfy the criteria for the query and then returns the actual rows. The following query is such an example:

   SELECT COUNT(OrderID) FROM Orders WHERE CustomerID = ?; SELECT OrderID, CustomerID, OrderDate FROM Orders     WHERE CustomerID = ?

   Visual Basic .NET

   Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) strSQL = "SELECT COUNT(OrderID) FROM Orders WHERE CustomerID = ?;" & _          "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _          "WHERE CustomerID = ?" Dim cmd As New OleDbCommand(strSQL, cn) cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5) cmd.Parameters.Add("@CustomerID2", OleDbType.WChar, 5) cmd.Parameters("@CustomerID").Value = "ALFKI" cmd.Parameters("@CustomerID2").Value = "ALFKI" cn.Open() Dim rdr As OleDbDataReader = cmd.ExecuteReader() rdr.Read() If rdr(0) > 0 Then     'Query returned rows     rdr.NextResult()     gridOrders.DataSource = rdr     gridOrders.DataBind() Else     'Query did not return rows End If rdr.Close() cn.Close()

   Visual C# .NET

   string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); strSQL = "SELECT COUNT(OrderID) FROM Orders WHERE CustomerID = ?;" +          "SELECT OrderID, CustomerID, OrderDate FROM Orders " +          "WHERE CustomerID = ?"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5); cmd.Parameters.Add("@CustomerID2", OleDbType.WChar, 5); cmd.Parameters["@CustomerID"].Value = "ALFKI"; cmd.Parameters["@CustomerID2"].Value = "ALFKI"; cn.Open(); OleDbDataReader rdr = cmd.ExecuteReader(); rdr.Read(); if (Convert.ToInt32(rdr[0]) > 0) {     //Query returned rows     rdr.NextResult();     gridOrders.DataSource = rdr;     gridOrders.DataBind(); } else {     //Query did not return rows } rdr.Close(); cn.Close();

   If you're working with a database that does not support row-returning batch queries, you can use the same approach but use separate queries instead of a batch.

3. Q. How do I handle optimistic concurrency when submitting changes to my database in a Web application?

4. A. The answer really depends on the needs of your application. The ShoppingCart sample relies on just the primary key columns when submitting changes to the cart. This logic works because the application maintains a separate shopping cart for each session. So there's no chance that multiple users will attempt to edit the contents of the same shopping cart.

   If you need more restrictive concurrency checking, you could cache the contents of the row that the user is editing just before the user edits it. You would then have the original values for the row and could use those values in the WHERE clause for your update attempt to ensure that the update does not succeed if another user has modified the same row of data.

   A more elegant solution is to include a timestamp value in your database table and then rely on the primary key and timestamp values in the WHERE clause. This solution is more elegant because it requires you to cache less data in ViewState, hidden fields, or Session.

5. Q. I'm working with a DataSet that contains two DataTable objects related by a DataRelation object. How do I display just the child rows for a particular parent row in a bound DataGrid?

6. A. The DataRow object has a GetChildRows method that returns an array of DataRow objects with only the child rows in the array. However, you can't bind controls such as the DataGrid to an array of DataRow objects. You could create a DataView object, initialize it to the child DataTable and then set the RowFilter property on the DataView so that only the desired child rows are visible through the DataView. Thankfully, there's an easier way.

   Create a DataView object that's initialized to the parent DataTable. Then locate the desired parent row in the DataView and call the CreateChildView method to create a DataView that contains just the child rows.

   Visual Basic .NET

   Dim dsCustomersOrders As New DataSet()  Dim vueCustomers, vueOrders As DataView vueCustomers = New DataView(dsCustomersOrders.Tables("Customers")) vueCustomers.Sort = "CustomerID" Dim intCustomerIndex As Integer = vueCustomers.Find("ALFKI") If intCustomerIndex >= 0 Then     'Located the desired parent row     Dim drvCustomer As DataRowView = vueCustomers(intCustomerIndex)     vueOrders = drvCustomer.CreateChildView("CustomersOrders")     gridOrders.DataSource = vueOrders     gridOrders.DataBind() Else     'Couldn't locate the desired parent row End If

   Visual C# .NET

   DataSet dsCustomersOrders = new DataSet();  DataView vueCustomers, vueOrders; vueCustomers = new DataView(dsCustomersOrders.Tables["Customers"]); vueCustomers.Sort = "CustomerID"; int intCustomerIndex = vueCustomers.Find("ALFKI"); if (intCustomerIndex >= 0) {     //Located the desired parent row     DataRowView drvCustomer = vueCustomers[intCustomerIndex];     vueOrders = drvCustomer.CreateChildView("CustomersOrders");     gridOrders.DataSource = vueOrders;     gridOrders.DataBind(); } else {     //Couldn't locate the desired parent row }