Initial ADO. NET data operations

The following describes the direct source and Baidu encyclopedia. The introduction is comprehensive and detailed. as a side dish, we have no reason not to read these boring introductions:

The name of ADO. NET originated from ADO (ActiveX Data Objects) and is a COM component library used to access Data in Microsoft technology. The reason for using the ADO. NET Name Is that Microsoft wants to indicate that this is a preferred data access interface in the NET programming environment.

1 Introduction ado.net allows developers to access data sources in a consistent manner (such as SQL Server and XML) and data sources published through OLE DB and ODBC. Consumer applications that share data can use ado.net to connect to these data sources and retrieve, process, and update the data contained in them. Ado.net divides the data access of data management into discontinuous elements, which can be used separately or in series.. NET Framework data provider for connecting to the database, executing commands, and retrieving results. These results will be directly processed and put into the ado.net DataSet object so that the user can be exposed by means of machine operation (Ad Hoc), combined with data from multiple sources, or transmitted between layers. DataSet objects can also be used separately from. NET Framework data providers to manage local or XML data of applications. The ado.net Class is located in System. Data. dll and is integrated with the Xml Class in System. XML. dll. Ado.net provides functions similar to ActiveX Data Objects (ADO) to native Component Object Model (COM) developers for developers who write Managed code. We recommend that you use ado.net instead of ADO to access data in. NET applications. ADO. NET provides the most direct method for developers to access data in. NET Framework. Privacy Statement:

System. data. dll, System. data. design. dll, System. data. oracleClient. dll, System. data. sqlXml. dll, System. data. linq. dll, System. data. sqlServerCe. dll and System. data. dataSetExtensions. dll components cannot distinguish users' private and non-private data. These components do not collect, store, or transmit private data of any user. However, third-party applications may use these components to collect, store, or transmit users' private data.

3. The traditional data processing architecture is mainly dependent on the interconnected double-layer model. As data processing develops towards a multi-layered architecture, program designers are also switching to interrupt connections to make applications more scalable ). Two main components used for data access and management in ADO. NET3.0 are. NET Framework data provider and DataSet. Data Provider

. NET Framework data provider is a component clearly designed for managing data and quick access to forward read-only data. The Connection object provides a Connection to the data source. The Command object allows developers to access database commands to send back data, modify data, execute Stored Procedure, and transmit or retrieve parameter information. DataReader provides high-performance data streams from data sources. Finally, DataAdapter provides a Bridge between the DataSet object and the data source ). DataAdapter uses the Command object and data source to execute SQL commands to load data into DataSet and adjust data changes in DataSet back to the data source.

DataSetado.net DataSet has been designed to clearly indicate that it can access data independently from any data source. Therefore, it can be used together with multiple sources of data, XML data, or local data used to manage applications. DataSet contains one or more able object sets composed of data columns and data rows, as well as the primary index key, external index key, and condition Constraint in the DataTable object) and related information. 4. It provides platform interoperability and Scalable Data Access. ADO. NET enhances support for non-connection programming modes and supports rich xml. Since the transmitted data is in XML format, any application that can read the XML format can process data. In fact, components that accept data are not necessarily ADO. NET components. They can be a Microsoft Visual Studio-based solution or any application running on other platforms. Ado.net is an object-oriented class library used to interact with data sources. Generally, the data source is a database, but it can also be a text file, an Excel table or an XML file. Ado.net allows interaction with different types of data sources and databases. However, there is not a series of classes related to this to complete such work. Because different data sources use different protocols, different data sources must adopt corresponding protocols. Some old-fashioned data sources use the ODBC protocol, and many new data sources use the OleDb protocol, and more data sources are still emerging.. NET class library. Ado.net provides public methods for interacting with data sources, but uses a set of different class libraries for different data sources. These class libraries are called Data Providers and are usually named based on the protocols used to interact with them and the Data source type. [4]

Ado.net is A. NET technology that interacts with data sources. There are many Data Providers that allow communication with different Data sources-depending on the protocol or database they use. However, no matter what Data Provider is used, developers will use similar objects to interact with the Data source. SqlConnection object management and data source connection. The SqlCommand object allows developers to communicate with the data source and send commands to it. To quickly read data only forward, use SqlDataReader. To disconnect data, use DataSet and implement the SqlDataAdapter that can read or write data sources.

