Working with DataTables


A DataTable object represents a database table. A data table is a collection of columns and rows. The DataRow object represents a table row, and the DataColumn object represents a column or a field of the table. Assuming you haven't imported, reengineered, or otherwise "inherited" a database and its DataTables, the first step to working with these three objects is to create a DataTable schema, which is defined by the DataColumnCollection object. You use the Add method of DataColumnCollection to add columns to the collection. The Columns property of the DataTable object represents the DataColumnCollection, which is a collection of DataColumn objects in a DataTable. You use a DataRow object to add data to a DataTable. The DataRowCollection object represents a collection of rows of a DataTable object, which can be accessed by its Rows property.

Figure 3-6 shows the relationship between the DataTable, the DataRow, and the DataColumn.


Figure 3-6: Relationship between the DataTable, the DataRow, and the DataColumn

A database table schema is the structure or design of the table, which includes table columns, their types, relationships, and constraints. You can view a database schema from the Server Explorer as well as from other places such as the SQL Server Enterprise Manager or Visio. To view a table schema from the Server Explorer, simply right-click the table in the Server Explorer and then click the Design Table menu item. You'll see the Column Name, Data Type, Length, and Allow Nulls columns. These columns represent the name, data type, length, and whether the column allows null values, respectively. The Columns area represents the properties of a column; you can see and specify the description, default value, formula if any, and other properties of a column.

Note

A database table column is also called a field.

So, when we talk about creating a DataTable, we mean building a database table (in memory, of course) and its schema using the DataTable and the DataColumn objects. Based on a column requirement, you can also apply constraints on a column. A constraint is a rule that restricts adding unwanted data to a column. For example, the uniqueness of a column means that a column can't have duplicate values. You'll see constraints in more detail in Chapter 8. After creating a data table schema, the next step is to add DataRows to the DataTable. You use DataRows using the DataRow object. A DataRow object represents a row in a table.

Tip

To see data in the Customers table, double-click the Customers table in the Server Explorer.

OK, now you'll learn how to accomplish this task in ADO.NET using the DataTable, DataColumn, and DataRow objects. First we discuss the DataColumn and the DataRow objects, followed by the DataRelation and the DataTable objects. Then, you'll create a sample project containing all these objects to build a DataTable and add data to it programmatically.

The DataColumn

To understand a DataTable, you must first understand DataRows and DataColumns. As you can see from Figure 3-6, the DataColumnCollection type returns a collection of columns that you can access through the Columns property of the DataTable.

The DataColumnCollection object represents a collection of columns attached to a DataTable. You add a DataColumn to the DataColumnCollection using its Add method. The DataColumn object represents a column of a DataTable. For example, say you want to create a Customers table that consists of three columns: ID, Address, and Name. So, you create three DataColumn objects and add these columns to the DataColumnCollection using the DataTable.Column.Add method.

The DataColumn has some properties. These properties describe a column, such as its uniqueness, what kind of data you can store in that column, its default value, its caption, its name, and so on. Table 3-3 describes some of the DataColumn class members.

Table 3-3: The DataColumn Class Properties

PROPERTIES

DESCRIPTION

AllowDBNull

Both read and write; represents whether the column can store null values

AutoIncrement

Represents whether column's value is auto increment

AutoIncrementSeed

Starting value of auto increment, applicable when AutoIncrement is True

AutoIncrementStep

Indicates the increment value

Caption

Caption of the column

ColumnMapping

Represents the MappingType of the column

ColumnName

Name of the column

DataType

Data type stored by the column

DefaultValue

Default value of the column

Expression

Represents the expression used to filter rows, calculate values, and so on

MaxLenght

Represents maximum length of a text column

ReadOnly

Represents if a column is read only

Unique

Indicates whether the values in a column must be unique

Creating a DataColumn

The DataColumn class provides five overloaded constructors to create a DataColumn. By using these constructors you can initialize a DataColumn with its name, data type, expressions, attributes, and any combination of these.

This is the format for creating a DataColumn with no arguments:

 public DataColumn() 

For example:

 Dim dtColumn As DataColumn = New DataColumn() 

This is the format for creating a DataColumn with the column name:

 Public DataColumn(String) 

where String is the column name. For example:

 // Create Quantity Column DataColumn qtCol = new DataColumn("Quantity"); 

This is the format for creating a DataColumn with the column name and its type:

 Public DataColumn(String, Type) 

where String is the column name and Type is the column data type.

This is the format for creating a DataColumn with the column name, its type, and its expression:

 Public DataColumn(String, Type, String) 

where first String is the column name, Type is the data type, and the second String is an expression.

For example:

 Dim myDataType As System.Type myDataType = System.Type.GetType("System.String") Dim dtColumn As DataColumn = New DataColumn("Name",myDataType) 

This is the format for creating a DataColumn with the column name, expression, and MappingType:

 public DataColumn(String, Type, String, MappingType); 

where String is the column name, Type is the data type, the second String is an expression, and MappingType is an attribute.

In the following example, strExpr is an expression, which is the result of the Price and the Quantity column multiplication:

 // Creating an expression string strExpr = "Price * Quantity"; // Create Total Column, which is result of Price*Quantity DataColumn totCol = new DataColumn("Total", myDataType, strExpr, MappingType.Attribute); 

Note

As you can see from the previous code, the expression strExpr is a multiplication of the Price and Quantity columns. The Price and Quantity columns must exist in the table before you use them in an expression. Otherwise, the compiler will throw an exception of "column not found."

Listing 3-1 summarizes all the constructors. As you can see, dcConstructorsTest creates the Price, Quantity, and Total columns of a DataTable, which later is added to a DataSet. The DataSet binds to a DataGrid using the SetDataBinding method.

Listing 3-1: Creating Columns Using Different DataColumn Constructors

start example
 Private Sub dcConstructorsTest()     ' Create Customers table     Dim custTable As DataTable = New DataTable("Customers")     Dim dtSet As DataSet = New DataSet()     ' Create Price Column     Dim myDataType As System.Type     myDataType = System.Type.GetType("System.Int32")     Dim priceCol As DataColumn = New DataColumn("Price", myDataType)     priceCol.Caption = "Price"     custTable.Columns.Add(priceCol)     ' Create Quantity Column     Dim qtCol As DataColumn = New DataColumn()     qtCol.ColumnName = "Quantity"     qtCol.DataType = System.Type.GetType("System.Int32")     qtCol.Caption = "Quantity"     custTable.Columns.Add(qtCol)     ' Creating an expression     Dim strExpr As String = "Price * Quantity"     ' Create Total Column, which is result of Price*Quantity     Dim totCol As DataColumn = New DataColumn("Total", myDataType, strExpr, _                                                MappingType.Attribute)     totCol.Caption = "Total"     ' Add Name column to the table.     custTable.Columns.Add(totCol)     ' Add custTable to DataSet     dtSet.Tables.Add(custTable)     ' Bind dataset to the data grid     DataGrid1.SetDataBinding(dtSet, "Customers")   End Sub 
