Ado. NET Database access Technology

I. ADO. The definition of net

Ado. NET originates from COM component Library ADO (that is, ActiveX Data Objects), is a new generation of Microsoft Corporation. NET database access model is currently used by database program designers to develop based on. NET's primary interface for database applications. It uses. NET Data Provider (data Provider) for database connection and access, through the ADO database program can use a variety of objects to access the content of eligible databases, so that the various vendors providing database management system can open the corresponding. NET Data according to this standard Provider, so that the design database application personnel do not need to understand the details of the various types of database operations, as long as the model of the object provided by ADO, you can access all support. NET Data Provider database.

Ado. The net structure model is as follows:

Figure 1. Ado. NET structure model

Ado. NET is a class library that provides a number of objects that can be used to complete database connections and additions and deletions. 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 to the database, such as adding and deleting the operation of the search and change; 3) DataReader: Used to return read-only data from the database, populate the DataSet object with the data source, 4) DataAdapter: Use with the DataSet object, achieve the control of the database, 5) DataSet: can be regarded as an in-line memory database; These five objects provide two ways to read the database; the first way: Use connection, command, DataReader, whose permissions can only read or query the database; the second way: Use connection, command, DataAdapter, DataSet, whose permissions can operate on a variety of databases. Ado. NET Read database operations:

Figure 2. Ado. NET Read Database

two. To access the database using ADO online (SQL Server database is used here)1. Connect to the database

Directly on a section of 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 (); Operation conn the database data. Close ();

(1). First add the namespace System.Data.SqlClient; (2). Define the database connection string: The first method: The database connection string is stored directly in the string object, as shown in the code above; The second method Web: The database connection information is stored in the Web. config configuration file and then invoked by using the Configurationstringsettings class. Let's take an example to illustrate: (a). First, the database connection information is defined in the section of 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). In the project file, add a reference to the Configuration and add a using System.Configuration to the head. Then define the database connection string as:

String connstring = configurationmanager.connectionstrings["MyDatabase"]. ConnectionString;

Note: You must add a reference to the System.Configuration assembly to resolve the ConfigurationManager class used in the preceding code.

2. Create a database connection

1) Create Connection object: String conn = new SqlConnection (connstring); 2) Open the database: Conn. Open (); Generally, when in. NET when using scarce resources, such as database connections, Windows, or graphical objects, it is best to ensure that each resource is closed immediately after it is used. Although. NET designers implement an automated garbage collection mechanism, and garbage is eventually recycled, but resources need to be released as early as possible to avoid resource scarcity. When you write code that accesses a database, it can affect other sessions because the connection is opened for a slightly longer time than needed. In extreme cases, not shutting down the connection may make it impossible for other users to enter a whole set of data tables, greatly reducing the performance of the application. There are two main ways to ensure that similar "scarce" resources such as database connections are released immediately after they are used. These two approaches are as follows: (1) The first way: Use the try...catch...finally statement block to ensure that any open connections are closed in finally.

try {//Open the connection conn. Open (); Manipulating database Data} catch (SqlException ex) {//log the exception} finally {Conn. Close (); }

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

using (SqlConnection conn = new SqlConnection (connstring)) {//Open the connection conn. Open (); Working with Database Data}

The using clause ensures that the database connection is closed, regardless of how the block exits.

3. Create DATABASE Operations Command:

After the connection object is connected to the data source, it uses the command object to perform operations such as querying, inserting, modifying, and deleting the data source. (1) Create SQL Database Operations Command: SQLQuery query statement specific rules please see my series of articles: "Reading notes" SQL Server query Statement _ Deng Zhijong (2) Create a Command object: (a). Method One: SqlCommand command = new SqlCommand (); Command. Connection = conn; Command.commandtext = "SQL statement"; (b). Method Two: SqlCommand command = new SqlCommand ("SQL statement", conn); Note: 1). If the SQL query statement contains variables of the C # program and is concatenated as a string, note that the data should be enclosed in single quotation marks for non-numeric variables (2). Using parameterized query statements in SQL query statements, for example: Command.commandtext = "SELECT * from myTable WHERE [email protected]"; When you need to assign a value to the parameter, you can use the command object to establish the Parameter object, and then assign the value: command. Parameters.Add (New SqlParameter (@siteName, siteName)); Command. parameters["@siteName"]. Value = "http://#"; Note: in the. Net Framework 2.0, SqlClient adds the AddWithValue (string parametername, Object value) method. This method simplifies the process of invoking the input parameters of the stored procedure, judging the type of data entered at run time, and obtaining the corresponding database type. Therefore, this method is less efficient than the ADD (string parametername, SqlDbType sqldbtype, int size, string SourceColumn) method. The Add () method is still recommended where high efficiency is required, and addwithvalue () can be used to simplify code writing in other situations.

4. Execute the sqlquery command:

Once you have defined the command, you need to execute it. There are several ways to execute a statement, depending on what data you want to return from the command. The command class provides the following executable commands: (1) ExecuteNonQuery ()-Executes the command, but does not return any results. Typically used in update, INSERT, or DELETE statements where the only return value is the number of records affected. However, if you call a stored procedure with an output parameter, the method has a return value. (2) ExecuteReader ()-Executes the command, returning a typed IDataReader. Is the simplest and quickest way to select some data from a data source. (3) ExecuteScalar ()-Executes the command, returning the value of the first column in the first row of the result set.

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

Conn. Close ();

three. Offline Database access

The DataAdapter object mainly performs data transfer between the connection object and the DataSet object, populates the data into the DataSet object, or returns the updated data from the DataSet object to the data source or schema on the command object. Generates DataAdapter SQL operations commands such as INSERT, UPDATE, and delete through the Commandbuilding object. After you use DataSet and DataTable objects to access the data source, ADO. NET automatically goes offline, processes data in memory, and, if there is an action to modify data, automatically reconnect the data source and update the database. The relationship between the DataSet object, the DataAdapter object, and the data source is as follows:

DataSet <---> DataAdapter <---> Data sources

Here are the steps to use DataAdapter for offline database access: 1. Create DataAdapter, DataTable object (using SQL Server database) sqladapter da = new Sqladapter ("SQLQuery query Statement", conn); DataSet = ds = new DataSet (); DataTable dt = new DataTable (); 2. Populate the DataTable object da with data. Fill (ds, "TableName"); DT = ds. tables["TableName"]; 3. Processing data in a DataTable the properties and methods of the Tatatable object are:

name	Properties/Methods	Description
Rows.Add ()	Method	Insert new data row
Rows[n]. Delete ()	Method	Delete the record of the nth row
Rows.Count	Property	Get row Count
rows[i]["ColumnName"]	Property	Gets the value of row I, column named ColumnName
ROWS[I][J]	Property	Get the value of row I, column J

There is also a data query technology: LINQ. We'll discuss it next.

Ado. NET Database access Technology