Developing a Data Access Layer for Sybase using ADO.NET: Working With Stored Procedures - Retrieving a data table by executing a Sybase stored procedure: explanation
(Page 5 of 5 )
This section deals with the source code provided in the previous section. Let us go in a step by step manner. Let us first start with the following code:
Dim cmd As AseCommand
cmd = New AseCommand(ProcName, New AseConnection
(_ConnectionString))
The above code fragment creates a new Sybase command to execute the stored procedure. I even created a database connection inline itself. Further proceeding we have the following:
Dim Parm As AseParameter
Dim privateDataTable As New DataTable
The above two lines are to work with the parameter cache and parameters of the stored procedure. Further proceeding we have:
With cmd
.CommandType = CommandType.StoredProcedure
Dim UsedParameter As ASEProcParameter Dim ConvertedParameter As AseParameter
Dim drParam As DataRow
For Each drParam In dtParameterList.Rows
UsedParameter = Nothing
Dim ser As New XmlSerializer(GetType
(ASEProcParameter))
UsedParameter = CType(ser.Deserialize(New
StringReader(drParam(0))), ASEProcParameter)
ConvertedParameter =
UsedParameter.getASEParameter
.Parameters.Add(ConvertedParameter)
Next
From the above code fragment, I first mentioned that the command to execute at the Sybase database is a stored procedure. After that, I have a few declarations to work with the parameter cache. The loop is the heart of the method, which adds all the parameters available in the parameter cache to the command (which shall further proceed to the database and execute the stored procedure).
We convert each of our parameters in the parameter cache to the “AseParameter” type and finally add them to the command object. The command object executes the specified stored procedure by passing all those parameters added to it.
Dim privateSQLDataAdapter As New AseDataAdapter
(cmd)
privateSQLDataAdapter.Fill(privateDataTable)
.Connection.Close()
.Dispose()
privateSQLDataAdapter.Dispose()
End With
Return privateDataTable
The above code fragment is where the real execution of the stored procedure takes place. We use a data adapter to work with the command, and to return the output in the form of data table. Once we have executed the stored procedure, we clear all the resources and finally return the data table to the calling program.
Summary
This ends our approach to designing a simple Data Access Layer. Even though it may not be helpful for enterprises, it would certainly be helpful for small to medium projects. Further, you can extend the class and create a service component (COM+) to support enterprises. You can have a look at one of my articles dealing with the enterprise level here to guide you to a better standard.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