end example

To test this source code, you need to create a Windows application with a form and a DataGrid control on it. After that you can call dcConstructorsTest from either Form_Load or the button click event handler:

 Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     dcConstructorsTest() End Sub 

Setting DataColumn Properties

The DataColumn class provides properties to set a column type, name, constraints, caption, and so on. You already saw a list of DataColumn properties in Table 3-3. You'll now learn how to use these properties to get and set their values.

Listing 3-2 creates a column with a name ID and sets its DataType, ReadOnly, AllowDBNull, Unique, AutoIncrement, AutoIncremetnSeed, and AutoIncrementStep properties.

Listing 3-2: Creating a DataColumn and Setting Its Properties

start example
 Dim IdCol As DataColumn = New DataColumn() IdCol.ColumnName = "ID" IdCol.DataType = Type.GetType("System.Int32") IdCol.ReadOnly = True IdCol.AllowDBNull = False IdCol.Unique = True IdCol.AutoIncrement = True IdCol.AutoIncrementSeed = 1 IdCol.AutoIncrementStep = 1 
end example

As you can see from Listing 3-2, this code sets the AutoIncrement property as True along with the AutoIncrementSeed and AutoIncrementStep properties. The AutoIncrement property sets a column value as an auto number. When you add a new row to the table, the value of this column is assigned automatically depending on the values of AutoIncrementStep and AutoIncrementSeed. The first value of the column starts with AutoIncrementSeed, and the next value will be the previous column value added to the AutoIncrementStep. In this code, the ID number value starts with 1 and increases by 1 if you add a new row to the table. If you set the AutoIncrementStep value to 10, the value of the auto number column will increase by 10.

Having a primary key in a table is a common practice to maintaining the data's integrity. A primary key in a table is a unique key that identifies a DataRow. For example, in the Customers table, each customer should have a unique ID. So, it's always a good idea to apply a primary key constraint on the ID table. The properties AllowDBNull as False and Unique as True set a key value as the primary key, and you use the PrimaryKey property of a DataTable to assign a DataTable's primary key. Listing 3-2 shows the AllowDBNull as False and the Unique property as True. Now you'll set DataTable's PrimaryKey property as the ID column (see Listing 3-3).

Listing 3-3: Setting a DataColumn as the Primary Key

start example
 ' Make the ID column the primary key column. Dim PrimaryKeyColumns() As DataColumn = New DataColumn(1) {} PrimaryKeyColumns(0) = custTable.Columns("ID") custTable.PrimaryKey = PrimaryKeyColumns 
end example

Adding a DataColumn to a DataTable

You add a DataColumn to a DataTable using the DataTable.Column.Add method. The Add method takes one argument of the DataColumn type. Listing 3-4 creates two data columns, Id and Name, and adds them to the DataTable custTable.

Listing 3-4: Creating the Id and Name Data Columns of the Customers Table

start example
 Dim custTable As DataTable = New DataTable("Customers") Dim IdCol As DataColumn = New DataColumn() custTable.Columns.Add(IdCol) Dim nameCol As DataColumn = New DataColumn() custTable.Columns.Add(nameCol) 
end example

Now you'll put all the pieces together (see Listing 3-5). In Listing 3-5, you create a Customers table with the columns ID, Name, Address, DOB (date of birth), and VAR where ID is a primary key. Name and Address are string types. DOB is a date type field, and VAR is a Boolean type field.

Listing 3-5: Creating a Table Using DataTable and DataColumn

start example
 Private Sub CreateCustTable()     ' Create a new DataTable     Dim custTable As DataTable = New DataTable("Customers")     ' Create ID Column     Dim IdCol As DataColumn = New DataColumn()     IdCol.ColumnName = "ID"     IdCol.DataType = Type.GetType("System.Int32")     IdCol.ReadOnly = True     IdCol.AllowDBNull = False     IdCol.Unique = True      IdCol.AutoIncrement = True     IdCol.AutoIncrementSeed = 1     IdCol.AutoIncrementStep = 1     custTable.Columns.Add(IdCol)     ' Create Name Column     Dim nameCol As DataColumn = New DataColumn()     nameCol.ColumnName = "Name"     nameCol.DataType = Type.GetType("System.String")     custTable.Columns.Add(nameCol)     ' Create Address Column     Dim addCol As DataColumn = New DataColumn()     addCol.ColumnName = "Address"     addCol.DataType = Type.GetType("System.String")     custTable.Columns.Add(addCol)     ' Create DOB Column     Dim dobCol As DataColumn = New DataColumn()     dobCol.ColumnName = "DOB"     dobCol.DataType = Type.GetType("System.DateTime")     custTable.Columns.Add(dobCol)     ' VAR Column     Dim fullTimeCol As DataColumn = New DataColumn()     fullTimeCol.ColumnName = "VAR"     fullTimeCol.DataType = Type.GetType("System.Boolean")     custTable.Columns.Add(fullTimeCol)     ' Make the ID column the primary key column.     Dim PrimaryKeyColumns() As DataColumn = New DataColumn(1) {}     PrimaryKeyColumns(0) = custTable.Columns("ID")     custTable.PrimaryKey = PrimaryKeyColumns     ' Create a dataset     Dim ds As DataSet = New DataSet("Customers")     ' Add Customers table to the dataset     ds.Tables.Add(custTable)     ' Attach the dataset to a DataGrid     dataGrid1.DataSource = ds.DefaultViewManager   End Sub 
end example

Note

To test this program, create a Windows application and add a DataGrid control to the form.

The output of Listing 3-5 looks like Figure 3-7, which shows a table with empty columns in a DataGrid controlempty columns in a DataGrid control.

click to expand
Figure 3-7: The DataGrid view of an empty DataTable

More DataColumn Properties

You just saw some common properties of the DataColumn class, and you learned how to create and add a DataColumn to a DataTable. But guess what? DataColumn isn't finished yet. It has more to offer. Now let's see the remaining properties of DataColumn.

The ColumnMapping property of DataColumn represents how a DataColumn is mapped in a DataSet and how the value of the column will be written when a DataSet is saved in XML format using the WriteXml method of DataSet. The ColumnMapping takes a value of MappingType enumeration. The MappingType enumeration has four values: Attribute, Element, Hidden, and SimpleContent, which are mapped to an XML attribute, element, internal structure, and XmlText (respectively). Listing 3-6 shows you how to use the ColumnMapping property.

