Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the ADO.NET database access technologies". In the daily operation, I believe many people have doubts about the ADO.NET database access technology. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what are the ADO.NET database access technologies?" Next, please follow the editor to study!

one。 Definition of ADO.NET

ADO.NET, which comes from COM component library ADO (ActiveX Data Objects), is the access model of Microsoft's new generation .NET database, and is the main interface used by database programmers to develop database applications based on .NET. It uses .NET Data Provider (data provider) to connect and access databases, and through ADO.NET database programs can use various objects to access qualified database contents, so that manufacturers providing database management systems can open their corresponding. NET Data Provider according to this standard, so that database application designers do not have to know the details of the underlying operation of all kinds of databases. As long as you master the model of the objects provided by ADO.NET, you can access all databases that support .NET Data Provider.

The ADO.NET structure model is as follows:

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 add, delete, check and modify and other operations. These include the following five objects: 1) Connection: used to connect to the database (SQL Server uses SqlConnection objects); 2) Command: used to perform SQL commands on the database, such as additions, deletions, queries, etc.; 3) DataReader: used to return read-only data from the database and populate DataSet objects with data sources; 4) DataAdapter: used in combination with DataSet objects to control the database; 5) DataSet: can be regarded as offline in the database in memory These five objects provide two ways to read the database; the first way: using Connection, Command, DataReader, its permissions can only read or query the database; the second way: using Connection, Command, DataAdapter, DataSet, its permissions can carry out a variety of database operations. Schematic diagram of ADO.NET read database operation:

Figure 1. ADO.NET structure model

two。 To access a database online using ADO.NET (here the SQL Server database is used)

1. Connect to the database

Go straight to the previous piece 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 (); / / A pair of database data to operate conn.Close ()

(1)。 First add the namespace System.Data.SqlClient

(2)。 Define the database connection string:

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

The second method, Web, stores the database connection information in the web.config configuration file and then invokes it by using the ConfigurationStringSettings class. Let's give an example to illustrate:

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

(B). In the project file, add a reference to Configuration and add using System.Configuration in the header. Then define the database connection string as:

The copy code is as follows:

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 above code.

two。 Create a database connection

1) create a Connection object:

String conn = new SqlConnection (connString)

2) Open the database: conn.Open (); in general, when using "scarce" resources in .NET, such as database connections, windows, or graphical objects, it is best to make sure that each resource is closed immediately after use. Although .NET designers have implemented an automatic garbage collection mechanism and garbage will eventually be recycled, resources need to be released as early as possible to avoid resource scarcity. When writing code that accesses the database, other sessions may be affected because the connection takes slightly longer to open than it takes. In extreme cases, not closing the connection may prevent other users from entering 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. The two ways are as follows: (1) the first way: use the try...catch...finally statement block to ensure that any open connections are closed in the finally.

Try {/ / open the connection conn.Open (); / / an operation on 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 not easy to see. There is another way to ensure that resources are turned off-- the using statement. (2) use using statement blocks

Using (SqlConnection conn = new SqlConnection (connstring)) {/ / Open the connection conn.Open (); / / A pair of database data operations}

No matter how the block exits, the using clause ensures that the database connection is closed.

3. Create database operation command:

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

(1) create SQL database operation command: sqlQuery query statement specific rules, please see my series of articles: [reading Notes] SQL Server query statement _ Deng Zhirong (2) create Command object:

(a). Method 1:

The copy 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)

Remarks: 1). If SQL query contains variables of C # program and concatenates them in the form of strings, it should be noted that variables with non-numeric data are enclosed in single quotation marks. 2). If you use parameterized query statements in SQL query statements, such as:

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 the parameter object, and then assign the value:

The copy code is as follows:

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

Note: AddWithValue (string parameterName, object value) method is added to SqlClient in .net Framework 2.0. This method simplifies the input parameter process of the call storage process, judges the input data type at run time, and obtains the corresponding database type. Therefore, the running efficiency of this method is lower than that of Add (string parameterName, SqlDbType sqlDbType, int size, string sourceColumn) method. The Add () method is still recommended where efficiency is high, and AddWithValue () can be used to simplify the amount of code written in other situations.

4. Execute the sqlQuery command:

Once the command is defined, you need to execute it. Statements are executed in a variety of ways, depending on what data is returned from the command. The Command class provides the following executable commands: (1) ExecuteNonQuery ()-- executes the command without returning 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 output parameters, the method has a return value. (2) ExecuteReader ()-executes the command and returns a typed IDataReader. Is the easiest and quickest way to select some data from a data source. (3) ExecuteScalar ()-executes the command and returns the value of the first row and first column in the result set.

5. Close the database connection after the operation on the database:

Conn.Close ()

three。 Offline database access

The DataAdapter object mainly performs the data transmission between the Connection object and the DataSet object, fills the data into the DataSet object, or returns the updated data of the DataSet object to the data source, or can be constructed on the Command object to generate Insert, Update, Delete and other SQL operation commands of DataAdapter through the CommandBuilding object. After accessing the data source using DataSet and DataTable objects, ADO.NET will automatically go offline and process the data in memory. If there is any operation to modify the data, it will automatically reconnect to the data source and update the database. The relationships between DataSet objects, DataAdapter objects, and data sources are as follows:

DataSet DataAdapter data source

Here are the steps for offline database access using DataAdapter:

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

The copy code is as follows:

SqlAdapter da = new SqlAdapter ("sqlQuery query statement", conn); DataSet = ds = new DataSet (); DataTable dt = new DataTable ()

two。 Populate data into DataTable objects

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

3. The properties and methods of the TataTable object for working with data in DataTable are:

Name property / method description

The Rows.Add () method inserts a new data row

The rows [n] .Delete () method deletes the record of the nth line

The Rows.Count property gets the number of rows

The Rows [I] ["ColumnName"] attribute gets the value of row I and column name ColumnName

The Rows [I] [j] attribute gets the values of row I and column j

At this point, the study of "what are the ADO.NET database access technologies" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.