I have been watching the "ADO.NET Practical Guide" in the library recently, and it is really a good book. Reading naturally has experience, I am based on the clues of the book, and the learning experience is mainly recorded in the form of code. (The corresponding code on http://www.adoguy.com/book)
1. The biggest feature of connecting ADO.NET is to support the content of the database in the case of disconnection, which can greatly save the consumption caused by too many connections, which has been given in the article in front. A specific example shows this feature of ADO.NET. We can open the connection when you get data from the database. After getting the data, disconnect, the data in the DataSet, then open the connection when updating the contents in the DataSet to the database. For DataReader, you must keep a connection. When using this feature, do you pay attention to: (1) When changing connection properties, you must disconnect (2) When switching the database, select Conn.ChangeDatabase (DBNAME), reducing disconnects and new connection rounds Consumption ADO.NET also supports the database comes with the pool. After a connection is closed, the connection will remain in the pool for a period of time, and then actually shut down, if someone requests to establish the same connection before the timeout, the open connection is assigned to the requester, which is often opened and broken. The open connection can reduce much consumption. However, in SQL Server 2000, integrated security is used to enter the pool. The incident involved in the connection has Dispose, Infomessage, StateChange, can be found in MSDN, and will not be described again. Template code: DIM CONN As SqlConnection Conn = New SqlConnection ("...") 'Inside Conn.Open () CONN.CLOSE ()
2, Command Objects ADO.NET allows data for Data, DataSet, DataRead, Command, which is available in three different ways, is the most basic method, and obtains data in the form of the SQL command.
(1) Creating a new Command object DIM CMD as new sqlcommand cmd.connection = conn cmd.comMD.connection = conn cmd.commandtext = "select * from customer" B, get a reference DIM CMD AS for Command objects in Conn SQLCommand cmd = conn.createCommand (); cmd.comMandText = "Select * from customer" recommended second method
(2) Execute four execution mode ExecuteNonQuery () Returns EXECUTESCALAR () EXECUTESCALAR () Returns the first line of the first column (using the set function) EXECUTEREADER () Returns a DataReader object EXECUTEXMLReader () Returns an XMLReader object
(3) Parameter is mainly used in the stored procedure, has complex and streamlined two forms of complex methods: DIM Param as new sqlparameter ("@ retturn", sqldbtype.int) param.direction = parameterDirection.ReturnValue cmd.Parameters.Add ( PARAM) CMD.Parameters.Add ("@ Return_Value", dbtype.int32) .direction = parameterDirection.ReturnValue suggestion: If you need to use parameters when you need to process the output value, you will not use the parameters when you use the input value. (4) Transaction SQL statement: Begin TRAN SQL Operation IF @@ error <> 0 Begin Rollback Tran Return @@ Error End Commit Tran Return 0 Writing transaction in ADO.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 transaction with some external systems (for example The database update is updated simultaneously. If the web update fails to rollback transactions), select the client to write transaction (written with ADO.NET) Just do database transactions, write a transaction statement directly on the server (in SQL Server2000 write transactions) You can create SavePoint in your transaction to implement some transaction rollback cmd.transaction.save ("New Customer") cmd.transaction.rollback ("New Customer")
(5) Batch query If there are multiple SQL statements and can be executed together, batch query can be made. Support the data set obtained by supporting the batch query in DataReader. Cmd.comMandtext = "Select * from customer;" DIM RDR AS SQLDATAREADER RDR = cmd.executeReader () RDR is included in the result of two SQL statements
3. DataReader object DataReader object can only be accessed from the data set obtained by the query, but the efficiency is high. If you just access data, you can use DataReader. But DataReader requires a connection, so putting a small portion of the result first in memory, and then read a portion from the database after reading, which is equivalent to a cache mechanism. This is an obvious advantage for the case of the results of millions of queries. Template code: do while rdr.read () console.writeline (RDR (0)) 'can also output RDR ("Customerid") loop If you want to perform type restrictions, you can output (string) RDR (0) or Rdr.getstring. 0) When reading data from DataReader, pay attention to whether the attribute is empty. If the property can be empty, it should be judged when the data is read, and if not rdr.isdbnull (0) console.writeline reads with DataReader When you take the record, the database defaults to lock, and you can change by changing the default attribute of DataReader.
If the data in DataReader is to get the batch statement, you can access template code through NextResult: do do while rdr.read () console.writeline (RDR (0)) loop loop while rdr.nextResult ()
Treatment metadata (display of each attribute) DIM Schema as dataable schema = rdr.getschematable () gets the metadata table, each column of each column corresponds to the property of each attribute, corresponding to each column, through Row ("DataType") gets the data type of this list. Example: SUB Main () DIM Conn As New SqlConnection ("Data Source = localhost; Initial Catalog = StudentCourse;" & _ "User ID =; password =;") DIM CMD As SqlCommand Conn () 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 ("Datatype")) Debug.Writeline (Schema.Rows (i) ("Columnize") Conn.close () End Sub