Listing 3-6: Using the ColumnMapping Property of DataColumn

start example
 Dim custTable As DataTable = New DataTable("Customers") Dim nameCol As DataColumn = New DataColumn("Name", _ Type.GetType("System.Int32"), MappingType.Attribute) nameCol.DataType = Type.GetType("System.String") nameCol.ColumnMapping = MappingType.Element custTable.Columns.Add(nameCol) DataGrid1.DataSource = custTable 
end example

So far you saw how to create a new table column, set its various properties, and add it to a table. But what if you wanted to store some custom information about a column, such as a description? Guess what? You can do it. The Extended-Properties property of DataColumn lets you read and write custom properties about a DataColumn. The ExtendedProperties property takes a value of type PropertyCollection, which is inherited from the HashTable class. A hash table is a table that stores data in a key/value pair. The PropertyCollection class defines methods to add, clear, and remover properties to a collection. Some of the PropertyCollection class properties are Count, IsFixedSize, IsReadOnly, IsSynchronized, Item, Keys, and Values. Listing 3-7 creates a column and adds four custom properties to it. After that, the code removes the Description property and displays the rest of the properties using the ExtendedProperties property of DataColumn.

Listing 3-7: Adding and Removing Custom Properties

start example
 Dim custTable As DataTable = New DataTable("Customers") Dim nameCol As DataColumn = New DataColumn("Name", _   Type.GetType("System.Int32"), MappingType.Attribute) ' Add custom properties nameCol.DataType = Type.GetType("System.String") nameCol.ExtendedProperties.Add("Description", "The Name Column") nameCol.ExtendedProperties.Add("Author", "Mahesh Chand") nameCol.ExtendedProperties.Add("UserId", "MCB") nameCol.ExtendedProperties.Add("PWD", "Password") custTable.Columns.Add(nameCol) ' Remove Author property nameCol.ExtendedProperties.Remove("Author") ' Read custom properties Dim str As String Dim i As Integer str = nameCol.ExtendedProperties("Description").ToString() str = str + ", " + nameCol.ExtendedProperties("UserId").ToString() str = str + ", " + nameCol.ExtendedProperties("PWD").ToString() MessageBox.Show(str) 
end example

The Ordinal property (read only) of DataColumn returns the position of a column in a DataColumnCollection, and the MaxLength (get and set both types) property represents the maximum length of a column. Listing 3-8 shows how to use the Ordinal and MaxLength properties.

Listing 3-8: Using the Ordinal and MaxLength Properties

start example
 Dim custTable As DataTable = New DataTable("Customers") Dim nameCol As DataColumn = New DataColumn("Name", _ Type.GetType("System.Int32"), MappingType.Attribute) custTable.Columns.Add(nameCol) nameCol = New DataColumn() nameCol.DataType = Type.GetType("System.String") nameCol.Caption = "New Column" nameCol.ColumnName = "Col2" nameCol.MaxLength = 240 custTable.Columns.Add(nameCol) Dim str As String str = "Ordinal " + nameCol.Ordinal.ToString() str = str + " ,Length " + nameCol.MaxLength.ToString() MessageBox.Show(str) 
end example

The DataRow

A DataRow represents a row of data in a DataTable. You add data to the DataTable using the DataRow object. A DataRowCollection object represents a collection of DataRows of a DataTable. You use the DataTable's NewRow method to return a DataRow object, add values to the DataRow, and add the row to the DataTable again by using DataRowCollection's Add method.

Table 3-4 describes DataRow class properties, and Table 3-5 describes the methods.

Table 3-4: The DataRow Class Properties

PROPERTY

DESCRIPTION

Item

Represents an item of a row

ItemArray

Represents all values in a row

RowState

Indicates the current state of a row

Table

Returns the DataTable to which this row is attached

Table 3-5: The DataRow Class Methods

METHOD

DESCRIPTION

AcceptChanges

Commits all the changes made to this row

BeginEdit

Starts an edit operation on a row

CancelEdit

Cancels the current edit on a row

Delete

Deletes a DataRow

EndEdit

Ends the current edit on a row

GetChildRows

Returns child rows of a DataRow

GetParentRows

Returns parent rows of a DataRow.

RejectChanges

Rejects all the changes made since last AcceptChanges

You access DataRow members through the DataTable columns. A column acts as an item of the row. For example, if a DataTable has three columns such as Id, Name, and Address, then a row will have three members: Id, Name, and Address. You access DataTable members using the column names. For example, Listing 3-9 sets values of the Id, Name, and Address columns.

Listing 3-9: Setting the Values of the Id, Address, and Name Columns of a DataRow

start example
 Dim row1 As DataRow = custTable.NewRow() row1("id") = 1001 row1("Address") = "43 Lanewood Road, Cito, CA" row1("Name") = "George Bishop " 
end example

After setting a row member's values, you add the row to the row collection with the DataTable.Rows.Add method. The following code adds a row to the collection:

 custTable.Rows.Add(row1) 

The RejectChanges method of the DataRow rejects recent changes on that row. For example, if you have recently added row1 to the DataTable, then calling the RejectChanges method as follows:

 row1.RejectChanges() 

won't add the row to the DataTable.

You can also delete a row from a DataTable by calling the DataRow's Delete method:

 Row1.Delete() 

Caution

The RejectChanges and Delete methods may not work together if you're applying both methods on the same row because RejectChanges doesn't add a row to the DataTable.

Listing 3-10 shows a program that creates a DataTable with three columns (Id, Name, and Address) and adds three rows to the DataTable. At the end, this program attaches the newly created DataTable to a DataGrid control using a DataSet.

Listing 3-10: Adding Rows to a DataTable Using DataRow

