Part III: Working with Data Off Line--The ADO.NET
Using DataSet Objects
In some ways, the DataSet and its child objects resemble matryoshka—those nested wooden Russian dolls. A DataSet contains DataTable objects and DataRelation objects. A DataTable contains DataRow, DataColumn, and Constraint objects.
Rather than try to explain how each object is used one at a time, I will cover the basic functionality of the DataSet in this chapter by working through simple examples. Along the way, you'll learn a little about each of the other objects I just mentioned.
Creating a DataSet Object
Instantiating a DataSet object in code is straightforward. You simply use the New keyword in your language of choice. The DataSet object has one optional constructor that you can use to set the DataSetName property of the DataSet:
Visual Basic .NET
Dim ds As New DataSet("DataSetName") Console.WriteLine(ds.DataSetName)
Visual C# .NET
DataSet ds = new DataSet("DataSetName"); Console.WriteLine(ds.DataSetName); The DataSet class and the classes contained in DataSet objects—DataTable, DataColumn, DataRow, Constraint, and DataRelation—reside in the System.Data namespace.
Examining the Structure Created by Calling DataAdapter.Fill
In Chapter 5, you learned how to fetch the results of a query into a DataSet using the DataAdapter object's Fill method, as shown here:
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _ "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Customers")
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " + "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Customers");
Before we examine the results of the query, let's take a quick look at the structure that the DataAdapter created to store those results.
DataTable Object
The DataAdapter stores the results of your query in a DataTable, an object similar to the DataReader object we discussed in Chapter 4. You can use either object to examine the results of a query. Each object exposes the results as a collection of rows and columns.
As you might recall, the DataReader is tuned for performance. It lets you tear through the results of your query quickly but offers little functionality beyond that. You already know that you can't modify the data in the DataReader and that you can't move back to a previous row. The DataTable is designed for more durable data and thus provides more robust functionality than the DataAdapter. You can modify, sort, and filter the data in a DataTablefeatures not available through the DataReader.
In order to handle this more durable data, the DataTable exposes a more durable structure for the data it contains. The DataTable object has a Columns property that returns a collection of DataColumn objects. Each DataColumn corresponds to a column in the results of your query.
This structure will be familiar to programmers with DAO and ADO experience because the Recordset object in DAO and ADO has a Fields property that returns a collection of Field objects.
DataColumn Object
Simply put, DataColumn objects define the schema for your DataTable. When you use the DataAdapter object's Fill method to create a new DataTable, the DataAdapter also creates a DataColumn object for each column in the results of your query. The new DataColumn objects that the DataAdapter creates will have only their most basic properties set—Name, Ordinal, and DataType.
Here's a quick sample that displays basic information about the DataColumn objects created by calling DataAdapter.Fill:
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _ "FROM Orders" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Orders") Dim tbl As DataTable = ds.Tables(0) Console.WriteLine("Column information for " & tbl.TableName & _ " DataTable") Dim col As DataColumn For Each col In tbl.Columns Console.WriteLine(vbTab & col.ColumnName & " - " & _ col.DataType.ToString) Next col
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " + "FROM Orders"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Orders"); DataTable tbl = ds.Tables[0]; Console.WriteLine("Column information for " + tbl.TableName + " DataTable"); foreach (DataColumn col in tbl.Columns) Console.WriteLine("\t" + col.ColumnName + " - " + col.DataType.ToString()); There's a lot more information available in the DataColumn object than name, position, and data type. But for now, we'll take a quick break from the DataColumn object and learn how to examine the data that the DataAdapter placed in our new DataTable.
Examining the Data Returned by a DataAdapter
At this point, the DataTable takes a sharp right turn from previous object data access models. The Recordset object in both ADO and DAO, RDO's rdoResultset object, and ADO.NET's DataReader all support the concept of a "current row" of data. Each object lets you examine the results of your query one row at a time. The Recordset and rdoResultset objects let you control the currently available row using methods such as MoveFirst, MovePrevious, MoveNext, and MoveLast.
The ADO.NET DataTable takes a different approach, which is more in line with XML documents in which you can access any node in the tree at any given time. With the DataTable, all rows are available all the time—24 hours a day, 7 days a week, 365 (and change) days a year, and...well, you get the general idea.
The DataTable class exposes a Rows property that returns the collection of DataRow objects available in your DataTable. Now let's look at how you can use DataRow objects to examine the results of your query.
DataRow Object
The DataRow object lets you examine and modify the contents of a row in your DataTable. To assign a DataRow object to a particular row in the DataTable, you use the DataTable object's Rows property. This property returns a DataRowCollection object that contains a collection of DataRow objects. Like most collection objects, the DataRowCollection object lets you specify an integer to indicate the item you want to access.
The following code snippet uses the DataAdapter object's Fill method to fetch the results of a query into a new DataTable object. The code then assigns the first row returned to a DataRow object and displays the contents of two of the columns in the row.
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _ "FROM Orders" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Orders") Dim tbl As DataTable = ds.Tables(0) Dim row As DataRow = tbl.Rows(0) Console.WriteLine("OrderID = " & row("OrderID")) Console.WriteLine("CustomerID = " & row("CustomerID"))
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " + "FROM Orders"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Orders"); DataTable tbl = ds.Tables[0]; DataRow row = tbl.Rows[0]; Console.WriteLine("OrderID = " + row["OrderID"]); Console.WriteLine("CustomerID = " + row["CustomerID"]); As you can see, once you set the DataRow object to a row in the DataTable, accessing the value of a particular column is similar to accessing data in a DataReader. The DataRow object has a parameterized Item property that returns the contents of the specified column. You can supply a column name, as shown in the preceding code snippet, or an integer that represents the column's ordinal position in the DataTable. As with the DataReader, using an index-based lookup will return data more quickly than a string-based lookup. I've used column names to make the code snippet easier to follow.
Examining the Data Stored in a DataRow
What if you want to write a more generic routine to display the contents of a DataRow? Let's say you want to write a procedure that accepts a DataRow object and displays the column names and values contained in that DataRow.
If you were writing code using the DataReader object, you could check its FieldCount property to determine the number of columns. You could then use the GetName and Item properties to retrieve the name and value for each column. However, the DataRow does not have a counterpart to the DataReader object's FieldCount property.
Instead, the DataRow object exposes a Table property. This property returns the DataTable that contains the DataRow. You can use this property to get back to the DataTable to retrieve the total number of columns as well as the name of each column. Here's a quick sample that uses this property of the DataRow object to display the contents of a DataRow, including column names:
Visual Basic .NET
Private Sub DisplayRow(ByVal row As DataRow) Dim tbl As DataTable = row.Table Dim col As DataColumn For Each col In tbl.Columns Console.WriteLine(vbTab & col.ColumnName & ": " & row(col)) Next col End Sub
Visual C# .NET
static void DisplayRow(DataRow row) { DataTable tbl = row.Table; foreach (DataColumn col in tbl.Columns) Console.WriteLine("\t" + col.ColumnName + ": " + row[col]); } The preceding code snippet demonstrates a third way to examine the contents of a particular column. The DataRow object's Item method accepts a DataColumn object. As of this writing, fetching the contents of a row by supplying a DataColumn slightly outperforms (by about 6 percent) ordinal-based lookups.
Examining the DataRow Objects in a DataTable
You can loop through the DataRow objects in a DataTable as easily as with any other collection in the .NET Framework. You use a For loop or a For Each loop in your language of choice. The following code snippet loops through the contents of the DataTable created by calling DataAdapter.Fill and relies on the DisplayRow procedure developed in the previous snippet.
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _ "FROM Orders" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Orders") Dim tbl As DataTable = ds.Tables(0) Dim row As DataRow Dim intCounter As Integer For Each row In tbl.Rows intCounter += 1 Console.WriteLine("Contents of row #" & intCounter) DisplayRow(row) Next row
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " + "FROM Orders"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Orders"); DataTable tbl = ds.Tables[0]; int intCounter; foreach (DataRow row in tbl.Rows) { intCounter++; Console.WriteLine("Contents of row #" + intCounter); DisplayRow(row); }
Validating Data in Your DataSet
Databases offer different mechanisms that you can use to ensure that the data in your database is valid. The sample Northwind database has many rules and constraints defined. The CustomerID column in the Customers table must be populated with a string of up to five characters, and that value must be unique within the table. The Orders table generates a new OrderID value for each row and requires that the CustomerID value for each row refer to an existing entry in the Customers table.
Sometimes you'll want to apply similar rules to validate data in your application before submitting changes to your database. For example, let's say you're shopping on line and reach the page where you purchase the items in your basket. Most Web sites will make sure you've entered information into each of the required fields before they submit your order information to the appropriate database.
This type of logic might seem redundant because the database probably has similar validation rules defined. However, adding validation rules to your application can improve its performance. If a user fails to enter a credit card number, either by accident or in the hope that the system programmers were extremely lazy, the code for the Web page can easily determine that it can't successfully submit the order without having to contact the database. The other benefits of this approach are a slight reduction of network traffic and a lighter load on your database.
The ADO.NET DataSet offers many of the same data validation mechanisms available in database systems. You can separate these validation mechanisms, also called constraints, into two categories—column-level restrictions and table-level restrictions.
Validation Properties of the DataColumn
The DataColumn object exposes a number of properties that you can use to validate your data.
ReadOnly
The simplest way to ensure that your data is valid is to not let users modify it. If you want to make the data in a DataColumn read-only, set the ReadOnly property of the DataColumn to True.
AllowDBNull
Some database columns require values, while others accept empty, or null, values. The DataColumn object exposes an AllowDBNull property that you can set to control whether the column in your DataSet accepts null values.
MaxLength
Many databases place restrictions on the size of a string in a column. In the Customers table, for example, the CustomerID column accepts a string of up to 5 characters and the CompanyName column accepts up to 40 characters. You can place similar restrictions on a DataColumn using the MaxLength property.
Unique
The DataColumn lets you specify which values in a column are unique using the Unique property. When you set this property to True on a DataColumn, ADO.NET will examine the value stored in this column of each row in your DataTable. If you add or modify a row in your DataTable to create a duplicate value in a unique column, ADO.NET will throw a ConstraintException.
The DataTable Object's Constraints Collection
You can also validate data in your DataSet by setting properties of the DataTable object. The ADO.NET object model includes two classes that you can use to define constraints in a DataTable. These classes, UniqueConstraint and ForeignKeyConstraint, are derived from the Constraint class. The DataTable exposes a Constraints property that you can use to add to, modify, or examine the constraints on the DataTable.
UniqueConstraints
If you set the Unique property of a DataColumn to True, you've defined a unique constraint in the DataTable that contains that column. At the same time, you've also added a UniqueConstraint object to the DataTable object's Constraints collection. Setting the Unique property of a DataColumn is simpler than creating a new UniqueConstraint in a DataTable object's Constraints collection. However, there are times when you'll want to explicitly create a UniqueConstraint, such as when you need to make sure that the combinations of values from multiple columns are unique.
PrimaryKey
A primary key is a special type of unique constraint. The ADO.NET DataRowCollection object has a Find method that you can use to locate a row in your DataTable by the value or values in its primary key column, as shown here. (I'll discuss the Find method in detail in Chapter 8.)
row = MyTable.Rows.Find("ALFKI") A DataTable can have multiple unique constraints but can contain at most one primary key. You can set or examine a DataTable object's primary key using its PrimaryKey property.
ForeignKeyConstraint
You can also add foreign constraints to a DataTable. I described an example of a foreign key constraint just a couple pages back. Each order in the Northwind database's Orders table must have a value for its CustomerID column that is used in the Customers table. You can place similar restrictions on the data in your DataSet by creating a ForeignKeyConstraint and adding it to the table whose rows you want to validate.
You generally won't need to explicitly create a ForeignKeyConstraint. Creating a DataRelation between two DataTable objects within your DataSet creates a ForeignKeyConstraint in the process. In the next chapter, I'll discuss the DataRelation object and how you can use it to work with relational data.
 |  |
 | ADO.NET does not know what data resides in your database. Constraints you define on columns and tables within your DataSet are valid only within that DataSet. This is an important point to keep in mind. Here's why. Say you define a UniqueConstraint based on the CustomerID column in your DataTable. If you add a row with a CustomerID of ZZZZZ, ADO.NET will throw an exception only if another row in your DataTable has that same value for the CustomerID column. Foreign key constraints are enforced in a similar fashion. If you define a foreign key on your orders DataTable object based on the CustomerID column in your orders and customers DataTable objects, ADO.NET will let you add only orders with a value for the CustomerID column that appears in your customers DataTable. ADO.NET will throw an exception if you add a new order with a CustomerID that is used in your database but that does not reside in your customers DataTable. |
 |  |
Retrieving Schema Information Using DataAdapter.FillSchema
Validating data takes time. In many scenarios, you don't want to set validation properties on your DataSet, so the DataAdapter does not set validation properties on DataColumn objects or add constraints to a DataTable object's Constraints collection when it creates the DataTable in the DataAdapter object's Fill method unless you make an explicit request.
There are two ways to tell the DataAdapter that you want to retrieve this schema information from your database when adding columns to your DataTableby setting the DataAdapter object's MissingSchemaAction property to AddWithKey or by calling the DataAdapter object's FillSchema method. (I covered these features of the DataAdapter in Chapter 5.)
Try This at Home, But Only at Home....
ADO.NET has a few features that you should avoid using in your applications whenever possible. Fetching schema information for your DataSet through the DataAdapter is one of them.
Using the DataAdapter to gather schema information can save time during the design process. In fact, Visual Studio .NET uses the DataAdapter to generate your DataSet objects at design time (as you'll see later in this chapter). If you're building a small sample or a proof-of-concept application, you might find that using the DataAdapter to gather schema information reduces the amount of code you have to write.
But unless your application is an ad-hoc query tool, you should know which columns your queries return, so you should have no need to use features such as DataAdapter.FillSchema in your full-blown applications.
If you ask your DataAdapter to fetch additional schema information using these features, the DataAdapter will query your database for schema information beyond the name and data type for each new DataColumn it creates. Examine any of these DataColumn objects and you'll find that the ReadOnly, AllowDBNull, MaxLength, and ReadOnly properties are set correctly.
The DataAdapter will also attempt to generate a primary key for your DataTable. This is where you pay a significant performance penalty to fetch schema information. Here's why.
The DataAdapter has to query the database to determine which table your query references, and then it has to query the database again to gather information about the primary key for that table. If no primary key is defined for your table, the DataAdapter will request information about unique indexes for the table. Once the DataAdapter has gathered this information about your table, it will examine the columns returned by your query. This ensures that if your table contains a primary key comprising two columns but the query you're using does not include both of these columns, the DataAdapter will not use this primary key in your DataTable.
| |
| The DataAdapter will also set the AutoIncrement property of new DataColumn objects. I'll cover this property briefly later in this chapter. For more in-depth information on using this property, see Chapter 11. |
| |
Creating DataTable Objects in Code
You've learned how to create DataTable objects using the Fill and FillSchema methods of the DataAdapter. You've also learned that you should create your own DataTable objects, especially if you want to validate your data using column or table-level restrictions. Now it's time to learn how to build DataTable objects using code.
Creating a DataTable Object
You can create a DataTable object the same way that you create a DataSet object. The DataTable has an optional constructor that you can use to set the TableName property of the new DataTable object, as shown here:
Visual Basic .NET
Dim tbl As New DataTable("TableName") Console.WriteLine(tbl.TableName)
Visual C# .NET
DataTable tbl = new DataTable("TableName"); Console.WriteLine(tbl.TableName);
Adding Your DataTable to a DataSet Object's Tables Collection
Once you've created a DataTable, you can add it to an existing DataSet object's Tables collection using the DataTableCollection object's Add method, as shown here:
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As New DataTable("Customers") ds.Tables.Add(tbl)
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = new DataTable("Customers"); ds.Tables.Add(tbl); That's not much code, but the always-clever developers at Microsoft have actually provided a simpler way of adding a new DataTable to a DataSet object's Tables collection by overloading the DataTableCollection object's Add method. You can create a new DataTable and add it to an existing DataSet object's Tables collection in a single call, as shown in the following code snippet.
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As DataTable = ds.Tables.Add("Customers")
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = ds.Tables.Add("Customers"); You can determine whether a DataTable resides within a DataSet by checking the DataTable object's DataSet property. If the DataTable resides in a DataSet object's Tables collection, the DataSet property returns that DataSet. Otherwise, the property returns Nothing or null, depending on your language of choice. The DataSet property of the DataTable object is read-only.
It is also worth noting that a DataTable can reside in at most one DataSet. If you want to add a DataTable to multiple DataSet objects, you must use either the Copy or Clone method. The Copy method creates a new DataTable with the same structure that contains the same set of rows as the original DataTable. The Clone method creates a new DataTable with the same structure, as the Copy method does, but it creates a DataTable that contains no rows.
Adding Columns to Your DataTable
It's time to add some meat to the structure of our new DataTable. In order to store the results of a query, the DataTable needs to have columns. Earlier in the chapter, you saw how the DataAdapter can create new DataColumn objects for you. Now let's create our own DataColumn objects. We can add DataColumn objects to a Table object's Columns collection using code that's nearly identical to the code we used to add a new DataTable to a DataSet object's Tables collection:
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As DataTable = ds.Tables.Add("Customers") Dim col As DataColumn = tbl.Columns.Add("CustomerID")
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = ds.Tables.Add("Customers"); DataColumn col = tbl.Columns.Add("CustomerID");
Specifying a Data Type for a DataColumn
When you create a new DataColumn, you'll also want to specify the data type that the DataColumn contains. You can use the DataType property of the DataColumn to set or check the data type that the column will contain. The DataColumn object's DataType property is read-write until you add data to the DataTable object's Rows collection.
Although the data type you select for your DataColumn will depend on the data type for that column in your database, there isn't a one-to-one mapping between database data types and DataColumn data types.
For example, Microsoft SQL Server lets you choose from a number of data types for string-based data. When you define the structure of a table in a SQL Server database, you can specify whether you want to store your string-based data as a fixed-length or variable-length string. You can also control whether SQL Server stores that data as single-byte (ANSI) or double-byte (Unicode) characters.
However, as far as ADO.NET is concerned, a string is a string. Regardless of whether the database data type is fixed-length or variable-length, single-byte or double-byte, the data type for the DataColumn is simply string. The DataType property of the DataColumn object works with .NET data types rather than database data types.
By default, DataColumn objects have a DataType property of string. The DataColumn has a constructor that allows you to specify a data type, as well as a column name, for the new column you're creating. Similarly, the DataColumnCollection object's Add method is overloaded to let you specify values for the ColumnName and DataType properties for your new DataTable object's new DataColumn, as shown here:
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As DataTable = ds.Tables.Add("Orders") Dim col As DataColumn = tbl.Columns.Add("OrderID", GetType(Integer))
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = ds.Tables.Add("Orders"); DataColumn col = tbl.Columns.Add("OrderID", typeof(int)); The data type for the DataType property is Type. The preceding code snippet demonstrates how to get a Type value that corresponds to the integer data type in each language. Visual Basic .NET and Visual C# .NET use different functions to generate types for backward-compatibility reasons. Prior to .NET, both C++ and Visual Basic included a typeof function, although the function did not return the same information in each language. As a result, Visual Basic .NET includes a GetType function to return type information.
Adding a Primary Key
I gave a bit of a lecture earlier in the chapter extolling the virtues of setting validation features of the DataColumn and DataTable in your own code rather than relying on the DataAdapter to query your database for this information. That lecture would ring hollow without an explanation of how to set validation properties on your DataColumn objects and DataTable objects.
Just before that lecture, you learned about the AllowDBNull, ReadOnly, MaxLength, and Unique properties of the DataColumn object and how to use them to validate the data stored in your columns. Setting those properties in your code is simple.
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As DataTable = ds.Tables.Add("Customers") Dim col As DataColumn = tbl.Columns.Add("CustomerID") col.AllowDBNull = False col.MaxLength = 5 col.Unique = True
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = ds.Tables.Add("Customers"); DataColumn col = tbl.Columns.Add("CustomerID"); col.AllowDBNull = false; col.MaxLength = 5; col.Unique = true; Setting the primary key for a DataTable is slightly more complicated. The PrimaryKey property contains an array of DataColumn objects. So you can't simply set the property to the name of the column or columns that you want to use for your primary key.
Some of the DataTable objects you create will rely on single columns as their primary keys; others will rely on combinations of columns. The following code snippet includes code for each scenario. The Customers table uses a single column, CustomerID, while the Order Details table uses a combination of two columns, OrderID and ProductID. In both cases, you need to create an array of DataColumn objects and assign that array to the DataTable object's PrimaryKey property:
Visual Basic .NET
Dim ds As New DataSet() 'Create the Customers DataTable. With ds.Tables.Add("Customers") .Columns.Add("CustomerID", GetType(String)) 
.PrimaryKey = New DataColumn() {.Columns("CustomerID")} End With 'Create the Order Details DataTable. With ds.Tables.Add("Order Details") .Columns.Add("OrderID", GetType(Integer)) .Columns.Add("ProductID", GetType(Integer)) .PrimaryKey = New DataColumn() {.Columns("OrderID"), _ .Columns("ProductID")} End With
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl; //Create the Customers DataTable. tbl = ds.Tables.Add("Customers"); tbl.Columns.Add("CustomerID", typeof(string)); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["CustomerID"]}; //Create the Order Details DataTable. tbl = ds.Tables.Add("Order Details"); tbl.Columns.Add("OrderID", typeof(int)); tbl.Columns.Add("ProductID", typeof(int)); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["OrderID"], tbl.Columns["ProductID"]};
| |
| When you set the primary key for your DataTable, ADO.NET automatically sets the AllowDBNull property of the DataColumn object or objects referenced in your primary key to False. |
| |
Adding Other Constraints
Primary keys are the most widely used constraint, but you can also add unique key and foreign key constraints to a DataTable. The DataTable object's Constraints collection has an overloaded Add method that you can use to add new primary key, unique key, and foreign key constraints. The following code snippet demonstrates how to use the Add method to add single-column and multiple-column unique keys as well as how to create a new foreign key constraint.
The code adds a unique key to the Customers DataTable based on the CustomerID column and adds a unique key to the Order Details DataTable based on the OrderID and ProductID columns. It also adds a foreign key constraint to the Order Details table to ensure that the value for the OrderID column matches a corresponding row in the Orders DataTable.
In each case, the code includes two ways to create the constraint. The first example explicitly creates a new constraint object and appends that item to the DataTable object's Constraints collection. It looks something like this:
tbl.Constraints.Add(New UniqueConstraint(...))
The ConstraintCollection object's Add method accepts any object that inherits from the Constraint object, so you can supply either a UniqueConstraint object or a ForeignKeyConstraint object.
The second example uses the ConstraintCollection object's Add method to create the new constraint and add it to the collection.
tbl.Constraints.Add("ConstraintName", ColumnInformation) The Add method is overloaded so that you can create new unique, primary key, or foreign key constraints. However, I generally avoid using this syntax. I prefer to explicitly create constraints because I find the resulting code easier to read. I've included both syntaxes in the following code snippet because you might encounter situations in which you want to use the overloaded Add methods that create and append your new constraint all at once.
Visual Basic .NET
Dim ds As New DataSet() 'Create the Customers DataTable. With ds.Tables.Add("Customers") .Columns.Add("CustomerID", GetType(String)) 'Add a unique key based on the CustomerID column. .Constraints.Add(New UniqueConstraint(.Columns("CustomerID")) 'or .Constraints.Add("UK_Customers", .Columns("CustomerID"), False) End With 'Create the Order Details DataTable. With ds.Tables.Add("Order Details") .Columns.Add("OrderID", GetType(Integer)) .Columns.Add("ProductID", GetType(Integer)) 'Add a unique key based on the OrderID and ProductID columns. Dim cols As New DataColumn() {.Columns("OrderID"), _ .Columns("ProductID")} .Constraints.Add(New UniqueConstraint(cols)) 'or .Constraints.Add("UK_Order Details", cols, False) 'Add a foreign key constraint based on the OrderID column that 'requires a corresponding OrderID in the Orders table. .Constraints.Add(New ForeignKeyConstraint _ (ds.Tables("Orders").Columns("OrderID"), _ .Columns("OrderID"))) 'or .Constraints.Add("FK_Order Details_Orders", _ ds.Tables("Orders").Columns("OrderID"), _ .Columns("OrderID")) End With
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl; //Create the Customers DataTable. tbl = ds.Tables.Add("Customers"); tbl.Columns.Add("CustomerID", typeof(string)); //Add a unique key based on the CustomerID column. tbl.Constraints.Add(new UniqueConstraint(tbl.Columns["CustomerID"])); //or tbl.Constraints.Add("UK_Customers", tbl.Columns["CustomerID"], false); //Create the Order Details DataTable. tbl = ds.Tables.Add("Order Details"); tbl.Columns.Add("OrderID", typeof(int)); tbl.Columns.Add("ProductID", typeof(int)); //Add a unique key based on the OrderID and ProductID columns. DataColumn[] cols = new DataColumn[] {tbl.Columns["OrderID"], tbl.Columns["ProductID"]}; tbl.Constraints.Add(new UniqueConstraint(cols)); //or tbl.Constraints.Add("UK_Order Details", cols, false); //Add a foreign key constraint based on the OrderID column that //requires a corresponding OrderID in the Orders table. tbl.Constraints.Add(new ForeignKeyConstraint (ds.Tables["Orders"].Columns["OrderID"], tbl.Columns["OrderID"])); //or tbl.Constraints.Add("FK_Order Details_Orders", ds.Tables["Orders"].Columns["OrderID"], tbl.Columns["OrderID"]);
Working with Autoincrement Columns
ADO.NET includes support for autoincrement columns through three properties of the DataColumn: AutoIncrement, AutoIncrementSeed, and AutoIncrementStep.
If you want ADO.NET to generate such autoincrement values for new rows in your DataTable, set the AutoIncrement property of your DataColumn to True, as shown here:
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As DataTable = ds.Tables.Add("Orders") Dim col As DataColumn = tbl.Columns.Add("OrderID", GetType(Integer)) col.AutoIncrement = True col.AutoIncrementSeed = -1 col.AutoIncrementStep = -1 col.ReadOnly = True
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = ds.Tables.Add("Orders"); DataColumn col = tbl.Columns.Add("OrderID", typeof(int)); col.AutoIncrement = true; col.AutoIncrementSeed = -1; col.AutoIncrementStep = -1; col.ReadOnly = true; The preceding code snippet marked the OrderID column as autoincrement, but it also set the AutoIncrementSeed and AutoIncrementStep properties to -1. I strongly recommend setting these two properties to -1 whenever you set AutoIncrement to True. Allow me to explain why.
The AutoIncrementSeed and AutoIncrementStep properties control how ADO.NET generates new values. When you're working with an empty table, ADO.NET will assign the value stored in AutoIncrementSeed to the autoincrement column for your first row. ADO.NET will use the AutoIncrementStep property to generate subsequent autoincrement values.
For example, if you set AutoIncrement to True and set both AutoIncrementSeed and AutoIncrementStep to 2, ADO.NET will generate the following values for the autoincrement column of your first five rows: 2, 4, 6, 8, 10.
This behavior changes slightly if you add rows to your DataTable by calling DataAdapter.Fill. Say you're working with a DataTable whose structure matches the Orders table in the Northwind database and you've set the AutoIncrementSeed and AutoIncrementStep properties to 5 for the OrderID DataColumn. If you add new rows to this DataTable while it's empty, those new rows will have values of 5, 10, 15, 20, and so on for the OrderID column. However, if you add rows to the DataTable from your database using DataAdapter.Fill and then add new rows using DataTable.Rows.Add, the new values you generate for the OrderID column will depend on the data you fetched from your database. ADO.NET will generate subsequent autoincrement values based on the largest autoincrement value that appears in the DataTable and the value of the AutoIncrementStep properties.
Let's say that in this example, the largest current value for the OrderID column in your DataTable is 973. If you generate a new row at this point, ADO.NET will add the value stored in the AutoIncrementStep property (5) to the largest current value in the DataTable (973) for a new OrderID of 978.
It is extremely important to keep in mind that ADO.NET is aware only of data that exists in your DataTable. It does not know what your database will generate for the next autoincrement value. I said that the largest value for the OrderID column that appears in our DataTable based on the results of our query was 973. Maybe the query fetched orders only for a particular customer:
SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE CustomerID = 'ALFKI'
The database might contain larger values for the OrderID column than the ones that appear in the DataTable. ADO.NET has no way to know, so it might generate autoincrement values for new rows in your DataTable, but those autoincrement values might already be in use in your database.
I've included this information to help you understand how the ADO.NET autoincrement features work. Armed with this information, you should be able to make intelligent decisions about when and how to generate new autoincrement values using ADO.NET.
During the development of ADO.NET and the .NET Framework as a whole, a developer asked me whether there was any way to achieve paging in a DataTable (that is, he wanted to return or display just a portion of the contents of a DataTable). Rather than try to count the rows and construct elaborate filters based on search criteria and row ordering, you can achieve paging in a much simpler way by letting ADO.NET count the rows as they're added to the DataTable, thanks to ADO.NET's autoincrement features.
Autoincrement Do's and Don'ts
Here's a quick list of autoincrement do's and don'ts:
-
Do Use the ADO.NET autoincrement features.
-
Don't Submit autoincrement values that ADO.NET generates to your database. The values that ADO.NET generates are merely placeholders. Let the database generate the real new values. Chapter 11 includes examples that show how to let the database generate values as well as how to fetch these new values into the corresponding rows in your DataTable.
-
Don't Display autoincrement values for new rows that have not been submitted to the database. The database will probably generate different values from the ones ADO.NET generates. The user of your application might not be aware that the autoincrement value that ADO.NET generated for the new row is just a placeholder. If your application is an order-entry system, do you really want to take the chance that the user, who is taking orders over the phone from customers, might mistakenly assume that the value ADO.NET generated for the Order ID is accurate—and then read that value to the customer?
-
Do Set the AutoIncrementSeed and AutoIncrementStep properties to -1. Doing so ensures that you're generating placeholder values that do not appear in your database. Even if you display this value in your application, it will prevent users from mistakenly assuming that the autoincrement values that ADO.NET generates will be the same as the ones the database will generate.
The following code snippet fills a DataTable based on the results of a simple query. Before filling the table, the code adds an autoincrement column to the table. Because the query does not return data for this autoincrement column, ADO.NET generates new values for the column for each row returned by the query.
The code snippet uses the DataView and DataRowView objects. We'll discuss these objects in Chapter 8, but in this sample their use should be self-explanatory. Once we've filled the DataTable based on the results of the query, we'll use the DataView to view just one "page" of the DataTable and write the contents of that page to the screen.
Yes, I know I used the FillSchema method to create the structure of my DataTable, but I used it only to compress the code snippet. I would not have used it in a real-world application. Scout's honor.
Visual Basic .NET
Dim ds As New DataSet() Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName " & _ "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) da.FillSchema(ds, SchemaType.Source, "Customers") Dim tbl As DataTable = ds.Tables("Customers") Dim col As DataColumn = tbl.Columns.Add("RowID", GetType(Integer)) col.AutoIncrement = True col.AutoIncrementSeed = 1 col.AutoIncrementStep = 1 da.Fill(ds, "Customers") Dim vue As New DataView(tbl) Dim intPageSize As Integer = 10 Dim intPageNum As Integer = 3 vue.RowFilter = "RowID > " & (intPageNum - 1) * intPageSize & _ " AND RowID <= " & intPageNum * intPageSize Dim row As DataRowView Dim intCounter As Integer For intCounter = 0 to vue.Count - 1 row = vue(intCounter) Console.WriteLine(row("RowID") & vbTab & row("CustomerID") & _ vbTab & row("CompanyName")) Next intCounter
Visual C# .NET
DataSet ds = new DataSet(); string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName " + "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.FillSchema(ds, SchemaType.Source, "Customers"); DataTable tbl = ds.Tables["Customers"]; DataColumn col = tbl.Columns.Add("RowID", typeof(int)); col.AutoIncrement = true; col.AutoIncrementSeed = 1; col.AutoIncrementStep = 1; da.Fill(ds, "Customers"); DataView vue = new DataView(tbl); int intPageSize = 10; int intPageNum = 3; vue.RowFilter = "RowID > " + (intPageNum - 1) * intPageSize + " AND RowID <= " + intPageNum * intPageSize; DataRowView row; for (int intCounter = 0; intCounter < vue.Count; intCounter++) { row = vue[intCounter]; Console.WriteLine(row["RowID"] + "\t" + row["CustomerID"] + "\t" + row["CompanyName"]); } Should you use this code to achieve paging in your Web applications? Probably not. It's not very scalable. We'll discuss other ways to achieve paging in Chapter 14. I've included this sample strictly to demonstrate that you can solve some interesting problems using the ADO.NET autoincrement features.
Adding an Expression-Based Column
Database administrators generally avoid including data in their databases that can be derived from data that already exists within the database. For example, the Order Details table in the Northwind database contains columns that store the unit price and quantity for each line item in an order, but it does not contain a column for the total cost of the line item.
Users don't care whether the total cost of the line item is stored in the database or not, as long as they can see the total cost of the line item.
Most databases support expressions in their query language so that you can include calculated columns in the results of your query. If you want the database to calculate and return the total cost of a line item in the result set, you could use the following query:
SELECT OrderID, ProductID, UnitPrice, Quantity, UnitPrice * Quantity AS ItemTotal FROM [Order Details]
If you fill a DataTable with the results of this query, you'll have a column that contains the results of the desired expression. But if you change the contents of the UnitPrice or Quantity column in a row in your DataTable, the contents of the calculated column will remain unchanged. That's because the definition for the calculated column appears in the query itself. The database performs the actual calculation, and you simply retrieve the results of the query. Once you've retrieved the results of the query, the contents of the calculated column will not change.
ADO.NET lets you create expression-based DataColumn objects. Rather than including an expression such as the preceding one into your query, you can set the Expression property of a DataColumn to an expression. When you examine the contents of the column, ADO.NET will evaluate the expression and return the results. You can then modify an order item by changing the unit price or number of units in the order, and when you check the contents of the item total column, you'll see that the column value has been recalculated to include the change you've made.
The following code snippet adds a column to our order detail DataTable that will contain the total cost of the order item:
Visual Basic .NET
Dim ds As New DataSet() Dim tbl As DataTable = ds.Tables.Add("Order Details") tbl.Columns.Add("Quantity", GetType(Integer)) tbl.Columns.Add("UnitPrice", GetType(Decimal)) tbl.Columns.Add("ItemTotal", GetType(Decimal), _ "Quantity * UnitPrice")
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl = ds.Tables.Add("Order Details"); tbl.Columns.Add("Quantity", typeof(int)); tbl.Columns.Add("UnitPrice", typeof(Decimal)); tbl.Columns.Add("ItemTotal", typeof(Decimal), "Quantity * UnitPrice");
| |
| This isn't actually the proper way to calculate the total cost of a line item for an order in the Northwind database. The Order Details table contains a Discount column that is used to compute discounts on line items. The column accepts values between 0 and 1. If a line item has a value of .25 in the discount column, there's a 25 percent discount on the total cost of the line item. Thus, the true way to calculate the total cost of a line item is as follows:
Quantity * UnitPrice * (1 - Discount) I simplified the process in the earlier code snippets because I wanted to focus on creating calculated columns rather than have to digress about the structure of the Northwind database. |
| |
The Expression property supports a wide variety of functions, including aggregate functions that can reference data in other DataTable objects in the DataSet. We'll create expression-based columns that use aggregate functions in the next chapter when we discuss working with relational data. For more information on the list of functions that the Expression property supports, see the MSDN documentation on the Expression property.
Creating DataTable Objects for the Customers, Orders, and Order Details Tables
We've covered a lot of features of the DataSet, DataTable, and DataColumn objects. Now let's tie all of them together into a single DataSet. The following code snippet creates a new DataSet that contains three DataTable objects. (Smaller snippets shown earlier in the chapter demonstrated how to create pieces of each of these DataTable objects.) In the process, the code sets properties on the DataColumn objects (including DataType, AllowDBNull, and AutoIncrement) and creates both primary key and foreign key constraints.
The DataSet that this code snippet creates mirrors the one that we created in Chapter 2 when we used the Data Form Wizard, except for a few key differences. This code adds a couple more refined settings to our DataSet and sets the AutoIncrementStep and AutoIncrementSeed values on the OrderID column in the Orders DataTable to obtain more control over the autoincrement values that ADO.NET generates for new orders. It also sets the MaxLength property of the string-based columns.
The code creates foreign key constraints but does not populate the DataSet object's Relations collection. (I'll cover the DataRelation object in the next chapter.)
The Data Form Wizard creates a strongly typed DataSet. I'll cover strongly typed DataSet objects in Chapter 9. For now, think of a strongly typed DataSet as a class that has all the features of a DataSet but also exposes structures such as DataTable objects and DataColumn objects as well-defined properties rather than as simple collections. The following code creates a DataSet that's not strongly typed:
Visual Basic .NET
Dim ds As New DataSet() Dim col As DataColumn Dim fk As ForeignKeyConstraint 'Create the customers table. With ds.Tables.Add("Customers") col = .Columns.Add("CustomerID", GetType(String)) col.MaxLength = 5 col = .Columns.Add("CompanyName", GetType(String)) col.MaxLength = 40 col = .Columns.Add("ContactName", GetType(String)) col.MaxLength = 30 col = .Columns.Add("Phone", GetType(String)) col.MaxLength = 24 .PrimaryKey = New DataColumn() {.Columns("CustomerID")} End With 'Create the orders table. With ds.Tables.Add("Orders") col = .Columns.Add("OrderID", GetType(Integer)) col.AutoIncrement = True col.AutoIncrementSeed = -1 col.AutoIncrementStep = -1 col.ReadOnly = True col = .Columns.Add("CustomerID", GetType(String)) col.AllowDBNull = False col.MaxLength = 5 .Columns.Add("EmployeeID", GetType(Integer)) .Columns.Add("OrderDate", GetType(DateTime)) .PrimaryKey = New DataColumn() {.Columns("OrderID")} End With 'Create the order details table. With ds.Tables.Add("Order Details") .Columns.Add("OrderID", GetType(Integer)) .Columns.Add("ProductID", GetType(Integer)) .Columns.Add("UnitPrice", GetType(Decimal)) col.AllowDBNull = False col = .Columns.Add("Quantity", GetType(Integer)) col.AllowDBNull = False col.DefaultValue = "1" col = .Columns.Add("Discount", GetType(Decimal)) col.DefaultValue = "0" .Columns.Add("ItemTotal", GetType(Decimal), _ "UnitPrice * Quantity * (1 - Discount)") .PrimaryKey = New DataColumn() {.Columns("OrderID"), _ .Columns("ProductID")} End With 'Create the foreign key constraints. fk = New ForeignKeyConstraint(ds.Tables("Customers").Columns("CustomerID"), _ ds.Tables("Orders").Columns("CustomerID")) ds.Tables("Orders").Constraints.Add(fk) fk = New ForeignKeyConstraint(ds.Tables("Orders").Columns("OrderID"), _ ds.Tables("Order Details").Columns("OrderID")) ds.Tables("Order Details").Constraints.Add(fk)
Visual C# .NET
DataSet ds = new DataSet(); DataTable tbl; DataColumn col; ForeignKeyConstraint fk; //Create the customers table. tbl = ds.Tables.Add("Customers"); col = tbl.Columns.Add("CustomerID", typeof(string)); col.MaxLength = 5; col = tbl.Columns.Add("CompanyName", typeof(string)); col.MaxLength = 40; col = tbl.Columns.Add("ContactName", typeof(string)); col.MaxLength = 30; col = tbl.Columns.Add("Phone", typeof(string)); col.MaxLength = 24; tbl.PrimaryKey = new DataColumn[] {tbl.Columns["CustomerID"]}; //Create the orders table. tbl = ds.Tables.Add("Orders"); col = tbl.Columns.Add("OrderID", typeof(int)); col.AutoIncrement = true; col.AutoIncrementSeed = -1; col.AutoIncrementStep = -1; col.ReadOnly = true; col = tbl.Columns.Add("CustomerID", typeof(string)); col.AllowDBNull = false; col.MaxLength = 5; tbl.Columns.Add("EmployeeID", typeof(int)); tbl.Columns.Add("OrderDate", typeof(DateTime)); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["OrderID"]}; //Create the order details table. tbl = ds.Tables.Add("Order Details"); tbl.Columns.Add("OrderID", typeof(int)); tbl.Columns.Add("ProductID", typeof(int)); col = tbl.Columns.Add("UnitPrice", typeof(Decimal)); col.AllowDBNull = false; col = tbl.Columns.Add("Quantity", typeof(int)); col.AllowDBNull = false; col.DefaultValue = 1; col = tbl.Columns.Add("Discount", typeof(Decimal)); col.DefaultValue = 0; tbl.Columns.Add("ItemTotal", typeof(Decimal), "UnitPrice * Quantity * (1 - Discount)"); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["OrderID"], tbl.Columns["ProductID"]}; //Create the foreign key constraints. fk = new ForeignKeyConstraint(ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]); ds.Tables["Orders"].Constraints.Add(fk); fk = new ForeignKeyConstraint(ds.Tables["Orders"].Columns["OrderID"], ds.Tables["Order Details"].Columns["OrderID"]); ds.Tables["Order Details"].Constraints.Add(fk);
Modifying the Contents of a DataTable
You now know how to create DataSet, DataTable, and DataColumn objects, and you know how to use a DataAdapter to store the results of a query into DataTable objects. You also know how to examine the contents of a DataTable. Now let's look at how to add, modify, and delete DataRow objects.
Adding a New DataRow
Now that we have a DataSet, let's add some data to it. In Chapter 5, you learned how to use DataAdapter objects to fill a DataTable with data from a database. You can also load data from an XML file, a feature we'll examine in Chapter 12. For now, we'll focus on loading data on a row-by-row basis.
Each DataTable object has a Rows property that returns a DataRowCollection object, which contains a collection of DataRow objects. As with most collections, you can use the DataRowCollection object's Add method to add a new object to the collection. However, DataRow objects differ from other ADO.NET objects in how you create them.
Let's say you want to programmatically add 10 DataRow objects to a DataTable that contains 10 DataColumn objects. To add a row to the table, you assign values to each of the columns. But how does the DataRow determine its structure—which columns it contains? The DataTable object has a NewRow method that returns a new DataRow object that contains information about each of the columns in the table.
Visual Basic .NET
Dim row As DataRow = ds.Tables("Customers").NewRow row("CustomerID") = "ALFKI"
Visual C# .NET
DataRow row = ds.Tables["Customers"].NewRow(); row["CustomerID"] = "ALFKI";
Once you've created your new DataRow, you can populate the various columns using its Item property. You can also use the Item property to examine the contents of a column in your row. The Item property is the default property of the DataRow object, so you don't even need to explicitly call Item in order to use it. To set the value of a column in a DataRow, you supply the name of the column (or its index or the DataColumn itself) and then assign the desired value.
The NewRow method of the DataTable creates a new row, but it does not add that row to the DataTable. Generally speaking, you don't want to add your new row as soon as you've created it because at that point it's empty. The values in the columns are set to the appropriate default values or to Null if they don't have defaults. By creating the new DataRow but not adding it to the Rows collection, you can assign values to your columns before making the new DataRow part of your DataTable. The CustomerID column of our Customers table does not accept Null values but does not have a default value. Say you have a Customers DataTable that has a primary key based on the CustomerID column. If you try to add a new Customers row to the table without assigning a value to the CustomerID column, you'll generate an exception.
Once you've supplied values for all the desired columns in your new row and you're ready to add it to the DataTable, you use the Add method of the DataRowCollection and supply your new row, as shown here:
Visual Basic .NET
Dim row As DataRow = ds.Tables("Customers").NewRow row("CustomerID") = "ALFKI" ... ds.Tables("Customers").Rows.Add(row)
Visual C# .NET
DataRow row = ds.Tables["Customers"].NewRow(); row["CustomerID"] = "ALFKI"; ... ds.Tables["Customers"].Rows.Add(row);
The DataTable object offers a second way to add a new row to the table: the LoadDataRow method. To use this method, you supply an array of values in the first parameter. The items in the array correspond to columns in the table. The second parameter of the LoadDataRow method, AcceptChanges, lets you control the value of the RowState property of the new DataRow. Passing a value of False into this parameter, as shown in the following code snippet, causes the new row to have a RowState of Added, just as if you'd added the row by using DataTable.NewRow and Rows.Add, as in the earlier examples.
Visual Basic .NET
Dim aValues As Object() = {"ALFKI", "Alfreds Futterkiste", _ "Maria Anders", "030-0074321"} ds.Tables("Customers").LoadDataRow(aValues, False)
Visual C# .NET
object[] aValues = {"ALFKI", "Alfreds Futterkiste", "Maria Anders", "030-0074321"}; ds.Tables["Customers"].LoadDataRow(aValues, false); When you submit changes to your database by calling the Update method of the DataAdapter object, the DataAdapter examines the RowState of each DataRow to determine how to update the database—by modifying an existing row, adding a new row, or deleting an existing row. If you pass a value of True to the second parameter in LoadDataRow, the new DataRow will have a RowState of Unmodified, which means that the row does not contain a pending change that the DataAdapter would submit to the database. We'll discuss the DataRow object's RowState property and updating your database in more detail in Chapter 10.
Modifying an Existing Row
There are three ways to modify the contents of a row programmatically. Let's start with the simplest.
Once you have a DataRow object, you can set the value of a column using the DataRow object's Item property. Earlier in the chapter, you saw how to use this property to check the contents of a column. The property is read/write, so you can also use it to set the value of a column. The code snippets that follow use the Rows collection's Find method to locate a row in the Customers DataTable and then change the values in the CompanyName and ContactName columns. We'll discuss the Find method in more detail in Chapter 8. For now, consider this a preview.
Visual Basic .NET
Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ANTON") If rowCustomer Is Nothing Then 'Customer not found! Else rowCustomer("CompanyName") = "NewCompanyName" rowCustomer("ContactName") = "NewContactName" End If
Visual C# .NET
DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ANTON"); if (rowCustomer == null) //Customer not found! else { rowCustomer["CompanyName"] = "NewCompanyName"; rowCustomer["ContactName"] = "NewContactName"; } The second way to update a row is similar to the first, except that you add calls to the DataRow object's BeginEdit and EndEdit methods.
Visual Basic .NET
Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ANTON") If rowCustomer Is Nothing Then 'Customer not found! Else rowCustomer.BeginEdit rowCustomer("CompanyName") = "NewCompanyName" rowCustomer("ContactName") = "NewContactName" rowCustomer.EndEdit End If
Visual C# .NET
DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ANTON"); if (rowCustomer == null) //Customer not found! else { rowCustomer.BeginEdit(); rowCustomer["CompanyName"] = "NewCompanyName"; rowCustomer["ContactName"] = "NewContactName"; rowCustomer.EndEdit(); } Using BeginEdit and EndEdit lets you buffer the changes to the row. Calling EndEdit saves the changes to the row. If you decide that you don't want to keep the changes, you can call CancelEdit instead to undo the changes and the row will revert to its state at the time you called BeginEdit.
There's another difference between these two ways of modifying a row. The DataTable has events such as RowChanging, RowChanged, ColumnChanging, and ColumnChanged that you can use to examine the changes to a row or column. When, or if, these events fire depends on how you modify a row—with or without calling BeginEdit and EndEdit.
In the first example, the contents of the row changed each time we modified a column in the row. The DataTable object's events fire each time you modify the contents of a column. Using BeginEdit blocks the events from occurring until you call EndEdit. (If you call CancelEdit instead of EndEdit, the buffered changes will be discarded, and because the row is not updated, the events will not fire.)
The third way to modify the contents of a row is by using the ItemArray property. Like the Item property, this property can be used to retrieve or modify the contents of the row. The difference between the properties is that the Item property works with one column at a time and the ItemArray property returns and accepts an array in which each item corresponds to a column.
Visual Basic .NET
Dim aCustomer As Object() = {"ALFKI", "NewCompanyName", _ "NewContactName", "NewPhoneNo"} Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ALFKI") rowCustomer.ItemArray = aCustomer
Visual C# .NET
object[] aCustomer = {"ALFKI", "NewCompanyName", "NewContactName", "NewPhoneNo"}; DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); rowCustomer.ItemArray = aCustomer; If you want to use the ItemArray property but you don't want to change the value of every column in your row, you can use the Nothing keyword in Visual Basic .NET or null in Visual C# .NET. The following code snippets leave the first column in the DataRow untouched but modify the other three columns:
Visual Basic .NET
Dim aCustomer As Object() = {Nothing, "NewCompanyName", _ "NewContactName", "NewPhoneNo"} Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ALFKI") rowCustomer.ItemArray = aCustomer
Visual C# .NET
object[] aCustomer = {null, "NewCompanyName", "NewContactName", "NewPhoneNo"}; DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); rowCustomer.ItemArray = aCustomer;
| |
| Modifying the contents of a row does not automatically modify the contents of the corresponding row in your database. The changes you make to the row are considered pending changes that you can later submit to your database using the DataAdapter object. We'll discuss this process in more detail in Chapter 10 and Chapter 11. |
| |
So which method should you use to modify rows in your DataTable? I prefer using BeginEdit and EndEdit because it forces me to write code that's better structured, easier to read, and easier to maintain. Plus, this approach allows me to cancel the entire set of updates to a row if an unexpected problem occurs.
In this book, however, I'll generally avoid using the BeginEdit and EndEdit methods in order to make the code snippets more concise.
Working with Null Values in a DataRow
During the .NET beta, developers asked a lot of questions about setting database values to Null or checking database values for Null. Determining whether a column in a row contains a Null value is actually very simple.
The DataRow object has an IsNull method that you can use to check whether a column contains a Null value. Like the DataRow object's Item method, the IsNull method accepts a column name, an integer that represents the index for the column, or a DataColumn object.
The following code snippet demonstrates the use of the DataRow object's IsNull method.
Visual Basic .NET
Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ALFKI") If rowCustomer.IsNull("Phone") Then Console.WriteLine("It's Null") Else Console.WriteLine("It's not Null") End If
Visual C# .NET
DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); if (rowCustomer.IsNull("Phone")) Console.WriteLine("It's Null"); else Console.WriteLine("It's not Null"); When you want to set the value of a column to Null, don't use the Null keyword from your programming language of choice. The .NET Framework includes a class in the System namespace called DBNull. To set the value of a column in a DataRow to Null, use the Value property of the DBNull class, as shown here:
Visual Basic .NET
Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ALFKI") rowCustomer("Phone") = DBNull.Value
Visual C# .NET
DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); rowCustomer["Phone"] = DBNull.Value;
Deleting a DataRow
Deleting a row is simpler than modifying one. You simply call the Delete method on the DataRow. However, deleting the row does not remove it from the DataTable. Instead, ADO.NET marks the row as a pending deletion. Why doesn't ADO.NET just remove the DataRow from the table?
Remember that the data storage objects in the ADO.NET object model act as a data cache so that you can retrieve data from your database, modify that data in a disconnected mode, and later submit the pending changes. When you call the Delete method on the DataRow, you're not deleting the corresponding row in your database. Instead, you're marking the row as a pending deletion so that you can later submit that pending change to the database. If you completely remove the row from your DataTable, you will not delete the corresponding row in your database when you submit the pending changes stored in your DataSet or DataTable.
We'll examine submitting pending changes to your database in Chapter 10.
Removing a DataRow
If you really want to remove a row from your DataTable rather than mark it as a pending deletion, you can use the Remove or RemoveAt method on the DataRowCollection class, as shown in the following code snippet. Use the Remove method if you have a reference to the DataRow you want to remove. If you have the index number for the DataRow, use the RemoveAt method instead.
Visual Basic .NET
Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ALFKI") ds.Tables("Customers").Remove(rowCustomer) 'or ds.Tables("Customers").RemoveAt(intIndex)
Visual C# .NET
DataRow rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); rowCustomer.ItemArray = aCustomer; ds.Tables["Customers"].Remove(rowCustomer); //or ds.Tables["Customers"].RemoveAt(intIndex); I generally prefer using the Remove method. The RemoveAt method requires that you determine the index of the DataRow you want to remove. The DataRow object does not expose a property to return this information.
In addition, the DataSet and DataTable classes each have a Clear method that you can use to remove all DataRow objects from the DataSet or DataTable while preserving its structure.
Using the DataRow.RowState Property
The DataSet, DataTable, and DataRow objects act as an offline data cache. You can query your database and store the results in these objects. As you've just learned, you can add, modify, and delete rows. Because these ADO.NET objects are not connected to your database, the changes you make will not affect the contents of your database. Of course, modifying data off line isn't very useful if you can't submit those changes to your database later.
ADO.NET supports submitting changes back to your database. In Chapter 10, I'll cover this functionality in depth. For now, I'll cover some of the basics of how the DataSet supports this functionality. In order to cache a change to a DataRow so that ADO.NET can later submit the change to your database, ADO.NET must remember what type of change you've made to the row. Why, you ask?
One way to update the data stored in your database is to issue action queries such as this:
UPDATE MyTable SET FieldToModify = NewValue WHERE PKField = PKValue AND FieldToModify = OriginalValue
or this:
INSERT INTO MyTable (Field1, Field2, ... FieldN) VALUES (Value1, Value2, ... ValueN)
or this:
DELETE FROM MyTable WHERE PKField = PKValue
You can also use stored procedures to perform updates in a similar fashion.
The point is that the logic used to modify a row is different from the logic used to insert a row and the logic used to delete a row. Therefore, ADO.NET must keep track of what kind of change you've made to your DataRow in order to successfully submit the change to your database later.
ADO.NET stores this information in a property of the DataRow called RowState, which uses the values in the DataRowState enumeration. (See Table 6-1.) By checking this property, you can determine whether the row has been changed, along with the type of change (modification, insertion, or deletion) the row contains.
| Constant | Value | Description |
| Unchanged | 2 | The row does not contain any pending changes. |
| Detached | 1 | The row is not a member of a DataTable. |
| Added | 4 | The row has been added to the DataTable but does not exist in the database. |
| Modified | 16 | The row contains pending changes. |
| Deleted | 8 | The row is a pending deletion. |
The list of possible values might lead you to believe that the RowState property can return a combination of values from DataRowState, but the RowState property always returns a value from the enumeration. Table 6-2 provides a few scenarios and the resulting value.
| Example | DataRowState |
| Newly created but detached row:
row = tbl.NewRow row("ColX") = "InitValue" | Detached |
| Adding the new row to a DataTable:
tbl.Rows.Add(row) | Added |
| Newly retrieved row:
row = tbl.Rows(0) | Unchanged |
| After an edit:
row.BeginEdit() row("ColX") = "NewValue1" row.EndEdit() | Modified |
| After deleting a row:
row.Delete() | Deleted |
Examining the Pending Changes in a DataRow
Let's say that we've looped through the contents of your DataTable and, thanks to the RowState property, we've located a modified row. You've seen how to use the Item property of the DataRow to examine the contents of the columns in the row. You can also use the Item property to determine what the contents of the columns were before you modified the row.
The Item property accepts a second optional parameter from the DataRowVersion enumeration, as described in Table 6-3.
| Constant | Value | Description |
| Current | 512 | The current value stored in the column |
| Original | 256 | The original value stored in the column |
| Proposed | 1024 | The proposed value for the column (valid only while editing a row using BeginEdit) |
| Default | 1536 | Default action |
Generally speaking, the DataRow has two "versions"—what's currently stored in the row and what was originally stored in the row. You'll usually need both sets of information to locate the row. After you've updated a row, you can check the current contents of a column as well as the original contents of a column. The following code changes the contents of the CompanyName column in a DataRow and then retrieves both the current (new) value and the original value of the column.
Visual Basic .NET
Dim rowCustomer As DataRow rowCustomer = ds.Tables("Customers").Rows.Find("ALFKI") rowCustomer("CompanyName") = "NewCompanyName" Dim strNewCompanyName, strOldCompanyName As String Console.WriteLine(rowCustomer("CompanyName", _ DataRowVersion.Current)) Console.WriteLine(rowCustomer("CompanyName", _ DataRowVersion.Original))
Visual C# .NET
DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); rowCustomer["CompanyName"] = "NewCompanyName"; string strNewCompanyName, strOldCompanyName; Console.WriteLine(rowCustomer["CompanyName", DataRowVersion.Current]); Console.WriteLine(rowCustomer["CompanyName", DataRowVersion.Original]); When you edit a row using BeginEdit and EndEdit, you might want to examine another version of the column: the "proposed" version. Once you call EndEdit, the changes will be stored in the current version of the row. Before then, however, the changes you make to the row will only be pending because you can still cancel the changes by calling CancelEdit.
While you're editing a row, you can check the proposed view of a column by checking its Item property and supplying the Proposed constant from the DataRowVersion enumeration. Using the Current constant will return the value of the column before BeginEdit is called—which is not necessarily the original version of the column.
Let's look at the various states of a DataRow and the different values the Item property returns based on the value of DataRowVersion you use. (This is sort of like how a bill becomes a law, but without the Saturday morning cartoon animation.)
Table 6-4 lists the values returned by the Item property, depending on the DataRowVersion enumeration specified and the current state of the row. Entries marked as [Exception] represent scenarios in which calling the Item property with the DataRowVersion enumeration specified will throw an exception.
| Example | Current | Original | Proposed | Default |
| Newly created but detached row:
row = tbl.NewRow row("ColumnX") = "InitValue" | InitialValue | [Exception] | [Exception] | NewValue |
| Adding the new row to a DataTable:
tbl.Rows.Add(row) | InitialValue | [Exception] | [Exception] | NewValue |
| Newly retrieved row:
row = tbl.Rows(0) | RetrievedValue | RetrievedValue | [Exception] | RetrievedValue |
| During first edit:
row.BeginEdit() row("ColX") = "NewValue1" | RetrievedValue | RetrievedValue | NewValue1 | NewValue1 |
| After first edit:
row.EndEdit() | NewValue1 | RetrievedValue | [Exception] | NewValue1 |
| During second edit:
row.BeginEdit() row("ColX") = "NewValue2" | NewValue1 | RetrievedValue | NewValue2 | NewValue2 |
| After second edit:
row.EndEdit() | NewValue2 | RetrievedValue | [Exception] | NewValue2 |
| After canceled edit:
row.BeginEdit() row("ColX") = "ValueToCancel" row.CancelEdit() | NewValue2 | RetrievedValue | [Exception] | NewValue2 |
| After deleting a row:
row.Delete() | [Exception] | RetrievedValue | [Exception] | [Exception] |
| |
| Performing a successful edit changes the current value but does not affect the original value. Calling CancelEdit resets the current value to the value before BeginEdit is called, which is not necessarily the same as the original value. After you've deleted a row, you'll receive an exception if you try to examine its current values, but you can still access its original values. |
| |
We've discussed three of the four values in the DataRowVersion enumeration. Now let's talk about the Default value. Using this value in the Item property will not return the default value for the column. That's the DefaultValue property's job. This Default value in the enumeration represents the default value for the parameter on the DataRow object's Item property.
Earlier in the chapter, I said that the Item property returns the current value of a column in the row. The accuracy of that statement might depend on your definition of "current."
If you're not in the process of editing a row, calling Item and omitting the optional parameter is equivalent to supplying the DataRowVersion.Current constant for the optional parameter. However, if you're in the process of editing a row and you omit the optional parameter on the Item property, you'll receive the "proposed" version for the column.
EAN: 2147483647
Pages: 104
- Measuring and Managing E-Business Initiatives Through the Balanced Scorecard
- A View on Knowledge Management: Utilizing a Balanced Scorecard Methodology for Analyzing Knowledge Metrics
- Measuring ROI in E-Commerce Applications: Analysis to Action
- Governing Information Technology Through COBIT
- Governance Structures for IT in the Health Care Industry