chevron-thin-right chevron-thin-left brand cancel-circle search youtube-icon google-plus-icon linkedin-icon facebook-icon twitter-icon toolbox download check linkedin phone twitter-old google-plus facebook profile-male chat calendar profile-male
Ask a question
The Unit Testing Company
Development Resources
Knowledge Base
Welcome to Typemock Community! Here you can ask and receive answers from other community members. If you liked or disliked an answer or thread: react with an up- or downvote.

Related questions

Alternate to mocking database calls?

0 votes
I watched the video on mocking database calls, and while I understand it, it's seem 'heavy' for the end result. So, while I don't have near the knowledge Gil has (well let's be honest, he's really smart and I'm just an average Joe), I wanted to try and understand why it requires so much and see how much about Typemock I've learned in the last week. So I wrote the following test: 

      [TestMethod(), VerifyMocks()]
      public void GetUserTest()
      {
         MockManager.MockAll<SqlConnection>(Constructor.Mocked);
         MockManager.MockAll<SqlCommand>(Constructor.Mocked);

         using (RecordExpectations recorder = new RecordExpectations())
         {
            SqlDataReader dr = RecorderManager.CreateMockedObject<SqlDataReader>();
            recorder.ExpectAndReturn(dr.Read(), true);
            recorder.ExpectAndReturn(dr.GetString(0), "BOB").CheckArguments(0);
         }

         TestDBMock_Accessor accessor = new TestDBMock_Accessor();
         Assert.AreEqual<string>("BOB", accessor.GetUser());
      }


Which goes against this: 
   class TestDBMock
   {
      public string GetUser()
      {
         string user = null;

         using (SqlConnection conn = new SqlConnection("SomeConnectionString"))
         {
            using (SqlCommand cmd = conn.CreateCommand())
            {
               cmd.CommandText = "TEST";
               cmd.Parameters.AddWithValue("@id", 5);

               conn.Open();
               using (SqlDataReader dr = cmd.ExecuteReader())
               {
                  while (dr.Read())
                  {
                     user = dr.GetString(0);
                  }
               }
            }
         }

         return user;
      }
   }


The idea being that I don't care about anything with the connection or command or any of it's calls - if there was such a thing I'd call a method like .IgnoreRecording or just plain .Ignore because all I'm really interested in doing is faking the methods of dr to return what I want.

Problem is that I get a 'NullReferenceException' when the code is run and a VSTestHost.exe crash 3/4 times on the conn.Open() statement - the method is returning default I guess?

So I imagine I don't understand the usage of MockAll (and probably several other things), but you can see what I'm going for here. Is there a less 'heavy' approach to mocking yoru database calls? I can already hear the developer next to me noticing we have to write the same code in two places (the using command for SqlConnection and SqlCommand) and I'm trying to avoid that.
asked by boo
Report User
 (21.8k points)

5 Answers

0 votes
Deleted...redundant now that I figured out more...see next.
answered by boo
Report User
 (21.8k points)
0 votes
Ok, think I have something half baked now that the others won't have issues with. Any takers on a peer review on this? The idea is that this 'helper' method could be extracted out into a utility class for the test project.

I changed my calls from MockAll to Mock, because MockAll wasn't needed. Notice I added a second test that returns multiple records just to show the reusability of the call, but of interest I also added a reflective ExpectAlways to 'setConnection' - this is because for demoing purpose I used a different 'style' of doing the database connection - I used connection string, instead of using constructor (at bottom). It seems like this could be cleaned up some more and maybe there's a way to get rid of the reflective mocks and use natural mocks, but I'm not smart enough to figure out how. 

      [TestMethod(), VerifyMocks()]
      public void GetUserTest()
      {
         SqlDataReader dr = SetUpMocksForDatabaseCallAndReturnReader();

         using (RecordExpectations recorder = new RecordExpectations())
         {
            recorder.ExpectAndReturn(dr.Read(), true);
            recorder.ExpectAndReturn(dr.GetString(0), "BOB").CheckArguments(0);
         }

         TestDBMock_Accessor accessor = new TestDBMock_Accessor();
         Assert.AreEqual<string>("BOB", accessor.GetUser());
      }

      static bool _isCalled = false;
      private static SqlDataReader SetUpMocksForDatabaseCallAndReturnReader()
      {
         SqlDataReader dr = RecorderManager.CreateMockedObject<SqlDataReader>();

         Mock<SqlConnection> mockedConn = 
            MockManager.Mock<SqlConnection>(Constructor.Mocked);
         mockedConn.ExpectAlways("Open", null);
         mockedConn.ExpectAlways("Close", null);
         mockedConn.ExpectAlways("Dispose", null);
         mockedConn.ExpectAlways("set_ConnectionString", null);

         Mock<SqlCommand> mockedCmd = MockManager.Mock<SqlCommand>(Constructor.Mocked);
         mockedCmd.AlwaysReturn("ExecuteReader", dr, null);

         return dr;
      }

      [TestMethod(), VerifyMocks()]
      public void GetUsersTest()
      {
         SqlDataReader dr = SetUpMocksForDatabaseCallAndReturnReader();

         using (RecordExpectations recorder = new RecordExpectations())
         {
            recorder.ExpectAndReturn(dr.Read(), true);
            recorder.ExpectAndReturn(dr.GetString(0), "BOB").CheckArguments(0);

            recorder.ExpectAndReturn(dr.Read(), true);
            recorder.ExpectAndReturn(dr.GetString(0), "TOM").CheckArguments(0);

            recorder.ExpectAndReturn(dr.Read(), true);
            recorder.ExpectAndReturn(dr.GetString(0), "JOE").CheckArguments(0);
         }

         TestDBMock_Accessor accessor = new TestDBMock_Accessor();
         List<string> users = accessor.GetUsers();
         Assert.AreEqual<int>(3, users.Count);

         //No value in these checks, just demoing...
         Assert.AreEqual<string>("BOB", users[0]);
         Assert.AreEqual<string>("TOM", users[1]);
         Assert.AreEqual<string>("JOE", users[2]);
      }


I would like to understand MockManager better though - is it static? I mean, since I asked the manager to mock SqlConnection, do I really need to do it on the next test too, or is it already mocked from the previous test? If it is static...how do I 'unmock' it?


Different 'style' of dealing from original with ADO.NET calls, the utility test helper method above handles without problem: 
      public List<string> GetUsers()
      {
         List<string> users = new List<string>();

         using (SqlConnection conn = new SqlConnection())
         {
            //Set connection here instead of constructor
            conn.ConnectionString = "NotReal";
            //Don't use 'using statement'
            SqlCommand cmd = new SqlCommand("TEST");
            //Set connection after creation.
            cmd.Connection = conn;

            conn.Open();
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
               while (dr.Read())
               {
                  users.Add(dr.GetString(0));
               }
            }

               cmd.Dispose();
         }

         return users;
      }
