Working with ADO.NET Disconnected Data Objects


ADO.NET is Microsoft data access technology for those developers who are creating .NET applications to connect to different type of data sources. Be a part of .NET Framework, It is a set of classes and components that can be used by programmers to access data and data services. It provides classes to access and modify data stored in relational database systems both in connected and disconnected way. In this tutorial I will give you overview of the ADO.NET disconnected data objects such as DataSet, DataTable, DataColumn, DataRow, UniqueConstraint, ForeignKeyConstraint and DataRelation.

The DataSet is the most famous and known disconnected data object. It is a memory based representation of your data stored in the database. It provides developer a temporary cache to store data in memory and to work on data in a disconnected manner. Once your data is available in DataSet, It can be used without ever connecting to a database. To understand DataSet fully, you must need to understand its structure and the objects and collections it compose inside. The figure below shows the internal structure of the DataSet object.

DataSet Structure


The DataSet contains two commonly used collections Tables which is DataTableCollection type object and Relations which is the object of DataRelationCollection type. These collections stored DataTable and DataRelation objects in them.

The DataTable object represents tabular data in memory. Just like tables in relational database DataTable has collection of rows, columns and constraints. It can be used independently without DataSet object to represent tabular data and used very commonly as a lightweight alternate of DataSet.

Now you have basic idea what DataSet and DataTable objects are and what type of collections they contain. It is time to show you how you can work with these objects in code. To demonstrate all the objects and to give you complete understanding of these objects I am creating all these objects programmatically without connecting any database at all. This will give you better understanding because you will set all common properties to use these objects yourself.

To start this tutorial create an ASP.NET website and just add a two GridView controls on ASP.NET page. All the code in this tutorial can be placed in the Page_Load event and it requires no database connection string setting in web.config.

The first object I am creating is DataSet object. In the start it has no table in Tables collection.

// Creating an Empry DataSet with Name 
DataSet productsDataSet = new DataSet("ProductsDataSet");

The next object is DataTable which is also very straight forward to create.

// Creating an Empty DataTable with Name 
DataTable productsTable = new DataTable("ProductsDataTable");

Remember when your table is created it has no column or row to display. So just like database tables we have to create DataColumn class objects to add in DataTable Columns collection. Following code demonstrate how to create DataColumn objects with different data types.

// Creating DataColumn objects with different DataTypes

DataColumn productId = new DataColumn("ProductID", typeof(System.Int32)); 
DataColumn productName = new DataColumn("ProductName", typeof(System.String)); 
DataColumn unitPrice = new DataColumn("UnitPrice", typeof(System.Decimal)); 
DataColumn discountPercent = new DataColumn("DiscountPercent", typeof(System.Decimal));

We can also create calculated columns in DataTable just as we can do in relational database tables. DataColumn class has Expression property which you can use to create any type of calculated expression as shown below:

// Creating Calculated Columns with Expression Property

DataColumn discountAmount = new DataColumn("DiscountAmount", typeof(System.Decimal)); 
discountAmount.Expression = "(UnitPrice * DiscountPercent) / 100"; 

DataColumn finalPrice = new DataColumn("FinalPrice", typeof(System.Decimal)); 
finalPrice.Expression = "UnitPrice - DiscountAmount";

Once we have all the above DataColumn objects ready we need to add them in DataTable Columns collection as shown below:

// Adding DataColumn objects in DataTable Columns Collection 

productsTable.Columns.Add(productId); 
productsTable.Columns.Add(productName); 
productsTable.Columns.Add(unitPrice); 
productsTable.Columns.Add(discountPercent); 
productsTable.Columns.Add(discountAmount); 
productsTable.Columns.Add(finalPrice);

DataColumn class provides many useful properties which can be used to modify column data or to add different type of constraints. Following code shows you how you can set almost all the basic properties.

// Specifying Identity (Auto Number) Columns in DataTable
productId.AutoIncrement = true; 
productId.AutoIncrementSeed = 1; 
productId.AutoIncrementStep = 1; 

// Specifying Single Primary Key Column in DataTable 
productsTable.PrimaryKey = new DataColumn[] { productId }; 

// Specifying Multiple Primary Key Columns in DataTable 
productsTable.PrimaryKey = new DataColumn[] { productId, productName }; 

// Specifying Null Values in Columns 
productName.AllowDBNull = true;

// Specifying Maximum Length in Columns 
productName.MaxLength = 50; 

// Specifying Default Values in Columns 
unitPrice.DefaultValue = 0.0M; 
discountPercent.DefaultValue = 5M;

If you want to attach unique constraint with any data column you can use UniqueConstraint class which need a constraint name and the DataColumn as  constructor parameters as shown in the following code. Please keep in mind that you have to add constraint in the Constraints collection of DataTable object.

// Specifying Unique Constraint on Columns 
UniqueConstraint uniqueConstraint = new UniqueConstraint("UK_ProductsDataTable", productName); 

// Adding Unique Constraint to DataTable Constraints collection 
productsTable.Constraints.Add(uniqueConstraint);

Once your DataTable have information about the DataColumn objects added in it you can request DataTable to create DataRow objects for you. Keep in mind that you can not create DataRow objects using its default constructor you have to obtain it from DataTable using NewRow method. Also keep in mind that you cannot add same DataRow in the DataTable Rows collection twice. Following code demonstrates how to create new DataRow, set data and then add to DataTable Rows collection.

// Creating DataRow objects 

DataRow row1 = productsTable.NewRow(); 
DataRow row2 = productsTable.NewRow(); 
DataRow row3 = productsTable.NewRow(); 
DataRow row4 = productsTable.NewRow(); 

// Add Data to in DataRow 

