How to write an ODBC DataSet object into a database table using C#?

Question

I am unit/auto-testing a large application which uses MSFT Sql Server, Oracle as well as Sybase as its back end. Maybe there are better ways to interface with a db, but ODBC library is what I have to use. Given these constraints, there is something that I need to figure out, and I would love your help on this. My tests do change the state of the database, and I seek an inexpensive, 99.99% robust way to restore things after I am done ( I feel like a full db restore after each test is too much of a penalty). So, I seek a complement to this function below - I need a way to populate a table from a DataSet.

    private DataSet ReadFromTable(ODBCConnection connection, string tableName)
    {
        string selectQueryString = String.Format("select * from {0};", tableName);
        DataSet dataSet = new DataSet();
        using (OdbcCommand command = new OdbcCommand(selectQueryString, connection))
        using (OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(command))
        {
            odbcAdapter.Fill(dataSet);
        }

        return dataSet;
    }

    // The method that I seek.
    private void WriteToTable(ODBCConnection connection, string tableName, DataSet data)
    {
        ...
    }

I realize that things can be more complicated - that there are triggers, that some tables depend on others. However, we barely use any constraints for the sake of efficiency of the application under test. I am giving you this information, so that perhaps you have a suggestion on how to do things better/differently. I am open to different approaches, as long as they work well.

The non-negotiables are: MsTest library, VS2010, C#, ODBC Library, support for all 3 vendors.

Answer 1

Is this what you mean? I might be overlooking something

In ReadFromTable

dataset.WriteXmlSchema(memorySchemaStream);
dataset.WriteXml(memoryDataStream);

In WriteToTable

/*  empty the table first */

Dataset template = new DataSet();
template.ReadXmlSchema(memorySchemaStream);
template.ReadXml(memoryDataStream);

Dataset actual = new DataSet();
actual.ReadXmlSchema(memorySchemaStream);

actual.Merge(template, false);

actual.Update();

Other variant might be: read the current data, do compare with template and based on what you are missing add the data to the actual dataset. The only thing to remember is that you cannot copy the actual DataRows from one dataset to another, you have to recreate DataRows