Microsoft SQL Server 2005 Developer’s Guide- P14

Microsoft SQL Server 2005 Developer’s Guide- P14: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- P14 Chapter 8: Developing Database Applications with ADO 259delivered as part of the SQL Server 2000 client components. ADO was delivered aspart of the Visual Basic 6.0 and the older pre- .NET Visual Studio Enterprise Edition,which included Visual Basic 6.0 and Visual C++ 6.0. ADO has since been succeeded byADO.NET and Visual Studio 2005, which you can read about in Chapter 7. However,there are still many COM-based ADO applications written in Visual Basic 6.0 thatconnect to SQL Server. As you saw in Figure 8-2, OLE DB provides two distinctly different methods foraccessing SQL Server data: the OLE DB for SQL Server provider and the OLE DBprovider for ODBC. ADO can work with both of these OLE DB providers. ADO takesadvantage of a multilevel architecture that insulates the applications using the ADOobject framework from the underlying network protocols and topology. Figure 8-3illustrates the relationship of ADO, OLE DB, ODBC, and the PCs networking support. At the top of the figure, you can see the Visual Basic ADO application. TheVisual Basic application creates and uses the various ADO objects. The ADO objectframework makes calls to the appropriate OLE DB provider. If the ADO applicationis using the OLE DB provider for ODBC, then the MSDASQL OLE DB providerwill be used. If the ADO application is using the OLE DB for SQL Server provider,then the SQLOLEDB provider will be used. When using the OLE DB provider forODBC, ADO loads the msdasql.dll file, which, in turn, loads the ODBC DriverManager. The OLE DB provider for ODBC maps the OLE DB calls made by ADOinto ODBC calls, which are passed on to the ODBC Driver Manager.Figure 8-3 ADO Network architecture260 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 The ODBC Driver Manager handles loading the appropriate ODBC driver. The ODBC driver typically uses a network interprocess communication (IPC) method like Named Pipes, TCP/IP Sockets, or SPX to communicate to a remote IPC server that provides access to the target database. The native OLE DB provider for SQL Server doesn’t use any additional middle layers. When using the OLE DB provider for SQL Server, ADO loads sqloledb.dll, which directly loads and uses the appropriate network IPC method to communicate with the database. The IPC client component establishes a communications link with the corresponding server IPC through the networking protocol in use. The network protocol is responsible for sending and receiving the IPC data stream over the network. The most common network protocol is TCP/IP. Finally, at the bottom of this stack is the physical network topology. The physical network includes the adapter cards and cabling that make the actual connections between the networked systems. Ethernet is the most common network topology. OLE DB and ADO Files Here is a summary of the client files used to implement ADO: File Description msdasql.dll OLE DB Provider for ODBC Sqloledb.dll OLE DB Provider for SQL Server msado15.dll ADO Object Library ADO Architecture As with several of the other data access object models, ADO is implemented using a hierarchical object framework. However, the ADO object model is simpler and flatter than Microsoft’s previous data access object libraries, such as Data Access Objects (DAO) or Remote Database Objects (RDO) frameworks. In Figure 8-4, you can see an overview of ADO’s object hierarchy. The Connection, Recordset, and Command objects are the three primary objects in the ADO object model. The Connection object represents a connection to the remote data source. In addition to establishing the connection to a data source, Chapter 8: Developing Database Applications with ADO 261 ADO Application Connection Errors Error Command Parameters Parameter Recordset Fields Field Record Fields Field StreamFigure 8-4 ADO object hierarchyConnection objects can also be used to control the transaction scope. A Connectionobject can be associated with either a Recordset object or a Command object. The Recordset object represents a result set returned from the data source. AnADO Recordset object can either use an open Connection object or establish itsown connection to the target data source. Recordset objects let you both query andmodify data. Each Recordset object contains a collection of Field objects, whereeach Field object represents a column of data in the Recordset. The Command object is used to issue commands and parameterized SQL statements.Command objects can be used to call stored procedures and execute SQL actionstatements, as well as SQL queries that return recordsets. Like the ADO Recordsetobject, the Command object can either use an active Connection object or establishits own connection to the target data source. The Command object contains a Parameters262 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D ...