5. The Connection class of the class library must be connected to the database for interaction. Connection help specifies the database server, database name, user name, password, and other parameters required to connect to the database. The Connection object will be used by the Command object to know which data source to execute the Command on. The process of interacting with the database means that the operation to be performed must be specified. This depends on the Command object. Developers use Command objects to send SQL statements to databases. The Command object uses the Connection object to indicate which data source to connect. A developer can use a Command object to directly execute commands, or pass a reference to a Command object to DataAdapter. It saves a group of commands that can operate the data described below. After the Command object establishes a connection with the data, you can use the Command object to execute commands such as query, modification, insertion, and deletion. commonly used methods of Command object include ExecuteReader () method, ExecuteScalar () method, and ExecuteNonQuery () method. You can use the ExecuteNonQuery () method to execute the INSERT command. Many data operations of the DataReader class require developers to read only a string of data. The DataReader object allows developers to obtain results from the SELECT statement of the Command object. Considering performance, data returned from DataReader is fast and only forward data streams. This means that developers can only retrieve data from the data stream in a certain order. This is good for speed, but if developers need to operate data, a better way is to use DataSet. The CDataSet object DataSet object is the representation of data in memory. It includes multiple able objects, and DataTable contains columns and rows, just like a table in a common database. Developers can even define the relationship between tables to create a master-slave relationship (parent-child relationships ). DataSet is used in specific scenarios-it helps to manage data in memory and supports data disconnection. DataSet is the object used by all Data Providers, so it does not need a special prefix like Data Provider. DataAdapter class

At some time, the data used by developers is mainly read-only, and developers seldom need to change it to the underlying data source. In the same situations, you need to cache data in the memory to reduce the number of times that unchanged data is called by the Data Base. DataAdapter disconnects the model to help developers easily process the above situations. When a single batch of read/write operations on the database are continuously changed and returned to the data database, the DataAdapter fills the (fill) DataSet object. DataAadapter includes a reference to the connection object and automatically enable or disable the connection when reading or writing data to the database. In addition, DataAdapter contains Command object references for SELECT, INSERT, UPDATE, and DELETE operations on data. The developer will define a DataAadapter for each Table in the DataSet. It will take care of all connections to the database for the developer. Therefore, developers tell DataAdapter when to load or write data to the database.

Command object operation data

1. query data using the Command object

