Start a new CLR Console project project named DataSetApp.

Add an external reference to the System::Configuration assembly by using the Properties dialog box, as detailed in the sidebar Referencing external assemblies earlier in the chapter.

Add using statements to the top of the source file for the assemblies that you are going to be using.

// ADO.NET namespaces
using namespace System::Data;
using namespace System::Data::Common;
// For reading the configuration data
using namespace System::Configuration;
// For printing the content of the DataSet
using namespace System::IO;

Add an application configuration file to the project. In Solution Explorer, right-click the project name. On the shortcut menu that appears, point to Add, and then click New Item. In the New Item dialog box that opens, in the pane on the left, click Visual C++, and then click Utility. In the center pane, click Configuration file (app.config).

Remember to add the post-build step to the project settings so that app.config will be renamed to match the executable name. You can find details on how to do this in the previous exercise.

Copy the content of the app.config file from the previous exercise, Creating a connected application. Here is the content that you need:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <clear/>
    <add name="Blog"
      connectionString=
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:path	olog.mdb"
      providerName="System.Data.OleDb" />
  </connectionStrings>
</configuration>

Remember to edit the path to reflect wherever you have stored the blog.mdb file.

Copy the code to read the connection string settings and create the DbProviderFactory. (The code is given here, but it is exactly the same as for the previous exercise.)

// Get the connection settings
ConnectionStringSettings ^settings =
    ConfigurationManager::ConnectionStrings["Blog"];
if (settings == nullptr)
{
    Console::WriteLine("Couldn't get settings");
    return -1;
}
Console::WriteLine("Connection settings OK");

// Get the factory object for this provider type
DbProviderFactory ^fac = DbProviderFactories::GetFactory(settings->ProviderName);

Add a try block in which you create a connection, a catch block to handle any errors, and a finally block to close the connection. (Again, I have reproduced the code here, but you should be able to copy it from the previous exercise.)

DbConnection ^conn = nullptr;
try
{
    // Create a connection and set its connection string
    conn = fac->CreateConnection();
    conn->ConnectionString = settings->ConnectionString;

    conn->Open();
    Console::WriteLine("Connection opened");
}
catch (Exception ^ex)
{
    Console::WriteLine(ex->Message);
}
finally
{
    if (conn != nullptr)
    {
        conn->Close();
        Console::WriteLine("Connection closed");
    }
}

With that setup complete, you can begin retrieving data. Start by asking the factory to create a DataAdapter.

// Create a DataAdapter and set its select command
DbDataAdapter ^adapter = fac->CreateDataAdapter();

A DataAdapter can have four commands associated with it, but because you are only going to be retrieving data, you only need to set the Select command. Do this by creating a DbCommand object, as in the previous exercise, and then assigning it to the SelectCommand property of the adapter.

DbCommand ^cmd = fac->CreateCommand();
cmd->CommandText = "SELECT * FROM Entries";
cmd->CommandType = CommandType::Text;
cmd->Connection = conn;

adapter->SelectCommand = cmd;

You can now create a DataSet and ask the adapter to fill it.

DataSet ^dataset = gcnew DataSet("Blog");
adapter->Fill(dataset, "Entries");

The first line creates an empty DataSet called “Blog”. Calling Fill on the adapter causes it to execute its SelectCommand, which creates a DataTable called “Entries”, fills it with the result of the query, and then adds it to the DataSet’s collection of DataTables.

Giving names to DataSets and DataTables is optional, but as you will see shortly, it is very useful when building XML documents from DataSet data.

Now that you have a DataSet, it would be useful to look at what it contains. The WriteXml function writes the content of a DataSet in XML format to any stream. The XmlTextWriter class provides a useful stream for our purposes because it writes the output to a file in properly formatted form.

XmlTextWriter ^xwriter = gcnew XmlTextWriter("c:\SbS\dataset.xml", nullptr);
xwriter->Formatting = Formatting::Indented;

The first two lines create an XmlTextWriter and ensure that it writes out the XML with indentation. Edit the path to put the XML file in a suitable location. Remember that you need to add a using namespace statement for System::Xml, or use the full name System::Xml::XmlTextWriter.

XmlTextWriter ^xwriter = gcnew XmlTextWriter("c:\SbS\dataset.xml",
Encoding::Unicode);

Use the table’s WriteXml method to write the data out to the file.

DataTable ^table = dataset->Tables[0];

table->WriteXml(xwriter, XmlWriteMode::IgnoreSchema);
xwriter->Close();

The declaration of the DataTable handle makes the following code simpler and shows how the table created by the adapter is the first one in the DataSet’s Tables collection. Because you gave the table a name when the adapter created it, you could also specify the name here rather than the ordinal. The second argument to WriteXml shows that you only want the data and not the schema.

Build and run the application and then open the XML file in Notepad; you should see that the first few lines look like this:

<Blog>
  <Entries>
    <ID>2</ID>
    <Date>Jul 01, 2009</Date>
    <Text>A first entry</Text>
    <Author>Julian</Author>
  </Entries>
  <Entries>
    <ID>3</ID>
    <Date>Jun 27, 2009</Date>
    <Text>Second entry</Text>
    <Author>Julian</Author>
  </Entries>
  ...

The root element has the same name as the DataSet, and each row is named after the table. If you hadn’t assigned names to the DataSet and DataTable, the root element would have been called “NewDataSet” and each row would have been called “Table”.

Change the WriteXml statement so that it includes the schema in the generated data.

table->WriteXml(xwriter, XmlWriteMode::WriteSchema);

Build and run the application again; you should see that the output file contains an XML schema that describes the data.

<Blog>
  <xs:schema id="Blog" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
             xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="Blog" msdata:IsDataSet="true"
                 msdata:MainDataTable="Entries" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Entries">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ID" type="xs:int" minOccurs="0" />
                <xs:element name="Date" type="xs:string" minOccurs="0" />
                <xs:element name="Text" type="xs:string" minOccurs="0" />
                <xs:element name="Author" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Entries>
    <ID>2</ID>
    <Date>Jul 01, 2009</Date>
    <Text>A first entry</Text>
    <Author>Julian</Author>
  </Entries>
  ...