Ado. Net Study Notes (1)

I have been reading "ADO. Net Practical Guide" in the library for a few days and found that it is a good book. Naturally, I have a learning experience. Based on the clues in the book, I will record my learning experience in the form of code. (The corresponding code is in http://www.adoguy.com/book)

1. Connection
ADO. the biggest feature of net is that it supports operations on the content in the database when the connection is disconnected, which can greatly save the consumption caused by excessive connections, the previous article has provided an example to illustrate ADO. net. We can open the connection when getting data from the database, disconnect the connection after obtaining the data, and operate the data in the dataset, then open the connection when updating the content in dataset to the database. Datareader must be connected all the time.
Pay attention to the following points when using this feature:
(1) The connection must be closed when the connection attribute is changed.
(2) Select conn. changedatabase (dbname) when switching the database to reduce the consumption caused by disconnection and new connection round-trip
Ado. Net also supports built-in database connection pools. After a connection is closed, the connection remains in the pool for a period of time before it is actually closed. If someone requests to establish the same connection before the timeout, the opened connection is allocated to the requester, which can reduce the consumption of frequently opened and disconnected connections. However, in SQL Server 2000, connections with integrated security cannot be pooled.
The events involved in the connection include dispose, infomessage, and statechange, which can be found on msdn and will not be described in detail.
Template code:
Dim conn as sqlconnection
Conn = new sqlconnection ("...... ") 'Is the connection string
Conn. open ()
'.
Conn. Close ()

2. Command object
Ado. Net allows three different methods to obtain data commands, dataset, datareader, and command in the database. It is the most basic method to obtain data by executing SQL commands.

(1) creation can be created in two ways
A. Create a new command object
Dim cmd as new sqlcommand
Cmd. Connection = Conn
Cmd. commandtext = "select * from customer"
B. Obtain the reference to the command object in conn.
Dim cmd as sqlcommand
Cmd = conn. createcommand ();
Cmd. commandtext = "select * from customer"
Recommended method 2

(2) Four Execution Methods
Executenonquery () returns the number of lines affected by the command
Executescalar () returns the first column of the First row (used with the set function)
Executereader () returns a datareader object
Executexmlreader () returns an xmlreader object

(3) parameters are mainly used in the stored procedure in the complex and simplified forms.
Complex Method:
Dim Param as new sqlparameter ("@ return", sqldbtype. INT)
Param. Direction = parameterdirection. returnvalue
Cmd. Parameters. Add (PARAM)
Simplified Method
Cmd. Parameters. Add ("@ return_value", dbtype. int32). Direction = parameterdirection. returnvalue
Suggestion: If you need to use a parameter when processing the output value, you do not need to use a parameter when only processing the input value.

(4) Transactions
SQL statement:
Begin tran
SQL operations
If @ error <> 0
Begin
Rollback tran
Return @ Error
End
Commit tran
Return 0
Write transactions in ADO. net
Cmd. Transaction = conn. begintransaction ()
Try
{
Cmd. commandtext = "..."
Cmd. executenonquery ()
Cmd. transaction. Commit ()
}
Catch (exception ex)
Cmd. transaction. rollback ()
End try
If you want to combine the Database Transaction Processing with some external systems (for example, when the database is updated simultaneously, if the Web update fails, you want to roll back the transaction ), select the client to write the transaction processing (using ADO. net)
Write the transaction statement directly on the server if you only perform database transaction processing (write the transaction in SQL Server2000)
You can create savepoint in a transaction to implement partial transaction rollback.
Cmd. transaction. Save ("new customer ")
Cmd. transaction. rollback ("new customer ")

(5) batch query if multiple SQL statements can be executed together, you can perform batch query. Datareader supports batch query of datasets.
Cmd. commandtext = "select * from customer; select * From inovince ;"
Dim RDR as sqldatareader
RDR = cmd. executereader ()
RDR contains the execution results of two SQL statements.

3. datareader object
The datareader object can only perform top-down access to the queried dataset, but it is highly efficient. If you only access data, you can use datareader. However, datareader must be connected all the time. Therefore, a small part of the result is first stored in the memory and then read from the database, which is equivalent to a cache mechanism. This is obvious for millions of query results.
Template code:
Do while RDR. Read ()
Console. writeline (RDR (0) 'can also output RDR ("mermerid ")
Loop
If you want to limit the type, you can output (string) RDR (0) or RDR. getstring (0)

When reading data from datareader, check whether the attribute is empty. If the attribute can be empty, check whether the attribute is empty.
If not RDR. isdbnull (0)
Console. writeline (...)

When reading records with datareader, the database is locked by default. You can change the default attribute of datareader by changing it.

If the data in datareader is obtained by executing a batch processing statement, you can access it through nextresult.
Template code:
Do
Do while RDR. Read ()
Console. writeline (RDR (0 ))
Loop
Loop while RDR. nextresult ()

Process metadata (display the status of each attribute)
Dim schema as datatable
Schema = RDR. getschematable () 'to obtain the metadata table. Each column in the table corresponds to the feature set of each attribute.
For the row corresponding to each column attribute, the Data Type of this column attribute is obtained through row ("datatype.
Example:
Sub main ()
Dim conn as new sqlconnection ("Data Source = localhost; initial catalog = studentcourse ;"&_
"User ID =; Password = ;")
Dim cmd as sqlcommand
Conn. open ()
Cmd = conn. createcommand
Cmd. commandtext = "select * from student"
Dim RDR as sqldatareader
RDR = cmd. executereader
Dim schema as datatable
Schema = RDR. getschematable
Dim I as integer
I = 0
Debug. writeline (schema. Rows (I) ("columnname "))
Debug. writeline (schema. Rows (I) ("datatype "))
Debug. writeline (schema. Rows (I) ("columnsize "))
Conn. Close ()
End sub