Using System; using System. collections. generic; using System. configuration; using System. data; using System. data. sqlClient; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; namespace WebApplication {public partial class DemoCommand: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {// create a database connection SqlConnection myConn = GetConnection (); // open the database link myConn. open (); // define the query SQL statement string sqlStr = "select * from mytable"; // initialize the query command SqlCommand myCmd = new SqlCommand (sqlStr, myConn ); // execute the query SqlDataReader dr = myCmd. executeReader (); // read the query data while (dr. read () {Response. write (dr [1]. toString () + "<br>");} myConn. close () ;}} private SqlConnection GetConnection () {// obtain the database link configuration in the configuration file string myStr = ConfigurationManager. appSettings ["ConnectionString"]. toString (); SqlConnection myConn = new SqlConnection (myStr); return myConn ;}}}

2. Use the Command object to add data

Using System; using System. configuration; using System. data; using System. linq; using System. web; using System. web. security; using System. web. UI; using System. web. UI. htmlControls; using System. web. UI. webControls; using System. web. UI. webControls. webParts; using System. xml. linq; using System. data. sqlClient; public partial class _ Default: System. web. UI. page {protected void Page_Load (object sender, EventArgs e ){ If (! IsPostBack) {// create a database connection SqlConnection myConn = GetConnection (); // open the database link myConn. open (); // define the query SQL statement string sqlStr = "insert into tb_Class (ClassName) values ('" + TextBox1.Text + "')"; // initialize the query command SqlCommand myCmd = new SqlCommand (sqlStr, myConn); if (myCmd. executeNonQuery ()> 0) {Response. write ("added successfully! ");} Else {Response. Write (" failed to add! ");} MyConn. Close ();} else {Response. Write (" the content cannot be blank! ") ;}} Public SqlConnection GetConnection () {// obtain the database link configuration in the configuration file string myStr = ConfigurationManager. appSettings ["ConnectionString"]. toString (); SqlConnection myConn = new SqlConnection (myStr); return myConn ;}}

Modification and deletion methods are similar ....

Use DataSet object and DataAdapter object

1. Use the DataAdapter object to fill in the DataSet object

This example shows how to query information from Mytable and call the Fill method of DataAdapter to Fill in the DataSet, and then bind the DataSet data to the GridView.

Using System; using System. collections. generic; using System. data; using System. data. sqlClient; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; namespace WebApplication {public partial class DemoDaset: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {// create a DataSet ds = new DataSet (); string sqlStr = "select * from Mytable "; sqlConnection con = new SqlConnection ("Server = MRWXK \ MRWXK; database = db_12; UId = sa; pwd =;"); SqlDataAdapter dap = new SqlDataAdapter (sqlStr, con ); // connect to the database con. open (); // use the Fill method of the SqlDataAdapter object to Fill in the dataset dap. fill (ds, "Student"); // display all data in the dataset to the GridView1.DataSource = ds; GridView1.DataBind () ;}} in the GridView control ();}}}}

2. Use DataAdapter to update data in the database

Using System; using System. collections. generic; using System. data. sqlClient; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; namespace WebApplication {public partial class DemoDataSet1: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {// connection string and SQL statement SqlConnection con = new SqlConnection ("Server = mrwxk \ mrwxk; database = db_12; UId = sa; pwd =; "); con. open (); string sqlStr = "select * from MyNewTables"; SqlDataAdapter dap = new SqlDataAdapter (sqlStr, con); // create a DataSet object DataSet ds = new DataSet (); // create a SqlCommandBuilder object and associate it with SqlCommandBuilder builder = new SqlCommandBuilder (dap); dap. fill (ds, "News"); for (int I = 0; I <= ds. tables ["News"]. rows. count-1; I ++) {ds. tables ["News"]. rows [I] ["NewsContent"] = ds. tables ["News"]. rows [I] ["NewsContent"]. toString () + DateTime. today. toShortDateString ();} // update SQL Server database dap from DataSet. update (ds, "News"); GridView1.DataSource = ds; GridView1.DataKeyNames = new string [] {"NewsID"}; GridView1.DataBind (); dap. dispose (); ds. dispose (); con. close ();}}}

Note: Data in DadaSet must have at least one primary key column or unique column. If no primary key column or unique Column exists, an InvalidOperation exception will occur when Update is called, the INSERT, UPDATE, or DELETE commands for automatically updating databases are not generated.

Use DataReader object

Use the DataReader object to read data

Using System; using System. collections. generic; using System. data; using System. data. sqlClient; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; namespace WebApplication {public partial class DemoDataReader: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {SqlConnection myConn = new SqlConnection ("Server = mrwxk \ mrwxk; database = db_12; UId = sa; pwd = ;"); string sqlStr = "select * from Mytable"; SqlCommand myCmd = new SqlCommand (sqlStr, myConn); myCmd. commandType = CommandType. text; try {// open the database connection myConn. open (); // execute the SQL statement and return the DataReader object SqlDataReader myDr = myCmd. executeReader (); // display the title string strTxt = "<B> NO. News content </B> <br>" in bold; // read the result set cyclically while (myDr. read ()) {// read the information in the database and display strTxt + = "" + myDr ["NewsID"] + "" + myDr ["NewsContent"] + "<br>" ;} // disable DataReader myDr. close (); Response. write (strTxt);} catch (SqlException ex) {// exception handling Response. write (ex. toString ();} finally {// close the database connection myConn. close ();}}}}}

Note:

After the Reader method is called, the information of the current row is returned to the DataReader object. There are three methods to access data from a specific column.

• Use the list indexer.
• Use the ordinal index.
• Use the type accessors.

Fastest access speed type> ordinal number> column name ...........

Difference between DataReader object and DataSet object

The steps for DataSet to query data for users are as follows:

The steps for DataReader to query data for users are as follows: