ADO.NET is made up of two basic parts, the DataSet and the Managed Providers. You'll learn about the latter in Chapter 3, “Managed Providers in ADO.NET.” For now, let's take a high-level look at the Document Object Model (DOM) for the ADO.NET DataSet (each piece of the ADO.NET DOM will be more thoroughly dissected throughout this book). Figure 2.3 shows you the ADO.NET Document Object Model.
As previously mentioned, the DataSet is an in-memory copy of data that provides a consistent programming model regardless of the original data store.
The DataSet is made up of a RelationsCollection and a TablesCollection. Each DataTable in the TablesCollection has Columns and Rows collections. The following sections will take a quick look at the objects in these collections.
Note
To use ADO.NET in ASP.NET, you must import the System.Data namespace. The following code samples are provided as a demonstration. You're not expected to fully understand everything at this point, but this will give you a glimpse of what the code looks like.
A DataTable represents one table of in-memory data in the DataSet. It contains a collection of DataColumns that represent the table schema and a collection of DataRows that represent the data in the table. The DataTable maintains the original state of the data along with its current state, and it tracks the changes that have occurred.
The following code adds a new DataTable to a DataSet:
[VB] Dim myDataSet As New DataSet myDataSet.Tables.Add(New DataTable("Products")) [C#] DataSet myDataSet = new DataSet(); myDataSet.Tables.Add(new DataTable("Products"));
The DataColumns represent the database table's schema. The DataTable allows you to create a variety of DataColumns within the table's columns collection, including the following:
Listing 2.1 demonstrates how to add three columns—ID, Name and Cost—to an existing DataTable in a DataSet. Then the ID column is set as the primary key.
[VB] 01: Imports System 02: Imports System.Data 03: 04: Namespace Chapter02 05: 06: Public Class C0202 07: 08: Public Function CreateCustomDataSet() As DataSet 09: Dim myDataSet As New DataSet 10: Dim keys(1) As DataColumn 11: 12: myDataSet.Tables.Add(new DataTable("Products")) 13: myDataSet.Tables("Products").Columns.Add("ID", System.Type.GetType("System.Int32")) 14: myDataSet.Tables("Products").Columns.Add("Name", System.Type.GetType("System.String")) 15: myDataSet.Tables("Products").Columns.Add("Cost", System.Type.GetType("System.Double")) 16: 17: keys(0) = myDataSet.Tables("Products").Columns("ID") 18: myDataSet.Tables("Products").PrimaryKey = keys 19: 20: Return myDataSet 21: End Function 22: End Class 23: End Namespace [C#] 01: using System; 02: using System.Data; 03: 04: namespace Chapter02{ 05: 06: public class C0202{ 07: 08: public DataSet CreateCustomDataSet(){ 09: DataSet myDataSet = new DataSet(); 10: DataColumn[] keys = new DataColumn[1]; 11: 12: myDataSet.Tables.Add(new DataTable("Products")); 13: myDataSet.Tables["Products"].Columns.Add("ID", System.Type.GetType("System.Int32")); 14: myDataSet.Tables["Products"].Columns.Add("Name", System.Type.GetType("System.String")); 15: myDataSet.Tables["Products"].Columns.Add("Cost", System.Type.GetType("System.Double")); 16: 17: keys[0] = myDataSet.Tables["Products"].Columns["ID"]; 18: myDataSet.Tables["Products"].PrimaryKey = keys; 19: 20: return myDataSet; 21: } 22: } 23: } |
In Listing 2.1 you dynamically create a new DataTable in a DataSet (line 12). On lines 13–15 you add three DataColumns to the DataTable using the Add() method of the DataColumnCollection class. The Add() method takes two arguments, the name of the DataColumn (string) and the data type for the DataColumn (Type).
On line 10 you create a 1-dimensional array of DataColumns, and on line 17 you assign the ID column to the only object in the array. You use an array because the DataTable's PrimaryKey property accepts an array of DataColumns. On line 18 you assign the keys array (which only has the ID column in it) as the PrimaryKey property of the Products DataTable.
A DataRow is a child element of the DataTable. Each DataRow represents a row of data from the original data store and has a RowState property. The possible RowStates are shown in Table 2.1.
Listing 2.2 adds a new DataRow to an existing DataTable.
[VB] 01: Imports System 02: Imports System.Data 03: 04: Namespace Chapter02 05: 06: Public Class C0202 07: 08: Public Function CreateCustomDataSet() As DataSet 09: Dim myDataSet As New DataSet 10: Dim keys(1) As DataColumn 11: 12: myDataSet.Tables.Add(new DataTable("Products")) 13: myDataSet.Tables("Products").Columns.Add("ID", System.Type.GetType("System.Int32")) 14: myDataSet.Tables("Products").Columns.Add("Name", System.Type.GetType("System.String")) 15: myDataSet.Tables("Products").Columns.Add("Cost", System.Type.GetType("System.Double")) 16: 17: keys(0) = myDataSet.Tables("Products").Columns("ID") 18: myDataSet.Tables("Products").PrimaryKey = keys 19: 20: Dim myRow As DataRow 21: myRow = myDataSet.Tables("Products").NewRow() 22: myRow("ID") = 1 23: myRow("Name") = "Doug's Doomsday Device" 24: myRow("Cost") = 19.95 25: myDataSet.Tables("Products").Rows.Add(myRow) 26: 27: Return myDataSet 28: End Function 29: End Class 30: End Namespace [C#] 01: using System; 02: using System.Data; 03: 04: namespace Chapter02{ 05: 06: public class C0202{ 07: 08: public DataSet CreateCustomDataSet(){ 09: DataSet myDataSet = new DataSet(); 10: DataColumn[] keys = new DataColumn[1]; 11: 12: myDataSet.Tables.Add(new DataTable("Products")); 13: myDataSet.Tables["Products"].Columns.Add("ID", System.Type.GetType("System.Int32")); 14: myDataSet.Tables["Products"].Columns.Add("Name", System.Type.GetType("System.String")); 15: myDataSet.Tables["Products"].Columns.Add("Cost", System.Type.GetType("System.Double")); 16: 17: keys[0] = myDataSet.Tables["Products"].Columns["ID"]; 18: myDataSet.Tables["Products"].PrimaryKey = keys; 19: 20: DataRow myRow = null; 21: myRow = myDataSet.Tables["Products"].NewRow(); 22: myRow["ID"] = 1; 23: myRow["Name"] = "Doug's Doomsday Device"; 24: myRow["Cost"] = 19.95; 25: myDataSet.Tables["Products"].Rows.Add(myRow); 26: 27: return myDataSet; 28: } 29: } 30: } |
In Listing 2.2 you extend the code from Listing 2.1 by adding lines 20–25. In these lines of code you create and add a new DataRow to the Products table. On line 20 you create an instance of the DataRow class, and on line 21 you set it to the result of the NewRow() method of the Products table. This creates a new DataRow using the DataColumn values from the Products table. On lines 22–24 you set the values of each column in the row, and on line 25 you add the new DataRow to the Rows collection of the Products table.
A DataRelation relates two tables in a DataSet to each other. The arguments of a DataRelation are the two columns that serve as the primary key and the foreign key columns in the relationship, as well as the name of the DataRelation. A DataRelation isn't restricted to only two columns, and it can use DataColumn arrays as its primary and foreign keys.
The following example shows how to create a DataRelation between two DataTables in a DataSet, representing the relationship between Products and OrderDetail.
Listing 2.3 creates a DataRelation between two DataTables in the DataSet.
[VB] 01: Imports System 02: Imports System.Data 03: 04: Namespace Chapter02 05: 06: Public Class C0203 07: 08: Public Function CreateCustomDataSet() As DataSet 09: Dim myDataSet As New DataSet 10: Dim keys(1) As DataColumn 11: 12: myDataSet.Tables.Add(new DataTable("Products")) 13: myDataSet.Tables("Products").Columns.Add("ID", System.Type.GetType("System.Int32")) 14: myDataSet.Tables("Products").Columns.Add("Name", System.Type.GetType("System.String")) 15: myDataSet.Tables("Products").Columns.Add("Cost", System.Type.GetType("System.Double")) 16: 17: keys(0) = myDataSet.Tables("Products").Columns("ID") 18: myDataSet.Tables("Products").PrimaryKey = keys 19: 20: Dim myRow As DataRow 21: myRow = myDataSet.Tables("Products").NewRow() 22: myRow("ID") = 1 23: myRow("Name") = "Doug's Doomsday Device" 24: myRow("Cost") = 19.95 25: myDataSet.Tables("Products").Rows.Add(myRow) 26: 27: ' Add OrderDetail Table 28: myDataSet.Tables.Add(new DataTable("OrderDetail")) 29: myDataSet.Tables("OrderDetail").Columns.Add("ProductID", System.Type.GetType ("System.Int32")) 30: myDataSet.Tables("OrderDetail").Columns.Add("Quantity", System.Type.GetType("System .Int32")) 31: 32: keys(0) = myDataSet.Tables("OrderDetail").Columns("ProductID") 33: myDataSet.Tables("OrderDetail").PrimaryKey = keys 34: 35: myRow = myDataSet.Tables("OrderDetail").NewRow() 36: myRow("ProductID") = 1 37: myRow("Quantity") = 7 38: myDataSet.Tables("OrderDetail").Rows.Add(myRow) 39: 40: ' Add DataRelations 41: myDataSet.Relations.Add("ProductToOrderDetails", myDataSet.Tables("Products") .Columns("ID"), myDataSet.Tables("OrderDetail").Columns("ProductID")) 42: 43: Return myDataSet 44: End Function 45: End Class 46: End Namespace [C#] 01: using System; 02: using System.Data; 03: 04: namespace Chapter02{ 05: 06: public class C0203{ 07: 08: public DataSet CreateCustomDataSet(){ 09: DataSet myDataSet = new DataSet(); 10: DataColumn[] keys = new DataColumn[1]; 11: 12: myDataSet.Tables.Add(new DataTable("Products")); 13: myDataSet.Tables["Products"].Columns.Add("ID", System.Type.GetType("System.Int32")); 14: myDataSet.Tables["Products"].Columns.Add("Name", System.Type.GetType("System.String")); 15: myDataSet.Tables["Products"].Columns.Add("Cost", System.Type.GetType("System.Double")); 16: 17: keys[0] = myDataSet.Tables["Products"].Columns["ID"]; 18: myDataSet.Tables["Products"].PrimaryKey = keys; 19: 20: DataRow myRow = null; 21: myRow = myDataSet.Tables["Products"].NewRow(); 22: myRow["ID"] = 1; 23: myRow["Name"] = "Doug's Doomsday Device"; 24: myRow["Cost"] = 19.95; 25: myDataSet.Tables["Products"].Rows.Add(myRow); 26: 27: // Add OrderDetail Table 28: myDataSet.Tables.Add(new DataTable("OrderDetail")); 29: myDataSet.Tables["OrderDetail"].Columns.Add("ProductID", System.Type.GetType ("System.Int32")); 30: myDataSet.Tables["OrderDetail"].Columns.Add("Quantity", System.Type.GetType("System .Int32")); 31: 32: keys[0] = myDataSet.Tables["OrderDetail"].Columns["ProductID"]; 33: myDataSet.Tables["OrderDetail"].PrimaryKey = keys; 34: 35: myRow = myDataSet.Tables["OrderDetail"].NewRow(); 36: myRow["ProductID"] = 1; 37: myRow["Quantity"] = 7; 38: myDataSet.Tables["OrderDetail"].Rows.Add(myRow); 39: 40: // Add DataRelations 41: myDataSet.Relations.Add("ProductToOrderDetails", myDataSet.Tables["Products"] .Columns["ID"], myDataSet.Tables["OrderDetail"].Columns["ProductID"]); 42: 43: return myDataSet; 44: } 45: } 46: } |
In Listing 2.3 you continue to extend the code from the previous examples. On lines 27–38 you add a new DataTable, OrderDetail, to the DataSet, and a new DataRow to the OrderDetail table. On line 41 you create a DataRelation between the Products table and the OrderDetail table. The DataRelation is called ProductToOrderDetail, and it links the Products.ID (primary key) column to the OrderDetail.ProductID (foreign key) column.
As with any data structure, maintaining data integrity is imperative. ADO.NET enables constraints as one means of maintaining data integrity. There are two kinds of constraints:
ForeignKeyConstraints
UniqueConstraints
When a row is deleted or updated, and the value that has changed is used also as a foreign key in one or more related tables, the ForeignKeyConstraint is used to determine how to react to the change. The possible ForeignKeyConstraint actions are shown in Table 2.2.
Listing 2.4 creates a ForeignKeyConstraint for a DataTable in your DataSet.
[VB] 01: Imports System 02: Imports System.Data 03: 04: Namespace Chapter02 05: 06: Public Class C0204 07: 08: Public Function CreateCustomDataSet() As DataSet 09: Dim myDataSet As New DataSet 10: Dim keys(1) As DataColumn 11: 12: myDataSet.Tables.Add(new DataTable("Products")) 13: myDataSet.Tables("Products").Columns.Add("ID", System.Type.GetType("System.Int32")) 14: myDataSet.Tables("Products").Columns.Add("Name", System.Type.GetType("System.String")) 15: myDataSet.Tables("Products").Columns.Add("Cost", System.Type.GetType("System.Double")) 16: 17: keys(0) = myDataSet.Tables("Products").Columns("ID") 18: myDataSet.Tables("Products").PrimaryKey = keys 19: 20: Dim myRow As DataRow 21: myRow = myDataSet.Tables("Products").NewRow() 22: myRow("ID") = 1 23: myRow("Name") = "Doug's Doomsday Device" 24: myRow("Cost") = 19.95 25: myDataSet.Tables("Products").Rows.Add(myRow) 26: 27: ' Add OrderDetail Table 28: myDataSet.Tables.Add(new DataTable("OrderDetail")) 29: myDataSet.Tables("OrderDetail").Columns.Add("ProductID", System.Type.GetType ("System.Int32")) 30: myDataSet.Tables("OrderDetail").Columns.Add("Quantity", System.Type.GetType("System .Int32")) 31: 32: keys(0) = myDataSet.Tables("OrderDetail").Columns("ProductID") 33: myDataSet.Tables("OrderDetail").PrimaryKey = keys 34: 35: myRow = myDataSet.Tables("OrderDetail").NewRow() 36: myRow("ProductID") = 1 37: myRow("Quantity") = 7 38: myDataSet.Tables("OrderDetail").Rows.Add(myRow) 39: 40: ' Add DataRelations 41: myDataSet.Relations.Add("ProductToOrderDetails", myDataSet.Tables("Products") .Columns("ID"), 42: myDataSet.Tables("OrderDetail").Columns("ProductID")) 42: 43: Dim fk As ForeignKeyConstraint 44: fk = New ForeignKeyConstraint( myDataSet.Tables("Products").Columns("ID"), myDataSet.Tables("OrderDetail").Columns("ProductID")) 45: fk.DeleteRule = Rule.Cascade 46: fk.UpdateRule = Rule.SetDefault 47: myDataSet.Tables("Products").Constraints.Add(fk) 48: 49: Return myDataSet 50: End Function 51: End Class 52: End Namespace [C#] 01: using System; 02: using System.Data; 03: 04: namespace Chapter02{ 05: 06: public class C0204{ 07: 08: public DataSet CreateCustomDataSet(){ 09: DataSet myDataSet = new DataSet(); 10: DataColumn[] keys = new DataColumn[1]; 11: 12: myDataSet.Tables.Add(new DataTable("Products")); 13: myDataSet.Tables["Products"].Columns.Add("ID", System.Type.GetType("System.Int32")); 14: myDataSet.Tables["Products"].Columns.Add("Name", System.Type.GetType("System.String")); 15: myDataSet.Tables["Products"].Columns.Add("Cost", System.Type.GetType("System.Double")); 16: 17: keys[0] = myDataSet.Tables["Products"].Columns["ID"]; 18: myDataSet.Tables["Products"].PrimaryKey = keys; 19: 20: DataRow myRow = null; 21: myRow = myDataSet.Tables["Products"].NewRow(); 22: myRow["ID"] = 1; 23: myRow["Name"] = "Doug's Doomsday Device"; 24: myRow["Cost"] = 19.95; 25: myDataSet.Tables["Products"].Rows.Add(myRow); 26: 27: // Add OrderDetail Table 28: myDataSet.Tables.Add(new DataTable("OrderDetail")); 29: myDataSet.Tables["OrderDetail"].Columns.Add("ProductID", System.Type.GetType ("System.Int32")); 30: myDataSet.Tables["OrderDetail"].Columns.Add("Quantity", System.Type.GetType("System .Int32")); 31: 32: keys[0] = myDataSet.Tables["OrderDetail"].Columns["ProductID"]; 33: myDataSet.Tables["OrderDetail"].PrimaryKey = keys; 34: 35: myRow = myDataSet.Tables["OrderDetail"].NewRow(); 36: myRow["ProductID"] = 1; 37: myRow["Quantity"] = 7; 38: myDataSet.Tables["OrderDetail"].Rows.Add(myRow); 39: 40: // Add DataRelations 41: myDataSet.Relations.Add("ProductToOrderDetails", myDataSet.Tables["Products"] .Columns["ID"], myDataSet.Tables["OrderDetail"].Columns["ProductID"]); 42: 43: ForeignKeyConstraint fk = null; 44: fk = new ForeignKeyConstraint( myDataSet.Tables["Products"].Columns["ID"], myDataSet.Tables["OrderDetail"].Columns["ProductID"]); 45: fk.DeleteRule = Rule.Cascade; 46: fk.UpdateRule = Rule.SetDefault; 47: myDataSet.Tables["Products"].Constraints.Add(fk); 48: 49: return myDataSet; 50: } 51: } 52: } |
In Listing 2.4 you continue extending the code from the previous examples. On lines 43–47 you add a ForeignKeyConstraint between the Products and OrderDetail tables. On line 44 you set the constraint between Products.ID and OrderDetail.ProductID (Products.ID is the parent column and primary key; OrderDetail.ProductID is the foreign key). On line 45 you set the DeleteRule to Cascade; the appropriate rows in OrderDetail will be deleted when a product is deleted from the Products table. On line 46 you set the UpdateRule to SetDefault, which will set the values in related rows to their default values. On line 47 you add the constraint to the Products table.
A UniqueConstraint is used to ensure that all values in a DataColumn are unique. It can be assigned to either an individual column or an array of columns in a single DataTable. Listing 2.5 shows how to add a UniqueConstraint.
[VB] 01: Imports System 02: Imports System.Data 03: 04: Namespace Chapter02 05: 06: Public Class C0204 07: 08: Public Function CreateCustomDataSet() As DataSet 09: Dim myDataSet As New DataSet 10: Dim keys(1) As DataColumn 11: 12: myDataSet.Tables.Add(new DataTable("Products")) 13: myDataSet.Tables("Products").Columns.Add("ID", System.Type.GetType("System.Int32")) 14: myDataSet.Tables("Products").Columns.Add("Name", System.Type.GetType("System.String")) 15: myDataSet.Tables("Products").Columns.Add("Cost", System.Type.GetType("System.Double")) 16: 17: keys(0) = myDataSet.Tables("Products").Columns("ID") 18: myDataSet.Tables("Products").PrimaryKey = keys 19: 20: Dim myRow As DataRow 21: myRow = myDataSet.Tables("Products").NewRow() 22: myRow("ID") = 1 23: myRow("Name") = "Doug's Doomsday Device" 24: myRow("Cost") = 19.95 25: myDataSet.Tables("Products").Rows.Add(myRow) 26: 27: ' Add OrderDetail Table 28: myDataSet.Tables.Add(new DataTable("OrderDetail")) 29: myDataSet.Tables("OrderDetail").Columns.Add("ProductID", System.Type.GetType ("System.Int32")) 30: myDataSet.Tables("OrderDetail").Columns.Add("Quantity", System.Type.GetType("System .Int32")) 31: 32: keys(0) = myDataSet.Tables("OrderDetail").Columns("ProductID") 33: myDataSet.Tables("OrderDetail").PrimaryKey = keys 34: 35: myRow = myDataSet.Tables("OrderDetail").NewRow() 36: myRow("ProductID") = 1 37: myRow("Quantity") = 7 38: myDataSet.Tables("OrderDetail").Rows.Add(myRow) 39: 40: ' Add DataRelations 41: myDataSet.Relations.Add("ProductToOrderDetails", myDataSet.Tables("Products") .Columns("ID"), 42: myDataSet.Tables("OrderDetail").Columns("ProductID")) 42: 43: Dim fk As ForeignKeyConstraint 44: fk = New ForeignKeyConstraint( myDataSet.Tables("Products").Columns("ID"), myDataSet.Tables("OrderDetail").Columns("ProductID")) 45: fk.DeleteRule = Rule.Cascade 46: fk.UpdateRule = Rule.SetDefault 47: myDataSet.Tables("Products").Constraints.Add(fk) 48: 49: Dim uc As UniqueConstraint 50: uc = New UniqueConstraint(myDataSet.Tables("Products").Columns("ID")) 51: myDataSet.Tables("Products").Constraints.Add(uc) 52: 53: Return myDataSet 54: End Function 55: End Class 56: End Namespace [C#] 01: using System; 02: using System.Data; 03: 04: namespace Chapter02{ 05: 06: public class C0204{ 07: 08: public DataSet CreateCustomDataSet(){ 09: DataSet myDataSet = new DataSet(); 10: DataColumn[] keys = new DataColumn[1]; 11: 12: myDataSet.Tables.Add(new DataTable("Products")); 13: myDataSet.Tables["Products"].Columns.Add("ID", System.Type.GetType("System.Int32")); 14: myDataSet.Tables["Products"].Columns.Add("Name", System.Type.GetType("System.String")); 15: myDataSet.Tables["Products"].Columns.Add("Cost", System.Type.GetType("System.Double")); 16: 17: keys[0] = myDataSet.Tables["Products"].Columns["ID"]; 18: myDataSet.Tables["Products"].PrimaryKey = keys; 19: 20: DataRow myRow = null; 21: myRow = myDataSet.Tables["Products"].NewRow(); 22: myRow["ID"] = 1; 23: myRow["Name"] = "Doug's Doomsday Device"; 24: myRow["Cost"] = 19.95; 25: myDataSet.Tables["Products"].Rows.Add(myRow); 26: 27: // Add OrderDetail Table 28: myDataSet.Tables.Add(new DataTable("OrderDetail")); 29: myDataSet.Tables["OrderDetail"].Columns.Add("ProductID", System.Type.GetType ("System.Int32")); 30: myDataSet.Tables["OrderDetail"].Columns.Add("Quantity", System.Type.GetType("System .Int32")); 31: 32: keys[0] = myDataSet.Tables["OrderDetail"].Columns["ProductID"]; 33: myDataSet.Tables["OrderDetail"].PrimaryKey = keys; 34: 35: myRow = myDataSet.Tables["OrderDetail"].NewRow(); 36: myRow["ProductID"] = 1; 37: myRow["Quantity"] = 7; 38: myDataSet.Tables["OrderDetail"].Rows.Add(myRow); 39: 40: // Add DataRelations 41: myDataSet.Relations.Add("ProductToOrderDetails", myDataSet.Tables["Products"] .Columns["ID"], myDataSet.Tables["OrderDetail"].Columns["ProductID"]); 42: 43: ForeignKeyConstraint fk = null; 44: fk = new ForeignKeyConstraint( myDataSet.Tables["Products"].Columns["ID"], myDataSet.Tables["OrderDetail"].Columns["ProductID"]); 45: fk.DeleteRule = Rule.Cascade; 46: fk.UpdateRule = Rule.SetDefault; 47: myDataSet.Tables["Products"].Constraints.Add(fk); 48: 49: UniqueConstraint uc = null; 50: uc = new UniqueConstraint(myDataSet.Tables["Products"].Columns["ID"]); 51: myDataSet.Tables["Products"].Constraints.Add(uc); 52: 53: return myDataSet; 54: } 55: } 56: } |
In Listing 2.5 you continue extending the code from the previous examples. On lines 49–51 you add a UniqueConstraint to the Products table. The UniqueConstraint ensures that all values added to the Products.ID column must be unique.
18.216.37.123