Eliminate any database connection logic from your ASP pages by implementing a simple VB component that wraps up the most common ADO functionalities and in conjunction with a UDL.
- 1. Create a wrapper ADO component using VB.
- Expose the all the typical methods you use such as GetRecordSet (for reads) and Execute (for Insert/Update/Delete). Add a string parameter to all of your ADO wrapper methods. This will hold the name of a UDL.
- The ADO wrapper component performs the task of loading the DB connection logic from the UDL–you just need to specify a standard location to store all the UDLs for your site. (i.e. C:UDL or D:UDL –only the ADO wrapper component needs to know about this physical file location).
- Put your wrapper ADO component in an MTS/COM+ Package.
- Your ASP files then need to instantiate the wrapper ADO component and pass the UDL name as another parameter. They don’t need to know anything about the database –just pass the UDL name.
- You can completely eliminate ADO and Recordsets from ASP using ADO 2.6 (or higher), MS SQL Server 2000 (or higher), and XML 3.0 (or higher). Wrap up the ADODB.Stream method, pass XML back directly from SQL Server to the component, then pass it on to the calling method, or ASP, from the wrapper ADO component. All your ASP does is work with XML.
Dim oADO 'holds custom ADO component wrapperDim rs 'my recordsetDim sSQL 'can contain SP with parameters or raw SQL - advancedfunctionality includes using Input Parameters and passing them in an arraysSQL = "SELECT * FROM AUTHORS"Set oADO = CreateObject("MY_ADO.Data_Services")Set rs = oADO.GetRecordSet(sSql,"PUBS") 'the wrapper ADO component willread all DB connection info from D:UDLPUBS.UDLDo While Not rs.EOF ... 'do something ... rs.MoveNextLoop
Other advanced techniques include referencing only the wrapper ADO component from other VB component methods, and having those methods return XML to the browser, thus eliminating ALL knowledge of the source of the data from ASP.