How do you use ADO.NET to create a data source rather then read and manipulate one

Question

I'm writing a plug-in for another program that generates a fairly complex tree structure of objects. The users will need to export this data from the plug-in for analysis and reporting. I definitely want them to be able to export to an MS Access database as it would be the most accessible for them to create quick and clean reports. But I also would like to set things up so it would be easy to export to other data sources (XLS, XML, SQL Server etc..) with out a lot of duplicate code.

I have created recursive methods that will populate various DataTables from the tree structure. I can then stuff these DataTables into a DataSet. But at that point I'm kinda lost.

All the examples I find for ADO.NET start with having some central data source. Then you create a DataConnection to the data through the correct DataProvider using a connection string. After which you can get out either a DataReader or DataSet depending on whether you need to save changed back to the source.

Yet I'm starting with the DataSet, and need to create a data source from it. Is there some easy reusable way that I could create a new DataConnection based on some existing empty data source, and then populate it with my DataSet?

For example I could create a DataConnection to an empty MS Access file. Then I could use my recursive methods to populate various DataTables in a DataSet from my tree structure. But how could I populate the blank access database with this resulting DataSet?

I'm hoping for a method that is generic enough so that I can easily export to different potential data sources by simply swapping in different DataProviders and/or connection strings.

Answer 1

You're essentially asking two separate questions here, one about inserting data without first selecting from the target table and another about keeping your persistence code database agnostic.

It's relatively easy to manually build up a data table in memory and persist it with the help of a DataAdapter and a DbCommandBuilder. The data adapter is used to synchronize the data table into the database and the command builder is used by the data adapter to automatically generate insert (and update) statements based on the select command provided.

So long as you don't need to run any complex SQL queries it should be fairly easy to keep your code database agnostic using a DbProviderFactory. It essentially hides the ADO.net provider implementation from you so that you code against the underlying interfaces in a generic way.

The following example should illustrate the above two concepts.

public static void Main()
{
    var providerName = "System.Data.OleDb";
    var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=output.mdb";
    var table = new DataTable {
                        Columns = {
                            new DataColumn("ID", typeof (int)),
                            new DataColumn("Name", typeof (string)) },
                        Rows = {
                            new object[] {1, "One"},
                            new object[] {2, "Two"} }
                    };
    SaveData(providerName, connectionString, table);
}
private static void SaveData(string providerName,
                             string connectionString,
                             DataTable table)
{
    var factory = DbProviderFactories.GetFactory(providerName);
    var connection = factory.CreateConnection();
    connection.ConnectionString = connectionString;
    var command= factory.CreateCommand();
    command.Connection = connection;
    command.CommandText = "select ID, Name from Person";
    var adapter = factory.CreateDataAdapter();
    adapter.SelectCommand = command;
    var builder = factory.CreateCommandBuilder();
    builder.DataAdapter = adapter;
    adapter.Update(table);
}

This solution only deals with persistence to databases. For exporting to Excel you can use the Jet OleDb provider (more info here) and for XML you can use XmlSerializer.