Data | database | database connection implementation of ASP database connection
Xiachuntao
(102 Department of Electronic Technology College, PLA Information Engineering University, Zhengzhou 450004, Henan)
(email:xct-tom@tom.com)
Absrtact: This paper gives a variety of implementation methods of database connection in ASP.
Keywords: asp;ado;ole-db;odbc; database connection
1 Introduction
ASP is one of the most popular web program design technologies, it uses ADO technology to access the database. ADO is Microsoft's main data access technology, which is the result of the evolution of Microsoft's various data access technologies, such as ODBC, DAO, RDO, ole-db, and so on.
ADO is a COM object that encapsulates a ole-db complex interface, and it accesses a variety of different data with a very simple COM interface. Figure 2 is a schema diagram for ADO data access. As can be seen from the diagram, ADO can access data from different data sources directly by OLE-DB, or it can indirectly access data from relational data sources by ODBC. In this paper, the implementation of database connection in ASP application is preliminarily discussed in these two ways.
Application/browser
Ado
Ole-db
Odbc
SQL Data
Non SQL Data
Mainframe and
Legancy Data
2 Connection objects
The connection object in ADO represents a connection to the underlying data supply program, which maintains information about the data supply program. In an ASP application environment, the connection object represents a connection from the Web server to the database server. The connection object calls the open method to implement a connection to the database, and its syntax is as follows:
Connection.Open [Connectionstring],[userid],[password],[options]
The parameters and descriptions of the Open method are shown in table 1:
Table 1 parameters and descriptions of the Open method
Parameters
Description
ConnectionString
A string that contains the details of the connection. Can be the name of the ODBC DSN, the name of the data link file, or the actual connection details. Optional parameters.
Userid
The name used by the user during the connection. Overrides any user name provided in the connection string. Optional parameters.
Password
The password for the user. Overrides any password provided in the connection string. Optional parameters.
Options
Can be adasyncconnect, which specifies that the connection be established asynchronously. Ignoring this parameter, a synchronous connection is established. Note: Because the scripting language cannot receive events from ADO, asynchronous connections are not used in an ASP environment, and this parameter is generally ignored.
As you can see from table 1, the key to connecting the Open method to the database is to give the correct connectionstring. The following is an example of a SQL Server2000 database, giving the connectionstring of various connection modes.
3 OLE-DB Connection mode
3.1 String mode
Connectionstring= "Provider=SQLOLEDB.1; Data source=yoursrc;
Uid=youruid; Pwd=yourpwd; Database=yourdb "
Or
Connectionstring= "Provider=SQLOLEDB.1; Data source=yoursrc;
User Id=youruid; Password=yourpwd; Initial Catalog=yourdb "
Where provider is the name of the underlying OLE-DB data provider that serves the connection, the name of the data source that is serving the underlying data provider, the user name used when the UID or user ID is connected, or the password used by PWD or password for the connection ; database or initial catalog is a specific database located on the database server.
3.2 Data Link file mode
Creates an empty text file and changes its extension to. udl to create a data link file. Double-click the file to open the Data Link Properties dialog box, select the provider on the provider page (do not select an ODBC-based provider, because it is actually indirectly using ODBC to implement a database connection), specify the details of the implementation connection on the connection page. The connectionstring for connecting using a data link file are as follows:
connectionstring= "File name=c:\yourfile.udl"
Using Notepad to open the data link file, you can see that the third line of text is a connection string that specifies the details of the connection. If "Allow Saving password" is selected on the Connection page, the user's password information is also recorded in the file.
4 ODBC connection mode
To use an ODBC connection, first configure an ODBC data source. Once the ODBC data source is configured successfully, you can specify the connection string described below for database connections.
4.1 ODBC System DSN connection mode
Connectionstring= "Dsn=sysdsnname; Uid=youruid; Pwd=yourpwd;database=yourdb "
Where the DSN is the name of the System DSN and the other parameters are the same as those described in 3.1.
4.2 ODBC File DSN connection method
Connectionstring= "Filedsn=filedsnname; Uid=youruid; Pwd=yourpwd;database=yourdb "
Where FileDSN is the name of the file DSN and the other parameters are the same as those described in 3.1.
Open the appropriate. dsn file (typically located in the C:\Program Files\Common Files\ODBC\Data Sources directory) with Notepad, and you can see that similar to the data link file, the details of the connection are also specified in the file.
4.3 ODBC non-DSN connection mode
Connectionstring= "Driver={sql Server}; Server=yourserver;
Uid=youruid; Pwd=yourpwd;database=yourdb ")
You do not need to configure an ODBC data source in this way. Where driver is the ODBC driver name, server is the name of the database server, and the other parameters are the same as those described in 3.1.
4.4 Data Link File mode
First, use the method in 3.2 to create the data link file. However, when making a file configuration, select an ODBC-based provider--microsoft OLE DB Provider for ODBC Drivers in the Provider page. The connectionstring in this way are the same as those described in 3.2.
5 examples
The following is a sample code that connects to a SQL Server2000 database using a ole-db string:
<%
Connectionstring= "Provider=SQLOLEDB.1; Data source=yoursrc;
Uid=youruid; Pwd=yourpwd; Database=yourdb "; Initialize connection string
Set conn=server.createobject ("ADODB. Connection "); Create Connection Object
Conn.Open ConnectionString; Calling the Open method to establish a connection
... ; Perform data access operations
Conn.close; Close the connection
Set conn=nothing; Release Connection object
%>
It should be noted that the connection should be established as late as possible, while closing the connection as early as possible, so as to ensure the shortest connection time, can give full play to the role of OLE-DB connection buffer pool, save connection resources; In addition, in practical applications, To avoid entering the details of the connection in connectionstring in each ASP page (which can be troublesome to program maintenance), you typically use the Include file or connection state to store the ConnectionString, as follows.
