Microsoft KB Archive/315678

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

Article ID: 315678

Article Last Modified on 12/6/2006

-

APPLIES TO


 * Microsoft ADO.NET 1.0
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic 2005

-



This article was previously published under Q315678



For a Microsoft Visual C# .NET version of this article, see 320714.

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.SqlClient



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 .</li> 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.</li> To create a typed DataSet, press CTRL+ALT+S to open the Server Explorer.</li> Click Servers, click  , click SQLServers, click  , click Northwind, and then click Views.</li> 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.</li> 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.</li> To view the new typed DataSet class, click Show All Files in the Solution Explorer.</li> 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.</li> 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.</li>  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. </li>  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(&quot;[YourConnectionString]&quot;) </li>  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(&quot;Select * from [Alphabetical list of products]&quot;, cn) </li>  Create an instance of the SqlDataAdapter object, passing the new SqlCommand object to the constructor: Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) </li>  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 </li>  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) </li>  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. </li> <li> 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 </li></ol>

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

Complete code listing (WebForm1.aspx.vb)
Imports System.Data.SqlClient

Public Class WebForm1 //Note in Visual Studio 2005, &quot;Public Class WebForm1&quot; should be changed to &quot;Partial Class _Default&quot; 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( _   &quot;server=C-176012-C\NetSDK;uid=sa;password=;database=Northwind&quot;) Dim cmd As System.Data.SqlClient.SqlCommand = _ New System.Data.SqlClient.SqlCommand( _   &quot;Select * from [Alphabetical list of products]&quot;, 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


 * 1) Region &quot; Web Form Designer Generated Code &quot;

'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


 * 1) 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 &quot;Uncle Bob's Organic Dried Pears&quot; in the Product text box and &quot;Produce&quot; 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 &quot;There is no Row at Position 0&quot; 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.

<div class="references_section">