Using Strongly Typed DataSets
Using Strongly Typed DataSetsNormally, when you work with a DataSet , you must work with several collections. To retrieve the value of a certain column in a certain row, you must work with the DataSet 's Tables and Rows collections. For example, to display the value of the Title column for all the rows of the Titles table, you must write code that looks like this: Dim drowDataRow As DataRow For Each drowDataRow in dstDataSet.Tables( "Titles" ).Rows Response.Write( drowDataRow( "Title" ) ) Next This section examines an alternative way to access the information contained in a DataSet . Instead of accessing data in a DataSet by working with collections, you can access the data by using strongly typed methods and properties specified by an XML schema. In this section, you learn how to create a strongly typed DataSet . The advantage of using a strongly typed DataSet is subtle, and doing so might not always be worth the effort. However, if you create a strongly typed DataSet , you can create more readable code. With a strongly typed DataSet , you can refer to tables and columns by name . With a strongly typed DataSet , for example, you can display the value of the Title column for all the rows in the Titles table like this: Dim drowTitleRow As TitlesRow For Each drowTitleRow in objTitlesDS.Titles Response.Write( drowTitleRow.Title ) Next When you create a typed DataSet , you create a new class that represents the DataSet . In this example, you created a new class named objTitlesDS that represents the DataSet . After you create this class, you can retrieve a reference to the Titles DataTable by using the Titles property. Furthermore, you can refer to the value of the Title column by using the Title property. Again, the difference here is not earth shattering. A strongly typed DataSet simply provides you with a slightly more intuitive means of referring to the members of a DataSet . To create a strongly typed DataSet , you must complete each of the following steps:
Now, create a strongly typed DataSet that contains the Titles table from the Pubs database table. The first step is to create an XML schema that specifies the structure of the DataSet and Titles table. You could write this schema by hand; however, an easier way is to use the page in Listing 13.18. It contains an ASP.NET page that will automatically generate the proper XML schema for you. Listing 13.18 GetXmlSchema.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Xml" %> <Script Runat="Server"> Const strTableName As String = "Titles" Sub Page_Load Dim conPubs As SqlConnection Dim dadDataAdapter As SqlDataAdapter Dim dstDataSet As DataSet Dim strXmlSchema As String conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) dadDataAdapter = New SqlDataAdapter( "Select * From " & strTableName, conPubs ) dstDataSet = New DataSet() dstDataSet.DataSetName = strTableName & "DS" dadDataAdapter.Fill( dstDataSet, strTableName ) strXmlSchema = Server.HTMLEncode( dstDataSet.GetXmlSchema() ) Response.Write( "<pre>" & strXmlSchema & "</pre>" ) End Sub </Script> The C# version of this code can be found on the CD-ROM. When you execute the page in Listing 13.18, the page displays the XML schema for the Titles table in the Pubs database. If you want to display the schema for another table, you can modify the strTableName constant and change the database connection string to refer to the proper database. When you execute the page in Listing 13.18, the XML schema in Listing 13.19 is displayed. Listing 13.19 TitlesDS.xsd<xsd:schema id="TitlesDS" targetNamespace="" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xsd:element name="TitlesDS" msdata:IsDataSet="true"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> <xsd:element name="Titles"> <xsd:complexType> <xsd:sequence> <xsd:element name="title_id" type="xsd:string" minOccurs="0" /> <xsd:element name="title" type="xsd:string" minOccurs="0" /> <xsd:element name="type" type="xsd:string" minOccurs="0" /> <xsd:element name="pub_id" type="xsd:string" minOccurs="0" /> <xsd:element name="price" type="xsd:decimal" minOccurs="0" /> <xsd:element name="advance" type="xsd:decimal" minOccurs="0" /> <xsd:element name="royalty" type="xsd:int" minOccurs="0" /> <xsd:element name="ytd_sales" type="xsd:int" minOccurs="0" /> <xsd:element name="notes" type="xsd:string" minOccurs="0" /> <xsd:element name="pubdate" type="xsd:dateTime" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:choice> </xsd:complexType> </xsd:element> </xsd:schema> The C# version of this code can be found on the CD-ROM. If you look closely at the schema, you notice that the schema's ID is the name of the DataSet ( TitlesDS ), and the schema includes a Titles element that represents the Titles database table. Copy the schema generated by the GetXMLSchema.aspx page to a new file and save the file with the name TitlesDS.xsd . You need to use this schema when generating the source code for the strongly typed DataSet class. The next step is to generate the source code for the class. You use the Schema Definition Tool ( Xsd.exe ) from the command line. Open a DOS prompt and change to the directory that contains the TitlesDS.xsd file. Next, execute the following statement from the command line: Xsd.exe /d /l:vb TitlesDS.xsd This statement generates the source for a new Visual Basic class file named TitlesDS.vb . The /d directive indicates that a DataSet should be generated, and the /l directive specifies the language to use for the class file. If you are curious , go ahead and open the TitlesDS.vb file in Notepad. The class file contains methods and properties that correspond to the tables and columns contained in the TitlesDS DataSet . NOTE You can use the Xsd.exe tool to generate the class in a particular namespace. To do so, use the /n: directive and supply a namespace when generating the source for the class file. Now that you have the source for the class file, you need to compile it. Execute the following statement from the command line: vbc /t:library /r:System.dll,System.Data.dll,System.Xml.dll TitlesDS.vb After you execute this command, a new file named TitlesDS.dll is created. The final step is to copy the compiled class file to the application /bin directory. If the /bin directory does not exist at the root of your ASP.NET application, you can create it. Copying the compiled class to the /bin directory makes the class visible to all your ASP.NET pages. The page in Listing 13.20 illustrates how you can use the typed DataSet in an ASP.NET page. The page displays the values of the Title and Notes columns for all the rows in the Titles database table. Listing 13.20 TypedDataSet.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim dadTitles As SqlDataAdapter Dim objTitlesDS As TitlesDS Dim objTitleRow As TitlesDS.TitlesRow ' Fill the typed dataset conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) dadTitles = New SqlDataAdapter( "Select * From Titles", conPubs ) objTitlesDS = New TitlesDS() dadTitles.Fill( objTitlesDS, "Titles" ) ' Display its titles For each objTitleRow in objTitlesDS.Titles Response.Write( "<br>" & objTitleRow.Title ) If Not objTitleRow.IsNotesNull Then Response.Write( "<br>" & objTitleRow.Notes ) End If Response.Write( "<hr>" ) Next End Sub </Script> The C# version of this code can be found on the CD-ROM. In Listing 13.20, the Fill() method fills the typed DataSet with all the rows from the Titles table. After the rows are added, a For...Each loop displays the values of the Titles and Notes columns for each row. Notice that the Titles table is returned as a property of the DataSet . Also, notice that each column name is returned as a property of the TitlesRow object. Finally, IsNotesNull is a method of the typed DataSet . This method returns True when the Notes column has the value Null and False otherwise .  |
EAN: 2147483647
Pages: 263