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:
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()
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.