start example
 ' This method creates Customers table   Private Sub CreateCustomersTable()     ' Create a new DataTable.     Dim custTable As System.Data.DataTable = New DataTable("Customers")     Dim dtColumn As DataColumn     ' Create id Column.     dtColumn = New DataColumn()     dtColumn.DataType = System.Type.GetType("System.Int32")     dtColumn.ColumnName = "id"     dtColumn.Caption = "Cust ID"     dtColumn.ReadOnly = True     dtColumn.Unique = True     ' Add id Column to the DataColumnCollection.     custTable.Columns.Add(dtColumn)     ' Create Name column.     dtColumn = New DataColumn()     dtColumn.DataType = System.Type.GetType("System.String")     dtColumn.ColumnName = "Name"     dtColumn.Caption = "Cust Name"     dtColumn.AutoIncrement = False     dtColumn.ReadOnly = False     dtColumn.Unique = False     ' Add Name column to the table.     custTable.Columns.Add(dtColumn)     ' Create Address column.     dtColumn = New DataColumn()     dtColumn.DataType = System.Type.GetType("System.String")     dtColumn.ColumnName = "Address"     dtColumn.Caption = "Address"     dtColumn.ReadOnly = False     dtColumn.Unique = False     ' Add Address column to the table.     custTable.Columns.Add(dtColumn)     ' Make the ID column the primary key column.     Dim PrimaryKeyColumns() As DataColumn = New DataColumn(1) {}     PrimaryKeyColumns(0) = custTable.Columns("id")     custTable.PrimaryKey = PrimaryKeyColumns     ' Instantiate the DataSet variable.     Dim ds As DataSet = New DataSet("Customers")     ' Add the custTable to the DataSet.     ds.Tables.Add(custTable)     ' Add rows to the custTable using its NewRow method     ' I add three customers with thier addresses, name and id     Dim row1 As DataRow = custTable.NewRow()     row1("id") = 1001     row1("Address") = "43 Lanewood Road, Cito, CA"     row1("Name") = "George Bishop "     custTable.Rows.Add(row1)     Dim row2 As DataRow = custTable.NewRow()     row2("id") = 1002     row2("Name") = "Rock Joe "     row2("Address") = "King of Prusssia, PA"     custTable.Rows.Add(row2)     Dim row3 As DataRow = custTable.NewRow()     row3("id") = 1003     row3("Name") = "Miranda "     row3("Address") = "279 P. Avenue, Bridgetown, PA"     custTable.Rows.Add(row3)     'row3.RejectChanges()     'row2.Delete()     ' Bind dataset to the data grid     dataGrid1.DataSource = ds.DefaultViewManager End Sub 
end example

Caution

As you can see from Listing 3-10, the Id column of the Customers table is read only (the ReadOnly property is True). That means you won't be able to add data to the table. If you want to add data from the front end, you need to set the ReadOnly property to False.

The output of the program looks like Figure 3-8.

click to expand
Figure 3-8: A DataTable with three rows

Note

To run this program, create a Windows application and drop a DataGrid control on the form. After that you can either call CreateCustomersTable in Listing 3-3 from the form's Load event or from a button click handler.You can write a Load event by double-clicking on the form or by opening the Properties window.

If you uncomment row3.RejectChanges() and row2.Delete() at the end of Listing 3-10, the RejectChanges method rejects the addition of row 3, and the Delete method deletes row 2.

Listing 3-11: Calling DataRow's RejectChanges and Delete Methods

start example
 row3.RejectChanges() row2.Delete() 
end example

The DataRowState enumeration returns the current state of a row. It's useful during operations when you want to know the current state of a row. Table 3-6 lists the values of the DataRowState enumeration.

Table 3-6: The DataRowState Enumeration Members

MEMBER

DESCRIPTION

Added

Row has been added, and AcceptChanges has not been called.

Deleted

Row was deleted using the Delete method.

Detached

Row was created but deleted before it was added to the collection.

Modified

Row has been modified, but AcceptChanges has not been called yet.

Unchanged

Row has not changed since the last AcceptChanges was called.

The RowState property of a DataRow returns the DataRowState enumeration. You can use this to find out the current state of a row. For example, Listing 3-12 calls RowState just after the Delete and RejectChanges methods.

Listing 3-12: Calling the RowState Property

start example
 row3.RejectChanges() MessageBox.Show(row2.RowState.ToString()) row2.Delete() MessageBox.Show(row3.RowState.ToString()) 
end example

The DataRelation

To provide data integrity and consistency, you should use relationships between two tables. You achieve this relationship by defining a primary key in one table and using a foreign key in the other table. Say a customer has multiple orders; the Customers table stores the customer details, and the Orders table stores all the order details. To avoid the redundancy of data, you define the primary key of the Customers table as a foreign key in the Orders table.

Note

In general this relationship is called the parent/child,or master/details, relationship.

In this example, the Customers table is also the parent table, and the Orders table is also the child table. The ParentRelations property of DataTable represents the parent relationship, and ChildRelations represents the child relationship.

Caution

The data type of both columns, which you're linking through a relationship in the Customers and the Orders tables, must be identical.

You can also access this relationship though a DataSet using its Relations property. To create a relationship between two columns, you create two DataColumn objects and pass them as DataRelation arguments.

Listing 3-13 shows you how to create a customer/order relationship between the Customers and Orders table through the Customers table's id column, referenced as CustId in the Orders table. The DataRelation constructor takes three arguments: the name of the relationship, the first DataColumn, and the second DataColumn. After that you call DataTable's ParentRelation.Add method with DataRelation as an argument. (Listing 3-15 shows the full source code of this example.) In this code, dtSet is a DataSet object, which we discuss in the following section.

Listing 3-13: Creating a Customer/Order Relationship Using DataRelation

start example
 Private Sub BindData()     Dim dtRelation As DataRelation     Dim CustCol As DataColumn = dtSet.Tables("Customers").Columns("id")     Dim orderCol As DataColumn = dtSet.Tables("Orders").Columns("CustId")     dtRelation = New DataRelation("CustOrderRelation", CustCol, orderCol)     dtSet.Tables("Orders").ParentRelations.Add(dtRelation)     dataGrid1.SetDataBinding(dtSet,"Customers") End Sub 
end example

The DataTable

In the previous sections you've seen that columns and rows are the building blocks of a DataTable. You need to work with the DataColumn and DataRow objects to create DataTables and add data to them. Besides creating a DataTable schema and adding rows to it, a DataTable has more to offer. The DataTable object represents a DataTable.

First you'll take a look at the DataTable class properties and methods. Table 3-7 describes some common DataTable properties, and Table 3-8 summarizes some of the common DataTable methods.

Table 3-7: The DataTable Class Properties

PROPERTIES

DESCRIPTION

Columns

Represents all table columns

Constraints

Represents all table constaints

DataSet

Returns the DataSet for the table

DefaultView

Customized view of the DataTable

ChildRelation

Returns child relations for the DataTable

ParentRelation

Returns parent relations for the DataTable

PrimaryKey

Represents an array of columns that function as primary key for the table

Rows

All rows of the DataTable

TableName

Name of the table

Table 3-8: The DataTable Class Methods

METHOD

DESCRIPTION

AcceptChanges

Commits all the changes made since the last AcceptChanges was called

Clear

Deletes all DataTable data

Clone

Creates a clone of a datatable including its schema

Copy

Copies a DataTable including its schema

NewRow

Creates a new row, which is later added by calling the Rows.Add method

RejectChanges

