Ado. NET database access Technology _ database other

I. ADO. NET's definition

ADO. NET originates from COM component Library ADO (that is, ActiveX Data Objects), is a new generation of Microsoft Company. NET database access model, is currently used by database program designers to develop based on. NET database application is the main interface. It uses a. NET Data Provider (data provider) for database connectivity and access, through the Ado.net database program can use a variety of objects to access eligible database content, so that vendors provide database management system can be based on this standard to open the corresponding. NET Data Provider, the database application staff does not have to understand the details of the underlying operations of various databases, and can access all databases that support the. NET Data Provider As long as they have a model of the objects provided by Ado.net.

Ado. NET structure model is shown below:

Figure 1. Ado. NET structure model

Ado. NET is a class library, these classes provide a lot of objects, can be used to complete database connections and additions and deletions 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 additions and deletions to check the operation; 3) DataReader: Used to return read-only data from the database, fill the DataSet object with the data source; 4 DataAdapter: To use the DataSet object to realize the control of the database; 5 DataSet: Can be considered as an off-line database in memory; These five objects provide two ways to read the database; the first way: Using 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 operation diagram:

Figure 1. Ado. NET structure model

Two. Steps for using Ado.net to access the database online (SQL Server database here)

1. Connecting to the database
Direct 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 (); 
Operation of database Data 
Conn. Close ();

(1). First add namespace System.Data.SqlClient;

(2). Define the database connection string:

The first method is to store the database connection string directly in the string object, as shown in the code above;

The second method Web: Store the database connection information in the Web.config configuration file, and then invoke it by using the Configurationstringsettings class. Here's an example to illustrate:

(a). First, define the database connection information 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. The database connection string is then defined as:

Copy Code code as follows:

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

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

2. Create a database connection
1 Create the Connection object:

2 Open the database: Conn. Open (); In general, when in. NET, such as database connections, Windows, or graphic objects, it is a good idea to ensure that each resource is closed immediately after it has been used. Although. NET designers implement the automatic garbage collection mechanism, garbage will eventually be recycled, but still need to release resources as early as possible to avoid the lack of resources. When you write code that accesses a database, you may be affected by other sessions because the connection is opened for a slightly longer time than needed. In extreme cases, not shutting down the connection may leave other users unable to access an entire 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 use. These two ways are as follows: (1) The first approach is to 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 level of the try...catch...finally block is sometimes not easy to see. There is also a way to ensure that resources are closed--using statements. (2) using a using statement block

using (SqlConnection conn = new SqlConnection (connstring)) { 
//Open the connection 
Conn. Open (); 
Operation of database Data 
}

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

3. Create DATABASE Operations Command:

When a connection object is connected to a data source, the command object is used to perform queries, inserts, modifications, and deletions on the data source.

(1) Create SQL Database Operations Command: SQLQuery query specific rules please see my series of articles: "Reading notes" SQL Server query Statement _ Deng Zhijong (2) Create command objects:

(a). Method I:

Copy Code code as follows:

SqlCommand command = new SqlCommand (); Command. Connection = conn; Command.commandtext = "SQL statement";

(b). Method II:

Note: 1). If a SQL query statement contains a variable of a C # program and joins as a string, you should note that the data is enclosed in single quotes for variables that are not numeric; 2. Use parameterized query statements in SQL query statements, such as:

When you need to assign a value to this parameter, you can use the command object to establish the Parameter object and then assign the value:

Copy Code code as follows:

Command. Parameters.Add (New SqlParameter (@siteName, siteName)); Command. parameters["@siteName"]. Value = "http://#";

Note: The AddWithValue (string parametername, Object value) method is added to SqlClient in the. Net Framework 2.0. This method simplifies the process of invoking the input parameters of the stored procedure, and determines the type of data entered at runtime to obtain the corresponding database type. Therefore, the method is lower in operational efficiency than with ADD (string parametername, SqlDbType sqldbtype, int size, string SourceColumn). The Add () method is still recommended where efficiency is high, and addwithvalue () can be used in other situations to simplify code writing.

4. Execute 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 is being returned from the command. The command class provides the following executable commands: (1) ExecuteNonQuery ()-Executes the command without returning any results. Typically used in an update, insert, or DELETE statement, where the unique return value is the number of records affected. However, if a stored procedure with an output parameter is invoked, the method has a return value. (2) ExecuteReader ()--Executes the command, returning a typed IDataReader. is the easiest and quickest way to select some data from a data source. (3) ExecuteScalar ()-Executes the command to return the value of the first row in the result set.

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

Conn. Close ();

three. Offline Database access

The DataAdapter object performs the transfer of data between the connection object and the DataSet object, populates the DataSet object with data, or returns the updated data of the DataSet object to the data source, or to the Command object. Generates DataAdapter SQL operations commands such as INSERT, UPDATE, and delete from the Commandbuilding object. After accessing a data source using a dataset and a DataTable object, ADO. NET automatically off-line, processing data in memory, such as the operation to modify the data, will automatically reconnect the data source, update the database. The relationship between the DataSet object, the DataAdapter object, and the data source is as follows:

DataSet <---> DataAdapter <---> Data source
The following are steps for offline database access using DataAdapter:

1. Create a DataAdapter, DataTable object (using a SQL Server database)

Copy Code code as follows:

Sqladapter da = new Sqladapter ("SQLQuery query Statement", conn); DataSet = ds = new DataSet (); DataTable dt = new DataTable ();

2. Populating data to a DataTable object

3. The properties and methods of processing tatatable objects for data in a DataTable are:

Name Attribute/Method Description
Rows.Add () method inserts new data rows
Rows[n]. Delete () method deletes records in row n
Rows.Count property to get the number of rows
rows[i]["ColumnName" property gets the value of line I, column name ColumnName
Rows[i][j] Property gets the value of line I, column J

There is also a data query technique: LINQ. We'll talk about it next time.

Through the above Ado.net database access technology, I hope to help.