Using stored procedures to implement ASP database access
Author：Eve Cole
Update Time：2009-06-24 17:21:43
1. Overview of ADO
ActiveX Data Objects (ADO) is an easy-to-use and scalable technology for adding database access to your Web pages. You can use ADO to write concise and scalable scripts to connect to OLE DB-compatible data sources. Such as databases, spreadsheets, sequential data files, or email directories. OLE DB is a system-level programming interface that provides a set of standard COM interfaces to display the functions of the database management system. Using ADO's object model, you can easily access these interfaces (using a scripting language such as VBScript or JScript) and add database functionality to your Web application. Additionally, you can use ADO to access Open Database Connectivity (ODBC)-compliant databases.
If you are a script writer with some knowledge of database interconnections, you will find ADO's command syntax to be simple and easy to use. If you are an experienced developer, you will appreciate the scalable, high-performance access ADO provides to a variety of data sources.
2. General methods of accessing the database. Accessing the database in general web page access is carried out according to the following steps. First, create an object of the ADODB.Connection interface, and then bind the corresponding data source to this object (you can use the named data source and Unnamed data source), create or not create a record set as needed, and then link to the data source and use the execute or open method to operate the corresponding table.
The general method of ASP accessing the database:
<
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.RecordSet")
set strConn="Provider=SQLOLEDB;User ID=sa; Initial Catalog=Pubs;Data Source=" &request.ServerVariables("SERVER_NAME")
oConn.open strConn
set ORS=oConn.execute("select * from test")
>
The above introduces how to use OLEDB for unnamed links. If it is a named link, set it as the data source test, and the user name and password are both empty. You only need to rewrite the above statement oConn.open "test","",""
The above briefly introduces the general methods of database access in web pages. These are already very mature and very useful. If the access to certain data is too complicated in practice, it will take nearly 10 SQL statements to write. Complete, then this method is a bit lacking. In addition, sometimes it is necessary to use the same processing process in different web pages, and this is the most advantageous to use stored procedures. Another biggest feature is that stored procedures are very technical. The confidentiality is relatively high and it is stored in the database of the remote server.
3. The use of stored procedures in ASP ADO provides access to stored procedures, which requires the use of the command object. On this object, users can directly execute the stored procedures of the SQL server, and the parameters required in the command can be It is processed using its properties Pamaters.
Note that for a Command object to be valid, it must be associated with a Connection object by setting the ActiveConnection property of the Command object to the Connection object. If a Connection object cannot be identified, the Command object is invalid until you associate it with a connection.
< Dim oConn
Dim strConn
Dim oCmd
Dim oRs,ors1
dim aa
dimsql
Set oConn = Server.CreateObject("ADODB.Connection")
set oCmd = Server.CreateObject("ADODB.Command")
set ors1=Server.CreateObject("ADODB.RecordSet")
'Open the link, use user ID SA, password is empty, connect to the database on the local server
strConn="Provider=SQLOLEDB;User ID=sa;Initial Catalog=pubs;Data Source="& Request.ServerVariables("SERVER_NAME")
'If you connect to a remote database, the database address is: 10.82.88.105, the user is tmp, and the password is 123.
'The following method can be used' strConn="Provider=SQLOLEDB;User ID=tmp;pwd=123;Initial Catalog=tjbb;Data Source="& "10.82.88.110"
oConn.Open strConn
'Add the established link to the active link attribute of the command
Set oCmd.ActiveConnection = oConn
'Set the calling stored procedure byroyalty and parameters, the parameters are by? introduce
oCmd.CommandText = "{call byroyalty(?)}"
oCmd.Parameters.Append oCmd.CreateParameter("@Percentage", adInteger, adParamInput)
' Provide input parameters
oCmd("@Percentage") = 75
'The use of the above input parameters in asp can also be achieved without parameter attributes, just use & in VB to form the corresponding data in the command text' The use of parameters is particularly useful during output
Set oRs = oCmd.Execute
ors1.activeconnection=oconn
'General SQL statements can also be used in this command object using the Source and open attributes, where source points out the data source.
ors1.source ="select * from [tmptable] where year=2000 and month=1"
ors1.cursorttype=adopenstatic
ors1.open
>
4. Introduction to stored procedures in SQL Using SQL language, you can write stored procedures for database access. The syntax is as follows:
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} [VARYING] [= default] [OUTPUT]
]
[,...n]
[WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[FOR REPLICATION]
AS
sql_statement [...n]
The content within [ ] is optional, while the content within () is required.
Example: If the user wants to create a stored procedure Select_delete to delete records in table tmp, it can be written as:
Create Proc select_del As
Delete tmp
Example: The user wants to query the stored procedure for data of a certain year in the tmp table
create proc select_query @year int as
select * from tmp where year=@year
Here @year is a parameter example of a stored procedure: this stored procedure starts from a certain node n to find the top-level parent node. This frequently used process can be performed by a stored procedure and can be reused in web pages to achieve sharing. .
Empty: indicates that the node is a top-level node
fjdid (parent node number)
Node n is not empty: indicates the parent node number of the node
dwmc (unit name)
CREATE proc search_dwmc @dwidold int,@dwmcresult varchar(100) output
as
declare @stop int
declare @result varchar(80)
declare @dwmc varchar(80)
declare @dwid int
set nocount on
set @stop=1
set @dwmc=""
select @dwmc=dwmc,@dwid=convert(int,fjdid) from jtdw where id=@dwidold
set @result=rtrim(@dwmc)
if @dwid=0
set @stop=0
while (@stop=1) and (@dwid<>0)
begin
set @dwidold=@dwid
select @dwmc=dwmc,@dwid=convert(int,fjdid) from jtdw where id=@dwidold
if @@rowcount=0
set @dwmc=""
else
set @result=@dwmc+@result
if (@dwid=0) or (@@rowcount=0)
set @stop=0
else
continue
end
set @dwmcresult=rtrim(@result)
Full and reasonable use of stored procedures can improve server throughput. The author uses stored procedures to obtain data from nearly 20 tables and combine them into a general table, generating nearly 20,000 records, and the time required is about 7 seconds. If This kind of operation can improve server development to a higher level with the help of more reasonable dynamic web pages, making full use of stored procedures, which can reduce the heavy processing caused by web design, so that the written code can be shared and reasonably utilized, and the code can be Hidden inside the database of the server, some technologies are kept confidential. This is also a major feature of stored procedures. I hope readers can benefit from it.
