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.
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.
Its really so much helpful. Thanks.
its realy nice one man keep it up i really enjoyed it
there isn’t any other word but Wonderful
and this is not for this article. it is about all the article i received.