cool hit counter NET objects - Connection, Command, DataReader, DataAdapter, DataSet, DataTable_Intefrankly

NET objects - Connection, Command, DataReader, DataAdapter, DataSet, DataTable

Copyright: This is an original post by the blogger and may not be reproduced without the blogger's permission.

Maybe it's because I didn't summarize it properly in the first place and learn . NET since the object in ADO.NET has been somewhat ambiguous, today I have revisited it, by checking the information, summarizing, and combining their own views to organize the ADO.NET in Connection, Command, DataReader, DataAdapter, and

DataSet, DataTable these objects related knowledge, I hope that the beginner can play a global control role, I hope that the big cattle can leave valuable guidance.

Those who do not plan for the world will not be able to plan for a single moment; those who do not plan for the whole picture will not be able to plan for a single area. A picture from the internet, to start with, shows the general relationship between the objects.

The following examples are only for connecting to SQL Server databases, so SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter, DataSet, and DataTable are used as examples for illustration.

ADO. NET object


When we make a phone call, there are usually three steps: dial → talk → hang up.

The same is true for dealing with databases. Usually when we access a database, we first have to establish a connection to the database through SqlConnection, then operate on the database, and finally close the connection to the database.

The following is a complete lifecycle of a SqlConnection.

<span style="font-family:SimSun;">    string strConn = "server=;database=DataBaseName;uid=sa;pwd=123456";  //Define the database connection string
SqlConnection conn = new SqlConnection(strConn);                                    // Instantiate the Sqlconnection object
     //Specific operations
     //Specific operations
    conn.Close();                                                                      // complete operation, Connection closed</span>


Literally it would be understoodSqlCommand It's an order., proper together with After the database connection is established, Then you can useSqlCommand to the database for the add, delete and change check、 Execution of stored procedures, etc.。

We can create SqlCommand objects in three ways.

1. Create directly and then assign values to the attributes separately.

<span style="font-family:SimSun;">    string strConn,cmdText;
    cmdText="select * from TableName"; 
    SqlCommand cmd=new SqlCommand();
    cmd.cmdText="select * from TableName";</span>

2. Use the constructor of SqlCommand to create.

<span style="font-family:SimSun;">    string strConn,cmdText;
    cmdText="select * from TableName"; 
    SqlCommand cmd=new SqlCommand(strConn,cmdText);</span>

3、 pass (a bill or inspection)SqlConnection target groupCreateCommand() approach establish:

<span style="font-family:SimSun;">    strConn,cmdText;
    SqlConnection conn=new SqlConnection(strConn);
    SqlCommand cmd=conn.CreateCommand();
    cmd.cmdText="select * from TableName"; </span>

SqlCommand Commonly Used properties There are.

◆ CommandText: The SQL statement, table name or stored procedure to be executed.

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

◆ Parameters:CommandText Parameters in, This can be done bycmd.Parameters.Add() perhapscmd.Parameters.AddRange() approach Adding parameters。

SqlCommand Commonly Used approach There are.

◆ ExecuteNonQuery(): The return value is shaped, indicating that the database add, delete and change After that, the number of rows affected by the database can also be executed directly.

◆ExecuteReader(): The return value isSqlDataReader types, For queries against the database, The following is a review ofSqlDataReader The descriptions of the will state。

◆ExecuteScalar(): The return value isobject types, For querying a single value in the database, For example, the name found is“ John Doe”, Then the query can be converted to character type; The age of Zhang San found is21, Then the query can be converted to an integer。

Read more aboutSqlCommand The details of the can consultMSDN: SqlCommand Class


SqlDataReader mainly together withSqlCommand Used in combination to quickly read, And this reading can only“ moving forward” Read data。

Let's say the query comes up with the above four pieces of data,SqlDataReader Only by name respectively“ John Doe”→“ the fact that the black sheep are in the wrong place”→“ Wang Wu (-5), Chinese character actor”→“ Zhao Jun (1876-1966), Chinese communist party politician” Read the tuple in descending order。SqlDataReader ofRead() approach The return value is a Boolean type, Read down if data exists in the next entry, returnTrue, If no data exists, returnFalse, similarlyVB hit the target EOF and BOF

establish:SqlDataReader can only be provided bySqlCommand target groupExecuteReader() approach establish。

SqlDataReader You can read data line by line, It can also be assigned directly as a data source to some control'sDateSource properties。

Note that: a SqlConnection connection allows only one SqlDataReader open, at this time the SqlConnection object only for this one SqlDataReader service, if you want to open another SqlDataReader, you must first talk about the previous one closed (of course, you can also create another SqlConnection object, understand the multi-threaded will understand). Just as it is impossible for a person to eat two bowls of rice at the same time with one mouth, you have to stop eating from the first bowl before you can go to the second bowl.


Operations on the database can be divided into two ways, one connection-based and one offline. As shown below.

SqlDataAdapter is usually used together with DataSet. In the above diagram, the left part of the SqlConnection is kept open while SqlCommand and SqlDataReader are operating; the workflow of the right part is: SqlConnection opens the connection to the database first, and SqlDataAdapter then extracts the data queried from the database and saves (through) it into the DataSet (through the Fill() method of SqlDataAdapter). Meanwhile the SqlDataAdapter connection to the DataSet is closed When the data in the DataSet is updated, the SqlDataAdapter then "carries" the updated data from the DataSet to the database and updates it (via the SqlDataAdapter's Update() method).

DataAdapter is like a mover, when querying data, it queries in the database and moves the query results to the DataSet, when the user performs an add, delete, change operation on the data in the DataSet (i.e., the data in the DataSet changes), the DataAdapter then moves the updated data back to the database (i.e., the database is updated).

We can do this with three approach come round establishDataAdapter targets( consult)

1. Create by database connection string and query statement.

<span style="font-family:SimSun;">    string strConn,cmdText;
    cmdText="select * from TableName";
    SqlDataAdapter sda=new SqlDataAdapter(cmdText,strConn);</span>

By looking at the second method below you will see that this method has a disadvantage, if you need to create multiple SqlDataAdapter objects in the system, each time you create a SqlDataAdapter object, you will "implicitly" create a SqlConnection object.

2, through the SqlConnection object to create.

<span style="font-family:SimSun;">    string strConn,cmdText;
    cmdText="select * from TableName";
    SqlConnection conn=new SqlConnection(strConn);
    SqlDataAdapter sda=new SqlDataAdapter(cmdText,conn);</span>

such approach together with first type approach The only difference is more establish have a look atSqlConnection targets, The benefits of doing so are, If the need arises again establish several SqlDataAdapter object, Just change the query statementcmdText be fine, The second parameter can also be used with the sameSqlConnection targetsconn, Save system resources。

3, through the SqlCommand object to create.

<span style="font-family:SimSun;">    string strConn,cmdText;
    cmdText="select * from TableName";
    SqlConnection conn=new SqlConnection(strConn);
    SqlCommand cmd=new SqlCommand(cmdText,conn);
    SqlDataAdapter sda=new SqlDataAdapter(ccmd);</span>

SqlDataAdapter object is actually equivalent to a more powerful SqlCommand object, SqlDataAdapter object contains InsertCommand, DeleteCommand, UpdateCommand, SelectCommand, four objects, respectively, used to add, delete, and check the operation. Examples are as follows.

public static DataTable SelectAll()
        SqlConnection conn=new SqlConnection("server=.;database=Test;uid=sa;pwd=123456");    // establish Database connection object
         SqlDataAdapter sda = new SqlDataAdapter();                                            // Instantiate the SqlDataAdapter object
        sda.SelectCommand = new SqlCommand("select * from Person", conn);                    // establish SqlDataAdapter object ofSelectCommand targets
         DataSet ds = new DataSet();                                                           // Instantiate a DataSet
        sda.Fill(ds, "Person");                                                              // paddingDataSet, establish anPerson table and put the query data into thePerson table
        return ds.Tables["Person"];                                                          // return toPerson Table of data

Note here that you do not need to open the database connection object when executing SelectCommand; if the connection is now closed, the SelectCommand object will automatically open the database connection.

More details on SqlDataAdapter can be found at SqlDataAdapter object

DataSet together withDataTable

The DataReader mentioned above is used in conjunction with SqlCommand and can only "read" data, but not save it. A DataSet can be thought of as an in-memory "database", or a collection of data tables. In the case of disconnection from the SqlDataAdapter, the DataSet provides the same relational data model as a relational database. DataSet consists of one or more tables that are DataTable, and the database table DataTable consists of DataRow and DataColumn.

An example of traversing a DataSet and outputting it is written below (the same is true for writing to a DataSet).

     DataSet ds=new DataSet();          // Instantiate a DataSet
     //...... // Query operation (populate DataSet)
     DataTable dt=ds. Tables[0];         // Get the first table of the DataSet (note that the first table has a subscript of 0)
     // traversing rows
    foreach(DataRow dr in dt.Rows)
          // traversing columns
         for(int i=0;i<dt.Columns.Count;i++)
              Response.Write(dr[i].ToString()+" ");


To quote the chart again.

SqlDataReader is usually used together with SqlCommand, and is commonly used for simple browsing and short database operations.

DataSet long with SqlDataAdapter, DataSet will read the required data into memory and then disconnected from the SqlDataAdapter, in the local memory to operate, if you need to update in the SqlDataAdapter refresh the database.

Also, SqlDataReader, DataSet, and DataTable can all be used as data sources to assign values directly to the DataSource property of certain controls.

1、20 Immature Tips I Have for Building Deep Neural Networks
2、Distributed Advancedzookeepers zab protocol works in crash recovery mode
3、Data Enhancement of Images for Deep Learning
4、Quick Start Series MVC 02 Routing
5、vue adds and removes table rows through datadriven implementation

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送