Rejects all changes made after the last AcceptChanges was called

Reset

Resets a DataTable's original state

Select

Gets an array of rows based on the criteria

The DataTable class provides methods and properties to remove, copy, and clone data tables. Not only that, but you can also apply filters on a DataTable. The Constraints property provides access to all the constraints that a DataTable has. You can also access the child/parent relationship using ChildRelation and ParentRelation. Now we'll create two tables—Customers and Orders—and set a relationship between them. To test this application, you build a Windows application using Visual Basic and add a DataGrid control to the form. After that you call the CreateCustomersTable, CreateOrdersTable, and BindData methods from the form constructor after InitializeComonent. The form constructor looks like Listing 3-14.

Listing 3-14: Form's Constructor Calling CreateCustomersTable, CreateOrdersTable, and BindData

start example
 Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     CreateCustomersTable()     CreateOrdersTable()     BindData() End Sub 
end example

You also need to add a DataSet variable, dtSet, in the beginning of your form:

 Public Class Form1   Inherits System.Windows.Forms.Form   Private dtSet As System.Data.DataSet 

In Listing 3-15, the CreateCustomersTable method creates the Customers data table with Id, Name, and Address columns and adds three data rows to it. The CreateOrdersTable method creates the Orders table with OrderId, CustId, Name, and Description columns and adds data to it. The BindData method creates a customer/orders relationship and binds the data to a DataGrid control using DataSet.

Listing 3-15: Customer/Orders Relationship Example

start example
   Private Sub CreateCustomersTable()     ' Create a new DataTable.     Dim custTable As System.Data.DataTable = New DataTable("Customers")     ' Create id, Name, and Address Columns and     ' add these columns to custTable     ' See source code for details     ' Make the ID column the primary key column.     Dim PrimaryKeyColumns() As DataColumn = New DataColumn(1) {}     PrimaryKeyColumns(0) = custTable.Columns("id")     custTable.PrimaryKey = PrimaryKeyColumns     ' Instantiate the DataSet variable.     dtSet = New DataSet("Customers")     ' Add the custTable to the DataSet.     dtSet.Tables.Add(custTable)     ' Add rows to the custTable using its NewRow method     ' I add three customers with thier addresses, name and id     ' See source code for details   End Sub   ' This method creates Orders table with   Private Sub CreateOrdersTable()     ' Create Orders table.     Dim ordersTable As DataTable = New DataTable("Orders")     ' Create OrderId, Name, CustId, Description columns     ' Add them to ordersTable     ' See source code for more details     ' Add ordersTable to the dataset     dtSet.Tables.Add(ordersTable)     ' Add two rows to Customer Id 1001     dtRow = ordersTable.NewRow()     dtRow("OrderId") = 0     dtRow("Name") = "ASP Book"     dtRow("CustId") = 1001     dtRow("Description") = "Same Day"     ordersTable.Rows.Add(dtRow)     dtRow = ordersTable.NewRow()     dtRow("OrderId") = 1     dtRow("Name") = "C# Book"     dtRow("CustId") = 1001     dtRow("Description") = "2 Day Air"     ordersTable.Rows.Add(dtRow)     ' Add two rows to Customer Id 1002     dtRow = ordersTable.NewRow()     dtRow("OrderId") = 2     dtRow("Name") = "Data Quest"     dtRow("Description") = "Monthly Magazine"     dtRow("CustId") = 1002     ordersTable.Rows.Add(dtRow)     dtRow = ordersTable.NewRow()     dtRow("OrderId") = 3     dtRow("Name") = "PC Magazine"     dtRow("Description") = "Monthly Magazine"     dtRow("CustId") = 1002     ordersTable.Rows.Add(dtRow)         ' Add two rows to Customer Id 1003     dtRow = ordersTable.NewRow()     dtRow("OrderId") = 4     dtRow("Name") = "PC Magazine"     dtRow("Description") = "Monthly Magazine"     dtRow("CustId") = 1003     ordersTable.Rows.Add(dtRow)     dtRow = ordersTable.NewRow()     dtRow("OrderId") = 5     dtRow("Name") = "C# Book"     dtRow("CustId") = 1003     dtRow("Description") = "2 Day Air"     ordersTable.Rows.Add(dtRow)   End Sub   ' This method creates a customer order relationship and binds data tables   ' to the data grid cotnrol using dataset.   Private Sub BindData()     Dim dtRelation As DataRelation     Dim CustCol As DataColumn = dtSet.Tables("Customers").Columns("id")     Dim orderCol As DataColumn = dtSet.Tables("Orders").Columns("CustId")     dtRelation = New DataRelation("CustOrderRelation", CustCol, orderCol)     dtSet.Tables("Orders").ParentRelations.Add(dtRelation)     DataGrid1.SetDataBinding(dtSet, "Customers")   End Sub 
end example

Listing 3-15 shows all three CreateCustomerTable, CreateOrdersTable, and BindData methods.

As you can see from the CreateCustomersTable method in Listing 3-15, it creates the Customers table using DataTable and adds the Id, Name and Address columns to the table. You use DataColumn to add these columns. The Id column has properties such as ReadOnly and Unique. As discussed earlier, to add a column to a DataTable, you create a DataColumn object, set its properties, and then call the DataTable.Coumns.Add method. Similar to the id column, you add two more columns, Name and Address, of string type to the table. After that you make the id column the primary key by setting DataTable.PrimaryKey as the id column:

 PrimaryKeyColumns(0) = custTable.Columns("id")  custTable.PrimaryKey = PrimaryKeyColumns 

After creating a DataTable you add it to a DataSet using the DataSet.Tables.Add method. This method takes one argument of type DataTable:

 dtSet = New DataSet("Customers") dtSet.Tables.Add(custTable) 

Now, the last step is to add data to DataTable. You add data using DataRow. First, you create a DataRow object using DataTable's NewRow method, add data to a DataRow's items, and add DataRow to the DataTable using the DataTable.Rows.Add method. You'll follow the same method for the second table in CreateOrdersTable to create the Orders table. The Orders table has the fields OrderId, Name, Description, and CustId. The BindData method creates a relationship by using DataRelation and binds the id column of the Customers tables to the CustId column of the Orders table. The name of the relationship is CustOrderRelation. After that you bind DataTable to the DataGrid using the SetDataBinding method.

The output of Listing 3-15 looks like Figure 3-9.

click to expand
Figure 3-9: A DataGrid with data relations

If you click the CustOrderRelation link, the output looks like Figure 3-10.

click to expand
Figure 3-10: Orders record for Customers id 1001

As you can see from Figure 3-10, DataGrid shows all the orders for Customer id 1001.

More DataTable Operations

