programming4us
programming4us
DATABASE

.NET Compact Framework 3.5 : Working with Data Sets (part 1) - Creating and Accessing DataSet, DataTable, and DataView Objects

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/30/2012 3:10:45 PM
The ADO.NET DataSet class lies at the center of the three-tiered approach . Understanding the DataSet class and its contained objects is a key to successful data management in managed code. Let’s recap what we have already said about data sets.
  • A DataSet object is a memory-resident database.

  • A DataSet object can contain multiple DataTable objects.

  • DataTable objects can have constraints defined for them (e.g., primary key, foreign key, and unique).

  • Parent/child relationships can be specified between the DataTable objects of a DataSet object.

  • A DataView object sorts and filters the rows of its associated DataTable object. Each DataView is a view of just one table. A DataTable can have multiple DataViews defined on it.

Remember that the DataSet class and associated classes are located in the System.Data namespace and therefore are not provider-specific. That is, a single DataSet object can be used to hold the data regardless of the provider from which the data was obtained and regardless of the environment in which it is being used. The DataSet class is used today in Web services, Web applications, and Windows applications, as well as mobile applications.

DataSet objects provide four major benefits for the mobile application developer.

  1. They can contain data from a variety of remote data sources.

  2. They function while disconnected from those data sources.

  3. They can persist their data locally as XML files through their ReadXml/WriteXml methods.

  4. They display their data through data binding.

Although DataSet objects do not have all the functionality of SQL Server CE (see the DataSet Class Limitations sidebar), their capability to retrieve, disconnect, data-bind, persist locally, reconnect, and update remotely is ideal for lightweight remote applications that do not need the full functionality of SQL Server CE. Additionally, if the application does require SQL Server CE, DataSet objects are still the best way to display the data to the user and to persist all of the data (or subsets of it) as XML files.

DataSet Class Limitations

DataSet objects do not have an ad hoc query capability. Instead, the data in a DataSet object is accessed programmatically, not through a SELECT statement. Properties and methods of the data objects are used to access the data. Thus, you access all rows of a data table by iterating through its Rows collection with a For Each statement; you access all rows of a data table that meet a certain criteria through the table’s Select method; and you access the rows of a relationship by using the parent row’s GetChildRows method.


1. Creating and Accessing DataSet, DataTable, and DataView Objects

Creating a DataSet object is easy: You simply “new” it, with the option of specifying a name, as follows:

         dsetDB = new DataSet("AnyName");

Within a DataSet object, you can create a DataTable object and populate it from scratch, as shown in Listing 1.

Listing 1. Building a DataTable Object from Scratch
private void CreateTable() {
      //  Create an empty table.
      DataTable  dtabCustomers = new DataTable("Customers");

      //  Create three columns.
      DataColumn  dcolID = new DataColumn("ID");
      dcolID.DataType = typeof(int);
      dcolID.AutoIncrement = true;
      dcolID.AutoIncrementSeed = 1;
      dcolID.AutoIncrementStep = 1;

      DataColumn  dcolName = new DataColumn("Name");
      dcolName.DataType = typeof(string );

      DataColumn  dcolAddress = new DataColumn("Address");
      dcolAddress.DataType = typeof(string );

      //  Add the columns to the table.
      dtabCustomers.Columns.Add(dcolID);
      dtabCustomers.Columns.Add(dcolName);
      dtabCustomers.Columns.Add(dcolAddress);

      //  Add a primary key constraint.
      dtabCustomers.Constraints.Add("PKCust", dcolID, true);

      //  Add two rows to the table
      DataRow drowCust;
      drowCust = dtabCustomers.NewRow();
      drowCust["Name"] = "Amalgamated United";
      drowCust["Address"] = "PO Box 123, 98765";
      dtabCustomers.Rows.Add(drowCust);
      drowCust = dtabCustomers.NewRow();
      drowCust["Name"] = "United Amalgamated";
      drowCust["Address"] = "PO Box 987, 12345";
      dtabCustomers.Rows.Add(drowCust);
      }
    }

					  

However, the most common way to create and populate a data table is to execute a SELECT statement against a database and place the results into a DataTable object. These SELECT statements can be as simple as SELECT * FROM Products or as complex as a multitable join with an aggregate function and grouping. Different data tables in the same data set can be drawn from different databases.

1.1. Understanding Data Tables

A data table consists of a collection of columns and a collection of rows. DataRow objects are a collection of Items, which are the fields of the row. To access a value in a data table you must specify the row number and column identifier (either column name or number), as shown in the following code, which sets the CategoryName column of the second row in the dtabCategories data table to Muzzy:

dtabCategories.Rows[1]["CategoryName"] = "Muzzy";

You can also use the DataTable class’s Select method to access rows by value. The following code returns the array of rows whose CategoryName is Widgets:

dsetDB.Tables["Categories"].Select("CategoryName = 'Widgets'");

