|
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 :
<%@ 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#.
<%@ 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.
Create a new displayData2.aspx page in the %DOCUMENT_ROOT%/NET folder. Copy/paste following text in it and hit the 'Save' button : 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 :
<%@ 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#.
<%@ 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.
Create a new displayData2.aspx page in the %DOCUMENT_ROOT%/NET folder. Copy/paste following text in it and hit the 'Save' button : | 
.NET
ASP (16) 
Contains 1 or more replies by the Author of this Article.
Contains 1 or more replies by 
