Freetutes.com

VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering


You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Using Stored Procedures to Execute Processes Without Cursors

The ADO Recordset object is versatile enough that you can perform any needed data maintenance action by programming its object model.

You can forego a Recordset in your code for many routine data maintenance activities, however, and instead use the Execute method of a Connection or Command object to make calls to SQL statements or stored procedures that implement those SQL statements. Here are the main areas where you could make such substitutions:

  • Deleting records - Instead of calling the Recordset's Delete method, execute a stored procedure that uses the SQL DELETE statement.

  • Adding records - Instead of using the AddNew and Update methods of the Recordset, execute a stored procedure that uses the SQL INSERT statement.

  • Updating existing records - Instead of changing fields in a Do...Loop through the Recordset and then calling the Update method, execute a stored procedure that uses the SQL UPDATE statement.

Listing 9.12 shows ADO code that uses Recordset manipulation (and therefore cursors) to add, delete, and modify records.

Listing 9.13 shows ADO code that calls the Execute method of Command or Connection objects (which are cursorless) to accomplish the same tasks with stored procedures. Listing 9.13 gives the texts of the stored procedure creation statements as they would appear in SQL Server.

LISTING 9.12
EXAMPLE OF A PROCESS THAT USES A CURSOR

Private Sub cmdInsert_Click()
Set rsPubs = cmdPubs.Execute
RsPubs.Fields("ID").Value = txtTitleID
RsPubs.Fields("Title").Value = txtTitle
RsPubs.Update
End Sub
x
y

LISTING 9.13
EXAMPLES OF THE SAME ACTION OF LISTING 9.12, BUT RAN WITHOUT A CURSOR

Private Sub cmdInsert_Click()
Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = connPubs
With cmdPubs
.CommandType = adCmdStoredProc
.CommandText = "Insert_Titles"
'Refresh Parameters collection
.Parameters.Refresh
'and then set properties of each parameter:
'@id parameter
.Parameters("@id").Value = txtTitleID
.Parameters("@id").Direction = adParamInput
'@title parameter
.Parameters("@title").Value = txtTitle
.Parameters("@title").Direction = adParamInput
'@output parameter
.Parameters("@PubID").Value = txtInsertPubID
.Parameters("@PubID").Direction = adParamInput
.Execute
End With
lblResults = ""
lstResults.Clear
End Sub

LISTING 9.14
STORED PROCEDURE USED BY THE EXAMPLE OF LISTING 9.13

create procedure insert_titles
@id varchar(6),
@title varchar(80),
@PubID varchar(4)
AS
INSERT titles
(title_id, title, Pub_ID)
VALUES
(@id, @title, @PubID)
GO

There are two advantages to executing a stored procedure instead of manipulating an ADO Recordset:

  • Better resource management - A Recordset object requires a cursor, and cursors can represent a considerable set of resources. By calling a stored procedure, you do not create another cursor and the server does the work.

  • Better management of tier integrity - You can encapsulate standard business rules in a centralized place (the database) with stored procedures. The stored procedures can be a "black box" to your application. The stored procedures can be changed to meet changing business climates.

Of course, the Recordset is more appropriate where you need local control, or where the application itself must exercise a great deal of intelligence about the way it processes data.


  

<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © Freetutes.com | All Rights Reserved