asked on

How to Unit Test a class which retrieves data from database

I have my .NET class which calls an Oracle database and gets some data. I want to automate the unit testing for the same. But I dont want to hit the database when unit testing as the database could be changing frequently. I tried the following approach: In my test project, I would create a temporary table and insert some rows. In my actual class, I would retrieve the data and send it back to the test project. In the test project again, I would assert if the inserted data and the returned data are equal or not and that would serve as a Unit Test. The problem I'm facing is that the temporary table created in the test project is descoped when we move to the actual class. So, if I try retrieving the data in the actual class, there is no table.
If anyone has a solution to this problem or if you have any other way of automating the unit test for this scenario, please let know.

Mez4343

Sounds like the Unit testing is 4 times more complicated than the actual business code!

I not sure why you have this problem. I created a new Unit Test and pass a DataTable to my Business process with 1 row added. The business process receives the datatable and the row added in Unit Test environment is there. So I dont think you have a scope issue. Need more info about the code you you are using. Can you provide a snipet or something?

/// <summary>
        /// Scenario: 
        /// Verify that Datatable passed from Unit Test is accessible in business process
        /// </summary>
        [TestMethod]
        public void TestDataTableRows()
        {
            DataTable dt = new DataTable();
            DataRow dr = dt.NewRow();
            dt.Rows.Add(dr);
            // connect to business process
            MyDBClass mydb = new MyDBClass(DatabaseConnectionString);
            int returnedrowcount = mydb.TestDataTable(dt);
            Assert.AreEqual(returnedrowcount, 1);

        }

Open in new window

Mez4343

One other thought, since you are Unit Testing and dont need a fullly seeded and production level DB, can you setup a Test database so that you could actually runs tests against it?

Its good to have a test platform anyway so that you ca reproduce customer problems in the field without having to actually connect to the Field.

This way your Unit Tests could actually drive through the entire code base and run any queries/stored procedures to see if they are broken as well.

praveen7k

ASKER

Am sorry.. I was not very clear in my post I think. I'm trying to have automated tests. So, in my Test.cs, I create a temporary table(..say, table1) and insert rows. In my actual.cs, I retrieve the data from table1( created in test.cs ) and send the results back to test.cs. Now, in test.cs, I compare the results and the hardcoded values I already had. So, that way, I know that the actual.cs is able to return the number of rows expected.
The problem here is that table1 is a temporary table created from test.cs. So, it wont be alive when the connection closes and reopens in actual.cs.
Am willing to completely modify the approach if there is a better one.

Mez4343

How are you creating the 'temporary table'? code snipet please

praveen7k

ASKER

In the test class:
OracleConnection con = new OracleConnection(connectionString);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
OracleTransaction objTrans = con.BeginTransaction();
cmd.Transaction = objTrans;

cmd.CommandText = "CREATE GLOBAL TEMPORARY TABLE my_temp_table( some columns) ON COMMIT PRESERVE ROWS";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO my_temp_table VALUES ( some values)";
int i = cmd.ExecuteNonQuery();
Call the actual class and get values.
Compare the values got from the actual class to the values inserted and assert if they are equal.

In the actual class: Retrieve the values from my_temp_table and return to the test class.

Mez4343

ok I see what your doing now. The 'ON PERSERVE ROWS' should persist the rows for the SESSION. Not sure if SESSIOn is the same when a Unit test calls into a business process but I would think so. So i can assume that for now.

I question wheather the rows are Commited. Take a look at this with use of Oracle Transaction and Commit() method. Maybe that will help but just a guess.

ASKER CERTIFIED SOLUTION

Mez4343

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

praveen7k

ASKER

Problem not fully resolved