Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Jul 15, 2002

Reading Data into an ASP.NET Page - Page 2

By DatabaseJournal.com Staff

All right, so you're tired of hearing the theory and concepts behind ADO.NET. Me too. Let's look at and discuss some techniques and code that will allow us to read data into our ASP.NET web pages. I am going to be using the "pubs" database that ships with SQL Server to demonstrate the examples. I will also be introducing one ASP.NET control that we are going to use to display the data we retrieve: the ASP DataGrid. Although this control will not be covered at length, you will get a sense of its basic functionality and how ASP.NET strives to separate presentation and business logic.

First of all, I'm going to show you all of the code for a small data access program. Take a minute and look over all the code for this example. Don't worry if you don't understand what's going on, I will explain each line.

 1 <%@ Import Namespace="System.Data" %>
 2 <%@ Import Namespace="System.Data.SQLClient" %>
 3 <html>
 4   <script language="vb" runat="server">
 5     'Page load event of the web page.
         This is similar to the Form_Load event in VB.'
 6     Protected Sub Page_Load(ByVal sender As System.Object,
           ByVal e As System.EventArgs) Handles MyBase.Load
 7       Dim myConnection as New SQLConnection(
           "Server=localhost;Database=pubs;UID=sa;PWD=;")
 8       myConnection.Open
 9
10       Dim strSQL as String = "select * from authors order by au_lname"
11       Dim myCommand as New SQLCommand(strSQL,myConnection)
12
13       Dim myDataReader as SQLDataReader
14       myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
15
16       myDataGrid.DataSource = myDataReader
17       myDataGrid.DataBind()				
18
19       myConnection.Close()
20     End Sub
21   </script>
22 <head>
23   <title>Viewing Data with ADO.NET</title>
24 </head>
25 <body>
26   <p>
27   <asp:DataGrid ID="myDataGrid" Runat="server" />
28   </p>
29 </body>
30 </html>

The first thing we are doing in this page is importing the proper namespaces. On lines 1 & 2 we import the System.Data and System.Data.SQLClient namespaces, which give us access to the objects we need to access SQL Server Data. If you need to access other data, such as Microsoft Access, Oracle, etc., simply import the System.Data.OleDb namespace rather than System.Data.SQLClient. Line 3 simply opens up our HTML tag for the web page.

Lines 4 through 21 hold the Visual Basic code that will provide the functionality in our page and I will come back to the specifics of this section in just a moment.

Lines 22 through 30 are self-explanatory HTML with one exception, and that is line 27:

27  <asp:DataGrid ID="myDataGrid" Runat="server" />

This is the ASP.NET DataGrid server control. The tag is just like a normal HTML tag, except for the fact that it opens with the ‘asp' namespace (in bold). The ‘ID' attribute of the tag sets the name of the control, which is how we will access the object programmatically. The ‘Runat' attribute tells the server that we want this control produced on the server. There are a plethora of other properties that can be set on this control, but they will not be discussed in this article. Just know, that these properties can be set either programmatically or in the HTML declaration of the control (notice that logical and presentational layers are separated, and that there is no HTML embedded within the <script> tags).

Finally, we get to the real meat and potatoes of the program, the code between the <script> tags in lines 4 through 21. The script tag also has the Runat attribute where we tell the server we want the code processed on the server. Line 6 is our procedure declaration. Don't worry about the parameters between the parentheses (just know they need to be there), but notice that this sub procedure "handles" the load event of this page.

6  Protected Sub Page_Load(ByVal sender As System.Object,
     ByVal e As System.EventArgs) Handles MyBase.Load

Lines 7 & 8 are where we see our first ADO.NET objects come into the picture. The SQLConnection object is much like the ADO Connection object. A nice feature of the .NET platform is its object orientation compared to VBScript. We can simply declare the new object and pass the connection string into it with one line of code. Once we have the SQLConnection object created, we open it… it's that easy.

7  Dim myConnection as New SQLConnection("Server=localhost;Database=pubs;UID=sa;PWD=;")
8  myConnection.Open

Next we put our query into a string and pass both it and our SQLConnection object into a new SQLCommand object. Again, this object is like its ADO 2.6 counterpart, though you will soon see a new method associated with ADO.NET's command object (just one of many new methods and properties).

10  Dim strSQL as String = "select * from authors order by au_lname"
11  Dim myCommand as New SQLCommand(strSQL,myConnection)

Continuing on, the SQLDataReader object appears. This object is optimized for reading data into a web page one time. You can think of this object as a forward only cursor, read only lock type ADO recordset. If you are pulling data into a page simply for display or to reference other data, you will use this object. On the other hand, ADO.NET provides a robust object called SQLDataSet which can be used in countless other scenarios. The DataSet object will be an article (or two!) completely on its own.

13  Dim myDataReader as SQLDataReader
14  myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

To fill the DataReader, we call the "ExecuteReader" method of the command object we created earlier. This method call returns a filled DataReader object. The parameter of this method simply tells the DataReader that when the connection is closed, it, too, is to be closed. This is important because the DataReader object is "connected" in nature, and closing it as soon as it is used will free up valuable resources on the server.

Finally, the DataGrid control we declared in our HTML is data bound by setting its DataSource property equal to the DataReader object we just filled. The DataBind method is then invoked to create the table that results (pictured below).

16  myDataGrid.DataSource = myDataReader
17  myDataGrid.DataBind()				
18
19  myConnection.Close()

Make sure you close your connection object out, and that's it! It is that simple to create a robust, data bound table with ADO.NET. No more looping and writing as we did with ASP, and our content and logic tiers are completely separated.

Sample Screen Capture

Now that you have seen a few objects in action, try them out on your own. I'm going to pick up the pace with my next article and get through several more key ADO.NET objects, so be ready. Until then… Happy Programming!



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM