HomeASP.NET Developing a Data Access Layer for Sybase ...
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.
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)
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.
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.
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:
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.
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.
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
ASP.NET 
ASP.NET Export to Excel and Word using VB.NET and C#
