How to create and use a typed DataSet by using Visual Basic .NET or Visual Basic 2005 (315678)

SUMMARY

This article shows 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.

Requirements

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

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

This article assumes that you are familiar with the following topics:

Microsoft SQL Server
Microsoft Visual Studio .NET or Microsoft Visual Studio 2005
Microsoft ADO.NET
Microsoft .NET Web Controls

Typed DataSets

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

Start Visual Studio .NET or Visual Studio 2005.
Create a new Web Application project named TDS in Visual Basic .NET.

Note In Visual Studio 2005, create a new Web site.
Make sure that the Solution Explorer is displayed. If the Solution Explorer is not displayed, press CTRL+ALT+L.
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.

Note In Visual Studio 2005, double-click Default.aspx.
Under the Editor window, click Design to switch to Design view.
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 the same way.
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.
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.
To create a typed DataSet, press CTRL+ALT+S to open the Server Explorer.
Click Servers, click computer name, click SQLServers, click server name, click Northwind, and then click Views.
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.
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.
To view the new typed DataSet class, click Show All Files in the Solution Explorer.
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.
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.
To include the classes that reside in the System.Data.SqlClient namespace, add the following Imports statement at the top of the codebehind:
```
Imports System.Data.SqlClient
					
```
Note The other namespaces that you need for this small Web application are automatically referenced in the project when you create a Web application in Visual Studio .NET.
In the Page_Load event procedure, create a Connection object by passing the connection string to the default constructor of the SqlConnection class:
```
Dim cn As SqlConnection = New SqlConnection("[YourConnectionString]")
					
```
Create a SqlCommand object that subsequently is 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.
```
Dim cmd As SqlCommand = New SqlCommand("Select * from [Alphabetical list of products]", cn)
					
```
Create an instance of the SqlDataAdapter object, passing the new SqlCommand object to the constructor:
```
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
					
```
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.
```
Dim tds As dsProducts = New dsProducts()
					
```
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)
					
```
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.
To display all 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()
					
```

Complete code listing (WebForm1.aspx)

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="howto_tds.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<meta content="Microsoft Visual Studio.NET 7.0" name=GENERATOR>
<meta content="Visual Basic 7.0" name=CODE_LANGUAGE>
<meta content=JavaScript name=vs_defaultClientScript>
<meta content=http://schemas.microsoft.com/intellisense/ie5 name=vs_targetSchema>
</HEAD>
<body ms_positioning="GridLayout">
<form id=Form1 method=post runat="server">
<P>
	<asp:label id=Label1 runat="server">Product</asp:label>
	<asp:textbox id=TextBox1 runat="server"></asp:textbox>
	<asp:label id=Label2 runat="server">Category</asp:label>
	<asp:textbox id=TextBox2 runat="server"></asp:textbox>
</P>
<P><asp:datagrid id=DataGrid1 runat="server"></asp:datagrid></P>
</form>
</body>
</HTML>

Complete code listing (WebForm1.aspx.vb)

Imports System.Data.SqlClient

Public Class WebForm1
//Note in Visual Studio 2005, "Public Class WebForm1" should be changed to "Partial Class _Default"
    Inherits System.Web.UI.Page
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim cn As System.Data.SqlClient.SqlConnection = _
    New System.Data.SqlClient.SqlConnection( _
    "server=C-176012-C\NetSDK;uid=sa;password=;database=Northwind")
        Dim cmd As System.Data.SqlClient.SqlCommand = _
    New System.Data.SqlClient.SqlCommand( _
    "Select * from [Alphabetical list of products]", cn)
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)

        Dim tds As dsProducts = 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()

    End Sub

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer.
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

End Class

Note In Visual Studio 2005, locate the following code:

Public Class WebForm1

Replace with the following:

Partial Class _Default

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 filled with product information appears under the text boxes.

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 or by Visual Studio 2005 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 usually preferable to make the changes you need at the SQL Server level and to generate a new typed DataSet than to change the resultant Schema.