Working with DataView Objects in Code

Questions That Should Be Asked More Frequently

  1. Q. How do I determine which method to use to search for data in my DataTable?

  2. A. That depends on what type of search you want to perform and what you want to do with the results of the search. Here's a simple set of guidelines:

    • If you need to locate a row based on its primary key value(s), use DataTable.Rows.Find.

    • If you need to bind controls to the rows that satisfy your search criteria, use a DataView.

    • If you're going to perform repeated searches against a nonkey column or combination of columns, use DataView.Find.

    • In all other situations, use the DataTable.Select method.

  3. Q. Setting up a DataView in code is relatively simple, so is there really any benefit to setting it up at design time using Visual Studio .NET?

  4. A. I'm so glad you asked. Creating DataView objects using Visual Studio .NET offers two major benefits over creating them in code. First, if you're creating the DataView for use with bound controls, you can bind the controls to the DataView at design time if you also create the DataView at design time. Personally, I'd rather set these properties at design time with mouse clicks than write code.

    Visual Studio .NET actually creates a DataView for you behind the scenes, which leads us to the second major benefit. As you set properties using the Properties window, Visual Studio .NET applies your input to the DataView object you created. If you set a property incorrectly by making a typo in the column name or by forgetting to include that column in the DataTable, Visual Studio .NET will alert you to this at design time. Believe it or not, the information in the alert, shown in Figure 8-2, is actually helpful. If you make a similar mistake in straight code, the code will compile successfully and you won't realize your error until you run your application.

    Figure 8-2

    An alert generated by an invalid column name entered in a DataView at design time in Visual Studio .NET

  5. Q. What is the purpose of the DataViewManager object?

  6. A. The DataViewManager object is a container that contains DataViewSetting objects, which are somewhat similar to DataView objects but have less functionality. I've yet to see a significant scenario in which you can use the DataViewManager but not the DataView. For that reason, I'm not covering the DataViewManager object in this book.

  7. Q. How do I locate a row in a DataView if I need to perform a search on a column other than the one that's referenced in the DataView object's Sort property?

  8. A. This is a fairly common scenario when you're using bound controls on a Windows form. Unfortunately, neither the DataView nor the Windows form's data binding objects offer functionality to handle this scenario elegantly.

    Let's say you have a grid bound to a DataView that shows customer information sorted by the Country column. You want to let the user locate a customer based on another column such as ContactName. Figure 8-3 shows such a form.

    Figure 8-3

    Customer information sorted by country

    The goal is to select the appropriate row in the grid. In the figure, the currently selected row has a value of Aria Cruz for the ContactName column and a value of Brazilfor the Country column.

    We first need to determine the index of the desired row in the DataView, which means we have to locate the desired row in the DataTable. You can do this in two ways: using the Select method of the DataTable or using the Find method of a DataView. Let's opt for the latter.

    We'll need a new DataView whose Sort property is set to the ContactName column. Then we can use the Find method to determine the index of the desired row within the DataView. With that information, we can access the desired DataRow and determine the country for that customer as well. The following code snippet does all of this:

    Visual Basic .NET

    Dim tbl As New DataTable("Customers")  Dim vueByCountry As New DataView(tbl) vueByCountry.Sort = "Country" Dim vueByContactName As New DataView(tbl) vueByContactName.Sort = "ContactName" Dim intIndexCountry, intIndexContactName As Integer intIndexCountry = -1 intIndexContactName = vueByContactName.Find("Aria Cruz") Dim row As DataRow = vueByContactName(intIndexContactName).Row Dim strCountry As String = row("Country")

    Visual C# .NET

    DataTable tbl = new DataTable("Customers");  DataView vueByCountry = new DataView(tbl); vueByCountry.Sort = "Country"; DataView vueByContactName = new DataView(tbl); vueByContactName.Sort = "ContactName"; int intIndexCountry, intIndexContactName; intIndexCountry = -1; intIndexContactName = vueByContactName.Find("Aria Cruz"); DataRow row = vueByContactName[intIndexContactName].Row; string strCountry = row["Country"];

    Believe it or not, that was the simple part. It gets uglier from here.

    The next step is to call the Find method of the DataView that has the customers sorted by Country. But multiple rows could have the same value for the Country column, so that won't be the end of our journey.

    Because the values in the Country column are not necessarily unique, it makes sense to call the FindRows method of the DataView. The GetRows method returns an array of DataRowView objects. One of the entries in that array represents the desired row, but there is no way to determine the index of a DataRowView object, so unfortunately the FindRows method cannot help us.

    Another option is to simply scan the entire DataView until we find the desired row. We could write very simple code to do this, but the code would be inefficient.

    In Figure 8-3, you can see multiple rows with the same value for the Country column as our desired row. There's one other major drawback to using the DataView object's Find method in this scenario: The index value returned is not guaranteed to correspond to the first row in the DataView that satisfies the search criteria.

    So, once we have the index for a customer that has the same country as the customer we're looking for, we might still have to look at other customers. The following code uses the return value of Find as a starting point, moving forward through the DataView until it finds the desired row or either moves beyond the bounds of the DataView or moves to a row that does not have the expected value for the Country column. If this process does not locate the desired row, the code will check rows that precede the row that served as the starting point. The code is inelegant, but it is as efficient as possible given that the DataView was not designed to handle such scenarios.

    Visual Basic .NET

    Dim intStartingPoint As Integer = vueByCountry.Find(row("Country")) Dim intCounter As Integer = intIndexStartingPoint Do     If vueByCountry(intCounter).Row Is row Then         intIndexCountry = intCounter         blnFound = True         Exit Do     End If     intCounter += 1 Loop While intCounter < vueByCountry.Count And _            vueByCountry(intCounter)("Country") = strCountry If Not blnFound Then     intCounter = intIndexStartingPoint - 1     Do While intCounter >= 0 And _              vueByCountry(intCounter)("Country") = strCountry         If vueByCountry(intCounter).Row Is row Then             intIndexCountry = intCounter             blnFound = True             Exit Do         End If         intCounter -= 1     Loop End If If blnFound Then     Console.WriteLine(vueByCountry(intIndexCountry)("CompanyName")) Else     Console.WriteLine("Not found!") End If

    Visual C# .NET

    int intStartingPoint = vueByCountry.Find(row["Country"]); while (intCounter < vueByCountry.Count &&         vueByCountry[intCounter]["Country"].Equals(strCountry)) {     if (vueByCountry[intCounter].Row == row)     {         intIndexCountry = intCounter;         blnFound = true;         break;     }     intCounter++; } if (!blnFound) {     intCounter = intStartingPoint - 1;     while (intCounter >= 0 &&             vueByCountry[intCounter]["Country"].Equals(strCountry))     {         if (vueByCountry[intCounter].Row == row)         {             intIndexCountry = intCounter;             blnFound = true;             break;         }         intCounter--;     } } if (blnFound)     Console.WriteLine(vueByCountry[intIndexCountry]["CompanyName"]); else     Console.WriteLine("Not found!");



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net