• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

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.
  • 5
  • 3
1 Solution
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>
        public void TestDataTableRows()
            DataTable dt = new DataTable();
            DataRow dr = dt.NewRow();
            // connect to business process
            MyDBClass mydb = new MyDBClass(DatabaseConnectionString);
            int returnedrowcount = mydb.TestDataTable(dt);
            Assert.AreEqual(returnedrowcount, 1);


Open in new window

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.
praveen7kAuthor Commented:
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.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

How are you creating the 'temporary table'? code snipet please
praveen7kAuthor Commented:
In the test class:          
  OracleConnection con = new OracleConnection(connectionString);
            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.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.
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.
praveen7kAuthor Commented:
Problem not fully resolved
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now