Creating a Dataset File with ADO.NET Methods

The last way to create a dataset file is to use the methods of the ADO.NET classes. The ADO.NET namespace is designed to make working with XML files almost effortless. It converts a dataset to and from its XML schema. Use the WriteXMLSchema() method to save the schema to a file. Simply pass it the filename to save it to.

First create a dataset object that links to the data you want to report on and populate it with some sample report data. You need to populate it with sample data so that it has the table structure in memory. How you populate the dataset isn’t relevant because this independent from the report itself. For example, some data sources you could load the data from are a SQL Server database, a MS Access database, an XML file, etc. Listing 17-7 shows how to load data from a SQL Server database.

Listing 17-7. Reading data from a SQL Server database
[VB.NET]
Dim myDataSet As DataSet
Dim myConnectionString As String
Dim myDataAdapter As SqlClient.SqlDataAdapter
Dim SQL As String
SQL = "SELECT Customers.*, Orders.* " & _
"FROM Customers INNER JOIN Orders " & _
"ON Customers.CustomerId = Orders.CustomerId"
myConnectionString = "Data Source=(local);UID=sa;pwd=pw;Database=Northwind"
myDataAdapter = New SqlClient.SqlDataAdapter(mySQL, myConnectionString)
myDataAdapter.Fill(myDataSet, "Customers")
[C#]
DataSet myDataSet;
string myConnectionString;
SqlClient.SqlDataAdapter myDataAdapter;
string SQL;
SQL = "SELECT Customers.*, Orders.* " + "FROM Customers INNER JOIN Orders " + "ON Customers.CustomerId = Orders.CustomerId";
myConnectionString = "Data Source=(local);UID=sa;pwd=pw;Database=Northwind";
myDataAdapter = new SqlClient.SqlDataAdapter(mySQL, myConnectionString);
myDataAdapter.Fill(myDataSet, "Customers");

If you need examples of how to connect to different types of data sources, you can reference the URL http://www.carlprothman.net/Default.aspx?tabid=81.

Once the dataset is created, call the WriteXMLSchema() method and pass it the filename. This saves the XML schema to the file to be used by your report. The code in listing 17-8 gives you a simple example of how to implement this. It is very generic and simply saves the dataset’s schema to an XSD file. Make sure that the myDataSet object has been populated elsewhere in your application before calling the WriteXmlSchema() method.

Listing 17-8. Saving an XML schema file from a dataset object.
[VB.NET]
'Write a populated dataset object to an XML schema file
'Assume that the dataset has already benn populated (see previous listing as an example)
myDataset.WriteXmlSchema("XmlData.xsd")
[C#]
myDataset.WriteXmlSchema("XmlData.xsd");

The key to using this code is that you only need to call it one time to create the schema file. In your code you should populate the dataset with the data you want to print, call this code to save the schema, and comment it out so that it isn’t called again. You could also delete the code, but this isn’t the best idea because if the structure of your data changes, you’ll want to uncomment the code so that you can overwrite the older schema file.

Once you’ve saved the schema file, you can start designing your report from it.

Question: I have many reports based on datasets that were built using Visual Studio 2003. When I upgrade to Visual Studio 2008, none of the reports print data anymore. Why not?

Answer: I solved my own question! I noticed that the XML schema file in 2008 is slightly different than the schema file in 2003. I rebuilt my dataset schemas and now every works fine. You could also copy/paste the data tables from the old project to the new project and this will update them as well.