Technical route of the Ado.net

In the past, we used the database to operate the method is to connect the previous database, in the state of the connection to maintain the data of various operations, such as additions and deletions to check. Such a situation will have two disadvantages, one is always maintain the connection will cause a waste of resources, and the other is the network of various instability factors will be the loss of this connection, so that the operation of the data will also be lost.

In view of the two or more other reasons mentioned above, Microsoft proposed another mode of operation, is the connectionless mode of data operations, of course, did not discard the previous ADO in the typical connection mode of data operations, and therefore the birth of ado.net and its two typical data operation mode, namely, connection mode and connectionless mode.

Of course, regardless of the pattern used to manipulate the data, the first thing is to get a data connection and then manipulate the data source or copy of the data source. The connection is the work that must be done in the first step. The connection method and the ADO era is not much different, nothing more than the first to establish a Connection object (or called instantiating a Connection object), to complete the connection must know the connection of the data source address, some also need to know the data source provider. The following are typical examples:

String connstring = "Data source=localhost;integrated security=sspi;initial catalog=northwind;";

String connstring= "Provider=sqloledb;data source=localhost;initial catalog=northwind;";

Generally, OLE DB data sources need to be set provider

Once in place an instantiated connection object

OleDbConnection myconn=new OleDbConnection (connstring);

or set the data source of the connection object with the Connection property

OleDbConnection myconn=new OleDbConnection ();

MyConn. connectionstring=connstring;

Next you can open the connection using the connection open () method

MyConn.Open ()

The connection has been completed, then enter the most important data operation phase (need to know more about the connection object to build the students can go to search for more information on their own OH)

　　First, the connection mode

