ASP.net: November 2010

Saturday, November 20, 2010

Inserting Data Using the Data Source Controls

The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities. In a nutshell, all three have an Insert method that, when invoked, kicks off the following inserting workflow:

The data source's Inserting event is fired
The inserting "action" occurs
The data source's Inserted event is fired

The inserting "action" differs between the data source controls. For the AccessDataSource and SqlDataSource, the action involves connecting to the specified database and executing the INSERT statement specified by the control's InsertCommand property. For the ObjectDataSource, the inserting "action" involves creating an instance of the data source's underlying object and invoking the specified InsertMethod. This article focuses on the SqlDataSource control; for a detailed look at inserting with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my Working with Data in ASP.NET 2.0 tutorial series.

Let's explore the inserting "action" for the AccessDataSource and SqlDataSource controls in more detail. The INSERT statement or stored procedure specified by the control's InsertCommand uses a parameterized query. That is, if you use an ad-hoc SQL statement for the InsertCommand, that INSERT statement will use parameters like so:

INSERT INTO TableName (ColumnName1, ColumnName2, ..., ColumnNameN)
VALUES (@Parameter1, @Parameter2, ..., @ParameterN)

In the Filtering Database Data with Parameters article, we looked at using parameters in the SelectCommand to filter the results, such as SELECT * FROM Products WHERE Price < @UnitPrice. The parameter - @UnitPrice, in this instance - has its value specified via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user to enter a price bound in a TextBox; from the querystring; from session state; and so on.

Likewise, the parameter values in the INSERT statement are assigned based on the parameters in the data source control's InsertParameters collection, and these parameters can use the same sources as the SelectParameters.

The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform their data access. That is, they connect to the database using a SqlConnection or OleDbConnection object and specify the command text and parameters via a SqlCommand or OleDbCommand object.

Given this information, the inserting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:

1. The data source's Inserting event is fired

2. SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created

3. The command object's CommandText property is assigned the data source control's InsertCommand property

4. The parameters in the data source control's InsertParameters collection are added the command object's Parameters collection

5. A connection to the database is established and the command is executed, thereby inserting the data

6. The data source's Inserted event is fired

The remainder of this article examines three inserting scenarios using the SqlDataSource control: inserting data through a manually-created Web form; inserting data using the DetailsView control; and retrieving the value of the just-inserted record's IDENTITY column. The full code for these demos is available in the download at the end of this article.

Inserting Data Using a Manually-Created Web Form

The demos available at the end of this article illustrate different techniques for inserting data into the Products table of the Northwind database. The Products table contains a number of columns. Each product record is uniquely identified by its ProductID, which is an AutoNumber/IDENTITY column. When inserting a record into this table, the only two columns that are required are ProductName andDiscontinued; all other columns can accept a value of NULL.

Imagine that we were tasked with creating a page that allowed users to add new records to the Products table by specifying the new item's name, category, price, and discontinued status. We could create a simple Web Form that included TextBoxes, a DropDownList, and a CheckBox control to collect these input fields, along with an "Add Product" Button control that, when clicked, would insert the new product into the database.

In addition to these user input controls we could also add a SqlDataSource control to handle the actual insertion. We could set this control's InsertCommand to the following INSERT statement:

INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

Note the use of the parameters following the VALUES keyword. These sources for these parameters' values would simply be the user input Web controls on the page. Map these parameters' values in the InsertParameters collection using ControlParameters that point to the appropriate Web controls on that page. There are a variety of ways to accomplish this. From the Designer, click on the SqlDataSource and go to the Properties window. There you will see an InsertQuery option that, if clicked, displays the Command and Parameter Editor shown below. Here you can specify the InsertCommand, the parameters, and their source. Note that each of the four parameters uses a Control as its Parameter source, with the ControlID drop-down list set to the appropriate Web control on the page.

Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."

      InsertCommand="INSERT   INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES   (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"

      ProviderName="...">
   <InsertParameters>
      <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />
      <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />
      <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />
      <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />
   </InsertParameters>
</asp:SqlDataSource>

Once the Web controls have been added to the page and the SqlDataSource's InsertCommand and InsertParameters properties have been correctly configured, inserting a new record is as simple as calling the data source control's Insert() method. That is, the only code you need to write is the following line of code (which would be placed in the "Add Product" Button's Click event handler):

Protected Sub btnAddProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddProduct.Click
'Add the new product!
AddProductDataSource.Insert()
End Sub

Inserting Data Using a DetailsView Control

A number of new data Web controls were introduced with ASP.NET 2.0. These include the GridView, DetailsView, and FormView, among others. The DetailsView and FormView controls display information about one record at a time (unlike the GridView, which displays information about a set of records). The DetailsView and FormView controls can also be configured to display an inserting interface. In short, you can use the DetailsView or FormView controls to create an interface for inserting data into the database without having to write a line of code!

The DetailsView vs. the FormView Control

The DetailsView and FormView controls have a lot in common - they both display one record at a time and can display interfaces for inserting and updating data. The difference between the two is that the DetailsView control is composed of DataFields (BoundFields, CheckBoxFields, TemplateFields, and so on), just like the GridView. This results in a very boxy appearance. The FormView, on the other hand, uses templates instead of DataFields; consequently, it allows for a much more flexible layout of its display, inserting, and updating interfaces.

Start by adding a SqlDataSource control to the page and use the same InsertCommand as in the previous example:

INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

Next, add the parameters to the control's InsertParameters collection. Rather than using ControlParameters, use the defaultParameter object. Also, the DetailsView we will create for this demo will not include an interface for the user to specify the category. Therefore, set the corresponding Parameter object's DefaultValue to "1". This will assign every product added through this page to the Beverages category.

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."

      InsertCommand="INSERT   INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES   (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"

      ProviderName="...">
   <InsertParameters>
      <asp:Parameter Name="ProductName" Type="String" />
      <asp:Parameter Name="CategoryID" DefaultValue="1" />
      <asp:Parameter Name="UnitPrice" Type="Decimal" />
      <asp:Parameter Name="Discontinued" Type="Boolean" />
   </InsertParameters>
</asp:SqlDataSource>

Next, add a DetailsView to the page and set its DataSourceID property to the ID of the SqlDataSource control (AddProductDataSource). From the DetailsView's smart tag, check the "Enable Inserting" checkbox. This adds a CommandField to the DetailsView and sets itsShowInsertButton property to True. A CommandField with this configuration displays a "New" Button when the DetailsView control is in its ReadOnly mode. When the "New" Button is clicked, a postback ensues and the DetailsView is shifted into its Insert mode, causing the CommandField to display "Insert" and "Cancel" Buttons.

The SqlDataSource control does not contain a value for its SelectCommand, so no data will be displayed in the DetailsView. In fact, for this example we want the DetailsView to always be in an insertable mode (that is, we don't want to make the user have to click "New" to start adding a new record). Set the DetailsView's DefaultMode property to Insert to indicate that the DetailsView control should display just its inserting interface.

Next, add two BoundFields and a CheckBoxField to the DetailsView, setting the HeaderText and DataField properties so that they are bound to the ProductName, UnitPrice, and Discontinued columns used by the SqlDataSource. Finally, set the AutoGenerateRowsproperty to False.

You can accomplish these two tasks from the Fields dialog box or by manually entering the control's declarative markup. To use the Fields dialog box, click the Edit Fields link from the DetailsView's smart tag. Add the two BoundFields and CheckBoxField and set their properties from the list on the right. To set the AutoGenerateRows property to False, simply uncheck the "Auto-generate fields" checkbox in the lower left corner.

Alternatively, you can specify the DetailsView's fields and set the AutoGenerateRows property to False through the declarative syntax:

<asp:DetailsView ID="DetailsView1" runat="server"

AutoGenerateRows="False" DataSourceID="AddProductDataSource"

      DefaultMode="Insert" CellPadding="4" ForeColor="#333333" GridLines="None">
   <Fields>
      <asp:BoundField DataField="ProductName" HeaderText="Product Name:" />
      <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price:" />
      <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued:" />
      <asp:CommandField ShowInsertButton="True" />
   </Fields>
</asp:DetailsView>

That's all there is to it! When a user visits this page and enters the name, price, and discontinued status of a product and clicks Insert, a postback occurs. The DetailsView automatically assigns the values assigned to its input controls to the SqlDataSource's corresponding InsertParameters before starting the inserting workflow. The net result is that a new record is inserted into the database without writing a lick of code and without having to manually map the SqlDataSource's InsertParameters to their sources (since the DetailsView does this for us automatically when Insert is clicked).

This example illustrates only the simplest facilities of the DetailsView and omits important steps in a real-world application, such as input validation and customizing the inserting interface. For example, since the ProductName column is required, the insert will fail if the user leaves this textbox empty. Likewise, if the user puts in an invalid unit price value (like "expensive!"), an error will occur when attempting to insert the illegal value into the database. The download at the end of this article includes another DetailsView example that illusrated adding validation controls and customizing the inserting interface to prompt the user for the new product's category via a drop-down list of available category choices. For more information on customizing the DetailsView's inserting interface, seeCustomizing the Data Modification Interface (VB Version) (C# version).

Inserting and Returning the Value of the Just-Inserted Record's IDENTITY Column

Most database tables provide some mechanism to uniquely identify each record. There are a variety of approaches, but a very common one is the use of an IDENTITY column, which is also referred to as an AutoNumber. An IDENTITY column is one whose value is automatically assigned by the database system when a new record is added to the table. These values start at some seed (usually 1) and increment by some specified amount with each new record (usually incremented by 1). Therefore, if you add three new records to the table, the IDENTITY column values for those first three records will be 1, 2, and 3, respectively.

When using IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted record's IDENTITY column. Perhaps after inserting a new record you want to automatically whisk the user to a details page where you need to pass along theIDENTITY column value through the querystring, or maybe you want to add additional records in a child table and need the just-inserted parent record's IDENTITY column value to properly link the child records to the parent. In either case, in Microsoft SQL Server you can use the SCOPE_IDENTITY() keyword to get the IDENTITY column value of the just-insert record.

In order to pull back this information when using the SqlDataSource we need to do the following:

1. Create a stored procedure that returns the just-inserted record's IDENTITY column value using an OUTPUT parameter. SeeRetrieving Scalar Data from a Stored Procedure for more information on this topic.

2. Configure the SqlDataSource to use this stored procedure. This involves updating the InsertCommand to the name of the stored procedure created from step 1, setting the data source control's InsertCommandType to StoredProcedure, and adding an output parameter to the InsertParameters collection.

3. To access the resulting output parameter's value we need to create an event handler for the SqlDataSource's Inserted event. Recall that this event fires after the insert "action" has been performed. Once we have the IDENTITY value of the just-inserted record we can use it as needed.

In the database included in the download at the end of this article you will find a stored procedure namedAddProductAndReturnNewProductIDValue that accepts four input parameters and has an OUTPUT parameter (@NewProductID). As the following T-SQL syntax shows, this stored procedure inserts a new record into Products and then assigns the value returned bySCOPE_IDENTITY() to @NewProductID:

ALTER PROCEDURE dbo.AddProductAndReturnNewProductIDValue (
    @ProductName nvarchar(40),
    @CategoryID int,
    @UnitPrice money,
    @Discontinued bit,

@NewProductID int OUTPUT

)
AS

-- Insert the record into the database
INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

-- Read the just-inserted ProductID into @NewProductID
SET @NewProductID = SCOPE_IDENTITY()

Next, update the SqlDataSource to use the AddProductAndReturnNewProductIDValue as its InsertCommand instead of an ad-hoc SQL statement. Also, add an ouput parameter to the InsertParameters collection. Note that the output parameter in the InsertParameters collection is a Parameter object whose Direction property is set to Output:

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."
      InsertCommand="AddProductAndReturnNewProductIDValue"
      ProviderName="..." InsertCommandType="StoredProcedure">
   <InsertParameters>
      <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />
      <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />
      <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />
      <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />

<asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />

</InsertParameters>
</asp:SqlDataSource>

Adding an output parameter to the SqlDataSource's InsertParameters collection adds an output parameter to the Parameterscollection of the internal SqlCommand object used by the data source control during the inserting "action." The value of this parameter can be examined in the Inserted event handler. As the following event handler code shows, the internal SqlCommand object is accessible through the e.Command property in the event handler. Here we can grab the specific parameter instance and insepct itsValue property to determine the IDENTITY column value of the just-inserted record:

Protected Sub   AddProductDataSource_Inserted(ByVal sender As Object, ByVal e As   System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles   AddProductDataSource.Inserted

   'Read the value of the @NewProductID OUTPUT parameter
   Dim newProductID As Integer = Convert.ToInt32(e.Command.Parameters("@NewProductID").Value)

   'Display confirmation message

   NewProductAddedMsg.Text   = String.Format("Product {0} has been added to the database... This new   product's ProductID value is {1}...", ProductName.Text, newProductID)

End Sub

Conclusion

In this article we looked at how to use the SqlDataSource to insert data into a database. We worked through three examples: inserting using a manually-created Web Form; inserting from a DetailsView control; and retrieving the IDENTITY column value of the just-inserted record. In all three cases, the SqlDataSource control encapsulates many of the data access tasks, such as connecting to the database, creating the command object, and executing the parameterized query. In future installments of this article series we will take a similar in-depth look at updating and deleting.

Displaying Data from a SQL Server Database using ASP.NET

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 :

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.

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'.

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.

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 :

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

<%@ Page Language="C#" %>

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#.

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 :

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

<%@ Page Language="C#" %>

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#.

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 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.

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.