More ASP.NET Tutorials
Also see: Binding a Datareader to a Datalist
using SQL Server and C#.
First, Import the System.Data.SqlClient namespace which gives us access to the classes necessary to retrieve data from a SQL Server database.
| Imports System.Data.SqlClient |
Next define the connection string and SQL query. For this example, we will be connecting to the Northwind database on the local SQL Server instance. Northwind is a sample database that ships with SQL Server.
Dim connString As String = "Server=localhost;Database=Northwind;Integrated Security=SSPI"
Dim sql As String = "SELECT * FROM Products" |
Based on the two strings above, create a SqlConnection and SqlCommand object. These will be used to connect to the database and subsequently issue a command to retrieve data from the Products table in the Northwind database.
Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn) |
A DataReader can then be created by calling the ExecuteReader method of the SqlCommand object. Of course, we must open the connection first before we can execute any commands.
|
conn.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
|
The last step is to assign the DataReader object to the DataSource property of the Datalist
and then call the DataBind method.
|
Me.Datalist1.DataSource = reader
Me.Datalist1.DataBind()
conn.Close()
|
The complete Page_Load event of the Web form should look like this:
|
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim connString As String = "Server=localhost;Database=Northwind;Integrated Security=SSPI"
Dim sql As String = "SELECT * FROM Products"
Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)
conn.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Me.Datalist1.DataSource = reader
Me.Datalist1.DataBind()
conn.Close()
End Sub
|
DataLists use a declarative data binding technique. Running the code above will yield no result as the DataList expects the developer to explicitly state what content to be rendered. This is done declaratively in the ASPX file.
|
<asp:DataList id="DataList1" runat="server"></asp:DataList>
|
This is the standard DataList declaration. Unlike the DataGrid, the code as-is above will not render any output. In order to render output, we must declaratively state what to render by manipulating one or more of five templates: ItemTemplate, AlternatingItemTemplate, HeaderTemplate, FooterTemplate, and SeparatorTemplate.
The ItemTemplate tag allows the developer to define what will be rendered for each row in the bound DataReader. For instance, using the Northwind database in the example above, we could use the following code to render the product names found in the Products table:
|
<asp:DataList id="DataList1" runat="server">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</ItemTemplate>
</asp:DataList>
|
Note the special databinding syntax (<%# and %>). The AlternatingItemTemplate is applied to every other item in the data source. For instance, if we wanted every other product to be bold formatted, we could use the following code:
|
<asp:DataList id="DataList1" runat="server">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</ItemTemplate>
<AlternatingItemTemplate>
<b><%# DataBinder.Eval(Container.DataItem, "ProductName") %></b>
</AlternatingItemTemplate>
</asp:DataList>
|
The header and footer templates modify the header and footer of the DataList respectively. The separator template defines that which should be rendered after each item or alternating item template.
Note that we can further manipulate the output of the DataList by modifying the DataList attributes. The following example shows how to use the RepeatColumns and RepeatDirection properties:
|
<asp:datalist id="DataList1" runat="server" RepeatColumns="4" RepeatDirection="Horizontal">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</ItemTemplate>
<AlternatingItemTemplate>
<b>
<%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</b>
</AlternatingItemTemplate>
</asp:datalist>
|
When things go wrong
We can solidify our code above by using structured exception handling which will enable us to catch any errors raised. For instance, consider what would happen if the SQL command fails. An exception would be thrown and the connection would never be closed as any statements after command.ExecuteReader() will not be executed.
|
Dim connString As String = "Server=localhost;Database=Northwind;Integrated Security=SSPI"
Dim sql As String = "SELECT * FROM Products"
Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)
Try
conn.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Me.Datalist1.DataSource = reader
Me.Datalist1.DataBind()
Catch ex As Exception
' Exception handling code goes here
Finally
conn.Close()
End Try
|
If an exception occurs within code inside a try block, execution will jump to the catch clause. Here, we can do something simple such as redirecting to an error page or something more complicated such as emailing the administrator that an error has occurred (or most likely a combination of both). It all depends on what you want your application to do when it encounters an error. The finally clause executes unconditionally whether an exception happens or not, hence the connection is always closed. |