ADO. NET database access technology, ado.net Database Access

ADO. NET database access technology, ado.net Database Access

1. Definitions of ADO. NET

ADO. NET comes from the COM Component Library ADO (ActiveX Data Objects), is a new generation of Microsoft. NET database access model, which is currently used by database program designers to develop based on. the main interface of the database application. It uses. NET Data Provider (Data Provider) for database connection and access, through ADO. the NET database program can use various objects to access the qualified database content, so that various vendors that provide the database management system can open the corresponding according to this standard. NET Data Provider. In this way, database application developers do not have to understand the details of the underlying operations of various databases, as long as they master ADO. the object model provided by. NET allows you to access all supported objects. NET Data Provider database.

The ADO. NET Structure Model is as follows:

Figure 1. ADO. NET Structure Model

ADO. NET is a class library that provides many objects for database connection, addition, deletion, query, modification, and other operations. These include the following five objects: 1) Connection: used to connect to the database (SQL Server uses the SqlConnection object); 2) Command: used to execute SQL commands on the database, such as addition, deletion, query, modification, and other operations; 3) DataReader: Used to return read-only data from the database and fill the DataSet object with the data source; 4) DataAdapter: used in combination with the DataSet object, implement Database Control; 5) DataSet: can be seen as a database in the memory when it is offline; these five objects provide two ways to read the database; the first method: with Connection, Command, and DataReader, the database can only be read or queried. The second method is to use Connection, Command, DataAdapter, and DataSet to perform operations on various databases. ADO. NET database read operations:

Figure 1. ADO. NET Structure Model

 
2. Steps for online database access using ADO. NET (SQL Server database is used here)
 
1. Connect to the database
The previous code:

Using System. data. sqlClient; string source = "server = (local); integrated security = SSPI; database = myDateabase; User ID = sa; pwd = sa"; SqlConnection conn = new SqlConnection (source ); conn. open (); // operate conn on database data. close ();

(1) first add the namespace System. Data. SqlClient;

(2). Define the database connection string:

Method 1: directly store the database connection string in the string object, as shown in the code above;

Method 2 Web: store the database connection information in the web. config configuration file, and call it by using the ConfigurationStringSettings class. Here is an example:

(A) First, define the database connection information in the web. config configuration file:

<configuration><connectionStrings><add name="myDatabase" connectionString="server=(local); integrated security=SSPI; database=myDateabase;User ID=sa;pwd=sa" providerName="System.Data.SqlClient" /></connectionStrings></configuration>

(B) Add a reference to Configuration in the project file and add using System. Configuration in the header. Then the database connection string is defined:

Copy codeThe Code is as follows: string connstring = ConfigurationManager. ConnectionStrings ["myDatabase"]. ConnectionString;

Note: you must add a reference to the System. Configuration assembly to parse the ConfigurationManager class used in the above Code.

2. Create a database connection
1) create a Connection object:

string conn = new SqlConnection(connString); 

2) Open the database: conn. open (); generally, when. when using "scarce" Resources in. NET, such as database connections, windows, or graphical objects, it is best to ensure that each resource is closed immediately after use. Although the. NET designer implements an automatic garbage collection mechanism, the garbage will be recycled, but the resource needs to be released as early as possible to avoid resource shortage. When writing code to access the database, other sessions may be affected because the connection is opened for a little longer than the required time. In extreme cases, not disabling a connection may prevent other users from entering a group of data tables, greatly reducing application performance. There are two ways to ensure that similar "scarce" resources such as database connections are released immediately after use. The two methods are as follows: (1) Method 1: Use the try... catch... finally statement block to ensure that any opened connections are closed in finally.

