How to create and use a typed DataSet by using Visual Basic .NET or Visual Basic 2005 (315678)
The information in this article applies to:
- Microsoft ADO.NET (included with the .NET Framework) 1.0
- Microsoft Visual Basic .NET (2002)
- Microsoft Visual Basic .NET (2003)
- 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:
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.
REFERENCES For more information about working with a typed DataSet, see the following Microsoft Web site:
Modification Type: | Minor | Last Reviewed: | 10/3/2006 |
---|
Keywords: | kbvs2005swept kbvs2005applies kbHOWTOmaster kbSqlClient kbSystemData KB315678 kbAudDeveloper |
---|
|