Stored Procedure con Visual Basic e ADO-DB

Introduzione

Questo brevissimo articolo tratta l’utilizzo tramite ADO (con Visual Basic) di stored procedures (di seguito SP) su Server SQL (in particolare, per l’esempio riportato è stato utilizzato Microsoft SQL Server).

Il concetto fondamentale è quello di portare l’utente ad utilizzare nella programmazione client server il minor numero possibile di elaborazioni dati eseguite su client lasciando invece il compito di eseguirle al server stesso.

I vantaggi nell’utilizzo di una SP rispetto ad un’elaborazione su client sono diversi:

Maggiore velocità (una SP compilata viene eseguita molto più velocemente di processi client)

Minor traffico di rete (i dati necessari alle elaborazioni non vengono trasmessi, viene trasmesso soltanto il risultato) 

Maggiore facilità di manutenzione (è più comodo aggiornare la SP su server piuttosto che i singoli client)

 

La Stored Procedure

 

Per l’esempio che andiamo ad esaminare utilizzeremo una semplice SP che, una volta lanciata fornendo come parametro il nome di una tabella, restituisce l’elenco di tutti i campi presenti nella tabella. Di seguito il codice della stored procedure:

/*

Stored Procedure STP_GETTABLECOLS

Parametri in ingresso: @TableName di tipo VARCHAR, che indica il nome

della tabella di cui si vuole ottenere

l’elenco dei campi.

*/

USE MARCOM — Uso il mio database privato

– Se esiste già la stored procedure la droppo.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = ‘STP_GETTABLECOLS’ AND TYPE = ‘P’) DROP PROC STP_GETTABLECOLS

GO
CREATE PROC STP_GETTABLECOLS @TableName VARCHAR(60) AS
SET NOCOUNT ON
– Dichiaro il cursore con cui scorro la selezione sulle colonne
DECLARE BaseCur CURSOR FOR SELECT SYSOBJECTS.NAME, SYSCOLUMNS.NAME
FROM
SYSOBJECTS LEFT JOIN SYSCOLUMNS ON
SYSCOLUMNS.ID = SYSOBJECTS.ID
WHERE
SYSOBJECTS.NAME = @TableName
– Variabili per contatore e nome field
DECLARE @NewCounter INT
DECLARE @FieldName VARCHAR(80)
DECLARE @DropData VARCHAR(1)

– Se esiste già la tabella temporanea la droppo.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = ‘MMSTPGETTABCOL’ AND TYPE = ‘U’)
DROP TABLE MMSTPGETTABCOL

– Creo la tabella temporanea

CREATE TABLE MMSTPGETTABCOL (COLKEY INT PRIMARY KEY, TABNAME VARCHAR(60), COLNAME VARCHAR(80))

– Apertura del cursore, impostazione contatore e primo fetching

OPEN BaseCur
SET @NewCounter = 1
FETCH NEXT FROM BaseCur
INTO @DropData, @FieldName

– Ciclo di riempimento della tabella temporanea

WHILE (@@FETCH_STATUS = 0)
BEGIN

/* Ho già eseguito il primo FETCH quindi inserisco direttamente */

INSERT INTO MMSTPGETTABCOL (COLKEY, TABNAME, COLNAME) VALUES (@NewCounter, @TableName, @FieldName)
SET @NewCounter = @NewCounter + 1
FETCH NEXT FROM BaseCur
INTO @DropData, @FieldName
END

– Chiudo e dealloco il cursore

CLOSE BaseCur
DEALLOCATE BaseCur

– Recupero i dati che saranno restituiti come resultset ADODB

SELECT * FROM MMSTPGETTABCOL

– Droppo la tabella temporanea in modo che non resti nel database.

– Anche se la tabella viene droppata, il recordset è già stato restituito

– tramite la select precedente.

DROP TABLE MMSTPGETTABCOL
SET NOCOUNT OFF

– Creo la stored procedure

GO

La SP restituisce l’elenco dei campi di una tabella. E’ stata creata una tabella temporanea per contenerli e riportarli all’utente per dimostrare come sia possibile eseguire tutte le varie operazioni a lui necessarie e infine riportare soltanto i dati effettivamente richiesti; l’esempio in questione è banale ma rende l’idea.

Il Codice in Visual Basic

Una volta creata la SP sul server, è possibile passare alla parte di codice Visual Basic in cui andremo ad utilizzare la SP appena creata. Tenete presente che non è strettamente necessario che il seguente codice venga realizzato su Visual Basic; potete utilizzarlo su Access o se volete convertirlo in un altro linguaggio che vi permetta di utilizzare ADO.

La cosa fondamentale resta il concetto.

‘Definisco il Command ADO, la Connection e il RecordSet che utilizzerò per recuperare i dati.

Dim cmdDBStoredProc As New Command ‘ Comando ADO
Dim cnnMyConn As ADODB.Connection ‘ Connection ADO
Dim rs As New ADODB.Recordset ‘ Recordset ADO (restituito dalla St.Proc.)
Dim Tabella as String

‘ Alloco la connessione e imposto il nome della tabella
Set cnnMyConn = New ADODB.Connection
Tabella = “PROVA”

‘ Gli fornisco il tipo del server, il nome, l’utente e la password da utilizzare
‘ In particolare, il database è MarcoM con user sa e nessuna password.

cnnMyConn.ConnectionString = “driver={SQL Server};server=(local);uid=sa;pwd=;database=MarcoM”

‘ Stabilisco anche il timeout da considerare (30 secondi)
cnnMyConn.ConnectionTimeout = 30

‘ Apro la connection
cnnMyConn.Open

‘ Imposto il nome della stored procedure che voglio utilizzare tramite il Command
cmdDBStoredProc.CommandText = ” STP_GETTABLECOLS ”

‘ Specifico che si tratta di una stored procedure
cmdDBStoredProc.CommandType = adCmdStoredProc

‘ Gli fornisco anche un nome, sebbene per il momento non intenda utilizzarlo.
‘ Può servire nel caso in cui la stored procedure venga lanciata tramite la

‘ connection ADO piuttosto che tramite il Command
cmdDBStoredProc.Name = “MyProc”

‘ Fornisco un parametro in output per un possible ritorno (anche se la stored
‘ procedure al momento non ritorna nulla
cmdDBStoredProc.Parameters.Append _
cmdDBStoredProc.CreateParameter(“Return”, adInteger, adParamReturnValue, , 0)

‘ Parametro in input, il nome della tabella
cmdDBStoredProc.Parameters.Append _
cmdDBStoredProc.CreateParameter(“TableName”, adVarchar, adParamInput, 60, Tabella)

‘ Imposto la Active Connection per la mia Stored Procedure. Fatto questo, posso

’ eventualmente eseguirla come metodo della Connection o come Command
Set cmdDBStoredProc.ActiveConnection = cnnMyConn

‘ Apro il recordset rs assegnandogli il risultato di esecuzione della stored
‘ procedure.
‘ L’alternativa potrebbe essere di utilizzare cnnMyConn.MyProc
Set rs = cmdDBStoredProc.Execute

‘ Verifico che il recordset non sia vuoto (cosa che succede se non esiste la
‘ tabella fornita come parametro)
If Not rs.EOF Then

‘ Stampo il nome della tabella e di seguito tutti I campi
Debug.Print “Tabella: “ & Tabella
Do While Not rs.EOF

‘ Il nome del campo è il terzo field del recordset
Debug.Print “ – “ & rs(2)

‘ Record successivo
rs.MoveNext
Loop
Else
Debug.Print “Nessuna tabella “ & Tabella & “ presente nel database.”
End If

Both comments and pings are currently closed.

Comments are closed.