On the object--connection, Command, DataReader, DataAdapter, DataSet and DataTable in ADO

Maybe it was not a good summary of the reason, study. NET since the object of the ADO has been a little vague, today re-review, through the data, summary, combined with their own views of the Connection,Command , DataReader , DataAdapter,

DataSet , DataTable The relevant knowledge of these objects, hope that beginners can play a global role in the control, I hope Daniel can leave valuable guidance.

The example below is only for the example of connecting a SQL Server database, so for example, SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter, DataSet, DataTable, respectively, are described.

ADO. NET Object " SqlConnection"

When we call, there are usually three steps: Dial → call → hang up the machine.

The same is true of the process of dealing with databases. Usually we access the database, first of all we must establish a connection with the database through SqlConnection, then operate the database, and finally close the connection to the database.

The following is a complete SqlConnection life cycle:

<span style= "Font-family:simsun;" >    String strconn = "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";  Define the database connection string    SqlConnection conn = new SqlConnection (strconn);                                   Instantiating SqlConnection objects    //Specific actions//    ...    ... Specific Operation    Conn. Close ();                                                                      Operation completed, connection closed </span>

"SqlCommand"

Literally you will understand that SqlCommand is a command, when connected with the database, you can use the SqlCommand to the database for pruning, execution of stored procedures and so on.

There are three ways to create SqlCommand objects:

1, directly create, and then assign values to the attribute:     

<span style= "Font-family:simsun;" >    string strconn,cmdtext;    Strconn= "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";    cmdtext= "SELECT * from TableName";     SqlCommand cmd=new SqlCommand ();    Cmd. Connection=strconn;    cmd.cmdtext= "SELECT * from TableName";</span>

2. Create using the SqlCommand constructor:    

<span style= "Font-family:simsun;" >    string strconn,cmdtext;    Strconn= "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";    cmdtext= "SELECT * from TableName";     SqlCommand cmd=new SqlCommand (strconn,cmdtext);</span>

3. Create by SqlConnection object's CreateCommand () method:

<span style= "Font-family:simsun;" >    strconn,cmdtext;    Strconn= "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";    SqlConnection conn=new SqlConnection (strconn);    SqlCommand Cmd=conn. CreateCommand ();    cmd.cmdtext= "SELECT * from TableName"; </span>

SqlCommand commonly used properties are:

Command Text: The SQL statement, table name, or stored procedure that needs to be executed.

CommandType: The type of CommandText that needs to be performed, the default value is "Text", which means that the SQL statement is executed; the value "StoredProcedure indicates that the stored procedure is being performed."

the parameters in the Parameters:commandtext can be used by CMD. Parameters.Add () or CMD. The Parameters.addrange () method adds a parameter.

The common methods of SqlCommand are:

ExecuteNonQuery (): The return value is shaped, which means that the number of rows affected by the database can be directly executed after adding or deleting the database.

◆ ExecuteReader (): The return value is SqlDataReader type, which is used for querying the database, as described in the SqlDataReader description below.

◆ ExecuteScalar (): The return value is the object type, used to query the database for a single value, such as the name of the lookup " Zhang San ", then the query can be converted to character type; The age of the Zhang San found is 21, then the query can be converted to an integer type.

more details on SqlCommand can be found in MSDN: SqlCommand class

" SqlDataReader"

SqlDataReader is primarily used in conjunction with SqlCommand for fast reading, and this reading can only "forward" read data.

For example, query to the above four data, SqlDataReader can only follow the name of "Zhang San"→"John Doe"→"Harry"→ "Zhao Liu" to read the tuple. The read () method of the SqlDataReader returns a Boolean type, and returns True if there is data on the next bar while reading down, or False if no data exists, similar to EOF and BOF in VB.

Create : SqlDataReader can only be created by the ExecuteReader () method of the SqlCommand object.

SqlDataReader can read the data individually, or it can be assigned to the Datesource property of some controls directly as a data source.

It is important to note that a SqlConnection connection is only allowed to open a SqlDataReader, when this SqlConnection object is only for this one SqlDataReader service, If you want to open a SqlDataReader again, you must first talk about the previous close (you can also create a SqlConnection object, to understand the multithreading will understand). Just like a person can not eat two bowls of rice at the same time, you have to stop eating the first bowl of rice to eat a second bowl of rice.

" SqlDataAdapter"

The operation of the database can be divided into two ways, one is based on the connection, one is offline. Such as:

    SqlDataAdapter are usually used with datasets, while the left part is SqlCommand and SqlDataReader while the SqlConnection remains in the open state The right part of the workflow is: SqlConnection first open the connection to the database, Sqldata Adapter extracts the data queried from the database and saves (passes) into the dataset (via sql adapter the Fill () method ), simultaneously SqlDataAdapter the connection to the dataset is closed when the data in the dataset is updated, sql data adapter then" moves "the updated data in the dataset into the database and updates ( Span style= "font-size:18px" > via sql data adapter The Update () method ).

DataAdapter is like a porter, when querying the data, it queries the database and moves the query results to the dataset, when the user performs an increment, delete, or modify the data in the dataset (that is, the data in the dataset changes). The DataAdapter then moves the updated data back to the database (that is, the database is updated).

There are three ways we can create DataAdapter objects (refer to)

1. Create by database connection string and query statement:

<span style= "Font-family:simsun;" >    string strconn,cmdtext;    Strconn= "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";    cmdtext= "SELECT * from TableName";    SqlDataAdapter Sda=new SqlDataAdapter (Cmdtext,strconn);</span>

By observing the second method below, it is a disadvantage that if more than one SqlDataAdapter object is created in the system, an SqlConnection object will be "implicitly" created each time the SqlDataAdapter object is created.

2. Create by SqlConnection object:

<span style= "Font-family:simsun;" >    string strconn,cmdtext;    Strconn= "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";    cmdtext= "SELECT * from TableName";    SqlConnection conn=new SqlConnection (strconn);    SqlDataAdapter Sda=new SqlDataAdapter (Cmdtext,conn);</span>

The only difference between this method and the first method is that you create more than one SqlConnection object, and the advantage is that if you need to create multiple SqlDataAdapter objects again, you just need to change the query statement cmdtext. The second parameter can also use the same SqlConnection object conn to conserve system resources.

3. Create by SqlCommand object:

<span style= "Font-family:simsun;" >    string strconn,cmdtext;    Strconn= "server=192.168.24.123;database=databasename;uid=sa;pwd=123456";    cmdtext= "SELECT * from TableName";    SqlConnection conn=new SqlConnection (strconn);    SqlCommand cmd=new SqlCommand (cmdtext,conn);    SqlDataAdapter Sda=new SqlDataAdapter (Ccmd);</span>

For more details on SqlDataAdapter, refer to: SqlDataAdapter object

" DataSet and DataTable"

The DataReader mentioned above is used with SqlCommand and can only "read" The data and cannot be saved. A dataset can be considered a "database" in memory or a collection of data tables. with SqlDataAdapter disconnected, the dataset provides the same relational data model as a relational database. A dataset consists of one or more tables, which are DataTable, and the database table DataTable is made up of DataRow and DataColumn.

Here is an example of traversing a dataset and outputting it (the same is true for a dataset write):

    DataSet ds=new DataSet ();         Instantiate a DataSet    //...                            Query operation (populate dataset)    DataTable Dt=ds. Tables[0];        Gets the first table of the dataset (note that the first table is labeled 0)    //Traversal line    foreach (DataRow dr in DT). Rows)    {         //Traverse column for         (int i=0;i<dt. columns.count;i++)         {              Response.Write (Dr[i]. ToString () + "");         }         Response.Write ("<br/>");    }

Summary：

Once again, quote this picture:

SqlDataReader are often used with SqlCommand, often with simple browsing and short-time database operations.

The dataset length is used in conjunction with SqlDataAdapter, which reads the required data into memory and then disconnects the SqlDataAdapter from the local memory, and refreshes the database as it is re-passed SqlDataAdapter if it needs to be updated.

At the same time, SqlDataReader, datasets, and DataTable can be assigned directly to the DataSource property of some controls as a data source.

On the object--connection, Command, DataReader, DataAdapter, DataSet and DataTable in ADO