ASP.NET ADO.NET
May 13, 2021 ASP.NET
Table of contents
ADO.NET
ADO.NET provides a bridge between the front-end controls and the back-end database. A DO.NET object encapsulates all data access operations and controls that interact with the data display object. S o the details of the data movement are hidden.
The following charts show the ADO.NET objects:
DataSet class
The dataset represents a subset of the database. I t does not have a continuous connection to the database. R econnecting is required in order to upgrade the database. D ataSet includes DataTable and DataRelation objects. T he DataRelation object represents the relationship between the two tables.
The following table is some of the important properties of the DataSet class:
| Property | Describe |
|---|---|
| CaseSensitive | Explains whether the strings compared to the data table are case sensitive. |
| Container | Get space for the component. |
| DataSetName | Gets or sets the name of an existing collection of data. |
| 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 followed when trying to upload files. |
| Events | Gets a list of event processors associated with this component. |
| ExtendedProperties | Gets a collection of custom user information related to DataSet. |
| HasErrors | Indicates if there are any errors. |
| IsInitialized | Indicates whether DataSet is initialized. |
| Locale | Gets or sets the information used to compare strings with tables. |
| Namespace | Get or set the namespace for DataSet. |
| Prefix | Get or set an XML prefix, which is an alias for the namespace. |
| Relations | Returns a collection of DataRelation objects. |
| Tables | Returns a collection of DataTable objects. |
The following table lists some important methods for the DataSet class:
| Method | Describe |
|---|---|
| AcceptChanges | Accept all changes due to loading DataSet or this method. |
| BeginInit | Start the initialization of DataSet. This initialization occurs at runtime. |
| Clear | Clear the data. |
| Clone | Clones the structure of DataSet, including the structure, relationships, and limitations of all DataTables. However, the data is not cloned. |
| Copy | Copy data and structures. |
| CreateDataReader() | DataTableReader with a result set is returned for each DataTable in the same order as the tables in the Tables collection. |
| CreateDataReader(DataTable[]) | Returns a DataTableReader with a result set for each DataTable. |
| EndInit | Ends the initialization of DataSet that is used on a form or by another component. Initialization occurs at runtime. |
| Equals(Object) | Determines whether the specified object is equal to the current object. |
| Finalize | Free resources to perform additional purges. |
| GetChanges | Gets a copy of DataSet that contains all changes made to the dataset since loading or since the last call to AcceptChanges. |
| GetChanges(DataRowState) | Gets a copy of the DataSet filtered by DataRowState that contains all the changes made to the dataset since the last load or since AcceptChanges was called. |
| GetDataSetSchema | Get a copy of XmlSchemaSet for DataSet. |
| GetObjectData | Fill the serialized information object with the data required to serialize DataSet. |
| GetType | Gets the Type of the current instance. |
| GetXML | Returns an XML representation of the data stored in DataSet. |
| GetXMLSchema | Returns the XML schema in the form of an XML representing the data stored in DataSet. |
| HasChanges() | Gets a value that indicates whether DataSet has changed, including new, deleted, or modified rows. |
| HasChanges(DataRowState) | Gets a value that indicates whether DataSet has dataRowState filtered changes, including new, deleted, or modified rows. |
| IsBinarySerialized | Check the format of the serialized represent of DataSet. |
| Load(IDataReader, LoadOption, DataTable[]) | Use the provided IDataReader to populate DataSet with the value of the data source, while using an array of DataTable instances to provide schema and namespace information. |
| Load(IDataReader, LoadOption, String[]) | Use the provided IDataReader and use an array of strings to give the table in DataSet a name to populate the DataSet with values from the data source. |
| Merge() | Combine the number of specified DataSet, DataTable, or DataRow objects into the current DataSet or DataTable. This method has different forms of overloading. |
| ReadXML() | Read the XML schema and data into DataSet. This method has different forms of overloading. |
| ReadXMLSchema(0) | Read the XML schema into DataSet. This method has different forms of overloading. |
| RejectChanges | Roll back all changes made to DataSet since it was created or since the last time DataSet.AcceptChanges was called. |
| WriteXML() | Write XML data and architecture from DataSet. This method has different forms of overloading. |
| WriteXMLSchema() | Write the XML schema from DataSet. This method has different forms of overloading. |
DataTable class
The DataTable class represents a table in the database. I t has the following important properties: Most properties are read-only properties except the PrimaryKey property:
| Property | Describe |
|---|---|
| ChildRelations | Gets a collection of child relationships for this DataTable. |
| Columns | Gets a collection of columns that belong to the table. |
| Constraints | Gets a collection of constraints maintained by the table. |
| DataSet | Gets the DataSet to which this table belongs. |
| DefaultView | Gets a custom view of a table that might include filtering views or cursor locations. |
| ParentRelations | Gets a collection of parent relationships for the DataTable. |
| PrimaryKey | Gets or sets an array of columns that act as the primary key of the data table. |
| Rows | Gets a collection of rows that belong to the table. |
The following table shows some important methods for dataTable classes:
| Method | Describe |
|---|---|
| AcceptChanges | Submit any changes made to this DataSet since it was loaded or last called AcceptChanges. |
| Clear | DataSet to clear any data by removing all rows in all tables. |
| GetChanges | Gets a copy of DataSet that contains all changes made to the dataset since the last load or since AcceptChanges was called. |
| GetErrors | Gets an array of DataRow objects that contain errors. |
| ImportRows | Copy DataRow to DataTable, keeping any property settings as well as the initial and current values. |
| LoadDataRow | Find and update specific rows. If no matching rows are found, a new row is created with the given value. |
| Merge | Combine the number of specified DataSet, DataTable, or DataRow objects into the current DataSet or DataTable. |
| NewRow | Create a new DataRow with the same schema as the table. |
| RejectChanges | Roll back any changes made to the table since it was loaded or since the last time AcceptChanges was called. |
| Reset | Clear all tables and remove all relationships, external constraints, and tables from DataSet. Subsyses should override Reset to restore DataSet to its original state. |
| Select | Gets an array of DataRow objects. |
DataRow class
The DataRow object represents a row in the table and has the following important properties:
| Property | Describe |
|---|---|
| HasErrors | Indicates if there is an error. |
| Items | Get or set up data stored in a specific column. |
| ItemArrays | Get or set all the values in this line. |
| Table | Return to the parent table. |
The following table shows the important methods of the DataRow class:
| Method | Describe |
|---|---|
| AcceptChanges | Apply all changes since the method was called. |
| BeginEdit | Start editing. |
| CancelEdit | Cancel the edit operation. |
| Delete | Delete the data row. |
| EndEdit | End the edit. |
| GetChildRows | Get a sub-bank of the Bank. |
| GetParentRow | Gets the parent row. |
| GetParentRows | Gets the parent row of DataRow. |
| RejectChanges | Roll back all changes after AcceptChanges calls. |
DataAdapter object
The DataAdapter object acts as an intermediary between the DataSet object and the database. T his helps DataSet get data from multiple databases or other data sources.
DataReader object
The DataReader object is an alternative to the combination of DataSet and DataAdapter. T his object provides targeted access to data records in the database. T hese objects are only suitable for read-only access, such as filling a list and then disconnecting.
DbCommand and DbConnection objects
The DbConnection object represents the connection of the data source.
This connection can be shared between different command objects.
The DbCommand object represents a command or a stored process from retrieving or manipulating data sent to the database.
Example
So far, we have applied the tables and databases on our computer. I n this case, we'll create a table, add columns, rows, and data, and display the table with the 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 for 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, "M H 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:
- The program first creates a collection of data and then constrains it with the DataBind() method of the GridView control.
- The Createataset() method is a user-defined feature that creates a new DataSet object and calls other user-defined CreateStudentTable() methods to create tables and then add them to the table collection of the data collection.
-
The CreateStudentTable() method calls the user-defined AddNewColumn() and AddNewRow() methods to create columns and rows of a table and add data to the rows at the same time.
When the page gets executed, it returns the table's rows as shown in the following image:
