How to create and use a typed DataSet by using Visual Basic .NET or Visual Basic 2005
This article refers to the following Microsoft .NET Framework Class Library namespace:
RequirementsThe 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
- Microsoft SQL Server
- Microsoft Visual Studio .NET or Microsoft Visual Studio 2005
- Microsoft ADO.NET
- Microsoft .NET Web Controls
Typed DataSetsFollow 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: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:
- 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: For this sample application, the RowIndex is hard-coded to 5:
dsProducts.DataTableName(RowIndex).ColumnNameBecause 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.
TextBox1.Text = tds.Alphabetical_list_of_products(5).ProductNameTextBox2.Text = tds.Alphabetical_list_of_products(5).CategoryName
- 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 = tdsDataGrid1.DataBind()
Complete code listing (WebForm1.aspx)
Complete code listing (WebForm1.aspx.vb)
Imports System.Data.SqlClientPublic 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 RegionEnd Class
Public Class WebForm1
Partial Class _Default
VerificationPress 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.
- 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.
Article ID: 315678 - Last Review: 12/06/2006 23:18:59 - Revision: 3.5
- kbvs2005swept kbvs2005applies kbhowtomaster kbsqlclient kbsystemdata KB315678