answered by boo
Report User
 (21.8k points)
0 votes
Hi Brian,

I normally would go with the second approach. That's what you do when writing a DAL component- you encapsulate all database action in a single place and return a data object.

This way you can mock the entire method, without writing any specific expectations, and return a data object that you build for the test purposes.


:arrow: By not writing specific expectations, you make your test more robust. The more your test relies on internal knowledge, the more brittle it is. The database video shows Isolator's capabilities that can be used when you can't modify the code. If you can refactor it - do that.

Thanks,
answered by gilz
Report User
 (14.5k points)
0 votes
Not sure I follow what you mean by 'second approach' - the second block of code I posted you mean? Maybe I should go back and watch the video again - maybe I missed something.
answered by boo
Report User
 (21.8k points)
0 votes
Brian,

That's correct. This one: 

public List<string> GetUsers()
{
    List<string> users = new List<string>();

    using (SqlConnection conn = new SqlConnection())
    {
        //Set connection here instead of constructor
        conn.ConnectionString = "NotReal";
        //Don't use 'using statement'
        SqlCommand cmd = new SqlCommand("TEST");
        //Set connection after creation.
        cmd.Connection = conn;

        conn.Open();
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                users.Add(dr.GetString(0));
            }
        }

        cmd.Dispose();
     }

     return users;
}


It encapsulates all calls to the database, and creates a data object (in this case a list). This entire method can be mocked to return a List of your own.
answered by gilz
Report User
 (14.5k points)
...

Products

Help

Company

Community

Contact

Typemock Store (US toll FREE 866–895–4680). Copyright © 2006 Typemock Inc. All rights reserved. Terms of Use.