Microsoft KB Archive/317940

= HOW TO: Write an XSD Schema from a Database by Using a DataSet =

Article ID: 317940

Article Last Modified on 9/4/2003

-

APPLIES TO


 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1

-



This article was previously published under Q317940



IN THIS TASK
SUMMARY Write an XSD Schema from a DataSet with One Table Where All Columns Are Not Required in the Resulting XSD Schema
 * Create a Visual Basic .NET Application

Write an XSD Schema from a DataSet with Multiple Datatables Where All Tables Are Not Required in the Resulting XSD Schema



SUMMARY
Use this step-by-step guide to write an XSD schema from a database. This article focuses on the following two scenarios:
 * How to write an XSD schema from a DataSet object with one table where all columns are not required in the resulting XSD schema.
 * How to write an XSD schema from a DataSet object with multiple datatables where all tables are not required in the resulting XSD schema.

To create an XSD schema from a database, you can use the DataSet object in .NET Framework. The DataSet object reads and writes XML files. The DataSet object can contain heirarchical or relational data. Therefore, it can take data from a relational store and render it in a hierarchical format.

back to the top

Write an XSD Schema from a DataSet with One Table Where All Columns Are Not Required in the Resulting XSD Schema

 * 1) Create a connection to the database.
 * 2) Fill the DataSet with the schema of the required table. To do this, use DataAdapter and the FillSchema method.
 * 3) Delete the columns in the table that are not required in the resulting schema.

NOTE: This step is included to show you how to remove a DataColumn from a DataTable.
 * 1) Write the schema to an external file or to a Stream object by using the WriteXMLSchema method of the DataSet.

back to the top

Create a Visual Basic .NET Application
 Create a New Console application by using Visual Basic .NET projects.  Import the following namespaces: Imports System.Data Imports System.Data.SqlClient Imports System.IO                     Type or paste the following code in the sub Main area: Dim cnNwind As New SqlConnection(&quot;Data Source=myserver;user id=myusername;password=mypassword;Initial catalog=Northwind;&quot;) Dim daOrders As New SqlDataAdapter(&quot;Select * from Orders&quot;, cnNwind)

Dim ds As New DataSet Try daOrders.FillSchema(ds, SchemaType.Mapped, &quot;Orders&quot;) Dim dt As DataColumn dt = ds.Tables(&quot;Orders&quot;).Columns(&quot;Employeeid&quot;) ds.Tables(&quot;Orders&quot;).Columns.Remove(dt) ds.WriteXmlSchema(&quot;C:\Orders.xsd&quot;) Console.WriteLine(&quot;The XSD schema is written to C:\Orders.xsd.&quot;) Console.Read Catch ex As Exception Console.WriteLine(ex.Message) Console.Read End Try  In the aforementioned code, change the Sql Server name, username, and password. Build and run the program. The following message appears in the Console window:

The XSD schema is written to C:\Orders.xsd.



back to the top

Write an XSD Schema from a DataSet with Multiple DataTables Where All Tables Are Not Required in the Resulting XSD Schema
 Create a connection to the database.</li> Fill the DataSet with the schema of the required tables. To do this, use DataAdapter and the FillSchema method.</li> Delete the tables that are not required in the resulting schema. Remove any relationships to other tables before you remove the table.

NOTE: This step is included to show you how to remove a datatable from a DataTable.</li> Write the schema to an external file or to a Stream object by using the WriteXMLSchema method of the DataSet.</li>  Type or paste the following code into the Sub Main area of the Console application. If necessary, comment out the previous code before you insert the new code sample. Sub Main Dim cnNwind As New SqlConnection(&quot;Data Source=myservername;user id=myusername;password=mypassword;Initial catalog=Northwind;&quot;) Dim daEmployees As New SqlDataAdapter(&quot;Select * from Employees&quot;, cnNwind) Dim daOrders As New SqlDataAdapter(&quot;Select * from Orders&quot;, cnNwind) Dim daOrderDetails As New SqlDataAdapter(&quot;select * from [Order details]&quot;, cnNwind)

Dim ds As New DataSet Try daEmployees.FillSchema(ds, SchemaType.Mapped, &quot;Employees&quot;)

daOrders.FillSchema(ds, SchemaType.Mapped, &quot;Orders&quot;) daOrderDetails.FillSchema(ds, SchemaType.Source, &quot;OrderDetails&quot;) Dim drEmpOrders As New DataRelation(&quot;EmpOrder&quot;, ds.Tables(&quot;Employees&quot;).Columns(&quot;Employeeid&quot;), ds.Tables(&quot;Orders&quot;).Columns(&quot;Employeeid&quot;)) ds.Relations.Add(drEmpOrders) ds.Tables(&quot;Orders&quot;).Constraints.Remove(&quot;EmpOrder&quot;)

Dim drOrderdetails As New DataRelation(&quot;OrderDetails&quot;, ds.Tables(&quot;Orders&quot;).Columns(&quot;Orderid&quot;), ds.Tables(&quot;Orderdetails&quot;).Columns(&quot;Orderid&quot;)) ds.Relations.Add(drOrderdetails)

ds.Relations.Remove(&quot;EmpOrder&quot;) ds.Tables.Remove(&quot;Employees&quot;)

Dim strm As New StreamWriter(&quot;C:\OrderDetails.xsd&quot;, False) ds.WriteXmlSchema(strm) Console.WriteLine(&quot;The XSD schema is written to C:\OrderDetails.xsd.&quot;) Console.Read Catch ex As Exception Console.WriteLine(ex.Message) Console.Read End Try

End Sub

</li> Build and run the program. The following message appears in the Console window:

The XSD schema is written to C:\OrderDetails.xsd.

</li></ol>

back to the top

Keywords: kbhowtomaster KB317940

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.