HomeASP.NET Developing a Data Access Layer for Sybase ...
Developing a Data Access Layer for Sybase Using ADO.NET: Essentials
This article mainly focuses on developing a simple DAL (Data Access Layer) for any database using ADO.NET. There exist several types of DAL for Microsoft SQL Server on the Internet (but not much about native Sybase connectivity). In this article, we consider Sybase as the database of choice for developing the DAL.
A downloadable file for this article is available here.
I enclosed the source code in the form of a single file (“.vb” file). You can use it right away or extend it further based on your needs. The entire discussion in this article will be based on version .NET 1.1.
Contents of the Data Access Layer
The primary job of a Data Access Layer (DAL) is to communicate seamlessly and effectively with the database by minimizing the resources at the server. This is possible only when we separate our business logic layer with the DAL and make the DAL available to a greater number of business logic layers (or even applications).
Considering this from the ADO.NET point of view, to retrieve some information from a database, we generally use some frequent objects such as connection, command, data reader, data adapter, and so on. Just imagine that we would like to bind four drop down lists (at various points in time). We cannot simply open different connections at all the levels, issue several commands and work with many ADO.NET objects every time!
We need to make the data access very simple, reusable, extensible, logable, maintainable, and deployable with several other features. This article doesn’t cover all of the features mentioned, but I tried to make it as simple as possible so that you can further extend it to suit your own needs.
The DAL that we are going to develop now mainly contains the following frequently used routines:
Those were the routines I used very frequently to retrieve information from any database. I didn’t add any “data manipulations” (like executing an SQL statement) for this DAL yet. But you can add it according to your needs.
Now, let us jump into some of the core issues of native Sybase data access using ADO.NET.
To work with a Sybase database using ADO.NET natively, we need to have a Sybase database installed on our computer. If the database exists on another server, then we need to install “Sybase client” (or “pc-client”) which is freely downloadable from Sybase's website.
You need to select a custom installation during the installation of Sybase client. You need to “switch on” all ODBC, OLEDB and ADO.NET components during the custom installation of Sybase client (to activate the full use of ADO.NET). The installation also includes some samples of VB.NET solutions for getting connected with the Sybase database. You can have a trial run using those samples for testing connections, datasets, and so on.
The following are the core DLLs of Sybase that deal with data access using ADO.NET:
Of all of the above DLLs, we will work with only the assembly “Sybase.Data.AseClient.dll” (which indeed works with the rest of the DLLs) in ADO.NET. It is always suggested to copy all of the three into the “bin” folder of your ASP.NET application (for instance, if you are developing a web application). You also need to add a reference to “Sybase.Data.AseClient” from the “.NET components” (and not from the “bin” folder) to your application, if it needs any data access to the Sybase database.
Another most important issue is that the files “sybdrvado11.dll” and “sybdrvssl.dll” should also be copied into the folder “C:windowsassemblygacSybase.Data.AseClient 1.1.327.0__26e0f1529304f4a7”. The folder is not directly visible from Windows Explorer. You need work with “Run” or “Command Prompt” to go to that folder and copy the files specified. This is a step that everyone tends to forget.
Once the above is properly configured, your .NET applications can easily interact with the Sybase database. A sample connection string (make it into a single line) to get connected to Sybase would be as follows:
Data Source='172.28.4.175'; Port='5000';UID='sa';PWD='';Database='databasename'
You need to modify the above connection string based on the configurations of the Sybase server. If you are not very familiar with such configurations, you need to consult your Sybase database administrator or system administrator.
It is always suggested that you have your connection string in “Web.Config” (if using web forms) or “app.config” (if using Windows forms). You can encrypt the connection string, if you don’t want it to be simply readable.
The “web.config” file may look something like the following:
Our DAL needs to have a “key” (the key of the connection string in web.config file) passed so that it gets connected accordingly. The following could be the code for the constructor:
Public Sub New(ByVal ConnStrConfigKey As String)
Try
Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings.Get (ConnStrConfigKey)
_ConnectionString = ConnectionString
If Len(Trim(_ConnectionString & "")) = 0 Then Throw New Exception("Connection String not configured")
Catch ex As Exception
Throw New Exception(ex.Message & ". Invalid Database Configuration ")
End Try
End Sub
Before defining the above constructor, I declared a simple class level variable to hold the connection string as follows:
Private _ConnectionString As String
From now on we can use above variable to work with the connection string. The following line retrieves the connecting string from web.config file:
Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings.Get (ConnStrConfigKey)
We check for the validation of connection string as follows:
If Len(Trim(_ConnectionString & "")) = 0 Then Throw New Exception("Connection String not configured")
And that completes our initial steps for working with the connection string. Apart from the above constructor, I also managed to import the Sybase namespace at the top of my class as follows:
Imports Sybase.Data.AseClient
That is the only external assembly I added as reference to the project. It is the most essential assembly for connecting to Sybase natively. The other imports are as follows:
This is one of the most frequently used methods (“getDataTable”) in the DAL. This mainly accepts an SQL SELECT statement as a parameter and simply returns the output of the SELECT statement in the form of a “data table” in ADO.NET. Let us walk through it first:
Public Function getDataTable(ByVal sqlSELECT As String) As System.Data.DataTable
Dim Conn As AseConnection
Dim da As AseDataAdapter
Try
Conn = New AseConnection(_ConnectionString)
Dim dt As New DataTable
da = New AseDataAdapter(sqlSELECT, Conn)
da.Fill(dt)
da.Dispose()
Return dt
Catch ex As Exception
Try
da.Dispose()
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & sqlSELECT)
End Try
End Function
The main native Sybase objects I used are “AseConnection” (to work with the database connection) and “AseDataAdapter” (to handle the communication between the database and offline data). I declare both of those objects as follows:
Dim Conn As AseConnection
Dim da As AseDataAdapter
Let us consider the following two statements:
Conn = New AseConnection(_ConnectionString)
da = New AseDataAdapter(sqlSELECT, Conn)
The first statement creates a new Sybase database connection (natively) using the connection string defined in the web.config file. The second statement creates an adapter, which tries to execute our SELECT statement (passed in the form of a parameter) based on the previous database connection.
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()
Return dt
I created a new “datatable” object, which tries to hold all the data retrieved by the data adapter. The second statement (in the fragment above) executes our SELECT statement and the output (or result) is filled into the data table, which is finally returned from the function.
This is also one of the most frequently used methods (“getRowValue”) in the DAL. This mainly accepts a SQL SELECT statement as a parameter and simply returns the output (only the value of the first row belonging to the first column value) of the SELECT statement in the form of a “string” in ADO.NET.
This function (or method) is mostly suitable for SELECT commands like the following:
SELECT ename FROM emp WHERE empno=1001 SELECT count(*) FROM emp SELECT avg(sal) FROM emp
Let us walk through the code first:
Public Function getRowValue(ByVal sqlSELECT As String) As String
Dim Conn As AseConnection
Dim cmd As AseCommand
Dim value As String = ""
Try
Conn = New AseConnection(_ConnectionString)
cmd = New AseCommand(sqlSELECT, Conn)
With cmd
.Connection.Open()
value = .ExecuteScalar() & "" 'concatenating an empty string..to eliminate null or nothing
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
cmd.Dispose()
End If
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & sqlSELECT)
End Try
End Function
The main native Sybase objects I used are “AseConnection” (to work with the database connection) and “AseCommand” (to execute any SQL command at the database and return the result). I declare both of those objects as follows:
Dim Conn As AseConnection
Dim cmd As AseCommand
Let us consider the following two statements:
Conn = New AseConnection(_ConnectionString)
cmd = New AseCommand(sqlSELECT, Conn)
The first statement creates a new Sybase database connection (natively) using the connection string defined in the web.config file. The second statement creates a command object, which tries to execute our SELECT statement (passed in the form of a parameter) based on the previous database connection.
With cmd
.Connection.Open()
value = .ExecuteScalar() & "" 'concatenating an empty string..to eliminate null or nothing
.Connection.Close()
.Dispose()
End With
Return value
From the above code fragment, we open the database connection using the command object first. We get the command to be executed at the database using “ExecuteScalar.” “ExecuteScalar” always returns only one value from the command it has executed. Finally, we close the connection, dispose (clear the resources occupied) the command object and return the value retrieved by the command object.
I created a new “datatable” object, which tries to hold all the data retrieved by the data adapter. The second statement (in the fragment above) executes our SELECT statement and the output (or result) is filled into the data table, which is finally returned from the function.
I shall cover the rest of the methods in the upcoming articles, so make sure that you sign up for a newsletter to notify you. Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com.
ASP.NET 
ASP.NET Export to Excel and Word using VB.NET and C#
