ADO.NET provides a bridge between front-end controls and back-end databases. ADO.NET objects encapsulate all data access operations and controls that interact with data display objects. The details of data movement are thus hidden.
The following diagram shows the ADO.NET object as a whole:
A data set represents a subset of a database. It has no persistent connection to the database. A reconnection is required in order to upgrade the database. DataSet includes DataTable and DataRelation objects. DataRelation objects represent the relationship between two tables.
The following table is some important properties of the DataSet class:
| property | describe |
|---|---|
| CaseSensitive | Indicates whether the string to be compared with the data table is case-sensitive. |
| Container | Get space for the component. |
| DataSetName | Gets or sets the name of an existing data collection. |
| DefaultViewManager | Returns a view of the data in the data collection. |
| DesignMode | Indicates whether the component is in design mode. |
| EnforceConstraints | Indicates whether restrictions are respected when trying to upload a file. |
| Events | Get the list of event handlers related to this component. |
| ExtendedProperties | Gets a collection of custom user information related to the DataSet. |
| HasErrors | Indicate if there are any errors. |
| IsInitialized | Indicates whether the DataSet is initialized. |
| Locale | Gets or sets information used to compare strings with the table. |
| Namespace | Gets or sets the namespace of the DataSet. |
| Prefix | Gets or sets an XML prefix that is an alias for a namespace. |
| Relations | Returns a collection of DataRelation objects. |
| Tables | Returns a collection of DataTable objects. |
The following table lists some important methods of the DataSet class:
| method | describe |
|---|---|
| AcceptChanges | Accepts all changes due to loading the DataSet or this method. |
| BeginInit | Start initialization of DataSet. This initialization occurs at runtime. |
| Clear | Clear data. |
| Clone | Clone the structure of the DataSet including all DataTable structures, relationships, and constraints. But the data is not cloned. |
| Copy | Copy data and structures. |
| CreateDataReader() | Returns a DataTableReader with a result set for each DataTable, in the same order as the tables in the Tables collection appear. |
| CreateDataReader(DataTable[]) | Returns a DataTableReader with one result set for each DataTable. |
| EndInit | Ends the initialization of a DataSet used on a form or used by another component. Initialization occurs at runtime. |
| Equals(Object) | Determines whether the specified object is equal to the current object. |
| Finalize | Release resources to perform additional cleanup. |
| GetChanges | Gets a copy of the DataSet that contains all changes to the DataSet since it was loaded or since the last time AcceptChanges was called. |
| GetChanges(DataRowState) | Gets a copy of the DataSet filtered by DataRowState that contains all changes to the data set since it was last loaded or since AcceptChanges was called. |
| GetDataSetSchema | Get a copy of the XmlSchemaSet for the DataSet. |
| GetObjectData | Populate the serialized information object with the data required to serialize the DataSet. |
| GetType | Get the Type of the current instance. |
| GetXML | Returns an XML representation of the data stored in the DataSet. |
| GetXMLSchema | Returns the XML schema of the XML representation of the data stored in the DataSet. |
| HasChanges() | Gets a value indicating whether the DataSet has changed, including new rows, deleted rows, or modified rows. |
| HasChanges(DataRowState) | Gets a value that indicates whether the DataSet has DataRowState filtered changes, including new rows, deleted rows, or modified rows. |
| IsBinarySerialized | Check the format of the serialized representation of the DataSet. |
| Load(IDataReader, LoadOption, DataTable[]) | Use the provided IDataReader to populate the DataSet with values from the data source, while using an array of DataTable instances to provide schema and namespace information. |
| Load(IDataReader, LoadOption, String[]) | Populate the DataSet with values from the data source using the provided IDataReader and using a string array to provide names for the tables in the DataSet. |
| Merge() | Merges the specified array of DataSet, DataTable, or DataRow objects into the current DataSet or DataTable. There are different overloads of this method. |
| ReadXML() | Read the XML schema and data into the DataSet. There are different overloads of this method. |
| ReadXMLSchema(0) | Read the XML schema into the DataSet. There are different overloads of this method. |
| RejectChanges | Rollback all changes made to the DataSet since it was created or since the last time DataSet.AcceptChanges was called. |
| WriteXML() | Write XML data and schema from DataSet. There are different overloads of this method. |
| WriteXMLSchema() | Write XML schema from DataSet. There are different overloads of this method. |
The DataTable class represents a table in a database. It has the following important properties: Most properties are read-only except the PrimaryKey property:
| property | describe |
|---|---|
| ChildRelations | Gets the collection of child relationships of this DataTable. |
| Columns | Gets the collection of columns belonging to this table. |
| Constraints | Gets the collection of constraints maintained by this table. |
| DataSet | Get the DataSet to which this table belongs. |
| DefaultView | Gets a custom view of a table that may include filtered views or cursor positions. |
| ParentRelations | Gets the collection of parent relationships of this DataTable. |
| PrimaryKey | Gets or sets the array of columns that serve as primary keys for the data table. |
| Rows | Gets the collection of rows belonging to this table. |
The following table lists some important methods of the DataTable class:
| method | describe |
|---|---|
| AcceptChanges | Commits all changes made to this DataSet since it was loaded or since AcceptChanges was last called. |
| Clear | Clears the DataSet of any data by removing all rows from all tables. |
| GetChanges | Gets a copy of the DataSet that contains all changes to the DataSet since it was last loaded or since AcceptChanges was called. |
| GetErrors | Gets an array of DataRow objects containing errors. |
| ImportRows | Copy the DataRow into the DataTable, retaining any property settings and initial and current values. |
| LoadDataRow | Find and update specific rows. If no matching row is found, a new row is created with the given value. |
| Merge | Merges the specified array of DataSet, DataTable, or DataRow objects into the current DataSet or DataTable. |
| NewRow | Create a new DataRow with the same schema as the table. |
| RejectChanges | Rollback all changes made to the table since the table was loaded or since AcceptChanges was last called. |
| Reset | Clears all tables and removes all relationships, external constraints, and tables from the DataSet. Subclasses should override Reset to restore the DataSet to its original state. |
| Select | Gets an array of DataRow objects. |
The DataRow object represents a row in the table and has the following important properties:
| property | describe |
|---|---|
| HasErrors | Indicates if there are errors. |
| Items | Get or set the data stored in a specific column. |
| ItemArrays | Get or set all values in this row. |
| Table | Return the parent table. |
The following table lists the important methods of the DataRow class:
| method | describe |
|---|---|
| AcceptChanges | Applies all changes since this method was called. |
| BeginEdit | Start editing operation. |
| CancelEdit | Cancel the editing operation. |
| Delete | Delete data rows. |
| EndEdit | End the editing operation. |
| GetChildRows | Get the sub-rows of this row. |
| GetParentRow | Get the parent row. |
| GetParentRows | Get the parent row of the DataRow. |
| RejectChanges | Rollback all changes made after AcceptChanges was called. |
The DataAdapter object acts as an intermediary between the DataSet object and the database. This helps the DataSet to obtain data from multiple databases or other data sources.
DataReader objects are an alternative to combining DataSet and DataAdapter. This object provides directed access to data records in the database. These objects are suitable only for read-only access, such as populating a list and then disconnecting.
The DbConnection object represents the connection to the data source. This connection can be shared between different command objects. A DbCommand object represents a command or a stored process that is sent to the database for retrieving or manipulating data.
So far, we have used tables and databases from our computers. In this case, we will create a table, add columns, rows and data, and display the table using a GridView control.
The source file code is as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="createdatabase._Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title> Untitled Page </title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </div> </form> </body></html>The code of the file is as follows:
namespace createdatabase{ public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataSet ds = CreateDataSet(); GridView1.DataSource = ds.Tables["Student"]; GridView1.DataBind(); } } private DataSet CreateDataSet() { //creating a DataSet object for tables DataSet dataset = new DataSet(); // creating the student table DataTable Students = CreateStudentTable(); dataset.Tables.Add(Students); return dataset; } private DataTable CreateStudentTable() { DataTable Students = new DataTable("Student"); // adding columns AddNewColumn(Students, "System.Int32", "StudentID"); AddNewColumn(Students, "System.String", "StudentName"); AddNewColumn(Students, "System.String", "StudentCity"); // adding rows AddNewRow(Students, 1, "MH Kabir", "Kolkata"); AddNewRow(Students, 1, "Shreya Sharma", "Delhi"); AddNewRow(Students, 1, "Rini Mukherjee", "Hyderabad"); AddNewRow(Students, 1, "Sunil Dubey", "Bikaner"); AddNewRow(Students, 1, "Rajat Mishra", "Patna"); return Students; } private void AddNewColumn(DataTable table, string columnType, string columnName) { DataColumn column = table.Columns.Add(columnName, Type.GetType(columnType)); } //adding data into the table private void AddNewRow(DataTable table, int id, string name, string city) { DataRow newrow = table.NewRow(); newrow["StudentID"] = id; newrow["StudentName"] = name; newrow["StudentCity"] = city; table.Rows.Add(newrow); } }}As you execute the program, observe the following aspects:
The program first creates a data collection and then binds it using the GridView control's DataBind() method.
The Createdataset() method is a user-defined function that creates a new DataSet object and calls the additional user-defined CreateStudentTable() method to create tables and then add them to the DataSet's table collection.
The CreateStudentTable() method calls the user-defined AddNewColumn() and AddNewRow() methods to create the columns and rows of the table and add data to the rows. When the page gets executed, it returns the table rows as shown below:
