Database operations through ADO. net

Database Operations mainly involve executing commands and reading data.

 

1. Connection

 

To access the database, you must first establish a connection with the database. In ADO. net, the connection object is used to establish a connection with the database.

 

For example, the code used to establish a connection to the mycharge database on the sqlserver Server:

 

Dim cnstr as string = "Password = 123456; persist Security info = true; user id = sa;" & _ "initialcatalog = mycharge; data Source = 192.168.24.60 "dim CN assqlconnection = new sqlconnection () CN. connectionstring = cnstr 'ononstring is the most important attribute of the connection object. It is used to specify the connection string CN used for establishing a connection with the database. open () 'Open the connection

Since the connection string is mentioned in the code, let's talk about the connection string. Usually, when establishing a connection with the database, you need to write that large connection string, in fact, writing is not troublesome (if you are familiar with and careful) and cannot be connected at all without fault. At this time, we can use the following method to obtain the connection string.

1. Create a text file and change its extension to udl.

2. Double-click the file you just created to open the "Database Connection Properties" dialog box.

3. Select a provider on the "providers" tab. Here we select SQL Server and click "Next ".

4. On the "connection" tab, enter the database name, user name, password, and other information. Click "test connection" to check whether the connection is successful.

5. If it succeeds, close this dialog box and use NotePad to open the file again, which contains the information you want.

Note: Because sqlserver objects are specific to sqlserver databases, you are not allowed to specify the provider attribute in their connection strings.
It's wrong to copy all the above information. Instead, you need to remove provider = sqloledb.1 and just copy the subsequent content! This problem does not exist for connection objects of other data providers, such as oledbconnection objects.

 

2. Commands

 

Basic methods for accessing data using ADO. net

1. Use Datasets
2. Perform operations on the database directly

 

How can I access a dataset?

1. Fill in the dataset

Load external data sources to a dataset (using an adapter)

 

What is an adapter?

The adapter is like a bridge used to slow down data between data sources and datasets,

You can use an adapter to add, delete, modify, and query data. The values are as follows:

1. Selectcommand: Specifies a command object to retrieve rows from the data storage area
2. Insertcommand: Specifies a command object to insert rows into the data storage area.
3. Updatecommand: Specifies a command object to modify rows in the data storage area.
4. Deletecommand: specify a bitter object to delete rows from the data storage area.

 

2. dataset update

1. Update data in a dataset (generally add, delete, or modify data)
2. The updated dataset is written to the same data source. That is to say, the changes are not directly written to the data source during the dataset update process. Because the data source is disconnected, the Code must be executed explicitly. The specific method is to call the update method of the data adapter.

Note: In some cases, datasets cannot be used. For example, to create database elements such as tables, you must use the command object

 

How can I operate databases directly?

 

Execute SQL statements or stored procedures using command objects. If the execution result returns a record set, you can use the datareader (data reader) object to read data .. Net creates command objects for different providers, for example:

• Oledbcommand: used for ole db providers
• Sqlcommand: SQL Server7.0 or later
• Odbccommand: command object of ODBC Data Source

Steps for using the command object

1. Construct a command object

The following code constructs the CMD command object. It uses cn as the connection object. The command to be executed is to select all data from the information table.

Dim cnstr as string = "Password = 123456; persist Security info = true; user id = sa;" & _ "initialcatalog = mycharge; data Source = 192.168.24.60 "dim CN assqlconnection = new sqlconnection () CN. connectionstring = cnstr 'ononstring is the most important attribute of the connection object. It is used to specify the connection string CN used for establishing a connection with the database. open () dim SQL as string = "select * from categories" dim cmd assqlcommand = new sqlcommand () cmd. connection = cncmd. commandtext = SQL

2. Run the command

 

There are many methods provided by the command object for executing commands. The specific method used depends on the data returned by the command execution result.

 

• Cancel: cancels Command Execution

• Executenonquery: executes non-query SQL statements and returns the affected number of rows.

• Executereader: executes the query and returns the query result to datareader.

• Executescalar: executes the query and returns the first column of the First row in the returned result set. The extra column or row is ignored.

• Executexmlreader: executes the query and returns the query result to an xmlreader object.

 

3. Read data

The following code reads data from the table information and outputs all data of the Data column ID and name to the console

Dim cnstr as string = "Password = 123456; persist Security info = true; user id = sa;" & _ "initialcatalog = mycharge; data Source = 192.168.24.60 "dim CN assqlconnection = new sqlconnection () CN. connectionstring = cnstr 'ononstring is the most important attribute of the connection object. It is used to specify the connection string CN used for establishing a connection with the database. open () dim SQL as string = "select * from categories" dim cmd assqlcommand = new sqlcommand (SQL, CN) dim Dr assqldatareader = cmd. executereader () while (dr. reader () dim ID as string = DR ("ID "). tostring () dim name as string = DR ("name "). tostring () console. writeline ("No.: {0} Name: {1}", ID, name) end whiledr. close () CN. close ()

Note:

1. Datereader is a forward-only connection cursor, that is, records can only be traversed in one direction. During this process, the database connection must always be open; otherwise, data cannot be read through datareader.

1. After reading data, you must call the close () method to close the datareader object,