Connecting to the data store

Data

If you need to access a data store, you should create a connection to the data store. As mentioned earlier: You can explicitly create a connection object, or let ADO implicitly create a connection. For any of these ways, you must know the details of the data store.
Although the actual details used for the connection are different, the actual method of connection is the same for all types of data storage. This is not surprising, because different providers require different types of information. Before allowing a user to access the data store, some providers require a user's certificate, while others accept the default security certificate.
There are several ways to connect to a data source:
· The connection string. Put the details of the connection in the string, or add the connection details directly to the command when you open the data store. The advantage of this approach is that the details of the connection will remain in the ASP page. Deficiencies, if you have more pages, in the change of connection details, will be bogged down in heavy maintenance work. The solution is to create a string variable that contains the details of the connection and put it in an ASP containing file, so that there is only one instance of the connection string, but it can be maintained in accordance with other ASP pages. Another common technique is to store the connection strings in an application in a state variable, which can be used by all pages in the application.
· Data link file. This is a file with connection details (. udl extension). The advantage is that the ASP page for any data requires only one data link file. To create a data link file, simply create a new text file and rename it (to ensure that Windows Explorer displays the file name extension). Once you have renamed the file, you can open it (double-click) to display the Data Link Properties dialog box. Previous versions of ADO allow data link files to be established from Windows Explorer's new menu. We'll see the contents of the Data Link file later in this chapter.
· ODBC data source, or DSN. is somewhat similar to a data link file, but applies only to ODBC data sources. They are concentrated for ASP pages, and the data source must be a system data source. The ODBC data source is created from the ODBC Data Source Administrator, which is located in the Administrative Tools folder.
Either of these three ways can be used, which is a preference. A direct connection string can be faster because it provides all the connection details. The data link file needs to read the connection details from the file, and the ODBC data source needs to read the connection details from the registry. Of course, the speed difference is very small, each method has its advantages and disadvantages.

8.3.1 Connection string
The connection string is dependent on the provider because each data provider may require different details.
It is important to note that the OLE DB Provider for ODBC is the default, so if you do not use the Provide= section, the system will automatically use ODBC.
The following is an example of a connection string for different providers, and you will see more examples later in this book.
1. Microsoft Access
If you are using ODBC instead of DSN:
Driver={microsoft Access Driver (*.mdb)}; Dbq=c:wroxdatabase_name.mdb
For local OLE DB providers:
provider=microsoft.jet.oledb.4.0; Data Source=c:wroxdatabase_name.mdb
The above example shows that the Access database is stored in the C:wrox directory. Although the reader may try to store the database in the same directory as a Web file, do not do so, or anyone can download the entire database file. It is always wise to keep a database outside the Web directory, and no one can access the file from outside.
2. Microsoft SQL Server
For Microsoft SQL Server, use a provider for ODBC:
Driver={sql Server}; Server=server_name; Database=database_name; Uid=user_name;
Pwd=user_password
For example:
Driver={sql Server}; Server=watcher; Database=pubs; Uid=davids; Pwd=whisky
For local OLE DB providers, the syntax is similar:
Provider=sqloledb; Data Source=server_name; Initial Catalog=database_name;
User Id=user_name; Password=user_password
For example:
Provider=sqloledb; Data Source=wathcher; Initial catalog=pubs; User Id=davids;
Password=whisky
3. Microsoft Indexing Service
Indexing Service can only be used by local OLE DB providers. Its syntax:
PROVIDER=MSIDXS; Data Source=catalog_name
For example, using a web directory
PROVIDER=MSIDXS; Data Source=web
4. ODBC driver
In the example of using an OLE DB Provider for ODBC, driver appears to be verbose. For example:
Driver={microsoft Access Driver (*.mdb)}; Dbq=c:wroxdatabase_name.mdb
When you create a new data source, the exact name of the driver used should be obtained from the list of drivers, as shown in Figure 8-6:

5. Data Link File
Previous versions of ADO allow you to create a data link file by right-clicking the mouse on the directory in the resource manager. After you create a new file, open the file to get the Data Link Properties dialog box. At the time of writing, Microsoft had removed the option from the right mouse menu because they thought it would be confusing to users. But Microsoft said it would provide a registry file to introduce the feature again.
Don't forget, you can also simply create a data link file by creating an empty text file and changing its extension to. udl.
Once you have a physical data link file, you can open the file by double-clicking the mouse or right-clicking the mouse. Next, the reader will see the dialog box shown in Figure 8-7:

The details of the diagram vary depending on the provider selected. The example above shows the SQL Server provider, connected to a SQL Server called Wather, logged in as a Davids (password is blocked), using the pubs database. Note that if you choose Allow saving password, the password you enter will be saved in the UDL file in clear text.
If you want to change the provider, you can select the Provider tab, as shown in Figure 8-8:

With this option, you can select the provider you want, and then press the Next button to fill in the appropriate connection details.
You can also edit the file in a text editor, as shown in Figure 8-9:

You can see that a connection string does exist in the UDL file.
To use a data link file, you only need to specify this data link file when you open the connection:
Conpubs.open "File name=c:wroxpubs.udl"
6. ODBC Data source
An ODBC data source (typically the name of the data source, or DSN) can be set by using the Administrative menu's data source option. Use it as a small program in the control Panel in previous versions of Windows. In order to access DSN in an ASP page, you must determine that the DSN has been set as a System DSN. This simply selects the System DSN tab in the data Source administrator and then selects the Add button, as shown in Figure 8-10:

You can then select the ODBC driver you want to use and fill in the appropriate ODBC parameters.
Once a DSN is established, you can use the "dsn=" property of the connection string. For example:
Conpubs.open "Dsn=pubs"

8.3.2 using include files
The inclusion file containing the file connection string provides a central area for storing the connection details required by many ASP pages. To do this, you just need to create a new ASP file, called Connection.asp, and add the following code:
<%
strconn = "PROVIDER=SQLOLEDB; Data Source=watcher; "& _
"Initial catalog=pubs; User Id=davids; Password=whisky "
%>
In the ASP page, you can now join this line at the top of the page:
<!--#INCLUDE file= "connection.asp"-->
This eliminates the need to enter connection details for each ASP page, and makes it easy to change the connections used by the entire site. Including files is also a good place to place metadata tags.
8.3.3 Use connection Status
Saving the connection string to an application variable is a common technique that works as well as using a containing file. For example, you can add the following code to the Global.asa file:
Sub Application_OnStart ()

strconn = "PROVIDER=SQLOLEDB; Data Source=watcher; "& _
"Initial catalog=pubs; User Id=davids; Password=whisky "
Set application ("ConnectionString") = strconn

End Sub
In the ASP page, you can use the following code:
Set conpubs = Server.CreateObject ("ADODB. Connection ")

Conpubs.application ("ConnectionString")
Personally, I prefer to use a method of including files because I write many different examples of connecting to various servers and databases. Using an application method means that you must restart the application each time you have to close the browser. Readers can use any of the methods they like, and there is no difference in speed.
For the example in this section of this book, a connection.asp file containing a connection string is used as a containing file.

8.3.4 Connection Syntax
What do you do when you really want to connect to the data store, as described in the relevant theory? If you use an explicitly defined Connection object, you can use the Open method, which has the following syntax:
Connection. Open [ConnectionString], [UserID], [Password], [Options]
The parameters are shown in table 8-1:
Table 8-1 the parameters and description 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

Userid
The name used by the user during the connection. Overwrite any user name provided in the connection string

Password
The password for the user. Overwrite any password provided in the connection string

Options
Can be adasyncconnect, which specifies that the connection be established asynchronously. Ignoring this parameter, a synchronous connection is established

Asynchronous connections are not used in the ASP environment because the scripting language cannot receive events from ADO.

Examples of 8.3.5 connections
Here are a few examples, where it is assumed that strconn contains a valid connection string.
To open a connection, use the Connection object's Open method. For example:
Set conpubs = server.connection ("ADODB. Connection ")

Conpubs.open strconn

' Some processing

Conpubs.close
You can also use the ConnectionString property:
Set conpubs = Server.CreateObject ("ADODB. Connection ")

conpubs.connectionstring = strconn
Conpubs.open

' Some processing

Conpubs.close
There is no difference between the two implementations, and if the previous method is used to implement the connection, the ConnectionString property is also assigned.
It is worth noting that, once a connection is made to the data store, ADO may change the value of the ConnectionString property. Don't worry, ADO just fills in some extra property values.

8.3.6 Connection Buffer Pool
The connection buffer pool (connection pool) is always confusing to many people, but the principle is very simple. When a connection is turned off, the connection is closed, as far as the user (and ADO) is concerned. But in fact OLE DB does not close the connection, just puts it in an inactive connection buffer pool. Any time a user (or someone else) opens a connection, OLE DB first detects whether a connection in the connection buffer pool has the same connection details. If so, the connection is made directly from the buffer pool. If not, a new connection is created for the user. To avoid wasting resources, clear the connection from the buffer pool after a default time period.
So, what are the advantages of it? Opening a connection may be one of the slowest operations in the operation, and connecting the buffer pool allows users to reconnect to the data store without recreating the connection. This is especially important for web sites that continuously open and close a large number of connections.
For ODBC connections, the connection buffer pool is controlled by the ODBC Data Source Administrator. For OLE DB, you cannot change the connection buffer pool (or the session buffer pool).
It is important to note that the connection buffer pool is not a connection share. A connection can be used again only after it has been closed by the customer.
House handling
In order for the connection buffer pool to take effect, you must ensure that the housekeeping (housekepping) is in an orderly state. This includes closing the connection objects in a timely manner so they can return to the buffer pool for reuse. You might think that constantly opening and closing connections is expensive for your system, but you have to weigh scalability-your application may be used by many people, and OLE db is very good at managing connection resources.
The general principle is to establish the connection as late as possible, and to close the connection as early as possible, so that the connection is open for the shortest period of time.