Several methods of ODBC SQL Server database

odbc|server| Data | database
Several methods of ODBC SQL Server database



This article is written for some netizens who want to know the technology in the CSDN forum. Because recently I personally in the forum saw many netizens to mention this question, therefore has written this article, readers.

SQL Server is an efficient relational database system that is tightly integrated with operating systems such as Windows nt/2000 and Windows 9x. It is also one of several large relational databases (SQL server\oracle ybase, etc.) on the market today. For today's complex b/s server system, SQL Server is a good choice.

First, set up a database to test the Db_test

Step one: Start the SQL Server7.0 database, go to the "SQL Server Enterprise Manager" Management interface, right-click [Server name] and choose [New key][database] into the "Database porperties" interface.
Step Two: Enter the database name db_test in "name". Tick the check box in front of "automatically", even if SQL Server can automatically increase the size of the database file as needed. Click OK to complete the creation of the database.

Second, let's take the example of creating a good db_test database, and talk about how to use ODBC to connect SQL Server databases in several ways.

There are three ways to access a database in ASP scripts:
IDC (Internet Database Connector) method
ADO (ActiveX Data Objects) method
RDS (Remote Data Service) mode

1. Internet Database Interface (IDC)
IDC is a traditional database query tool that defines and executes SQL commands for database queries, and returns a page with the specified data format to the browser. The most important feature of using IDC to access a database is that it is simple and requires little programming to access the database. However, it's rare to use IDC to connect to a database today, so I'm not going to describe it here.

2. ActiveX Data Objects (ADO)

ADO (ActiveX Data object) is a set of optimized set of dedicated objects to access the database, it provides the complete site database solution for ASP, it acts on the server side, provides the homepage content containing the database information, by executing the SQL command, lets the user enter in the browser screen, Update and delete information for the site database. The main advantages of ADO are ease of use, high speed, memory footprint, and low disk space, so it is ideal for server-side database access technology.
ADO mainly includes the Connection,recordset and command three objects, their main functions are as follows:
Connection object: Responsible for opening or connecting to the database file;
Recordset object: Accessing the contents of the database;
Command object: To assign an action query directive to a database, and to execute a stored procedure for SQL Server.

There are two ways to connect a database with an ADO component:

1, direct connection to the database mode

<%

Dim Conn,strconn

Set conn=server.createobjec ("ADODB. Connection ")

Strconn= "Driver={sql Server}; Server=servername; " & _

"Uid=username; Pwd=password; " & _

"Database=databasename"

Conn.Open strconn,1,1

%>

2. Create data source Name (DSN) method

There are three types of configuration ODBC data sources, which are user data sources, system data sources, and file data sources. Data source Name (DSN), which, according to the official Microsoft documentation, means that the application requests a name for a connection to an ODBC data source (CONNECTION), which means that it is a symbol that represents an ODBC connection. A DSN is configured so that it already contains items such as database file name, directory, database driver, user ID, password, and so on. So when you build a connection, you don't have to think about the database file name, where it is, and so on, just give it a DSN in ODBC. The entire process of creating a DSN is given below. (Take the System DSN as an example) in the WIN2000 operating system

In Control Panel, double-click Administrative Tools-> and double-click the ODBC icon to enter the ODBC Data Source Administrator interface. Select the System DSN page and go to the Create new data source interface.


Then select the driver as SQL Server. Press [Finish] to enter the "Create new data source to SQL Server" interface, enter the name "SJY", description, server. 



Then press [next]. Press the default setting and then press [next] to tick in the check box before the "Change the default database" option, and select your database. 



Press [next], leave the default, and then press [finish], so that a data source SJY is established.

The following is the code to connect the database through the data source, and for the sake of the security of the database, we write the connection code in the Golbal.asa file and call it on the page.

<script language= "VBSCRIPT" runat= "SERVER" >

Sub Application_OnStart

Application ("connectdatasourcestring") = "DSN=SJY; Uid=sa; pwd=; "

Sub Application_OnEnd

End Sub

</Script>

Calling on the ASP page

<%

Dim Conn,strconn

Set conn= server.createobjec ("ADODB.") Connection ")

Conn.Open Application ("Connectdatasourcestring")

%>

3. Remote Data Service (RDS)

Although ADO can provide very powerful database access capabilities, it does not support data remote operations (dataremoting). RDS is further than ADO, and it supports data remote operations. Not only can it execute the query and return the results of the database query, but the result is "dynamic", and the database on the server maintains a "live connection" to the data the client sees. That is, the server-side data to the client, after the client changes the data, call a database update command, you can write the changes to the data back to the database, like using a local database.

Because RDS integrates with ADO, the bottom of RDS is called ADO to complete, so you can also understand RDS as the RDS of ADO, the Remote Data service of the ActiveX Data object. Based on ADO, RDS provides the client with stronger data expressiveness and remote data manipulation functions by binding data display and manipulation controls. It can be said that RDS is currently the best web-based access to remote databases.

Third, Summary:

This article describes the ASP access to the database three ways, each of them have their own characteristics. IDC is very simple, using the. idc file and the. htx file to complete the database access and output tasks, but the use of inflexible. ADO is the recommended way in ASP, it is powerful, easy to use, is one of the core technologies of ASP, but it provides users with remote operation of the database function, more complex, to achieve a certain degree of difficulty. And RDS is based on ADO, and provides a powerful tool for remote operations databases. So RDS is a better choice when it comes to providing high-performance, highly reliable remote data manipulation capabilities.