row1["ProductName"] = "Nokia N95"; 
row1["UnitPrice"] = "399"; 
row1["DiscountPercent"] = "10"; 

row2["ProductName"] = "Nokia 6630"; 
row2["UnitPrice"] = "299"; 
row2["DiscountPercent"] = "20"; 

row3["ProductName"] = "Nokia 3310"; 
row3["UnitPrice"] = "110"; 

row4["ProductName"] = "Nokia N93"; 
row4["UnitPrice"] = "599"; 

// Adding DataRow objects in DataTable Rows Collection 

productsTable.Rows.Add(row1); 
productsTable.Rows.Add(row2); 
productsTable.Rows.Add(row3); 
productsTable.Rows.Add(row4);

To check how ForeignKeyConstraint and DataRelation objects work, we have to create another DataTable which allows us to create relations between two tables in DataSet Tables collection. Following code is creating another DataTable by using the similar code as we used above.

// Creating OrderDetails Table to demonstrate RelationShips 
DataTable orderDetailsTable = new DataTable("OrderDetailsDataTable");

// Creating DataColumn objects with different DataTypes 
DataColumn orderId = new DataColumn("OrderID", typeof(System.Int32)); 
DataColumn orderdetailsProductId = new DataColumn("ProductID", typeof(System.Int32)); 
DataColumn quantity = new DataColumn("Quantity", typeof(System.Int32)); 

// Specifying Identity (Auto Number) Columns in DataTable 
orderId.AutoIncrement = true; 
orderId.AutoIncrementSeed = 1; 
orderId.AutoIncrementStep = 1; 

// Adding DataColumn objects in DataTable Columns Collection 
orderDetailsTable.Columns.Add(orderId); 
orderDetailsTable.Columns.Add(orderdetailsProductId); 
orderDetailsTable.Columns.Add(quantity);

To add foreign key constraint you have to create object of ForeignKeyConstraint class which needs constraint name, DataColumn object in parent table and DataColumn object in child table as its constructor parameters. Once created, you have to add this object in Constraints collection of DataTable.

// Specifying Foreign Key Constraint on Columns 
ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("FK_ProductsDataTable_OrderDetailsDataTable", productId, orderdetailsProductId);

// Adding Foreign Key Constraint to DataTable Constraints collection 
orderDetailsTable.Constraints.Add(foreignKeyConstraint);

Following code is creates and add DataRow object in Rows collection in OrderDetailsDataTable object.

// Creating DataRow objects 

DataRow row5 = orderDetailsTable.NewRow(); 
DataRow row6 = orderDetailsTable.NewRow(); 
DataRow row7 = orderDetailsTable.NewRow(); 
DataRow row8 = orderDetailsTable.NewRow(); 
DataRow row9 = orderDetailsTable.NewRow();

// Add Data to in DataRow 

row5["ProductID"] = 1; 
row5["Quantity"] = "8"; 

row6["ProductID"] = 2; 
row6["Quantity"] = "10"; 

row7["ProductID"] = 1; 
row7["Quantity"] = "5"; 

row8["ProductID"] = 3; 
row8["Quantity"] = "15"; 

row9["ProductID"] = 2; 
row9["Quantity"] = "50"; 

// Adding DataRow objects in DataTable Rows Collection 

orderDetailsTable.Rows.Add(row5); 
orderDetailsTable.Rows.Add(row6); 
orderDetailsTable.Rows.Add(row7); 
orderDetailsTable.Rows.Add(row8); 
orderDetailsTable.Rows.Add(row9);

Finally we need to add both DataTable objects in Tables collection in DataSet as follows:

// Adding DataTable objects to DataSet Tables Collection 

productsDataSet.Tables.Add(productsTable); 
productsDataSet.Tables.Add(orderDetailsTable);

Once you have two DataTable objects in DataSet you can create relation between them just as you do in relational databases. DataRelation class need relation name, parent table DataColumn and child table DataColumn to create relation between two tables. You can create as many relations as you want between tables in DataSet but you have to add them in Relations collection in DataSet object as shown below:

// Creating DataRelation object to create DataTable relations 

DataRelation relation = new DataRelation("ProductsDataTable_OrderDetailsDataTable", 
  productId, orderdetailsProductId); 

// Adding Relation in DataSet Relations collection 

productsDataSet.Relations.Add(relation);

The last part of this tutorial is binding DataTable objects with two GridView controls you added on the page and showing DataSet and DataTable name on the Caption of the GridView control.

// Binding DataSet Tables to GridView Controls 

GridView1.DataSource = productsDataSet.Tables[0]; 
GridView1.DataBind(); 

GridView2.DataSource = productsDataSet.Tables[1]; 
GridView2.DataBind(); 

GridView1.Caption = productsDataSet.DataSetName + " - " + productsTable.TableName + " " + productsTable.Rows.Count + " Rows"; 

GridView2.Caption = productsDataSet.DataSetName + " - " + orderDetailsTable.TableName + " " + orderDetailsTable.Rows.Count + " Rows";

Following screen shot shows you how your page will look like after you implemented all the above code without any error.

ADO.NET Disconnected Data Objects


I hope now you have much better understanding of disconnected data objects available in ADO.NET.

About author


User avatar

Waqas Anwar

Founder of EzzyLearning.com, Senior Consultant, Microsoft .NET and Web Technologies

Comments 3

user
Ikram Awan
  • 04 June 2009

there isn't any other word but Wonderful
and this is not for this article. it is about all the article i received.

user
farhan
  • 07 December 2011

its realy nice one man keep it up i really enjoyed it

user
Hoimonty
  • 17 May 2012

Its really so much helpful. Thanks.

Add Comment