ASP. NET database usage example-read database data

 

Read data from the database

In ASP. NET, you can use datareader and dataset to read data from the database. The differences between the two methods are as follows:

To use a dataset object to read data, perform the following five steps:

(1) Create a database connection. You can choose sqlconnection or oledbconnection.

(2) Save the query in the sqldataadapter or oledbdataadapter object

(3) use the fill method of the dataadapter object to fill the data in the datatable in the dataset.

(4) create a dataview object for the datatable object to display data. This step can be omitted.

(5) bind dataview or dataset to server control.

The following six steps are required to use datareader to read data:

(1) Create a database connection. You can choose sqlconnection or oledbconnection.

(2) Use the OPEN method of the connection object to OPEN the database link

(3) Save the query in the sqlcommand or oledbcommand object.

(4) Call the executereader method of the command object to read data into the datareader object.

(5) Call the READ or GET method of datareader to READ a piece of data for display

(6) Call the close method of the connection object to close the database link.

1. Read data with datareader

We have learned how to create a database link and enable a database. However, executing SQL commands and enabling a table must depend on the command object. The program that uses the command object to execute SQL commands is roughly as follows:

Dim cmd as oledbcommand 'declares a command object

'Create a command object and specify the SQL statement

Response. write (server. mappath ("user. mdb "))

Cmd = new oledbcommand ("select * from reg", comm)

Dim rd as oledbdatareader 'declares A datareader object

Rdw.0000.exe cutereader () 'executes the SQL command and sets the result to datareader.

In the statement that creates the command object, the conn parameter is a connection, and the first parameter is an SQL command.

The command object must call the executereader method when executing the SQL command. The executereader method will assign the returned results of the SQL command to the datareader object.

The following uses the datareader object to read data in the database:

Dim I as integer

While rd. read ()

Response. write ("<ul> ")

For I = 0 to rd. fieldcount-1

Response. write ("<li>" & rd. getname (I) & "=" & rd. item (I ))

Next

Response. write ("</ul> ")

End while

The Getitem (index) method is used to obtain the field name, and item (index) is used to obtain the field value.

Pay attention to the following attributes when using datareader objects:

● The read method reads data from a single row. If the read is successful, the data of the read row is stored in the datareader object. Then, the honor list is automatically moved to the next row and true is returned, if the read fails, false is returned. Therefore, you can use the while statement to determine whether the last record of the database has been read. In addition, it is worth noting that after reading a data record, the datareader object will automatically move the cursor to the next record without the need to use the move next statement. This is a big difference between ADO. NET and ADO.

● Fieldcount attribute indicates the number of columns recorded in the datareader object. It is worth noting that the index value of the item attribute starts from 0, so the largest index value is the fieldcount-1.

2. Use dataset to read data

The datareader object can only read data records in one row. If you use datareader for large-scale data processing, it is a little too slow. Its advantage is to save memory, the biggest advantage of using a dataset object is that it can read the entire table into the memory at the same time, so it is best for data binding. For example, when using the datagrid Control to display data, using dataset to read data is the best choice. However, its biggest disadvantage is its memory consumption.

(1) Use dataadapter and dataset

Dataset objects must be used together with dataadapter objects.

Enter a table as a dataset object

Dim conn as oledbconnection 'declares an oledbconnection object

Conn = new oledbconnection () 'creates an oledbconnection object

'Set the connection string connectionstring

Conn. connectionstring = "provider = microsoft. jet. oledb.4.0;" & "data source =" & "server. mappath (" user. mdb ")

Conn. open ()

'Create a dataadapter object and specify the SQL statement

Da = new oledbdataadapter ("select * from reg", conn)

Dim ds as dataset 'declares a dataset object

Da. fill (ds, "Register User table") 'registers a data table named "Register User table" in the dataset object.

(2) operate data with datatable

After learning the relationship between the dataset object and the able object, the following describes how to use datatable to operate the data.

To read Table content using a datatable object, you must use the following two attributes:

● Columns attribute and data column information. This attribute is a collection object composed of datacolumn objects.

● Rows attribute data row information, which is a collection object composed of datarow objects

If you use a loop to read all the data, you can use the datareader object to read the data. The code is roughly as follows:

For I = 0 to dt. rows. count-1

Response. write ("<ul> ")

For j = 0 to dt. columns. count-1

Str = "<li>" & dt. columns (j). caption

Str = str & "=" dt. rows (I). item (j)

Response. write (str)

Next

Response. write ("</ul> ")

Next