Home.NET VB.NET Data Retrieval: How to Retrieve Ent...
VB.NET Data Retrieval: How to Retrieve Entire Table Rows in MS SQL Server
There are times when you can't do things the easy way in ASP.NET 3.5. What if you can't rely on its default settings and have to define everything? That's when you might want to use VB.NET. This article will show you how to do that with an application that connects and retrieves data from an MS SQL Server database.
You might already know that you can connect and retrieve information from the MS SQL Server database in ASP.NET 3.5 by using a "click and drag" technique. For example, if you are working with GridView and DetailsView web controls, you can simply configure the SQL data source to connect, retrieve data and then display it on the web browser, without the need for any hard coding or customized code.
There are times, however, that you really need to customize your web development. For instance, you may need to define everything, not just rely on the default settings and formatting which are available in the ASP.NET 3.5 controls. This means you won't be using click and drag techniques.
It also means that you will need to write your own code or edit an existing source code that will accomplish the following functions:
1. Connect to the MS SQL server database. (Done in default.aspx.vb)
2. Retrieve information from the database according the SQL query commands. (Done in default.aspx.vb)
3. Display the information back to the web browser. (Done in default.aspx using repeater, headertemplate, itemtemplate and footertemplate controls).
This tutorial will focus on the use of VB.NET to connect and retrieve data from an MS SQL Server database in order to display entire table rows to the web browser, without using GridView or DetailsView web control.
Like other programming languages, such as PHP, a basic tutorial includes information on how to retrieve the entire database table and display all of the records on the web browser.
Step 1. You will need to create the sample database, tables and data mentioned in this tutorial.
The sample database has the following specifications:
Database name: Firstdatabase.mdf
Database table name: movie
Database contents (see screen shot below):
Step 2. Say you would like to display the entire content of the database shown above in the browser as an HTML table. Then you will need to effectively use the "data binding technique" in ASP.NET 3.5, which is discussed in detail at the link.
Step 3. First, you need to declare the head section of your Default.aspx:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Display entire table rows in MS SQL server using VB.NET</title>
</head>
Discussion: Make sure that the CodeFile is set to Default.aspx.vb; this will contain the exact server side code in VB.NET.
Actually, if you created your website using Visual Web Developer, this is set up for you already, and you only need to tweak the title tag of the page.
Step 4. Start the body's content by putting in the source code of the repeater, header template and the table:
<body>
<form id="Form1" method="post" runat="server">
<asp:Repeater id="Repeater1" runat="server">
<HeaderTemplate>
<table>
<tr>
<th>Movie ID</th>
<th>Movie title</th>
<th>Movie genre</th>
<th>Running time</th>
<th>Director</th>
<th>Date released</th>
</tr>
</HeaderTemplate>
The overall purpose of the repeater is to loop data from database. Of course, since you are interested in returning all of the rows from the database, you need the repeater web control to loop through all of the database records.
The purpose of the header template is to signify the header of your HTML table. For example, the header should have column names such as Movie ID, Movie title, Movie genre, and so on.
Step 5. After defining your column header and repeater control, you are ready to display your records from the database on the web browser using data binding syntax:
Discussion: Now that the repeater will loop through the records, ItemTemplate is used to display these "items" or records in the HTML table on a per-row basis. The pulled records will then be "bound" using the syntax:
The MSSQL_table_column_name will be replaced with the actual database column name, such as movietitle, etc.
In the ItemTemplate above, the order in which we display records (from left to right in the HTML table) is as follows:
1. movieid
2. movietitle
3. moviegenre
4. runningtime
5. director
6. datereleased
It is important that this order is consistent with the order you used in the <headertemplate> discussed previously. If it isn't, the data that will be shown in the web browser will be labeled incorrectly.
Step 6. You need to close all tags, like the table, repeater and the form.
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>
The FooterTemplate is used to signify those items that you would like to display after ItemTemplate. Since you are only interested in closing the table tag, only </table> is enclosed within the footer template.
Of course the Default.aspx cannot do anything without the VB.NET script (in Default.aspx.vb). The main function of the Default.aspx script is to establish a connection to the MS SQL Server database, execute queries and get data from the database, and then prepare it for binding (that will be used by the Default.aspx repeater and binding controls discussed in the previous section).
Step 1. Define the namespace that SHOULD be used in order for the script to work properly:
Imports System.Data
Imports System.Data.SqlClient
More info about imports are discussed here: http://visualbasic.about.com/od/quicktips/qt/imports.htm
Step 2. Put in the partial class_Default, and the page load event:
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Discussion: The page load event fires when the page is loaded in the web browser. So if the Default.aspx is loaded in the browser, this function will execute in the server.
Step 3. Declare your MS SQL connection strings:
Dim cnn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory| Firstdatabase.mdf;Integrated Security=True;User Instance=True")
Discussion: Bear in mind that the above connection is used for connecting the database in local host environment (using Visual Web Developer Express). However if you are looking for connection strings that can be applied for an actual web hosting environment, you can refer to this page: http://www.connectionstrings.com/sql-server-2005
Step 4. Define your MS SQL query
Dim cmd As SqlDataAdapter = New SqlDataAdapter("select * from movie", cnn)
Discussion: Since you are interested in displaying the entire database rows/records to the web browser, you will need to use this query:
select * from movie
Of course, you can modify this query to fit your web application.
Step 5. Define your data set and put the retrieved data in preparation for data binding applications:
Discussion: It is important that you use the correct repeater ID name. This should match the one you are using in the Default.aspx source code.
For example, the VB.NET code uses Repeater1 as the ID because the repeater web control ID name in the Default.aspx source code is Repeater1 (refer to the Default.aspx source code discussed previously).
The repeater ID name should matched in both your Default.aspx and Default.aspx.vb code in order to prevent error.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim cnn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory| Firstdatabase.mdf;Integrated Security=True;User Instance=True")
Dim cmd As SqlDataAdapter = New SqlDataAdapter("select * from movie", cnn)
Dim ds As DataSet = New DataSet()
cmd.Fill(ds)
Repeater1.DataSource = ds
Repeater1.DataBind()
End Sub
End Class
Now that the default.aspx and default.aspx.vb source code are complete, try running your sample application in the web browser (File -> View in Browser). Your output should look like the screen shot below:
Final notes on Implementation
Since the objective of this tutorial is to show an alternative solution for retrieving database records without the use of GridView/DetailsView (this can be done very easily with GridView and DetailsView web controls), you might observe that with the use of repeater, itemtemplate and headertemplate, there is no formatting output in the page.
You will still need to define the detailed formatting, styles and layout manually. Since you need a purely customized ASP.NET web application, you need to define almost everything by yourself in code.
If you are looking for a solution that will query the MS SQL database and return only a single item as opposed to grabbing all of the table's entire records or rows, you might want to read this tutorial.
In that tutorial, the queryID is the input to the VB.NET script, which is then used to query the database and return a single value.
ASP.NET Export to Excel and Word using VB.NET and C#
