HOW TO: Create and Use a Typed DataSet by Using Visual C# .NET (320714)

SUMMARY

This article shows you how to create and use a typed DataSet in a small Web application. Typed DataSets, which inherit from the DataSet class, create first-class members of a DataSet's DataTables and DataColumns, yielding the many benefits that come with the use of strong typing.

back to the top

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you have to have to complete these steps:

• Microsoft SQL Server 6.5 (or later) with the Northwind database

Prior skills required:

• General familiarity with Microsoft SQL Server
• General familiarity with Microsoft Visual Studio .NET
• General familiarity with Microsoft ADO.NET
• General familiarity with Microsoft .NET Web Controls

back to the top

Typed DataSets

Follow these steps to create a small Web application by using Visual Studio .NET. The Web application uses a typed DataSet to display the results of an improvised SQL query in the Northwind database.

1. Start Visual Studio .NET.
2. Create a new Web Application project named TDS in Visual C# .NET.
3. Make sure that the Solution Explorer is displayed. If the Solution Explorer is not displayed, press CTRL+ALT+L.
4. Make sure that the WebForm1.aspx file is open in the Editor window. If the file is not open, double-click WebForm1.aspx in the Solution Explorer to open the file.
5. Under the Editor window, click Design to switch to Design view.
6. To open the toolbox, press CTRL+ALT+X. In the toolbox, click Web Forms. Select and drag the following to the upper-left corner of the page: two rows each of a label followed by a text box (positioned to the right of each label). Under these, add a DataGrid in the same way.
7. Click the top label. Press F4 to display the Properties window. Change the Text property to Product. Click the other label, and then change its Text property to Category.
8. To add a new DataSet to the project, press CTRL+SHIFT+A, and then click DataSet in the list of templates. Name the DataSet the following: dsProducts.xsd. Note that the file is actually an XML Schema. Click OK. You now see a pale yellow page in the Editor window.
9. To create a typed DataSet, press CTRL+ALT+S to open the Server Explorer.
10. Click Servers, click computer name, click SQLServers, click server name, click Northwind, and then click Views.
11. Select the Northwind SQL Server view Alphabetical list of products, and then drag the view to the pale yellow DataSet page. A visual representation of the results set that is generated by the view appears on the page. To see the actual XML for the Schema file, click the XML button under the Editor window.
12. A Schema based on a SQL Server object now exists. To create and fill a typed DataSet, first you must generate the class that maps to the Schema. Right-click the Design view, and then make sure that the Generate DataSet option is selected. If it is not selected, select it. Press CTRL+S to save the Schema and generate the class.
13. To view the new typed DataSet class, click Show All Files in the Solution Explorer.
14. Expand the tree next to dsProducts.xsd. You see a dsProducts.vb file, which contains the new class mapping to the Schema. You also see a dsProducts.xsx file, which is used for tracking changes to the files.
15. To write code to display the typed DataSet, double-click directly on the Web Form (not on a Web Control). The Web Form's codebehind appears, and the insertion point is inside the Page_Load event.
16. To include the classes that reside in the System.Data.SqlClient namespace, add the following using statement at the top of the codebehind:

    using System.Data.SqlClient;
    					

    NOTE: The other namespaces that you have to have for this small Web application are automatically referenced in the project when you create a Web application in Visual Studio .NET.
    
    
17. In the Page_Load event procedure, create a Connection object by passing the connection string to the default constructor of the SqlConnection class:

    SqlConnection cn = new SqlConnection("server=myserver;uid=myuid;pwd=mypassword;database=northwind");
    					

18. Create a SqlCommand object that is then passed to the SqlDataAdapter object. Pass an improvised SQL statement and the new Connection object to the SqlCommand constructor. The former sets the CommandText property of the new SqlCommand object. You can also pass the name of a stored procedure.

    SqlCommand cmd = new SqlCommand("select * from [Alphabetical list of products]", cn);
    					

19. Create an instance of the SqlDataAdapter object, passing the new SqlCommand object to the constructor:

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    					

20. Now you create the objects that are required to connect to the database and return data. The following is the code for the typed DataSet. Note that an instance of the dsProducts class is created: the class that maps to the dsProducts Schema and inherits from the DataSet class, not the generic DataSet class itself.

    dsProducts tds = new dsProducts();
    					

21. Call the Fill method of the SqlDataAdapter, passing in the typed DataSet object and the DataSet's typed DataTable TableName property:

    da.Fill(tds, tds.Tables[0].TableName);
    					

22. To set the Text property of the text box controls to the strongly typed columns in the typed DataSet's DataTable, use the following format:

    dsProducts.DataTableName[RowIndex].ColumnName
    						

    For this sample application, the RowIndex is hard-coded to 5:

    TextBox1.Text = tds.Alphabetical_list_of_products[5].ProductName;
    TextBox2.Text = tds.Alphabetical_list_of_products[5].CategoryName; 
    						

    Because the Rows collection is zero-based, when the page loads, note that the text box controls display the product and category names of the item in the sixth row of the DataGrid.
23. To display all of the results in the DataGrid, set the DataSource property of the DataGrid to the new typed DataSet, and call DataBind():

    DataGrid1.DataSource = tds;
    DataGrid1.DataBind();
    					

back to the top

Complete Code Listing (WebForm1.aspx)

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="TDS.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
	<title>WebForm1</title>
	<meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
	<meta name="CODE_LANGUAGE" Content="C#">
	<meta name="vs_defaultClientScript" content="JavaScript">
	<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
	<form id="Form1" method="post" runat="server">
		<asp:Label id="Label1"  runat="server" >Label</asp:Label>
		<asp:DataGrid id="DataGrid1"  runat="server" ></asp:DataGrid>
		<asp:TextBox id="TextBox2"  runat="server"></asp:TextBox>
		<asp:TextBox id="TextBox1"  runat="server"></asp:TextBox>
		<asp:Label id="Label2"  runat="server" >Label</asp:Label>
	</form>
</body>
</HTML>
				

back to the top

Complete Code Listing (WebForm1.aspx.cs)

using System.Data.SqlClient;

namespace TDS
{
  /// <summary>
  /// Summary description for WebForm1.
  /// </summary>
  public class WebForm1 : System.Web.UI.Page
  {
    protected System.Web.UI.WebControls.Label Label1;
    protected System.Web.UI.WebControls.Label Label2;
    protected System.Web.UI.WebControls.TextBox TextBox1;
    protected System.Web.UI.WebControls.TextBox TextBox2;
    protected System.Web.UI.WebControls.DataGrid DataGrid1;
	
    private void Page_Load(object sender, System.EventArgs e) 
    {
	SqlConnection cn = new SqlConnection("server=localhost;uid=sa;pwd=abcd;database=northwind");
	SqlCommand cmd = new SqlCommand("select * from [Alphabetical list of products]", cn);
	SqlDataAdapter da = new SqlDataAdapter(cmd);
	dsProducts tds = new dsProducts();

	da.Fill(tds, tds.Tables[0].TableName);
	TextBox1.Text = tds.Alphabetical_list_of_products[5].ProductName;
	TextBox2.Text = tds.Alphabetical_list_of_products[5].CategoryName; 

         DataGrid1.DataSource = tds;
	DataGrid1.DataBind();

    }

    #region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
	// 
	// CODEGEN: This call is required by the ASP.NET Web Form Designer.
	// 
	InitializeComponent();
	base.OnInit(e);
    }
		
    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {    
	this.TextBox1.TextChanged += new System.EventHandler(this.TextBox1_TextChanged);
	this.Load += new System.EventHandler(this.Page_Load);

    }
    #endregion
  }
}

				

back to the top

Verification

Press F5 to run the Web application. The browser displays "Uncle Bob's Organic Dried Pears" in the Product text box and "Produce" in the Category text box. A DataGrid that is filled with product information appears under the text boxes.

back to the top

Troubleshooting

• Generic DataSets do not require that you pass a TableName when you call the Fill method. Typed DataSets, however, require this, even if you are accessing the DataSet's Table collection by indexes instead of the TableName. Failure to pass the typed DataSet's TableName throws a "There is no Row at Position 0" error at runtime. This requirement of typed DataSets also means that you cannot make up your own TableName but you must pass the one that is referenced in the typed DataSet class, which is accessed as in the preceding code.
• Any manual changes that you make to the Schema that is generated by Visual Studio .NET are overwritten if you change the database object that you used initially to generate the Schema and typed DataSet class and drag it again to the Schema's Design view. It is generally preferable to make the changes that you have to at the Microsoft SQL Server level and to generate a new typed DataSet than to change the resultant Schema.

back to the top