[. Net] C #. Net ADO. NET data access model Overview

 

 

Ado. Net Object Model

The ADO. Net object model has five main components: connection object, command object, datareader object, dataadapter object, and DataSet object. The components that are responsible for establishing online and data operations are called the data operation component (managed providers), which is composed of the connection object, command object, dataadapter object, and datareader object. The data operation component is mainly used as a bridge between dataset objects and data sources. It is responsible for extracting data from the data source and inserting it into the DataSet object, and storing the data back to the data source.

Connection object

The connection object is mainly used to enable the connection between the program and the database. You cannot obtain data from the database if you do not use the linked object to open the database. This object is at the bottom layer of ADO. net. We can generate this object by ourselves or automatically generate it by other objects.

Command object

The command object can be used to send commands to the database, such as sending query, addition, modification, and deletion commands to the database, as well as calling the stored programs in the database. This object is structured on the connection object, that is, the command object is executed by connecting to the connection object of the data source. Therefore, the database to which the connection is linked, the command of the command object goes down to where.

Dataadapter object

The dataadapter object is mainly used to transmit data between data sources and Dataset. It can issue commands through the command object and put the obtained data into the DataSet object. This object is structured on the command object and provides many functions used with dataset.

DataSet object

The DataSet object can be regarded as a cache, which can keep the data queried from the database and even display the whole database. Dataset can not only store multiple tables, but also obtain data table structures such as primary keys through the dataadapter object and record the association between data tables. The DataSet object can be called ADO. net heavyweight object, which is structured on the dataadapter object and does not have the ability to communicate with the data source. That is to say, we use the dataadapter object as the DataSet object and a bridge between data sources for data transmission. The datareader object can be used when we only need to read data sequentially without other operations.

The datareader object only reads data from the data source in a descending order, and the data is read-only and does not allow other operations. Because datareader only reads one row at a time and can only be read-only, it not only saves resources but also improves efficiency. In addition to high efficiency, datareader can reduce network load because it does not need to transmit all data.

For different databases, ADO. NET provides two sets of class libraries: the first set of class libraries can access all the databases provided based on oledb, such as sqlserver, access, and Oracle. The second set of class libraries is used to access sqlserver databases.

The specific object names are as follows:

Object	Oledb object	SQL object
Connection	Oledbconnection	Sqlconnection
Command	Oledbcommand	Sqlcommand
Datareader	Oledbdatareader	Sqldatareader
Dataadapter	Oledbdataadapter	Sqldataadapter
Dataset	Dataset	Dataset

 

1. connection string:

SQL client data provider + Microsoft SQL Server 2000/2005

Authentication Mode: "Server = (local); database = Forum; user id = sa; Password = sa ;"

Trust mode: "Server = (local); database = Forum; trusted_connection = true ;"

Olddb data provider + Microsoft Office acess

"Provider = Microsoft. Jet. oledb.4.0; Data Source = mydb. mdb; user id =; Password = ;"

 

2. Connect to the database using the connect object

 

Example:

 

String sconnectionstring = "";

If (rbl_mode.selectedvalue = "0 ")
{
Sconnectionstring = string. format ("Server = {0}; database = {1}; user id = {2}; Password = {3}; connection timeout = 2", tb_server.text, tb_database.text, tb_userid.text, tb_password.text );

}
Else
{
Sconnectionstring = string. Format ("Server = {0}; database = {1}; trusted_connection = true; connection timeout = 2", tb_server.text, tb_database.text );
}
Sqlconnection conn = new sqlconnection (sconnectionstring );
Try
{
Conn. open ();
If (conn. State = connectionstate. open)
Response. Write ("database connected successfully ");
}
Catch (sqlexception)
{
Response. Write (sqlexception. Message );
}
Finally
{
If (conn. State = connectionstate. open)
Conn. Close ();
}

3. Use connectionstringbuilder to construct a string

// Use connectionstringbuilder to construct the connection string
Sqlconnectionstringbuilder sqlconnectionbuilder = new sqlconnectionstringbuilder ();
If (rbl_mode.selectedvalue = "0 ")
{
Sqlconnectionbuilder. datasource = tb_server.text;
Sqlconnectionbuilder. initialcatalog = tb_database.text;
Sqlconnectionbuilder. userid = tb_userid.text;
Sqlconnectionbuilder. Password = tb_password.text;
Sqlconnectionbuilder. connecttimeout = 2;
}
Else
{
Sqlconnectionbuilder. datasource = tb_server.text;
Sqlconnectionbuilder. initialcatalog = tb_database.text;
Sqlconnectionbuilder. integratedsecurity = true;
Sqlconnectionbuilder. connecttimeout = 2;
}
Sqlconnection conn = new sqlconnection (sqlconnectionbuilder. connectionstring );
Try
{
Conn. open ();
If (conn. State = connectionstate. open)
Response. Write ("database connected successfully ");
}
Catch (sqlexception)
{
Response. Write (sqlexception. Message );
}
Finally
{
If (conn. State = connectionstate. open)
Conn. Close ();
}
4. enumerate all available data sources

If (! Ispostback)
{
Sqldatasourceenumerator instance = sqldatasourceenumerator. instance;
Ddl_server.datasource = instance. getdatasources ();
Ddl_server.datatextfield = "servername ";
Ddl_server.databind (); // enumerate available SQL Server servers in the current network and fill in the drop-down list box
}