Displaying Database Information in ASP.NET

Unfortunately, that's pretty much like asking : "What occupations are available for me in the world?” Well, it's not quite that bad, but there are a lot of different ways to do it. First, let's break it down into two sections – Connecting to and getting information from the database, and Displaying it in the web page. For each of those sections (which could easily have been broken down further), there are multiple possibilities, which this tutorial will at least partially address.

However, to make this merely a simple tutorial, instead of a book, hitting the highlights of the basic things necessary for Dispaying DataBase information on a web page, using ASP.NET. Also, the only databases covered here will be SQL Server and MS Access, the only language covered here will be VB.NET and we're only going to address Select queries, leaving out (updates, deletes and inserts).

In connection to a database, there are several items which are necessary:

• Namespaces
• Connection String
• Connection
• SQL Query (or non-query, in the case of an insert, update or delete)
• Opening the Connection
• Executing the Query
• Closing the connection

Namespaces

For both SQL Server and MS Access, we import the System.Data namespace. Separately, for SQL Server, also:

<%@ Import Namespace="System.Data.SQLClient" %>

For MS Access (or any OleDb compliant database):

<%@ Import Namespace="System.Data.OleDB" %>

Connection String:

Of course, here we also have multiple possibilities, depending on the database. Here's a sample for SQL Server:

Dim strConn as string = "server=(local);uid=sa;pwd=pwd;database=Northwind"

And here's one for an Access database:

Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath(".\northwind.mdb") & ";"

Connection:

Then, using that connection string, we need to define an actual connection using the connection string. First, again, is SQL Server:

Dim MyConn as New SQLConnection(strConn)

And, using MS Access:

Dim MyConn as New OleDBConnection(strConn)

SQL Query:

A query is basically a kind of question, with which you address the database to say, "I'd like (this list of fields) from (This table)”. In SQL, which stands for Standard Query Language, this translates to:

'"Select (list of fields) from (tablename)"
'Defined as:
Dim MySQL as string = "Select (list of fields) from (tablename)"

Alternately, in SQL, you can narrow down your search/selection by using a Where clause, adding a specific criteria with which to search the database table:

"Select (list of fields) from (tablename) Where (field name)=(this filter)"

Opening the Connection

Here's where it gets a little complicated for someone who's unfamiliar with ASP.NET. There are two major ways to get information back from the database with ADO.NET (Database interaction with ASP.NET). One is with a DataReader and the other is a DataSet. The differences are boiled down, very simply, here. If you want to just get the information quickly and with as little overhead as possible and then display it, use a DataReader. If you have been in the Classic ASP world, it's just like a forward-only cursor for a recordset. Just remember, there are things you can't do with a DataSet, like Paging or Sorting with a DataGrid. Of course, this leads us to the other major way to get data from a database, the DataSet. When you use a DataSet, you query the database, and receive a result set that you can re-use as an entity in different ways. But, like was noted earlier, it uses much more overhead than a DataReader. As you can see, this is a very simplistic over-view of the DataReader and DataSet and it's not meant to be, in any way, an in-depth technical presentation for them.

DataReader

With a DataReader, there are two ways to use it for receiving data. You can either read through it, line by line, manipulating the data as it comes in, or you can merely bind it to a Server Control. One reason for using a DataReader and reading it line by line, is to query the database using a where clause, narrowing down your search, and then assigning the field names to variables Here's sample code of how to read the data line by line, doing just that:

Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, Myconn)
Myconn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
While objDR.Read()
    'These variables would natrually need to be declared (Dim) earlier.
    intID=objDR("ID")
    strFirst=objDR("Fname")
    strLast=objDR("Lname")
End While

The first line declares the variable for creating the DataReader. If you were using MS Access, it would be OleDbDataReader, instead of the SQL variety. Then, the second line declares and creates a command, using the SQL query and the connection defined earlier by the code. Again, this would read OleDBCommand, instead of SQLCommand, if you were using MS Access, or any other OLEDB compliant database. Next, we open the previously defined connection. The ExecuteReader line actually executes the command and puts the data into the DataReader. In the While/End While construct, we merely assign the retrieved fields to the variables. Then, we can use the variables anywhere between the Body tags, to display the information like this:

<%# strLast %>" />

The other way to use a DataReader is to first, open the connection (like listed above), and then bind the data to one of the server controls in ASP.NET, like a DataGrid, Repeater, or DataList, in order to display it on the page:

MyDataGrid.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
MyDataGrid.DataBind()

DataSet

Here's how to create a Dataset and bind it to a server control:

Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
      ' OleDB for the above line would naturally be: Dim Cmd as New OleDbDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"Customers")
MyDataGrid.Datasource=ds.Tables("Customers").DefaultView
MyDataGrid.DataBind()

Let's look at this line by line – the first line creates the new DataSet, and then we create the command with the SQL statement and the connection, but here, we assign it to a DataAdapter for use. Next, we the DataAdapter with the DataSet we created. Here, we use ‘Customers' as the name of the DataSet. Of course, to end up, in this case, we bind the DataGrid with the information we retrieved from the database.

In short – in binding data to a server control, we just ‘grab' the data desired from the database/table and kind of 'shove' it in a server control, in order to display it. For a DataGrid, it's fairly simple, since it's so columnar, like a spreadsheet – there are rows and columns, just like the data is stored in the database table. It can get more complex, of course, with a DataList, or Repeater. There is no way to go into all the different formatting techniques that are possible in this one tutorial, but we tried to hit the ‘highlights', if you will of connecting to a database, retrieving information and then displaying it in a very simple manner.