0

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.

Hamish Grubijan
  • 10,562
  • 23
  • 99
  • 147

1 Answers1

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

rene
  • 41,474
  • 78
  • 114
  • 152
  • Probably, thanks. It is entirely possible that the ODBC API has what I need. I am just not 100% sure where xml stuff comes in. That would be a way to persist things perhaps? What would happen to the dataset object if I were to change rows underneath? Perhaps I could just clone one? Perhaps you did this for the purposes of a merge. That is nice, but all I need is reset the state of the db after a test or a test suite is finished. However, I would also like to be able to compute a difference between two tables (for test comparison logic). Hopefully this makes sense. Feel free to ask questions. – Hamish Grubijan Feb 08 '11 at 00:08
  • The XML stuff is indeed to persist. Cloning will not work because because you want the records to be inserted back in the db. If you clone the dataset will have all its rows marked as unchanged so when you call Update nothing will change. If you only want to restore what has changed you could try to load the actual Dataset from the db and then call Merge. If you end up with the wrong result you have to write a more subtle merge by comparing row by row and add what you're missing, remove what is extra, update what is different. Not undoable, but not for free... – rene Feb 08 '11 at 09:44