2 Ways of Displaying Data from a SQL Server Database using ASP.NET
Overview
In this tutorial we will learn what are the two common ways of displaying data from an SQL Server database using ADO.NET in an ASP.NET page. Following are the topics covered in this tutorial :
In this tutorial we will learn what are the two common ways of displaying data from an SQL Server database using ADO.NET in an ASP.NET page. Following are the topics covered in this tutorial :
- How to create a database in SQL Server?
- How to create a table in SQL Server?
- How to insert data into that table?
- How to create an ASP.NET page?
- What are SqlConnection, SqlCommand, SqlDataReader classes?
- How to use above classes to display data from SQL Server database?
- How to display data using ASP 3.0 style SqlDataReader class?
- What are ASP.NET Web Form controls?
- How to display data using ASP.NET Web Form controls?
- What are the advantages/disadvantages of using one way of displaying data over the other?
- Is there any performance difference between these two methods?
You'll be able to see the online demo on the last page and will also be able to download complete source code from there. But I recommend that you follow this tutorial step-by-step and create the pages, tables etc as required instead of waiting for the last page. If you follow this article carefully and pay due attention, I assure you that you will be able to successfully connect and display data from your SQL Server database. Once you complete this article you'll be able to move on to more advanced topics, but completing this tutorial is a must.
I am assuming that this is your first interaction with ASP.NET as far as accessing data from a database is concerned, that's why I'll try to explain everything in detail.
I. Creating the desired database in SQL Server :
A database provides persistence to your data, you tell the database what kind of data you want to store, how large should it be? and who should be able to access it. All the gory details are handled by your database server. For this tutorial's sake we'll be focusing ourselves on an enterprise-class database system from Microsoft, SQL Server 2000.
A database provides persistence to your data, you tell the database what kind of data you want to store, how large should it be? and who should be able to access it. All the gory details are handled by your database server. For this tutorial's sake we'll be focusing ourselves on an enterprise-class database system from Microsoft, SQL Server 2000.
I'll assume that you've installed SQL Server correctly and that it has been started, if not start it now. Let's go to Start -> Programs -> Microsoft SQL Server -> Enterprise Manager. You'll see something like following :
SQL Server - Enterprise Manager
Expand 'Microsoft SQL Servers' node, you'll now see 'SQL Server Group' ( i.e. if you haven't changed anything ). Expanding this group will show your 'yourcomputername' ( substitute 'yourcomputername' with the name of your computer, it is the same as 'localhost' ) node. Expand this node to see something like following ( since name of my computer is 'MAIN', it is showing 'MAIN' ) :
SQL Server - Enterprise Manager
Right-click over 'Databases' node and choose 'New Database'. In the 'Database Properties' windows that appears, enter the name 'Stardeveloper'. All the tables that you create for tutorials on Stardeveloper should be created in this database.
SQL Server - Database Properties Window
After doing that hit 'ok' and now expand the 'Databases' node, you should see your 'Stardeveloper' database there.
II. Creating the desired 'Names' table in 'Stardeveloper' database :
Now expand the 'Stardeveloper' node from the node/s under the 'Databases' node. Right-click over 'Tables' icon and from the options choose 'New Table'.
SQL Server - Creating New Table
In the 'New Table' design windows, add three columns whose names, data types and length is given below :
Creating New Table
Set 'ID' to primary key and identity to 'Yes' ( see above picture ). Now hit the 'save' button and give this table a name of 'Names', yes that's right. This table will hold first and second names of people. Once you've done that, your table 'Names' will be created for you in 'Stardeveloper' database.
III. Populating the 'Names' table
Let's insert some data into our 'Names' table that we just created. Select 'Tables' icon under the 'Stardeveloper' database icon. You should see 'Names' table in the list of tables there. Now right click 'Names' table and from the options choose 'Open Table' and then from the options which pop-up choose 'Return all rows'.
Let's insert some data into our 'Names' table that we just created. Select 'Tables' icon under the 'Stardeveloper' database icon. You should see 'Names' table in the list of tables there. Now right click 'Names' table and from the options choose 'Open Table' and then from the options which pop-up choose 'Return all rows'.
Inserting Data into 'Names' Table
From the windows that opens, insert first and last names for 3-4 times. Remember, don't try to insert any value in the 'ID' column, that column will get a value on it's own, just enter values in 'FirstName' and 'LastName' columns as shown below :
Inserting Data
Now we are done as far as our database is concerned. We have our data, a list of first and last names in a table called 'Names' which is in a database 'Stardeveloper'. We are now all set to display this data, we can display this data using any number of technologies e.g. DBI, PHP, Java Servlets, ASP/ASP.NET. For this tutorial's sake we'll use ASP.NET pages to display this data.
IV. First Way of Displaying Data from the Database ( Classic ASP )
I'll assume here that you've installed .NET Framework from www.asp.net and are using IIS 5.0 on Windows NT/2000/XP/. Now the first way of displaying data is a way most of you will be pretty much used to. This is the way data is displayed in DBI, PHP, Java Servlets and ASP 3.0.
I'll assume here that you've installed .NET Framework from www.asp.net and are using IIS 5.0 on Windows NT/2000/XP/. Now the first way of displaying data is a way most of you will be pretty much used to. This is the way data is displayed in DBI, PHP, Java Servlets and ASP 3.0.
Here you first connect to the database, execute a query which returns results, iterate through records one by one and continue to display those records during the iteration, once all the records have been displayed, close the connection and any allotted resources.
For ASP.NET pages to work you have to create an application under IIS first. For that first create a sub-folder under your %DOCUMENT_ROOT%. Now this %DOCUMENT_ROOT% can be C:\Inetpub\wwwroot under default installation, whatever the %DOCUMENT_ROOT% is on your system, go to that folder and create a sub-folder with name of 'NET' ( note: this can be any name but for tutorial's sake I'll stick with 'NET' as the name of that folder/application ). We have created the folder but haven't yet made it an application.
To make our %DOCUMENT_ROOT%/NET folder an application go to Start -> Settings -> Control Panel -> Administrative Tools -> Internet Services Manager. Note the exact might be different on your system, the path that I've given is on Windows 2000
From the window that appears, keep on expanding the nodes on the left until you see your folder 'NET' under your local web site. Now right-click over 'NET' and from the options choose 'Properties'. Now click the 'Create' button and hit 'OK'.
Making a .NET Application in IIS
Congratulations! you've converted this folder to an application, we are now ready to build our ASP.NET page to demonstrate the first way of showing records from the database.
displayData1.aspx
Create a new page 'displayData1.aspx' in the %DOCUMENT_ROOT%/NET folder. Open it in Notepad ( or Visual Studio ) and copy/paste following text in it :
Create a new page 'displayData1.aspx' in the %DOCUMENT_ROOT%/NET folder. Open it in Notepad ( or Visual Studio ) and copy/paste following text in it :
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="C#" %>
<script runat="server">
protected void DisplayData() {
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader rd = null;
try {
con = new SqlConnection("server=localhost;uid=sa;" +
"pwd=;database=Stardeveloper");
cmd = new SqlCommand("SELECT * FROM Names", con);
con.Open();
rd = cmd.ExecuteReader();
while(rd.Read()) {
Response.Write(rd.GetString(1));
Response.Write(" ");
Response.Write(rd.GetString(2));
Response.Write("<br>");
}
} catch (Exception e) {
Response.Write("<p><font color=\"red\">Err: ");
Response.Write(e.Message);
Response.Write("</font></p>");
} finally {
if(rd != null)
rd.Close();
if(con != null)
con.Close();
}
}
</script>
<html>
<head>
<style>
body { font-family:Verdana; font-size:12pt; }
</style>
</head>
<body>
<% DisplayData(); %>
</body>
</html>
Now hit the 'Save' button to save this page.
Explanation
The complete code for connecting to database, retrieving data and displaying it to the user is contained in the DisplayData() method. As you might have noticed, this code has been written in C#.
The complete code for connecting to database, retrieving data and displaying it to the user is contained in the DisplayData() method. As you might have noticed, this code has been written in C#.
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="C#" %>
First we import the desired namespaces we'll be using in our ASP.NET page. Since we are dealing with SQL Server here, we import System.Data.SqlClient namespace which contain classes especially designed for SQL Server database. After that we set the language for this page to C#.
<script runat="server">
protected void DisplayData() {
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader rd = null;
try {
con = new SqlConnection("server=localhost;uid=sa;" +
"pwd=;database=Stardeveloper");
cmd = new SqlCommand("SELECT * FROM Names", con);
con.Open();
rd = cmd.ExecuteReader();
while(rd.Read()) {
Response.Write(rd.GetString(1));
Response.Write(" ");
Response.Write(rd.GetString(2));
Response.Write("<br>");
}
} catch (Exception e) {
Response.Write("<p><font color=\"red\">Err: ");
Response.Write(e.Message);
Response.Write("</font></p>");
} finally {
if(rd != null)
rd.Close();
if(con != null)
con.Close();
}
}
</script>
Next comes the script block whose 'runat' attribute has been set to 'server', so it will run on the server-side and the client will not be able to see it's code. In this script block we create the DisplayData() method.
The first thing we do in DisplayData() method is to declare 3 variables to hold the connection to our database, the SQL command that we want to execute and the recordset ( list of records ) which is retrieved from the database. Their initial value is set to 'null' ( nothing ).
In a try..catch block we create a new SqlConnection() object. The only argument to it's constructor is a connection string which specifies which database server we want to connect to, user name/password and the database name. You can change the uid/pwd combination to suite your own database server.
Next we create the SqlCommand object, using a constructor with 2 arguments. The first argument is the SQL query we want to run and the next one is the reference to an existing SqlConnection object. Then we call con.Open() to open the connection to the database. Now we are ready to execute the command that we specified in SqlCommand object.
We now have an established connection to the database and an SQL command ready to execute.
rd = cmd.ExecuteReader();
while(rd.Read()) {
Response.Write(rd.GetString(1));
Response.Write(" ");
Response.Write(rd.GetString(2));
Response.Write("<br>");
}
'rd' is SqlDataReader object, it is returned when we execute ExecuteReader() method of SqlCommand object. SqlDataReader object encapsulates a recordset ( our records in the database ) and provides different methods to retrieve records in different fields of the table as well as to move forward in the recordset.
We iterate through the records using rd.Read() method. This method returns true if there are records left in the recordset to be displayed, it also moves the recordset one row forward. In the 'while' loop we retrieve the value of 'FirstName' and 'LastName' fields using rd.GetString(n) method where 'n' is the number of fields from left to write in the SQL query. Since our SQL 'SELECT' query returned all records in the 'Names' table and our 'Names' table had only 3 fields, we can retrieve records using any 'n' value between 0 - 2 ( yes 'n' begins from 0 ). We left the '0th' value as it contained the value of 'ID' field which we are not displaying.
} catch (Exception e) {
Response.Write("<p><font color=\"red\">Err: ");
Response.Write(e.Message);
Response.Write("</font></p>");
}
We catch any exceptions ( exceptional events ) that might have been thrown during connecting to database and retrieving records, and display them to the user.
} finally {
if(rd != null)
rd.Close();
if(con != null)
con.Close();
}
In the 'finally' block we close the SqlDataReader and SqlConnection objects and free the resources.
Now you can run displayData1.aspx page on your local computer by first saving the displayData1.aspx page in %DOCUMENT_ROOT%/NET directory, then start Microsoft SQL Server ( run 'net start mssqlserver' on command prompt ) and IIS if they are not started ( run 'net start w3svc' ).
Open your browser and go to http://localhost/net/displayData1.aspx page. If everything has gone on well, you should see the records displayed to you. Here is how it looks in my browser :
displayData1.aspx
To see the online demo go to the last page of this tutorial.
V. Second Way of Displaying Data
Now let's learn the second and more useful ( ASP.NET like ) way of displaying data from the database in an ASP.NET page.
Now let's learn the second and more useful ( ASP.NET like ) way of displaying data from the database in an ASP.NET page.
Create a new displayData2.aspx page in the %DOCUMENT_ROOT%/NET folder. Copy/paste following text in it and hit the 'Save' button :
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="C#" %>
<script runat="server">
protected void Page_Load(Object sender, EventArgs e) {
SqlConnection con = null;
SqlCommand cmd = null;
try {
con = new SqlConnection("server=localhost;uid=sa;" +
"pwd=;database=Stardeveloper");
cmd = new SqlCommand("SELECT * FROM Names", con);
con.Open();
grid.DataSource = cmd.ExecuteReader();
grid.DataBind();
} catch (Exception err) {
message.Text = "<p><font color=\"red\">Err: " +
err.Message + "</font></p>";
} finally {
if(con != null)
con.Close();
}
}
</script>
<html>
<head>
<style>
body { font-family:Verdana; font-size:12pt; }
</style>
</head>
<body>
<asp:datagrid id="grid" runat="server" />
<asp:label id="message" runat="server" />
</body>
</html>
Explanation
displayData2.aspx page like displayData1.aspx page first imports the desired namespaces. Then it sets the language for this page to C#.
displayData2.aspx page like displayData1.aspx page first imports the desired namespaces. Then it sets the language for this page to C#.
Next is overrides the Page_Load() event method of the System.Web.UI.Page class. Every ASP.NET page extends System.Web.UI.Page class. The Page_Load() method is called by ASP.NET CLR every time the ASP.NET is loaded ( for every request it gets ). In this method we put all the code to connect to the database and retrieve records ( which we had put earlier in DisplayData() method of displayData1.aspx page ).
The code for displayData2.aspx is slightly different from the one in DisplayData() method of displayData1.aspx page. There is no SqlDataReader object in displayData2.aspx. Instead we make use of two ASP.NET Web Form controls; DataGrid and Label. We set the value of grid.DataSource to the returned SqlDataReader object and then bind this data source using grid.DataBind() method.
We use ASP:Label control to display any exception message. Both of these controls are embedded in the HTML for the displayData2.aspx page.
<html>
<head>
<style>
body { font-family:Verdana; font-size:12pt; }
</style>
</head>
<body>
<asp:datagrid id="grid" runat="server" />
<asp:label id="message" runat="server" />
</body>
</html>
Notice the ASP:DataGrid and ASP:Label controls in the HTML above. There values have been set in the Page_Load() method. These controls take control of all the hassle of displaying data in a formatted manner to the user. DataGrid control is a very useful control and we'll learn more about it in separate articles.
This is how displayData2.aspx page looks like in my browser :
displayData2.aspx
VI. Comparison between displayData1.aspx and displayData2.aspx
displayData1.aspx page contained 26 lines in DisplayData() method to display the records while displayData2.aspx page contains only 16 lines. So displayData2.aspx page wins as far as less lines of code is concerned.
displayData1.aspx page contained 26 lines in DisplayData() method to display the records while displayData2.aspx page contains only 16 lines. So displayData2.aspx page wins as far as less lines of code is concerned.
displayData1.aspx makes use of ASP 3.0 style to display records while iterating through them which is an old style. The displayData2.aspx page on the other hand conforms to the ASP.NET way of displaying data using ASP.NET Web Form controls.
I also ran a performance test using a single client to see how both of these pages perform. Following are the results :
Performance Comparison
Response time for displayData1.aspx is '5.38 ms' and that for displayData2.aspx is '6.45 ms'. Execution time for displayData1.aspx is '0.05 ms' and that for displayData2.aspx is '0.10 ms'. So displayData2.aspx lags behind as far as raw performance is concerned, this is probably due to DataGrid control which contains lots of features and thus is little heavy on usage. Even after this performance lose I'll still go for displayData2.aspx as it's code is more readable and maintainable.
VII. Summary
In this step-by-step tutorial we learned the two ways of displaying data from an SQL Server database using ASP.NET. After reading this tutorial my suggestion is that you download full .NET Framework SDK ( 131 MB+ ) from www.asp.net if you haven't already got it on your system. It not only contains ASP.NET CLR but also much important documentation which you'll need a lot from here onwards.
In this step-by-step tutorial we learned the two ways of displaying data from an SQL Server database using ASP.NET. After reading this tutorial my suggestion is that you download full .NET Framework SDK ( 131 MB+ ) from www.asp.net if you haven't already got it on your system. It not only contains ASP.NET CLR but also much important documentation which you'll need a lot from here onwards.
After that practice a lot by creating new tables in your SQL Server database and use different methods of SqlDataReader class to display them. Remember that you use SqlDataReader.GetString(n) method only if the field you are accessing is a varchar or nvarchar field. If the field is tinyint use GetByte() method, if field is smallint use GetInt16() method and if it is int then use GetInt32() method.
No comments:
Post a Comment