Developing a Data Access Layer for Sybase using ADO.NET: Essentials Continued
(Page 1 of 4 )
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)
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.
Next: Binding a dropdown list with information from Sybase database using ADO.NET: explanation >>
More ASP.NET Articles
More By Jagadish Chaterjee