Adding and deleting are two common operations when working with databases. You've already learned about how to add data to a DataTable using a DataRow. In this section you'll see how to add, delete, sort, and search data programmatically. You'll keep all data in memory in the form of DataTables, but you'll also see a Save option, which will save data in XML format. This is a simple Windows Forms application.

The first step is to build a GUI (see Figure 3-11). To build this GUI, you create a Windows Application project. After that, add a DataGrid control, four Button controls, seven TextBox controls, five GroupBox controls, and some Label controls. Then adjust them on your form. You can also change the background color of the controls.

click to expand
Figure 3-11: Add, delete, sort, and search operations in a DataTable

In this application, we show you all basic common operations such as adding, deleting, sorting, and searching rows. The Add Row button reads the name and address, adds a new row in the DataTable, and displays the added row in the DataGrid. The Delete Rows button deletes rows based on the entered criteria. To delete rows, you enter the column name and value, and the Delete Rows button will delete all the rows that meet the criteria. The Search button reads column name and values entered in the text boxes and returns data that matches the criteria. The Apply Sort button reads a column and sorts rows based on that column. The Save method saves the current data of the DataTable to an XML document.

Note

You don't have to create the same form as shown in Figure 3-11. The only thing you need to have is a DataGrid with the same number of TextBox and Button controls.

Now, you change the names of the form controls and add the DataSet variable dtSet and the DataTable variable custTable to the beginning of the form. Besides the variables defined for the controls (see the MoreDataTableOpsSamp sample available with the source code for details), add two variables of type DataTable and DataSet, as shown in Listing 3-16.

Listing 3-16: Class-Level DataTable and DataSet Variables

start example
 Private custTable As DataTable Private dtSet As DataSet 
end example

Now you create the Customers table with three columns: id, Name, and Address. You've already learned how to add columns to a DataTable using a DataColumn and binding it to a DataGrid. The CreateCustomersTable method creates the Customers table. After creating the Customers table, you add the DataTable to the DataSet using the DataSet.Tables.Add method. The CreateCustomersTable method looks like Listing 3-17.

Listing 3-17: The CreateCustomersTable Method

start example
 ' This method creates Customers table   Private Sub CreateCustomersTable()     ' Create a new DataTable.     custTable = New DataTable("Customers")     Dim dtColumn As DataColumn     ' Create id Column     dtColumn = New DataColumn()     dtColumn.DataType = System.Type.GetType("System.Int32")     dtColumn.ColumnName = "id"     dtColumn.AutoIncrement = True     dtColumn.AutoIncrementSeed = 100     dtColumn.AutoIncrementStep = 1     dtColumn.Caption = "Cust ID"     dtColumn.ReadOnly = True     dtColumn.Unique = True     ' Add id Column to the DataColumnCollection.     custTable.Columns.Add(dtColumn)     ' Create Name column.     dtColumn = New DataColumn()     dtColumn.DataType = System.Type.GetType("System.String")     dtColumn.ColumnName = "Name"     dtColumn.Caption = "Cust Name"     dtColumn.AutoIncrement = False     dtColumn.ReadOnly = False     dtColumn.Unique = False     ' Add Name column to the table.     custTable.Columns.Add(dtColumn)     ' Create Address column.     dtColumn = New DataColumn()     dtColumn.DataType = System.Type.GetType("System.String")     dtColumn.ColumnName = "Address"     dtColumn.Caption = "Address"     dtColumn.ReadOnly = False     dtColumn.Unique = False     ' Add Address column to the table.     custTable.Columns.Add(dtColumn)     ' Make the ID column the primary key column.     Dim PrimaryKeyColumns() As DataColumn = New DataColumn(1) {}     PrimaryKeyColumns(0) = custTable.Columns("id")     custTable.PrimaryKey = PrimaryKeyColumns     ' Instantiate the DataSet variable.     dtSet = New DataSet("Customers")     ' Add the custTable to the DataSet.     dtSet.Tables.Add(custTable)     RefreshData()   End Sub 
end example

At the end of the CreateCustomersTable method you call the RefreshData method, which refreshes the DataGrid contents and fills them with the current data of the DataTable by setting DataGrid's DataSource property to the DataSet's DefaultViewManager. The RefreshData method looks like the following:

 Private Sub RefreshData()     dataGrid1.DataSource = dtSet.DefaultViewManager End Sub 

As you can see from Figure 3-12, the Add Row button adds a new row to the Customers' DataTable with the Name and Address columns reading from the Name and Address text boxes. The Delete Rows button deletes the row number inserted in the Enter Row # text box. The Search button searches and returns rows that contain the name entered in the Enter Name text box of the Search group box.

click to expand
Figure 3-12: Adding rows to the DataTable

OK, now it's time to write code for the button event handlers. You can write button event handlers by double-clicking the buttons or using the Properties windows. First, you write the event handler for the Add Row button with the handler name AddRow_Click. After that, write event handlers for the Delete Rows and Search buttons; the event handler names for these buttons are DeleteRow_Click and SearchButton_Click, respectively.

You add a new row to the DataTable using DataRow and call the Add and AcceptChanges methods of the DataTable. Listing 3-18 shows the Add Row button click event handler.

Listing 3-18: The AddRowBtn_Click Method

start example
 Private Sub AddRowBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles AddRowBtn.Click     ' Add rows to the custTable using its NewRow method     ' I add three customers with thier addresses, name and id     Dim myDataRow As DataRow = custTable.NewRow()     myDataRow("Name") = TextBox4.Text.ToString()     myDataRow("Address") = TextBox5.Text.ToString()     custTable.Rows.Add(myDataRow)     custTable.AcceptChanges()     RefreshData() End Sub 
end example

As you can see, the code adds Name as TextBox4.Text and Address as TextBox4.Text.

You call NewRow of DataTable to add a new row to DataTable, set its field values, and call the DataTable.Rows.Add method to add it. After the Add method, you also call DataTable.AcceptChanges method to save the changes. At the end, you call the RefreshData method to fill the DataGrid with the records.

If you add six rows to the DataTable using the Add Row button, the result looks like Figure 3-12.

The Delete Rows button deletes the records from a column that matches the given criteria. The Remove Rows Section takes a column name and column value, and the Delete Rows button click event handler removes the rows from the DataTable.

The DeleteRowsBtn_Click method is the click event handler for the Delete Rows button (see Listing 3-19). As you can see, the code uses the Select method of DataTable. The Select method of DataTable takes a criterion and returns the rows that meet the given criteria. In this case, you pass a column name and its value as a criterion. The Select method returns all the matching rows in an array of DataRow. Once you've retrieved these rows, you call DataRow.Delete method to delete the rows one by one, followed by a call of DataRow.AcceptChanges method to save the changes.

Listing 3-19: The DeleteRow_Click Method

start example
 Private Sub DeleteRowsBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles DeleteRowsBtn.Click     Dim str As String = TextBox7.Text + "='" + TextBox6.Text + "'"     Dim rows() As DataRow = custTable.Select(str)     ' If no record found     If rows.Length = 0 Then       MessageBox.Show("Record not found!")       Return     End If     Dim i As Integer     For i = 0 To rows.Length - 1 Step i + 1       rows(i).Delete()       rows(i).AcceptChanges()     Next     RefreshData() End Sub 
end example

In the end, you call the RefreshData method to refresh the grid.

As you can see from Figure 3-13, if you enter column name as Name with the value Puneet, then the Delete Rows button removes the row with Id = 102.

click to expand
Figure 3-13: Deleting rows from the DataTable

You just saw the usage of the DataTable.Select method, which sorts and filters the DataTable rows. The Select method has four overloaded forms. The Select method can take an expression with optional sort and row state parameters.

A filter is a conditional statement that uses the SELECTWHERE clause to select conditional data from the database. All SQL conditional operators are valid in the filter string. For example, to filter rows where the id is greater than 22, the filter string will be Id>22; for selecting records with the name Ross, the filter string will be Name='Ross'. The SeachBtn_Click method searches for the criteria you specify on the form. As you can see from Listing 3-20, the code first calls the Select method with the given criteria, which returns an array of DataRow objects. It creates a new DataTable called searchTable and adds all rows returned by the Select method to this DataTable, and it later binds the searchTable to DataGrid to view the rows.

Listing 3-20: The SearchButtonClick Method

start example
 Private Sub SearchBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles SearchBtn.Click     Dim searchTable As DataTable = New DataTable("SortTable")     searchTable = custTable.Clone()     Dim str As String = TextBox1.Text & " ='" & TextBox2.Text + "'"     Dim rows() As DataRow = custTable.Select(str)     ' If no record found     If rows.Length = 0 Then       MessageBox.Show("Name not found!")       Return     End If     Dim i As Integer     For i = 0 To rows.Length - 1 Step i + 1       Dim row As DataRow = searchTable.NewRow()       row("Name") = rows(i)("Name")       row("Address") = rows(i)("Address")       searchTable.Rows.Add(row)     Next     Dim sortdtSet As DataSet = New DataSet("SortedData")     sortdtSet.Tables.Add(searchTable)     DataGrid1.DataSource = sortdtSet.DefaultViewManager End Sub 
end example

Now using the Search Section text boxes, you can search for records with the name Amy by entering Name in the Enter Column Name box, entering Amy in the Enter Value box, and clicking the Search button. The result looks like Figure 3-14.

click to expand
Figure 3-14: Result of clicking the Search button for Name="Amy"

Now using the Select method, you can also sort the data of a DataTable. As you can see from Figure 3-14, the Sort Section has a DESC check box and an Enter Column Name text box. If the DESC check box is clicked, rows will be sorted in descending order; otherwise, the default order is ascending. A filter followed by values ASC and DESC sorts the rows filtered using the Select method.

Listing 3-21 shows the code written on the Sort button click event handler. As you can see, first the code builds a filter string, checking whether the DESC check box is checked. After that it simply calls the Select method with the filter string and creates a new DataTable from the rows returned by the Select method and binds the new DataTable to the grid.

Listing 3-21: Sorting Data Using the Select Method

start example
 Private Sub SortBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles SortBtn.Click     Dim sortTable As DataTable = New DataTable("SortTable")     sortTable = custTable.Clone()     Dim strSort As String = TextBox3.Text     If (CheckBox1.Checked) Then       strSort = strSort & " DESC"     Else       strSort = strSort & " ASC"     End If     Dim rows() As DataRow = _     custTable.Select(String.Empty, strSort)     Dim i As Integer     For i = 0 To rows.Length - 1 Step i + 1       Dim row As DataRow = sortTable.NewRow()       row("Name") = rows(i)("Name")       row("Address") = rows(i)("Address")       sortTable.Rows.Add(row)     Next     Dim sortdtSet As DataSet = New DataSet("SortedData")     sortdtSet.Tables.Add(sortTable)     DataGrid1.DataSource = sortdtSet.DefaultViewManager End Sub 
end example

To sort the records, you enter the column name Name and check the DESC check box. Now, if you click the Apply Sort button, you'll see the records are sorted on the name. If you uncheck the DESC check box, you'll see the rows sorted in ascending order.

The last thing to do in this example is to create the Save button. The DataSet class provides methods to read and write XML documents. The WriteXml method of DataSet saves data to an XML document. (We discuss XML and ADO.NET in more detail in Chapter 6.)

Listing 3-22 shows the Save method button click event handler. As you can see, the code calls a SaveFileDialog and reads the name and path of the file. You can browse the path where you want to save the file. After saving data, if you open the XML file, you'll see all data from the DataTable is stored in this file.

Listing 3-22: Saving a DataTable Data in an XML Document

start example
   Private Sub SaveBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles SaveBtn.Click     Dim fdlg As SaveFileDialog = New SaveFileDialog()     fdlg.Title = "Applied ADO.NET Save File Dialog"     fdlg.InitialDirectory = "c:\\"     fdlg.Filter = "XML files (*.xml)|*.*|All files (*.*)|*.*"     If fdlg.ShowDialog() = DialogResult.OK Then       dtSet.WriteXml(fdlg.FileName)     End If End Sub 
end example

The Row and Column Collections

Do you remember the Rows and Columns property of DataTable? As mentioned earlier, the Columns property of DataSet, represented by a DataColumnCollection object, stands for all columns of a DataTable. The Rows property of DataTable represents a collection of rows of a DataTable. Generally, you access the rows and columns collections through the Rows and Columns properties of a DataTable. You've already seen how to add columns and rows to a DataTable in the previous sections. In the following sections, we discuss the methods and properties.

The DataRowCollection Class

The Count property of DataRowCollection returns the total number of rows in a collection. The following code reads the total number of rows in custTable:

 Dim rows As DataRowCollection = custTable.Rows Dim counter As Integer counter = rows.Count 

The Clear method of DataRowCollection removes all the rows in a collection and sets the Rows count to zero:

 Dim rows As DataRowCollection = custTable.Rows rows.Clear() 

The Remove and RemoveAt methods remove a specified DataRow based on the DataRow and its index, respectively.

The Contains method looks for a primary key value in the collection. This method returns True if a collection finds the specified value in the primary key column. The Contains method takes an argument of an object type.