To save the reference to the first row in the array, you would code the following line. (Remember that arrays are zero-based; the first row of this array is row 0.)

drowX = dsetDB.Tables["Categories"].Select(
        "CategoryName = 'Widgets'")[0];

Accessing columns by name makes your code more readable, but the code will execute more quickly if you access columns by index value rather than by name. A good performance strategy is to look up the row index value for the name once and thereafter access the column by index value.

We often think of a data table as a two-dimensional object, but we should think of it as a three-dimensional object. Each data element (i.e., each field) in a data table can have up to four values, referred to as versions: Current, Original, Proposed, and Default.

Having four possible values for each field does not necessarily make the data table four times as large, because values exist only when necessary. The Default version, for instance, applies at the column level, not the individual field level. There is at most one Default value per column, not one for each field.

In addition to multiple possible field versions, individual rows have a status value, such as Unchanged, Modified, Added, or Deleted. Understanding the meaning of the versions and status codes, and the relationship between them, is essential when programming for data sets.

To illustrate this, we focus on two field versions, Original and Current, and two row statuses, Unchanged and Modified.

When new rows are added to a data table, the Original and Current values are equal to each other and the status is set to Unchanged. When field values change in a data table—either because of changes done by your code or from user actions on a data-bound control—the new value becomes the Current value, the old value remains the Original value, and the state is set to Modified.

At some point in the execution of the program, you decide that you have “completed” your changes, that new values are no longer “new.” That is, you want the Original value thrown away and replaced with the Current value, and you want the row status set back to Unchanged. Normally, this desire to “shuffle” versions and statuses occurs because you have pushed the changes back to the underlying database, but it could occur for a variety of other reasons as well.

You accomplish this synchronization of versions and changing of states by calling the DataRow class’s AcceptChanges method. The DataSet and DataTable classes also have AcceptChanges methods, which operate by cascading the AcceptChanges call down to the individual rows. Never call the AcceptChanges method just prior to updating the database. The class used for transferring data from a data table to the database, the data adapter class, examines the rows’ status first. Data in rows that have their status marked as Unchanged do not cause updates to the database. 

Having mentioned the Current, Original, and Default versions of a data row, we need to say a word about the fourth version, the Proposed version. The Proposed version is used to provide transaction commit and rollback capabilities and exists only during the life of a transaction.

1.2. Working with Data Rows

As mentioned earlier, data rows contain the actual data, which can be accessed or modified by specifying the row and the column within the row. Columns can be specified by either name or index.

Data rows have methods for accessing other rows based on data relations that have been defined in the data set. Two of the most common methods are GetChildRows and GetParentRow. For example, the code in Listing 2 creates a relationship between the Categories and Products tables and then retrieves an array of all rows containing all products in the Widgets category, assuming that the dsetDB data set already contains the Categories and Products tables.

Listing 2. Creating and Using a DataRelation Object
//  Define the relationship
//     between Products and Categories data tables.

dsetDB.Relations.Add(
   "FKProdCat",
   .Tables["Categories"].Columns["CategoryID"],
   .Tables["Products"].Columns["CategoryID"],
   true);

//  Select the Widgets row in the Categories data table.
//    Use it to retrieve all Widgets rows from the
//    Products data table.

dsetDB.Tables["Categories"].
   Select("CategoryName = 'Widgets'")[0].
      GetChildRows("FKProdCat");
End With

1.3. Introducing Data Views

A data view is always a view of a single data table, and it provides only two capabilities: sorting and filtering rows. A DataView object cannot be used to do joins, evaluate aggregate functions, or filter out columns. It is not intended to behave like a SQL Server view. Its purpose, instead, has to do with data binding.

Every data table has a DefaultView property. When this property is first accessed, the DataView object is created, and its Filter and Sort properties are set to String.empty. So, its contents are, on first access, identical to the table’s contents. Thereafter, you can set the Filter and Sort properties to anything you want. Or you can create a new view of a table, specifying the table, row selection criteria, sort sequence, and row status criteria, as in the following statement:

new DataView(dsetTimeTracker.Tables["Projects"],
             "strIdent = '17'",
             "dateStart ASC, dateEnd DESC",
             DataViewRowState.CurrentRows);

Having discussed the DataSet object and its contained objects, it’s time to cover moving data between the data set and the presentation tier—in other words, data binding.

Other  
  •  .NET Compact Framework 3.5 : Examining ADO.NET
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 4) - Connecting the Source and Destination Adapters with a Path
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 3) - Setting Up Column Information
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 2)
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 1) - Creating Packages Programmatically - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 2) - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 1) - Control Flow
  •  SQL Server 2005 : Extending Your Database System with Data Mining - Data Mining Applied (part 2)
  •  SQL Server 2005 : Extending Your Database System with Data Mining - Data Mining Applied (part 1)
  •  # Oracle Coherence 3.5 : Achieving Performance, Scalability, and Availability Objectives (part 2)
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us