Try {// open the connection conn. open (); // operate on database data} catch (SqlException ex) {// log the exception} finally {conn. close ();}

Many resources may be opened in the given method, so that the levels of try... catch... finally blocks are sometimes difficult to see. There is also a way to ensure that the resource is closed-using statement. (2) Use using statement Blocks

Using (SqlConnection conn = new SqlConnection (connstring) {// Open the connection conn. Open (); // operate on database data}

No matter how the block exits, the using clause will disable the database connection.

 
3. Create Database Operation commands:

After the Connection object establishes a Connection with the data source, the Command object is used to query, insert, modify, and delete the data source.

(1) create a SQL database operation Command: For detailed rules of sqlQuery query statements, see my series of articles: [Reading Notes] SQL Server query statement _ Deng zhirong (2) create a Command object:

(A). Method 1:

Copy codeThe Code is as follows: SqlCommand command = new SqlCommand (); command. Connection = conn; command. CommandText = "SQL statement ";

(B). Method 2:

SqlCommand command = new SqlCommand ("SQL statement", conn );

Note: 1 ). if the SQL query statement contains C # program variables and is connected as strings, you should note that the data is enclosed in single quotes for non-numeric variables. 2 ). if you use a parameterized query statement in an SQL query statement, for example:

command.CommandText = "SELECT * FROM myTable WHERE siteName=@siteName"; 

When you need to assign a value to this parameter, you can use the Command object to create a parameter object, and then assign a value:

Copy codeThe Code is as follows: command. Parameters. Add (new SqlParameter (@ siteName, siteName); command. Parameters ["@ siteName"]. Value = "http ://#";

Note: In. Net Framework 2.0, SqlClient adds the AddWithValue (string parameterName, object value) method. This method simplifies the input parameter process for calling the stored procedure, and determines the input data type during runtime to obtain the corresponding database type. Therefore, this method is less efficient than the Add (string parameterName, SqlDbType sqlDbType, int size, string sourceColumn) method. We recommend that you use the Add () method where the efficiency requirement is high. In other cases, you can use AddWithValue () to simplify the amount of code writing.

 
4. Execute the sqlQuery command:

After the command is defined, You need to execute it. There are multiple methods to execute the statement, depending on the data to be returned from the command. The Command class provides the following executable commands: (1) ExecuteNonQuery () -- execute the Command, but no results are returned. It is generally used in UPDATE, INSERT, or DELETE statements. The unique return value is the number of affected records. However, if a stored procedure with output parameters is called, The method returns a value. (2) ExecuteReader () -- execute the command and return a typed IDataReader. Is the easiest and quickest way to select some data from the data source. (3) ExecuteScalar () -- execute the command to return the value of the first column in the first row of the result set.

 
5. Close the database connection after the database operation is completed:

conn.Close();

 
3. offline database access

The DataAdapter object transfers data between the Connection object and the DataSet object, fills in the data into the DataSet object, or returns the updated data of the DataSet object to the data source, you can also use the CommandBuilding object to generate SQL operation commands such as Insert, Update, and Delete of DataAdapter on the Command object. After the DataSet and able objects are used to access the data source, ADO. NET automatically goes offline and processes the data in the memory. If you modify the data, the data source is automatically reconnected to and the database is updated. The relationships between DataSet objects, DataAdapter objects, and data sources are as follows:

DataSet <---> DataAdapter <---> Data Source
The following describes how to use DataAdapter to access an offline database:

1. Create a DataAdapter and able object (using the SQL Server database)

Copy codeThe Code is as follows: SqlAdapter da = new SqlAdapter ("sqlQuery query statement", conn); DataSet = ds = new DataSet (); DataTable dt = new DataTable ();

2. Fill in the data to the able object

da.Fill(ds, "TableName"); dt = ds.Tables["TableName"]; 

3. attributes and methods for processing data in a DataTable TataTable object include:

Name attribute/method description
Rows. Add () method to insert new data Rows
The Rows [n]. Delete () method deletes the records of row n.
The Rows. Count attribute obtains the number of Rows.
Rows [I] ["ColumnName"] attribute to obtain the value of row I and column name ColumnName
Rows [I] [j] attribute obtains the value of row I and column j.
 
There is also a Data Query Technology: LINQ. Next, we will discuss it again.

Through the introduction of ADO. NET database access technology above, I hope to help you.