Developing a Data Access Layer for Sybase using ADO.NET: Essentials Continued

This article is the second one in a series focusing on developing a simple DAL (Data Access Layer) for any database using ADO.NET. In this series, we consider Sybase as the database of choice for developing the DAL.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 3
March 27, 2006
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

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, as is, or extend it further based on your necessities.  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.

Binding a dropdown list with information from Sybase database using ADO.NET

This is the method mainly used to bind pairs of values (key and description) from the database to a dropdown list staying on a web form (or ASP.NET)!  It has a lot of parameters to discuss.  Before starting our discussion of the list of parameters, let us walk through the following complete code first:

Public Sub BindDropDownList(ByRef dl As DropDownList, ByVal
sqlSELECT As String, Optional ByVal Caption As String = Nothing,
Optional ByVal ShowIDandDESCSeparated As Boolean = True, Optional
ByVal ClearExistingItems As Boolean = True)
        Try
            Dim dt As DataTable = getDataTable(sqlSELECT)
            If dt.Columns.Count < 2 Then
                Throw New Exception("Atleast 2 cols are required
to bind, Given SQL Statement: " & sqlSELECT)
            End If
 
            If ClearExistingItems Then dl.Items.Clear()
            If Not Caption Is Nothing Then
                dl.Items.Add(New ListItem(Caption, Caption))
                dl.Items.Add(New ListItem("-".PadLeft(150, "-"),
Caption))
            End If
            Dim dr As DataRow
            If ShowIDandDESCSeparated Then
                For Each dr In dt.Rows
                    dl.Items.Add(New ListItem(dr(0) & " - " & dr
(1) & "", dr(0) & ""))
                Next
            Else
                For Each dr In dt.Rows
                    dl.Items.Add(New ListItem(dr(1) & "", dr(0) &
""))
                Next
            End If
            dl.SelectedIndex = -1
        Catch ex As Exception
            Throw New Exception(ex.Message & ". Cannot bind
DropdownList(" & dl.ID & "). Given SQL Statement: " & sqlSELECT)
        End Try
    End Sub



Let us first discuss the parameter list of the above method.  Following is the list:

  • dl
  • sqlSELECT
  • Caption
  • ShowIDandDESCSeparated
  • ClearExistingItems

The first one is the dropdown list available in ASP.NET.  You should also observe the “byref” for its declaration.  We need to add items to the dropdown list within our method (which automatically gets affected at the ASP.NET application).  

The “sqlSELECT” parameter receives the SELECT statement in the form of a string.  We need to have at least two columns selected within the SELECT statement passed to it.  The “Caption” is the first element generally used to display some external message.  The messages would be something like “Select an Item,” “All,” and so on.

“ShowIDandDESCSeparated” is mainly used to confirm whether it is necessary to display all the IDs along with the description (separated with a hyphen) or not.  The default is true, which means it displays both key and description separated with a hyphen.

“ClearExistingItems” is mainly used to confirm whether all the items need to be cleared before adding new items or not.  The default is true, which means it clears the existing items before adding new items to the dropdown list.

I shall explain the above code in next section.

Binding a dropdown list with information from Sybase database using ADO.NET: explanation

This section mainly deals with the explanation of the code listed in the previous section.  Let me explain it part by part:

            Dim dt As DataTable = getDataTable(sqlSELECT)
            If dt.Columns.Count < 2 Then
                Throw New Exception("Atleast 2 cols are required
to bind, Given SQL Statement: " & sqlSELECT)
            End If

The above part mainly retrieves the information based on the SELECT statement passed to it.  It also checks whether the information is retrieved in the form of two columns or not.  If not, it would raise an error.

            If ClearExistingItems Then dl.Items.Clear()
            If Not Caption Is Nothing Then
                dl.Items.Add(New ListItem(Caption, Caption))
                dl.Items.Add(New ListItem("-".PadLeft(150, "-"),
Caption))
            End If

The above part first tries to clear the dropdown list based on the parameter we pass.  If we provide some “caption” to display, then it would be added as the first element along with a separator.  Further proceeding, we have the following:

            Dim dr As DataRow
            If ShowIDandDESCSeparated Then
                For Each dr In dt.Rows
                    dl.Items.Add(New ListItem(dr(0) & " - " & dr
(1) & "", dr(0) & ""))
                Next
            Else
                For Each dr In dt.Rows
                    dl.Items.Add(New ListItem(dr(1) & "", dr(0) &
""))
                Next
            End If
            dl.SelectedIndex = -1

The above part gets divided into two parts.  One is related to adding items with both key and description and the other is related to adding items with only description, without the key.  One of the parts gets executed based on the parameter we specify for “ShowIDandDESCSeparated.”  The last statement is mainly used to make the dropdown list point to no item after binding.

Getting a dataset of information from Sybase database using ADO.NET

This mainly accepts a SQL SELECT statement as a parameter and simply returns the output of the SELECT statement in the form of a dataset in ADO.NET.  Let us walk through the complete code first:

Public Function getDataSet(ByVal sqlSELECT As String, Optional
ByVal DataTableName As String = Nothing) As System.Data.DataSet
        Dim da As AseDataAdapter
        Try
            Dim ds As New DataSet
 
            da = New AseDataAdapter(sqlSELECT, _ConnectionString)
            If DataTableName Is Nothing Then
                da.Fill(ds)
            Else
                da.Fill(ds, DataTableName)
            End If
 
            da.Dispose()
            Return ds
 
        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 object I used is “AseDataAdapter” (to handle the communication between database and offline data).  I declared the object as follows:

        Dim da As AseDataAdapter

Let us consider the following statement:

        da = New AseDataAdapter(sqlSELECT, _ConnectionString)

The statement creates an adapter which tries to execute our SELECT statement (passed in the form of parameter) based on the database connection created inline.  We need not specify any connection related object.  Just providing the connection string would also do the job.

Dim ds As New DataSet
            If DataTableName Is Nothing Then
                da.Fill(ds)
            Else
                da.Fill(ds, DataTableName)
            End If
            da.Dispose()
            Return ds

 

I created a new dataset object which tries to hold all the data retrieved by the data adapter.  The second statement (in the fragment above) checks to see if “DataTableName” is provided or not.  If it is provided, it creates a data table within the data set with the user specified name or else it defaults to its own data table name.  And finally the data set is returned.

I also added another method for simplifying certain scenarios (especially “data view”).  The following would be the definition of “data view”:

Public Function getDataView(ByVal sqlSELECT As String) As
System.Data.DataView
        Return New DataView(getDataTable(sqlSELECT))
    End Function

It simply executes the SELECT statement, retrieves the data table and converts it to a new “Data View” object which finally gets returned.  The “data view” is mainly used to sort or filter the information existing inside a data table.  It is one of the most convenient ways to work with data returned from a database.

Getting a row of information from Sybase database using ADO.NET

This mainly accepts a SQL SELECT statement as a parameter and simply returns only the first row of the output of the SELECT statement in the form of a “datarow” in ADO.NET.  Let us walk through the complete code first:

Public Function getDataRow(ByVal sqlSELECT As String) As
System.Data.DataRow
        Dim da As AseDataAdapter
        Try
            Dim dt As New DataTable
            da = New AseDataAdapter(sqlSELECT, _ConnectionString)
            da.Fill(dt)
            da.Dispose()
            If dt.Rows.Count = 0 Then Return Nothing Else Return
dt.Rows(0) 'return only first row
        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 object I used is “AseDataAdapter” (to handle the communication between database and offline data).  I declare the object as follows:

        Dim da As AseDataAdapter

Let us consider the following statement:

        da = New AseDataAdapter(sqlSELECT, _ConnectionString)

The statement creates an adapter which tries to execute our SELECT statement (passed in the form of a parameter) based on the database connection created internally.  We need not specify any connection related object.  Just providing the connection string would also do the job.

            Dim dt As New DataTable
            da.Fill(dt)
            da.Dispose()
            If dt.Rows.Count = 0 Then Return Nothing Else Return
dt.Rows(0) 'return only first row

I created a new “datatable” object which tries to hold all the data retrieved by the data adapter.  The second statement makes the adapter fill the information into the data table.  The last statement in the above code fragment checks to see if any rows exist within the data table.  If no rows are available, the method returns nothing or else returns only the first row (which is nothing but the data row).

I shall cover the rest of the methods in upcoming articles, so make sure that you sign up for a newsletter to get notified.  Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

blog comments powered by Disqus
ASP.NET ARTICLES

- Implementing ASP.NET 4.0 Page.MetaDescriptio...
- ASP.Net Development Tips
- Intro to Sessions in ASP.Net
- Google Maps API Introduction in ASP.NET usin...
- Creating an ASP.NET 3.5 Gridview Image Galle...
- Encrypt QueryString in ASP.NET 3.5 using VB....
- ASP.NET 3.5 Drop Down List Controls
- Connect to Access Database with ASP.Net
- Secure Audio Streaming with ASP.Net and Flash
- Dynamic Sitemap and Navigation in ASP.Net
- Implement Gzip and Deflate Compression in AS...
- Run ASP.Net in Ubuntu with Apache
- ASP.Net Mono Website Contact Forms
- ASP.Net URL Rewriting Methods
- Murach`s ASP.NET 4 Web Programming with C# 2...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 2 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials