Export (0) Print
Expand All

Walkthrough: Accessing and Displaying Data with JScript .NET in ASP.NET

Visual Studio .NET 2003
 

Jason Cooke
Visual Studio Team
Microsoft Corporation

January 2002

Summary: This article illustrates how to use JScript .NET to write an ASP.NET page that accesses and displays information from a database. Techniques for accessing the data in an XML dataset are also illustrated. (9 printed pages)

Requires

  • Some experience with ASP.NET, JScript .NET, and database querying.
  • Permission to read the SQL or Access Northwind databases.
  • Permission to create and modify files on a Web server that has the .NET Framework installed.

Contents

Introduction
Setting up a Generic Page
Creating and Displaying a DataTable
Accessing and Displaying Data from the Northwind Database
Accessing and Displaying Data from an XML Dataset
Personalizing the Table
Conclusion

Introduction

ASP.NET provides many new server-side controls that make it easy to program rich ASP.NET applications. In this walkthrough, which features the DataGrid control, you first create a page that uses the DataGrid control. You then bind the control to a table that you create, to the results of a database query, and finally to an XML dataset.

Security is an important factor whenever an ASP.NET page accesses data on the server. The examples in this walkthrough implement some basic features to protect your information.

For more information about data access with ASP.NET pages, see .NET Data Access Architecture Guide at MSDN Online and the ASP.NET QuickStart Tutorials at GotDotNet (http://www.gotdotnet.com/QuickStart/aspplus/default.aspx?url=/quickstart/aspplus/doc/webdataaccess.aspx).

Setting up a Generic Page

The simplest way to display a table of data in an ASP.NET page is to bind the data to a DataGrid control, which has properties that control the presentation of data. To bind data to a DataGrid control, the data must be stored in an object that implements the System.Collections.IEnumerable interface, such as System.Collections.ArrayList and System.Data.DataView. For more information, see DataGrid Class.

In this section, you create a simple page that uses the DataGrid control. This page will serve as the basis for the pages in subsequent sections.

To create a simple ASP.NET page with a DataGrid control

  1. In your Web directory, located at C:\Inetpub\wwwroot by default, create a new directory named DataAccess.
  2. Create a new ASP.NET file named DataAccess.aspx in the C:\Inetpub\wwwroot\DataAccess directory.

    You can do this with an application such as Notepad.

    Note   You cannot use the Visual Studio .NET integrated development environment (IDE) to create a JScript .NET project. However, you can edit ASP.NET files in the IDE.
  3. Open the ASP.NET page for editing in either the Visual Studio .NET IDE or Notepad.
    Note   You cannot directly paste code copied from a richly formatted source (such as a Web page) into an ASP.NET page that you are editing in the Visual Studio .NET IDE. The IDE converts richly formatted text into HTML code that reproduces the look of the original text instead of pasting as unformatted text. You can use a two-step process to work around this restriction. First, paste a code example into a text-based editor (such as Notepad) to remove the rich formatting. Second, cut the code from the text-based editor and paste it into the IDE.
  4. Add the following code to the page:
    <%@ Page Language=jscript %>
    <html>
    <body>
    <script runat=server>
    function Page_Load(sender : Object, e : EventArgs) {
       // Put code in this function that you want to have loaded 
       // each time the page is requested from the server.
       if (!IsPostBack) {
          // Put code here that you want to run only the first time.
       }
    } // Page_Load
    </script>
    
    <h3>Category List</h3>
    <form runat=server>
       <asp:DataGrid id="ItemsGrid" runat="server">
       </asp:DataGrid>
    </form>
    
    </body>
    </html>
    
  5. Save the page.
  6. View the page by typing the following address into the address bar of Internet Explorer:

    http://localhost/DataAccess/DataAccess.aspx

When you access the page, it only displays the "Category List" header. The DataGrid control does not display data because no data has been bound to it.

Creating and Displaying a DataTable

Before you attempt to load data from a database, ensure that the DataGrid control works properly by using data from a well-controlled data source. In this section, you construct an ArrayList, and then you bind the ArrayList to the DataGrid control.

To bind data to a DataGrid control

  1. Add the following function to the script block.
    function CreateTestData() : ICollection {
       // Function to create a simple table.
       var al : ArrayList = new ArrayList();
       al.Add("orange");
       al.Add("pear");
       al.Add("banana");
       return al;
    } // CreateTestData
    
  2. Bind the data in the table to the DataGrid control by adding the following lines to the if (!IsPostBack) block of the Page_Load function:
    ItemsGrid.DataSource = CreateTestData();
    ItemsGrid.DataBind();
    
  3. Save the page.
  4. Reload the page in Internet Explorer, or type the following address in the address bar of Internet Explorer:

    http://localhost/DataAccess/DataAccess.aspx

The page now displays a simple table of several fruits.

Accessing and Displaying Data from the Northwind Database

You can use the OleDbConnection, OleDbCommand, and OleDbDataAdapter classes to manage database connections. These classes are part of the System.Data.OleDb namespace, one of the constituents of ADO.NET.

In this section, you use these classes to open a connection to a database, send a query, and populate a DataSet with the resulting data. You construct a DataView with the data from the DataSet, and then you bind that DataView to the DataGrid.

You also implement a few layers of security. This involves ensuring that the application has the correct configuration and that sensitive information, such as the connection string, is not stored in the application.

To create a configuration file

  1. Create a new file named web.config in the C:\Inetpub\wwwroot\DataAccess directory.

    This file holds configuration information for the application.

  2. Choose an appropriate connection string for your database.

    A typical connection string for the SQL Northwind database is:

    "server=YourServerName; provider=SQLOLEDB; database=Northwind;
       uid=YourUserID; pwd=YourStrongPassword"
    

    You must replace the placeholders YourServerName, YourUserID, and YourStrongPassword with the appropriate values for your database.

    Note   Make sure that you provide an appropriate user, not SA, with a strong password.

    A typical connection string for the Access Northwind database is:

    "provider=Microsoft.Jet.OLEDB.4.0; data source=YourPath\Northwind.MDB"
    

    You must replace the placeholder YourPath with the path to your Access Northwind database. By default, this path is C:\Program Files\Microsoft Office\Office\Samples.

    Note   The default installation of Access does not install the sample Access databases. You may need to run Office Setup to install Northwind.
  3. Add the following code to the configuration file.

    This setup helps protect potentially sensitive information by not displaying verbose errors to remote machines and by separating the connection string from the Web application. Be sure to replace the placeholder YourConnectionString with the connection string you chose in the previous step.

    <!-- Web.Config Configuration File -->
    <configuration>
       <system.web>
    <!-- Throw a generic error for remote requests. -->
          <customErrors mode="RemoteOnly" />
       </system.web>
       <appSettings>
    <!-- Separate the connection string from the application. -->
          <add key="DBConnStr" value=YourConnectionString />
       </appSettings>
    </configuration>
    
  4. Save web.config.

You now modify the DataAccess.aspx file to read data from the Northwind database that you specified in the web.config file.

To access a database

  1. In the DataAccess.aspx file, add the following directives to the line immediately following the <%@ Page %> directive:
    <%@ Import Namespace = "System.Data.OleDb" %>
    <%@ Import Namespace = "System.Data" %>
    

    The first directive allows the application to use the OleDbConnection, OleDbCommand, and OleDbDataAdapter classes, and the second directive allows the application to use the DataSet, DataTable, and DataView classes.

  2. Add the GetDBData function to the script block.
    function GetDBData() : ICollection {
       // Read a database table and return the DataView for that table.
       var ds : DataSet = new DataSet();
       try {
          // Use a property to access the connection string.
          var dbConn: OleDbConnection = new OleDbConnection(ConnectionString);
          var queryStr : System.String =
             "SELECT CategoryID, CategoryName, Description FROM Categories";
          var dbc : OleDbCommand = new OleDbCommand(queryStr, dbConn);
          var da : OleDbDataAdapter = new OleDbDataAdapter(dbc);
          da.Fill(ds);
       } finally {
          // Shut down the connection even on failure.
          if (dbc != null) dbc.Connection.Close();
       }
       // Select one table from the DataSet to populate a DataView.
       return new DataView(ds.Tables[0]);
    } // GetDBData
    
  3. Add the ConnectionString property to the script block:
    internal function get ConnectionString() : System.String {
    // Get connection string from configuration file.
       return ConfigurationSettings.AppSettings("DBConnStr");
    } // ConnectionString
    
  4. In the Page_Load function where a DataView is bound to the DataSource property, replace the call to the CreateTestData function with a call to the GetDBData function.
  5. Save DataAccess.aspx.
  6. Reload the ASP.NET page in Internet Explorer, or type the following address in the address bar of Internet Explorer:

    http://localhost/DataAccess/DataAccess.aspx

The page now displays the CategoryID, CategoryName, and Description columns of the Northwind database in a DataGrid control.

Accessing and Displaying Data from an XML Dataset

The process that accesses XML data is almost the same as the process that accesses database data. However, to access XML data, you use the FileStream, FileMode, FileAccess, and StreamReader classes. These classes are located in the System.IO namespace, yet another facet of ADO.NET.

You first create a sample XML dataset for the ASP.NET page to read.

To create an XML dataset

  1. Create a new XML file named schemadata.xml in the C:\Inetpub\wwwroot\DataAccess directory.
  2. Add the following XML data to the file:
    <NewDataSet>
     <xs:schema id="NewDataSet" xmlns=""
      xmlns:xs="http://www.w3.org/2001/XMLSchema" 
      xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
       <xs:element name="NewDataSet" msdata:IsDataSet="true">
         <xs:complexType>
           <xs:choice maxOccurs="unbounded">
             <xs:element name="Table">
               <xs:complexType>
                 <xs:sequence>
                  <xs:element name="ID" type="xs:string" minOccurs="0" />
                  <xs:element name="Name" type="xs:string" minOccurs="0" />
                 </xs:sequence>
               </xs:complexType>
             </xs:element>
           </xs:choice>
         </xs:complexType>
       </xs:element>
     </xs:schema>
     <Table> <ID>6</ID> <Name>Ford</Name> </Table>
     <Table> <ID>7</ID> <Name>Zaphod</Name> </Table>
     <Table> <ID>42</ID> <Name>Phil</Name> </Table>
    </NewDataSet>
    
    Note   The Extensible Markup Language (XML) is case-sensitive. As you add text to the schemadata.xml file, maintain the same case in the elements as included in the sample.
  3. Save schemadata.xml.

You now modify the DataAccess.aspx file to read data from the XML data stored in schemadata.xml.

To read XML data from a file

  1. In the DataAccess.aspx file, add the following directive to the line immediately following the <%@ Page %> directive:
    <%@ Import Namespace= "System.IO" %>
    <%@ Import Namespace= "System.Security.Permissions" %>
    

    This directive allows the application to use the FileStream, FileMode, FileAccess, and StreamReader classes.

  2. Add the GetXMLData function to the script block.
    function GetXMLData() : ICollection {
       // Read an XML dataset and return the DataView for that data.
       var ds : DataSet = new DataSet();
       var sPath : String = Server.MapPath("SchemaData.xml");
       var fp : FileIOPermission = 
          new FileIOPermission(FileIOPermissionAccess.Read, sPath);
       try {
          var fs : FileStream = 
                new FileStream(sPath, FileMode.Open, 
                               FileAccess.Read, FileShare.Read);
          var reader : StreamReader = new StreamReader(fs);
          ds.ReadXml(reader);
       } finally {
          // Shut down the connection even on failure.
          if (fs != null) fs.Close();
       }
       // Select one table from the DataSet to populate a DataView.
       return new DataView(ds.Tables[0]);
    } // GetXMLData
    

    Note that much of the code for accessing XML data is the same as in the GetDBData function. The differences are bolded.

  3. In the Page_Load function where a DataView is bound to the DataSource property, replace the call to the GetDBData function with a call to the GetXMLData function.
  4. Save DataAccess.aspx.
  5. Reload the ASP.NET page in Internet Explorer, or type the following address in the address bar of Internet Explorer:

    http://localhost/DataAccess/DataAccess.aspx

The page now displays a table with the ID and Name columns of the XML dataset.

Personalizing the Table

Although the default properties of the DataGrid control produce a simple looking table, you can modify the settings to change the appearance of the table. You can further change the behavior by adding other tags within the opening and closing tags of the DataGrid control. For more information, see DataGrid Web Server Control.

In this section, you change several property values of the control that influence the font and layout of the table. You also add tags to change the style of the header and the alternating rows of the table.

To modify the default DataGrid settings

  1. Change the opening <asp:DataGrid> tag to:
    <asp:DataGrid id="ItemsGrid" runat="server"
          BorderColor="black"
          BorderWidth="1"
          GridLines="Both"
          CellPadding="3"
          CellSpacing="0"
          Font-Name="Verdana"
          Font-Size="8pt">
    
  2. Add the following tags between the opening and closing <asp:DataGrid> tags:
    <HeaderStyle BackColor="#aaaadd" />
    <AlternatingItemStyle BackColor="#eeeeee" />
    
  3. Save DataAccess.aspx.
  4. Reload the ASP.NET page in Internet Explorer, or type the following address in the address bar of Internet Explorer:
    http://localhost/DataAccess/DataAccess.aspx
    

The page now displays the same data in a table that looks different.

Conclusion

This article has demonstrated how to display tabular data using the DataGrid control. You learned how to programmatically create a DataTable from within in an ASP.NET page. You also learned how to read data from a database and an XML dataset.

In particular, follow these recommendations:

  • Use the <asp:DataGrid> control to display tabular data.
  • Use the DataTable, DataColumn, DataRow, and DataView classes from ADO.NET to create data tables and views.
  • Use the OleDbConnection, OleDbCommand, and OleDbDataAdapter classes from ADO.NET to access database data.
  • Use the FileStream and StreamReader classes from ADO.NET to read XML data.

For more information about security in ASP.NET and in the .NET Framework, look for articles in MSDN Online library within the Web Development and .NET Development sections.

Show:
© 2014 Microsoft