Microsoft SQL Server 2005 Developer’s Guide- P15

Microsoft SQL Server 2005 Developer’s Guide- P15:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P15 Chapter 8: Developing Database Applications with ADO 279 Check object for nothing If cn = Then MsgBox No connection information has been entered End Else cn.Open cn.Close End If dl = NothingEnd Sub In the beginning of this listing, you can see where an instance of an existingADO Connection object is passed into the subroutine as a parameter. This ADOConnection object is set to an instance of the Connection object that will be returnedby the Data Link object. The following Dim statement then creates a new instance ofthe DataLinks object named dl. After the dl DataLinks object has been instantiated,you can then use the Data Link object’s PromptNew method to display the Data Linkdialog box, as shown in Figure 8-9.Figure 8-9 Selecting the OLE DB Provider using the Data Link dialog box280 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Figure 8-10 Providing the OLE DB Connection information on the Data Link dialog box When the PromptNew method is executed, the Data Link dialog box initially displays the Provider tab that lists all the OLE DB providers that are installed on the system. The Data Link dialog box lets you both configure and connect to a target data source. To connect to SQL Server using the Data Link dialog box, the user must first select the OLE DB provider to be used from the list of the OLE DB providers displayed on the Provider tab. In Figure 8-9, you can see the OLE DB Provider for SQL Server has been selected. Clicking the Next button or selecting the Connection tab displays the OLE DB Connection information dialog box, as shown in Figure 8-10. The Connection tab lets the user select the name of the SQL Server system that will be connected to, as well as enter authentication information and specify a default database. In Figure 8-10, you can see the Data Link dialog box is being used to connect to a system named teca-sql2005, that Integrated Security will be used, and AdventureWorks will be set as the default database. When all the connection information has been entered, clicking OK returns the connection information to the application. Chapter 8: Developing Database Applications with ADO 281 To connect to SQL Server, the Data Links object contains its own ADOConnection object. An instance of that Connection object is returned by thePromptNew method. The previous listing shows the Connection object returned bythe dl object’s PromptNew method being assigned to the ADO Connection objectnamed cn. If the user clicks Cancel in the dialog box, however, then no Connection objectis returned. Enabling VB’s error handler allows the properties of the cn Connectionobject to be tested without generating a run-time error. The cn Connection objectis checked to see if it contains a value. If the Connection object is equal to nothing,then the user clicked the Cancel button. The message is displayed and the program isended using the End function. Otherwise, the cn Connection object’s Open methodis executed to establish a session with the SQL Server system identified in the DataLink dialog box. Then all the system resources used by the dl DataLinks object arereleased when the object is set to nothing before the subroutine is exited.Ending a ConnectionAs the previous examples illustrate, before ending your application, you should usethe Connection object’s Close method to end the database connection. An exampleof the Close method follows:Dim cn As New ADODB.ConnectionPerform work with the connect and then end itcn.CloseRetrieving Data with the ADO RecordsetADO lets you retrieve data using either the Recordset or the Command object. Bothof these objects can be used with an active Connection object or can open their ownconnections. In the following section, you see how to retrieve data from SQL Serverusing the Recordset object. You learn about the differences between the varioustypes of ADO Recordset objects, as well as how to traverse Recordset objects andwork with column values using the ADO Fields collection.ADO Recordset TypesLike the Recordset object found in DAO or RDO’s Resultset object, the ADORecordset object represents a result set that’s returned from a database query.ADO Recordset objects support several different types of cursors that correspondto the different types of ODBC cursors. ADO provides support for forward-only,static, keyset, and dynamic Recordset objects. The type of cursor used in an ADO282 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Recordset object must be set before the Recordset is opened. If you don’t specify the type of Recordset object you want to use, ADO automatically uses a forward-only cursor. Forward-Only Cursors As a default, ADO uses a forward-only cursor. The forward- only cursor provides the best performance and the least overhead of any of the ADO cursor types; however, it’s also less capable than other ADO cursors. ADO Recordset objects that use forward-only cursors are updatable, but you can modify only the current row. Any changes in the base table that other users make aren’t reflected in the Recordset object. Static Cursors A static cursor provides a snapshot of the data at the time the cursor was opened. ADO Recordset objects that use static cursors aren’t updatable, and they don’t reflect any changes made in the base tables unless the cursor is closed and reopened. Because of their static nature, Recordset objects created by static cursors are generally less resource-intensive than Recordset objects t ...