The object of primary responsibility in connection mode is DataReader (however, when it comes to DataReader, you have to mention the Excutereader () method, because DataReader is a class that has no constructors, DataReader can only be instantiated through the command class's Excutereader () method, so DataReader has the following characteristics:

1. It can only be instantiated through the command's Excutereader () method without a constructor.

such as: SqlCommand Sqlcm=new SqlCommand (); Sqldatarader Dr =sqlcm.excutereader ();

2. When it uses the database, the database connection must remain open (in this state, connection-related operations are not possible)

3. It can only traverse information from the trip, can not stop halfway to modify the data

4. Based on what I said earlier, always keep the connection waste resources, plus the second above, then we have to shut down, such as:

Dr.close () or if we are afraid of forgetting to close, give a parameter when instantiating commandbehavior.closeconnection

Sqldatarader dr = Cmd.executereader (commandbehavior.closeconnection);

The use of DataReader is more flexible, which is usually achieved by conditional judgment, such as:

while (Dr.reader ())

{

................

}

It is also true to see whether it is desirable for some purpose to manipulate the DataReader data to be read out, and then to have a new empty DataTable to convert DataReader into a DataTable. If you really want to transform between the DataTable and the DataReader, you can use the following method:

DataReader to the DataTable:

Datatable.load (DataReader object, loadoption.overwritechanges);

DataTable turn DataReader:

DataReader Reader=datatable.createdatareader ()

　　Second, connectionless mode

The two objects that assume primary responsibility in connectionless mode are DataAdapter and dataset

Disconnected mode DataAdapter played a role in the bridge, see this some people may associate to the network adapter or power adapter, yes, their functions are the same, the various types of network type of power to the unified to meet the requirements of the type, The DataAdapter here is the data adapter that transforms the various types of data sources into data that can be filled into the dataset.

The use of Dataadatper objects generally has two functions, one is to execute SQL statements through command objects such as SqlCommand, to detect data from the data source, and to populate the dataset with the retrieved results; Another way is to update the user's changes to the DataSet object to the data source.

Some people are tempted to ask why the connectionless mode does not need to be taken this step???

In fact, in connection mode, the command object is used to update all the steps of the data object directly into the database, which is called monomer operation.

In connection mode, the dataset is disconnected from the data server. The operation of the monomer does not have the opportunity to update to the database at any time, which also requires such an object in all data operations after the completion of all kinds of data operations (such as additions and deletions and other operations) with a variety of corresponding SQL commands unified update to the database, And this object is Dataadapterle, so to speak, Dataadapterle uses its built-in SQL command set for bulk SQL operations.

Let's first look at the instantiation of Command and DataAdapter, which we can instantiate in general:

OleDbCommand olecmd=new OleDbCommand (sqlstring,connobj);

Oledataadapter oledpt=new Oledataadapter (sqlstring,connobj);

They both received an SQL string and a connection object connection, so what's the difference between command and DataAdapter?

In the face of SQL statements, the difference is:

The command is primarily to run pure SQL commands that directly enable the operation of SQL statements.

DataAdapter is embedded with a set of SQL commands (that is, its four attributes), such as select,delete,insert,update, to wait until one of them has to be executed, or to command objects to come forward, that is, to instantiate the internal Command object, If it is not instantiated, it is often an error, and cannot be compiled (many people are using the Dataadpter Update method), so we often use this:

Oledpt.selectcomand=new Olecommand ();

For fetching data

Command combined with Excutereader () using DataReader to implement

DataAdapter can be implemented using fill (), but it should not be implemented with fill.

Here's the Fill () method, which opens DataAdapter to discover that the fill () method comes from the implementation of his parent class, where the Fill method invokes the Fillfromcommand or Fillfromreader method. Command Object Excutereader is also called separately in the two methods. So the next thing is not difficult to understand, interested friends can take a look at the implementation of the Fill method.

The DataAdapter object can hide and connection the details of communication with the Command object, create and initialize the DataTable by DataAdapter object, and combine the DataSet object to store the data table copy in memory. Implementation of offline database operations. The DataAdapter object allows data from the DataSet object to be saved to the data source, or read from the data source, and can also be added, deleted, updated, and so on by the underlying data storage body.

The DataAdapter object contains four different operation commands, as follows:

(1), SelectCommand: Used or to go to the record in the data source;

(2), InsertCommand: Used to insert a new record into the data source;

(3), UpdateCommand: Used to update the data in the data source;

(4), DeleteCommand: Used to delete the record in the source of the number of dramas.

It is worth noting that, after instantiating the DataAdapter object, this DataAdapter is still an impractical data adapter because its operation on the database and the dataset is actually through its four command objects (SelectCommand, Insertcommand,updatecommand,deletecommand) to achieve. So we instantiate the SqlDataAdapter object and we need to instantiate its associated SqlCommand object.

Sqladapter.selectcommand = new SqlCommand ();

Sqladapter.insertcommand = new SqlCommand ();

Sqladapter.updatecommand = new SqlCommand ();

Sqladapter.deletecommand = new SqlCommand ();

These four SqlCommand references actually point to an instance of a SqlCommand object, and by using the CommandBuilder object, you can automatically instantiate three commands (Insertcommand,deletecommand, UpdateCommand) But note that to automatically generate three commands must use the instantiated SelectCommand to retrieve the required elements, then you need to pay attention to the DataAdapter instantiation Way, If you instantiate with an empty parameter method, you must set the SelectCommand property separately to use CommandBuilder, such as:

SqlDataAdapter adapter = new SqlDataAdapter ();

Adapter. SelectCommand = new SqlCommand (querystring, connection);

SqlCommandBuilder builder = new SqlCommandBuilder (adapter);

If, for example, a parameter is instantiated, there is no need to specifically declare the SelectCommand property, because the DataAdapter of the parameter instantiation has automatically completed the SelectCommand property when instantiated, as

SqlDataAdapter adapter = new SqlDataAdapter ("Select * from table", sqlconn);

SqlCommandBuilder builder = new SqlCommandBuilder (adapter);

Therefore, the generic null parameter instantiation of the DataAdapter and the SelectCommand property setting occurs simultaneously. DataAdapter instantiation with parameters can be used directly with CommandBuilder.

The disconnect mode of the dataset is relative to the data server disconnect, do not have to connect with the data server every time, and the dataset's data is actually in the cache of services (some people mistakenly believe that there is a client, it is wrong, think about it, then know that the data is certainly unsafe), This means that if the data is large, the memory of the server is wasted, which is also a disadvantage of the connectionless mode, in contrast to the way DataReader read a row and save a line of memory.

　　Review the relationship between the two models

The two patterns appear to have two routes, but there are many things in common, the most prominent of the following two aspects:

1, "Start the same" they need the form of the data connection, start by creating a Connection object to complete the connection

2, "The end of the same" they need to be in the SqlCommand object to execute the operation of the SQL statement, and finally in the SqlCommand object with the implementation of the data additions and deletions to check operations.

For example, SQL operations for connection mode:

SqlCommand sqlcm=new SqlCommand ()//It is obvious that the SqlCommand instance object was used

Sqldatarader Dr =sqlcm.excutereader ();

? if (Dr. Hasrow)

? {

while (Dr.read ())

{

dr[""]..........//get the value of the field

}

}

Non-connection mode SQL operations:

SqlDataAdapter Slqad=new SqlDataAdapter ();

Sqlad.selectcommand=new SqlCommand (Sqlstring,conn); Obviously, it's also used here SqlCommand