Database Independent Development using ASP.NET 2.0: Dealing with Stored Procedures - Executing a stored procedure as part of the Data Access Layer
(Page 3 of 6 )
The previous sections were dedicated to dealing with stored procedure parameters. Now, we shall deal with the execution of a stored procedure. The following is the method which executes a stored procedure based on the parameters added to the cache:
Public Sub SPExecute(ByVal SPName As String)
Dim cmd As DbCommand = GetDBCommand()
AddSPParamFromCache(cmd)
cmd.CommandText = SPName
cmd.CommandType = CommandType.StoredProcedure
Try
cmd.Connection.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(ex.Message & "-->Stored Procedure:" & SPName)
Finally
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
End If
cmd.Dispose()
SPParameterClearCache()
End Try
End Sub
The only new statement from the above code is the following:
AddSPParamFromCache(cmd)
It is a separate method from the same class, which is defined as follows:
Private Sub AddSPParamFromCache(ByRef cmd As DbCommand)
If alSPParamCache Is Nothing Then
Exit Sub 'no parameters to add
End If
For Each objSPParam As SPParameter In alSPParamCache
Dim p As DbParameter = cmd.CreateParameter
With p
.ParameterName = objSPParam.ParameterName
.Value = objSPParam.ParameterValue
.Direction = objSPParam.ParameterDirectionUsed
.DbType = objSPParam.ParameterDataType
.Size = objSPParam.ParameterSize
End With
cmd.Parameters.Add(p)
Next
End Sub
Before executing the stored procedure, we need to add all the parameters (available as part of the cache) to the command object. The above method simply adds all the parameters available in the cache "alSPParamCache" (of type ArrayList) to the Command object passed to it.
Next: Returning result sets (or a set of rows) by executing a stored procedure >>
More ASP.NET Articles
More By Jagadish Chaterjee
