Microsoft SQL Server 2005 Developer’s Guide- P16

Microsoft SQL Server 2005 Developer’s Guide- P16: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- P16 Chapter 8: Developing Database Applications with ADO 299Private Sub BookMarkFind(cn As ADODB.Connection, _ rs As ADODB.Recordset, oBookMark As Variant) With rs .CursorLocation = adUseClient .Open Select * from Sales.SpecialOffer Order By SpecialOfferID, cn End With Find Mountain Tire Sale and set a bookmark rs.Find Description = Mountain Tire Sale, , adSearchForward oBookMark = rs.Bookmark Find Volume Discount over 60, display the remainder of the resultset rs.Find Description = Volume Discount over 60, , adSearchBackward DisplayForwardGrid rs, hflxResultsEnd Sub In the beginning of the BookmarkFind subroutine, you can see where instancesof the ADO Connection and Recordset objects are passed into the subroutine. Inaddition, a Variant variable named oBookMark is used to pass back the bookmark tobe set inside this routine. Next, a With statement is used to assign values to properties of the rs Recordsetobject. Using a value of adUseClient indicates the Recordset will be maintainedon the client system rather than on the SQL Server system. Using a local cursortypically provides much better performance for processing small and medium resultsets consisting of a few hundred records. Then the Open method is used alongwith a SQL select statement that retrieves all the rows and columns from the Sales.SpecialOffer table and orders them by SpecialOfferID. After the Open method has completed, the rs Recordset object will be populated andthe Find method can then be used to locate specific records within the Recordset. Inthis code listing, the Find method is used twice. The first instance of the Find methodis used to locate the first row in the Recordset where the Description column containsthe value of Mountain Tire Sale. The first parameter of the Find method takes thesearch argument, which uses the same type of search criteria used in a typical Whereclause. The ADO Find method search criteria can use a single field name with onecomparison operator and a literal value to use in the search. The search parametersupports using equal, not equal, greater than, less than, and Like operators. The secondparameter of the Find method isn’t used in this example, but optionally, it indicatesthe number of records to skip before attempting to find the desired record. The thirdparameter indicates the direction of the search. The value of adSearchForward causesthe search to move forward from the current pointer position, while the value of300 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 adSearchBackward causes the search to go backward from the current position in the Recordset. If the Find isn’t successful, the EOF indicator will be set to True in the rs Recordset object. Likewise, if the pointer is at the end of the Recordset and another Find is executed, it will fail unless you reposition the pointer in the Recordset. After the row containing the value of Mountain Tire Sale is located using the Find method, then the Bookmark property of that row is assigned to the oBookmark variable to allow that row to be located easily later. Next, the Find method is used a second time to locate the row in the Recordset object where the Description column contained the value of Volume Discount over 60. In this case, because Volume Discount over 60 occurs before Mountain Tire Sale in the Recordset set object, the adSearchBackward flag is used to search the Recordset object in reverse order. After the pointer is positioned in the Recordset object to Volume Discount over 60, the DisplayForwardGrid subroutine is called to display the remaining contents of the Recordset object. The results of the Find method are shown in Figure 8-14. Figure 8-14 Using the Recordset object’s Find method Chapter 8: Developing Database Applications with ADO 301 After a bookmark has been saved, you can then use that saved bookmarkto position the pointer quickly to the bookmarked row in the Recordset. In theprevious code listing, the bookmark value of the row where the Description columncontained the value of Mountain Tire Sale was saved in the Variant variable namedoBookmark. In the next listing, you can see how to use that saved bookmark value toreposition the pointer in the Recordset.Private Sub BookMarkJump(cn As ADODB.Connection, _ rs As ADODB.Recordset, oBookMark As Variant) Jump to previous bookmark and display the result set rs.Bookmark = oBookMark DisplayForwardGrid rs, hflxResultsEnd Sub In the BookMarkJump subroutine shown in this listing, you can see where instancesof the ADO Connection and Recordset objects are passed into the subroutine, followedby the oBookMark Variant variable. In this example, the oBookMark variable containsthe value of the bookmark that was saved in the earlier listing. This means it containsa value that uniquely identifies the row in the Recordset that contains the value ofMountain Tire Sale. Assigning the rsBookMark property with the saved bookmark value immediatelyrepositions the pointer in the Recordset to the bookmarked row. Next, theDisplayForwardGrid subroutine is used to display the contents of the Recordset,beginning with the value of Mountain Tire Sale. You can see the results of using thebookmark in Figure 8-15.Using Prepared SQL and the ADO Command ObjectThe capability to use prepared SQL statements and parameter markers is one ofthe features that enables ADO to be used in developing high-performance databaseapplications. Using prepared statements in your database applications is one of thosesmall changes that can result in big performance gains. Dynamic SQL statementsm ...