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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
praveen7kAuthor Commented:
Problem not fully resolved
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.