The Find method is one more useful method of DataRowCollection; it searches for a row in the collection. Similar to the Contains method, the Find method also takes an argument of object type, which means Find can be used to find any types of values.

Listing 3-23 checks if the row collection's primary key column contains value 1002. If it does, it uses the Find method, which returns a DataRow and calls the DataRowCollection.Remove method to remove the row from the collection.

Listing 3-23: Using the Contains, Find, and Remove Method of DataRowCollection

start example
 Dim rows As DataRowCollection = custTable.Rows Dim row As DataRow If rows.Contains(1002) Then    row = rows.Find(1002)    rows.Remove(row)    MessageBox.Show("Row Removed") Else    MessageBox.Show("Row not found") End If 
end example

As you saw in earlier sections, the Add method of DataRowCollection adds a new row at the end of a collection. But what if you want to insert a row between two rows? The InsertAt method does the trick for you and inserts a row at the specified index, and the row at the index becomes the next row. Listing 3-24 inserts and removes rows at the specified index using the InsertAt and RemoveAt methods.

Listing 3-24: Using the InsertAt and RemoveAt Methods

start example
 Dim newRow As DataRow newRow = custTable.NewRow() newRow("id") = 1005 newRow("Address") = "New Address" newRow("Name") = "New Name" rows.InsertAt(newRow, 3) If (Not rows(1).IsNull("id")) Then    rows.RemoveAt(1) End If 
end example

The DataColumnCollection Class

Similar to DataRowCollection, the Count property of DataColumnCollection returns the number of columns in a collection. You already saw in previous samples that you can use the Add method to add a new column to a collection.

You can use the CanRemove method to find out if a column can be removed from a collection. It returns True if a column can be removed from a collection; otherwise, it returns False. This method performs several operations to make sure the column exists in the table and it's not involved in a constraint or relation. The Contains method checks if a column with a given name exists in the collection.

The Remove and RemoveAt methods remove a column from a collection. The Remove method takes a parameter of type DataColumn or a name of the column. Listing 3-25 uses some of these methods.

Listing 3-25: Removing a Column from a Collection

start example
 Dim cols As DataColumnCollection = custTable.Columns Dim str As String str = cols.Count.ToString() MessageBox.Show(str) If cols.Contains("Name") Then   If cols.CanRemove(cols("Name")) Then     cols.Remove("Name")   End If End If 
end example

The RemoveAt method removes a column at the given index. The Clear method removes all the columns from a column collection. The following codes uses these two methods:

 cols.RemoveAt(2) cols.Clear() 

The DataRow States and Versions

A DataRow changes its states as you work on it. The RowState property of DataRow represents the state of a row, which is a type of DataRowState enumeration. The DataRowState enumeration has five members:

  • The Added state means the row has been added to a collection and the DataRow.AcceptChanges method hasn't been called yet.

  • The Deleted state means the row was deleted using DataRow.Delete.

  • The Detached state means the row has been created but not added to a collection.

  • The Modified state means the row has been changed, and DataRow.AcceptChanges has not been called yet.

  • The Unchanged state means that DataRow.AcceptChanges has been called but the row wasn't changed.

You can examine the state of a DataRow after any operation on the row. Listing 3-26 uses the RowState property to determine the state of a row.

Listing 3-26: Determining the State of a Row

start example
 Dim row As DataRow row = custTable.NewRow() MessageBox.Show(row.RowState.ToString()) row("id") = 1007 row("Name") = "Noak " row("Address") = "Tame Lack, NJ" custTable.Rows.Add(row) MessageBox.Show(row.RowState.ToString()) custTable.Rows.Remove(row) custTable.AcceptChanges() 
end example

The DataRowView is one more important class you should know when working with tables and rows. You can create a DataRowView that corresponds to a DataRow, which you can then bind to data-bound controls directly.

A DataRowView class represents a customized view of a DataRow, which can be bound to data-bound controls such as a DataGrid. When you work with a DataRow (update data of a DataRow), the DataRow has different versions. These states are represented by a DataRowVersion enumeration, which has four values: Current, Default, Proposed , and Original. The version of a DataRow changes when you call the BeginEdit, CancelEdit, and EndEdit methods of DataRow as well as the AcceptChanges and RejectChanges methods of DataTable.

After calling DataRow.BeginEdit and changing the value of a row, both the Current and Proposed values become available. After calling DataRow.CancelEdit, the Proposed value isn't available. After a call of DataRow.EndEdit, the Current value becomes the Proposed value, and the Proposed value becomes unavailable. The call of DataRow.AcceptChanges means the original value becomes identical to the Current value. A call of DataTable.RejectChanges means the Proposed value is removed. The version becomes Current.

The DataView property of DataRowView class returns a DataView object for a row, which can be bound to data-bound controls. The IsEdit and IsNew properties represent whether a row is in edit mode or new mode, respectively. The DataRow property returns the DataRow being viewed, and the RowVersion property returns the version of a row.

The BeginEdit method starts editing a row, and the CancelEdit method cancels editing. The Delete method deletes a row, and the EndEdit method ends the editing. Listing 3-27 starts editing a row and later calls the CancelEdit and EndEdit methods, followed by the AcceptChanges method.

Listing 3-27: Editing Rows Using BeginEdit, CancelEdit, and EndEdit Methods

start example
   Dim row As DataRow     row = custTable.NewRow()     MessageBox.Show(row.RowState.ToString())     row("id") = 1007     row("Name") = "Noak "     row("Address") = "Tame Lack, NJ"     custTable.Rows.Add(row)     MessageBox.Show(row.RowState.ToString())     custTable.Rows.Remove(row)     custTable.AcceptChanges()     ' Start Editing the Row     custTable.Rows(0).BeginEdit()     custTable.Rows(0)("id") = 1008     custTable.Rows(0)("Name") = "New Name"     custTable.Rows(0)("Address") = "New Address"     ' Find out DataRowVersion     If custTable.Rows(0).HasVersion(DataRowVersion.Proposed) Then       MessageBox.Show(DataRowVersion.Proposed.ToString())     End If     If custTable.Rows(0).HasVersion(DataRowVersion.Original) Then       MessageBox.Show(DataRowVersion.Original.ToString())     End If     ' Cancel the edit.     custTable.Rows(0).CancelEdit()     ' BeginEdit again     custTable.Rows(0).BeginEdit()     custTable.Rows(0)("id") = 1008     custTable.Rows(0)("Name") = "New Name"     custTable.Rows(0)("Address") = "New Address"     custTable.Rows(0).EndEdit()     custTable.Rows(0).AcceptChanges() DataGrid1.DataSource = custTable 
end example




Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214

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