Developing a Data Access Layer for Sybase using ADO.NET: Working With Stored Procedures

I enclosed the source code in the form of a single file (“.vb” file).  You can use it straight away or extend it further based on your needs.  The entire discussion in this article will be based on version .NET 1.1.

If you are new to configuring Sybase for .NET or new to this series, I strongly suggest you refer to my first article of this series here.

Developing a separate class to hold all parameters of a Sybase stored procedure

In my previous two articles, you saw several methods for retrieving information from a Sybase database.  In this article we shall focus on retrieving information using stored procedures available within Sybase database.

In the case of stored procedures, sometimes we may need to execute them by passing some parameters (related to the stored procedure itself).  When we want to pass some parameter to a stored procedure, we need to know the parameter name, data type, size, and other details.

To hold all such information (sometimes called the parameter cache), I developed a separate class. The complete listing of the class is as follows:

The next section gives you a complete understanding of the class listed above.

Understanding the class

This section explains the class listed in the previous section.  Let us go through it part by part.  First of all, consider the following:

<XmlRoot("SybaseProcParameter")> _
Public Class ASEProcParameter

The name of the class is “ASEProcPrameter” (user-defined).  If we wanted to have this class serialized, the root of the XML would start at the  “SybaseProcParameter” element.  Even though for the above, serialization is not necessary, there are times when you want to pass the parameters between several tiers of your application.  Then you need to serialize the information before you do marshaling or un-marshaling.  In the above case, it would be automatically serialized to XML!  Further proceeding, we have the following:

Those are all the class level variables needed to hold the parameter information of a stored procedure.  I hope the name is reasonable and you can follow accordingly.  Further proceeding, we have the following:

The above code fragment is a constructor which accepts certain information of a single parameter and passes the information to the class level variables.  Further proceeding we have:

The above is very similar to the translation of our existing parameter information to the Sybase understandable parameter.

Adding a parameter before executing a Sybase stored procedure

We need to use the class defined in the previous sections when we want to add a parameter before executing a Sybase stored procedure.  To help us in adding a parameter, I developed a new method as follows:

The above method simply accepts a parameter name, value, data type, size, direction, and so on as parameters.  To easily work with data types, I defined my own enumeration as follows:

I will be mapping these data types to the original Sybase data types with the huge “select…case” (as in the previous code listing) within the above method itself.  The whole cache of parameters (which are going to be passed to stored procedures) gets stored in “dtParameterList.”

Retrieving a data table by executing a Sybase stored procedure: the code

To execute a Sybase stored procedure, we again need to work with all the objects, like connection, command, adapter and so on.  The following is a complete code listing for executing a stored procedure (based on the parameter cache in the previous section) at the Sybase database, which in turn returns a data table:

I excluded some code for clarity (such as exception handling).  You can get the complete program from the download (available from previous articles in this series).  The explanation for the above code is available in the next section.

Retrieving a data table by executing a Sybase stored procedure: explanation

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:  

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:

The above two lines are to work with the parameter cache and parameters of the stored procedure.  Further proceeding we have:

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.

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