Ado. NET Learning notes (i)

Ado| notes have been reading "ADO" in the library for the last few days. NET Practical guide, found really a good book. Reading naturally have experience, I based on the book clues, their learning experience mainly in the form of code records down. (The book corresponds to the code in the Http://www.adoguy.com/book)

1, Connection
Ado. NET's biggest feature is the support in the case of disconnected in the database to operate the content, so that can greatly reduce the consumption of too many connections, the previous article has given a specific example of this feature ado.net. We can open the connection when we get data from the database, disconnect after we get the data, manipulate the data in the dataset, and then open the connection when we update the contents of the dataset into the database. For DataReader, you must remain connected all the time.
There are a few things to be aware of when using this feature:
(1) must disconnect when changing connection properties
(2) Select Conn.changedatabase (dbname) when switching databases to reduce the cost of disconnecting from a new connection
Ado. NET also supports the database with its own connection pool. After a connection is closed, the connection remains in the pool for a period of time before it is actually closed, and if someone requests the same connection before timing out, the open connection is assigned to the requester, which can reduce a lot of consumption for frequent open and disconnected connections. However, a connection with integrated security in SQL SERVER 2000 cannot be pooled.
The connection involves a dispose,infomessage,statechange of events, which can be found in MSDN and no longer repeat.
Template code:
Dim Conn as SqlConnection
Conn=new SqlConnection ("...") ' inside for the connection string
Conn.Open ()
' Do the appropriate action
Conn.close ()

2. Command object
Ado. NET allows three different ways to get data inside a database Command,dataset,datareader,command is the most basic way to get 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, get a reference to the Command object in conn
Dim cmd as SqlCommand
Cmd=conn.createcommand ();
Cmd.commandtext= "SELECT * from Customer"
Recommend the second method

(2) Execution of four modes of execution
ExecuteNonQuery () returns the number of rows affected by the command
ExecuteScalar () returns the first column (use with SET function)
ExecuteReader () returns a DataReader object
ExecuteXmlReader () returns a XmlReader object

(3) Parameters are mainly used in stored procedures, there are two forms of complex and streamlined
Complex approach:
Dim param as New SqlParameter ("@Return", SqlDbType.Int)
Param. Direction=parameterdirection.returnvalue
Cmd. Parameters.Add (param)
Streamlining methods
Cmd. Parameters.Add ("@Return_value", Dbtype.int32). Direction=parameterdirection.returnvalue
Recommendation: If you need to process the output value when using parameters, only processing input values when you do not use parameters.

(4) Business
With SQL statements:
Begin TRAN
SQL operations
If @ @ERROR <>0
Begin
RollBack TRAN
return @ @ERROR
End
Commit TRAN
return 0
Writing 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 database transactions with some external systems (such as a web update at the same time as a database update, or when a Web update fails and you want to roll back a transaction), select client-side write transactions (written in ado.net)
Write transaction statements directly on the service side (write transactions in SQL SERVER2000) only when doing database transactions
SavePoint can be created in a transaction to implement partial transaction rollback
Cmd. Transaction.save ("New Customer")
Cmd. Transaction.rollback ("New Customer")

(5) Batch queries can be processed if there are multiple SQL statements and can be executed together. A data set obtained by a batch query is supported in DataReader.
cmd.commandtext= "SELECT * from Customer; SELECT * from Inovince;
Dim RDR as SqlDataReader
Rdr=cmd. ExecuteReader ()
The result of the execution of two SQL statements is included in the RDR

3, DataReader Object
The DataReader object can only be accessed from a top-down access to the data set that the query obtains, but is highly efficient. If you are simply accessing data, you can use DataReader. However, DataReader requires constant connection, so a small part of the result is placed in memory, read and then read from the database, which is equivalent to a caching mechanism. This is an obvious benefit for the millions of the query results.
Template code:
Do While rdr. Read ()
Console.WriteLine (RDR (0)) ' can also output rdr ("CustomerID")
Loop
You can output (String) RDR (0) or RDR If you want to type limit. GetString (0)

When reading data from the DataReader, pay attention to whether the attribute is null, if the attribute can be null, then read out the data should be judged
If not RDR. IsDBNull (0)
Console.WriteLine (...)

When you read a record with DataReader, the database is locked by default and can be changed by changing the default property of the DataReader.

If the data inside the DataReader is executed by a batch statement, you can access it via NextResult
Template code:
Todo
Do While rdr. Read ()
Console.WriteLine (RDR (0))
Loop
Loop while rdr. NextResult ()

Working with metadata (showing the case of each property)
Dim Schema as DataTable
Schema=rdr. GetSchemaTable () ' Gets the metadata table, each column in the list corresponds to a collection of attributes for each attribute
For the row for each column attribute, the data type of the column property is obtained by row ("DataType").