ASP database connection implementation
Xia Chun Tao
(Department of Teaching and Research, School of Electronic Technology, PLA Information Engineering, Zhengzhou 450004, China)
(Email: XCT-TOM@tom.com)
Abstract: This article gives a variety of implementation methods for database connections in ASP.
Key words: ASP; ADO; OLE-DB; ODBC; Database connection
1 Introduction
ASP is one of the most popular web programming technology, which uses ADO technology to access the database. ADO is the current major data access technology of Microsoft, which is Microsoft's various data access technologies such as ODBC, DAO, RDO, OLE-DB evolutionary results.
The ADO is a COM object encapsulated in the OLE-DB complex interface that allows you to access a variety of different data with an extremely simple COM interface. Figure 2 is an architectural diagram of ADO data access. As can be seen from the figure, the ADO can directly access the data of different data sources directly, and data can be accessed by ODBC to access data from the relational data source. This article initially explores the implementation of database connections in the ASP application in both ways.
Figure 1 ADO data access architecture
Application / Browser
ADO
OLE-DB
ODBC
SQL Data
Non SQL DATA
Mainframe and
Legancy Data
2 Connection object
The Connection object in the ADO represents a connection to the underlying data supply, which maintains information about the data supply. In the 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 connect with the database, and its syntax is as follows:
Connection.open [Connectionstring], [UserID], [Password], [Options]
The parameters and description of the OPEN method are shown in Table 1:
Table 1 Parameters and descriptions of the OPEN method
Parameter
Understand
Connectionstring
Contains a string of connection details. Can be the name of the ODBC DSN, the name of the data link file or the real connection details. Optional parameters.
UserID
During the connection, the user used the name. Overwrite any username provided in the connection string. Optional parameters.
Password
The user's password. Overwrite any passwords provided in the connection string. Optional parameters.
Options
Can be AdasyncConnect, specifying an asynchronous connection. Ignore this parameter, establish a synchronous connection. Note: Since the scripting language cannot receive an event from ADO, asynchronous connection is not used for an ASP environment, this parameter is generally ignored.
As can be seen from Table 1, the OPEN method implements the key to the database connection is to give the correct connectionString. The following uses the SQL Server2000 database as an example to give Connectionstring in various connection mode.
3 OLE-DB connection method
3.1 String mode
CONNECTIONSTRING = "provider = SQLOLEDB.1; DATA SOURCE = Yoursr;
UID = Youruid; PWD = YourPwd; Database = yourdb "
or
CONNECTIONSTRING = "provider = SQLOLEDB.1; DATA SOURCE = Yoursr;
User ID = Youruid; Password = YourPwd; Initial Catalog = YourDB "
Among them, Provider is the name of the underlying OLE-DB data supply program that serves the connection; Data Source is a data source name that serves the underlying data supply; the UID or user ID is used by the username when the connection is connected; the PWD or Password is connected. The password used; Database or Initial Catalog is a specific database located on the database server. 3.2 Data Link File Mode
Create an empty text file to change its extension to .udl to create a data link file. Double-click this file to open the Data Link Properties dialog box, select the provider in the Provider page (do not select ODBC-based providers, because this is actually indirectly implemented database connections), specifying the details of the connection in the Connection page. Connectionstring connects using the data link file as follows:
Connectionstring = "file name = c: /yourfile.ud"
Use Notepad to open the data link file, you can see that the third line of text is a connection string, specifying the details of the connection. If "Allow Saving Password" is selected in the Connection page, the user's password information is also recorded.
4 ODBC connection method
Use the ODBC connection to first configure the ODBC data source. After the ODBC data source is configured, you can specify the database connection to specify the connection string described below.
4.1 ODBC system DSN connection method
Connectionstring = "DSN = sysdsnname; uid = youruid; pwd = yourpwd; database = yourdb"
Where DSN is the name of the system DSN, the other parameters is the same as the parameters described in 3.1.
4.2 ODBC file DSN connection method
Connectionstring = "filedsn = filedsnname; uid = youruid; pwd = YourPwd; Database = yourdb"
Where the filedsn is the name of the file DSN, the other parameters is the same as the parameters described in 3.1.
Use Notepad to open the corresponding .dsn file (generally located in the C: / Program Files / Common files / ODBC / DATA SOURES directory), you can see that the details are also specified in the file, which also specifies the details of the connection.
4.3 ODBC no DSN connection method
Connectionstring = "driver = {sql server}; server = yourver;
UID = Youruid; PWD = YourPwd; Database = yourdb ")
This method does not have to configure the ODBC data source. Among them, Driver is the ODBC driver name, Server is the database server name, the other parameters are the same as the parameters described in 3.1.
4.4 Data Link File Mode
First, create a data link file using the method in 3.2. However, when file configuration, select ODBC-based provider - Microsoft Ole DB Provider for ODBC Drivers in the Provider page. The Connectionstring of this method is the same as in 3.2.
5 instance
Below is a sample code to connect to the SQL Server2000 database using OLE-DB:
<%
CONNECTIONSTRING = "provider = SQLOLEDB.1; DATA SOURCE = Yoursr;
UID = Youruid; PWD = YourPwd; Database = yourdb "; Initializing connection string
Set conn = server.createObject ("adoDb.connection"); create a Connection object
Conn.open connectionString; call the open method to establish a connection
...; data access
CONN.CLOSE; turn off connection
SET CONN = Nothing; Release Connection object
%>
It should be noted that the connection should be established as soon as possible, and it will close the connection as early as possible, so that the connection open has the shortest time, it can give full play to the role of the OLE-DB connection buffer pool, saving connection resources; In practical applications, in order to avoid entering the connection details in Connectionstring in each ASP page (this will bring trouble to program maintenance), which generally uses filestring, including files or connection status, the method is as follows.
(1) Use the included file
Create a new ASP file, you may be named Connection.asp and enter the following code in it:
<%
CONNECTIONSTRING = "provider = SQLOLEDB.1; DATA SOURCE = Yoursr;
UID = Youruid; PWD = YourPwd; Database = yourdb "
%>
Add this row of statements at the top of the ASP page:
You can use Connectionstring directly on this page.
(2) Use connection status
Add the following code in the global.asa file:
<%
SUB Application_onstart ()
CONNECTIONSTRING = "provider = SQLOLEDB.1; DATA SOURCE = Yoursr;
UID = Youruid; PWD = YourPwd; Database = yourdb "
Set Application ("Connectionstring") = connectionString
End Sub
%>
This method stores Connectionstring in the application variable, so you can use the following code to connect to the database in the ASP page:
<%
Set conn = server.createObject ("adoDb.connection")
Conn.open Application ("Connectionstring")
%>
6 Contrast various connection methods
Although the availability of the data can be accessed by OLE-DB and ODBC, it can be seen from the ADO data access architecture (Figure 2). The way to use ODBC is more than one layer than OLE-DB, so When accessing the same data, the ODBC may be slower than the OLE-DB.
The OLE-DB string connection mode is compared with the ODBC's system DSN, the file DSN connection method, saves the steps to establish an ODBC data source, and the operation is more concise. In addition, we can divide the connection into: string, data link file, and DSN. Direct connection string may be fast because it provides all connection details. Data link files require the connection details from the file, and the DSN mode needs to read the connection details of the ODBC data source from the registry.
In summary, in the ASP application, the OLE-DB connection method is better than the ODBC connection method; the string connection mode is better than non-string connection.
references:
[1] Li Wei.delphi5.x ADO / MTS / COM advanced programming. Beijing: Machinery Industry Press, 2000
[2] Weissinger A. Feng Yanhui, Wang Yongqing, Liu Hai Ming translated. OSP technical manual. Beijing: China Electric Press, 2001
[3] Anderson R et al. Liu Fu Tai et al. Senior programming. Beijing: Machinery Industry Press, 2000
[4] Li Huabin. Practical tutorial. Beijing: China Hydroelectric Power Press, 2000
