How can we test the stored procedure from the database by using the moq objects in C#?

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have two sp's in database. I have tested two sp's by using the MS Unit test. In deployment i faced a problem. I could not be able to access database in application server. That's why i came up with the moq framework. I need to test those two sp's by using the moq objects instead of actual database calls. Can you please suggest me is there any better way to do?

What I have tried:

I have tried like this. Just i have created one interface(moq repository) and added two methods.

public interface IImintMSNRepository
{
    bool InsertBulkImportPortfolios(DataTable mSNBulkImportPortfolioTVP,string updatedBy);
    bool InsertBulkImportIndexes(DataTable mSNBulkImportIndexTVP, string updatedBy);

}

I have tested those two methods by using moq like below.

public readonly IImintMSNRepository MockMSNRepository;

public ImintMSNUnitTests()
{

    Mock<IImintMSNRepository> mockRepo = new Mock<IImintMSNRepository>();
    mockRepo.Setup(a => a.InsertBulkImportPortfolios(It.IsAny<DataTable>(), It.IsAny<string>())).Returns(true);

    mockRepo.Setup(b => b.InsertBulkImportIndexes(It.IsAny<DataTable>(), It.IsAny<string>())).Returns(true);


    this.MockMSNRepository = mockRepo.Object;
}


[TestMethod]
public void TestPortfolioSPByMOQ()
{
    MSNBulkImportPortfolioTVP ptvp = new MSNBulkImportPortfolioTVP();

    DataRow tvprow = ptvp.NewRow();

    tvprow["PortfolioCode"] = "AutomationMoq1";
    tvprow["PortfolioName"] = "AutomationMoqName1";
    tvprow["ClientName"] = "Thomson Reuters";
    tvprow["RIC"] = "IBM.N";
    tvprow["CalculationMethodology"] = "TRGE";
    tvprow["CalendarEventCode"] = "US";
    tvprow["IsProformaPortfolio"] = 1;
    tvprow["IsParentPortfolio"] = 1;
    tvprow["IsGenerateGroupFragment"] = 1;
    tvprow["IsPushLastTick"] = 1;

    ptvp.Rows.Add(tvprow);

    bool expected = true;
    bool actual;

    actual = this.MockMSNRepository.InsertBulkImportPortfolios(ptvp, "MSNMoqUnitTestProcess");
    Assert.AreEqual(expected, actual);

}

Posted 3-Sep-18 23:44pm

Sree(Member 12916318)

Updated 4-Sep-18 2:21am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Accepted Answer · 2018-09-04T00:09:00

Solution 1

You don't test stored procedures via unit tests, unit tests are for testing your code logic so you use mocking frameworks to substitute the things you don't want to do (such as access a database) in a way that allows your business logic to still be tested. For example if you had a banking app that had a "GetBalance" method you would mock that method to return hard-coded values that you can use to test your logic, so "100" for testing transfers, "-100" for testing overdrafts etc.

If you look at the code you have...what logic are you actually testing? None really. You're creating a datatable and you're calling your mocked method with that table and that mocked method is returning "true". The only thing you're testing is that creating a DataTable doesn't throw an exception.

Basically you need to forget about unit testing stored procedures, that's not what unit tests are for. Mocking frameworks are for bypassing stored procs, not testing them.

Posted 4-Sep-18 0:09am

F-ES Sitecore

Comments

Sree(Member 12916318) 4-Sep-18 6:22am

Thanks for the answer. I have an idea. But, I am new to this topic. That's why I want to clarify in a more elaborate way. You mean, It does not possible to test the SP via mocking testing objects right?

F-ES Sitecore 4-Sep-18 6:31am

You can still call the SP via your unit tests if you want to, not the mocking framework, but you shouldn't, it's not what they're for and you run into the issues you've found regarding configuration, access to resources etc. A suite of unit tests is something that should be very quick to run and tests the logic only, not the external resources, and your build server should be able to run these tests also quickly and on any environment. When you start to access external resources like databases, file systems, APIs etc via your unit tests things get much more complicated, so mocking frameworks are to bypass that kind of access.

Sree(Member 12916318) 4-Sep-18 6:44am

Thanks for your valuable suggestion.

Sree(Member 12916318) 6-Sep-18 8:09am

Thank you for the suggestion. I understood what you have explained to me. I had a requirement like that. Is there any possibility to test the Stored Procedures by using the moq instead of actual database calls other than MS Unit test like NUnit test. Please suggest me.

F-ES Sitecore 6-Sep-18 9:54am

What do you mean by "test the storedprocedure"? The only way you're going to do that is to actually call the database. You use moq to mock the results of a SP call so it looks like you called the SP but you didn't really, you got a hard-coded result back.

Sree(Member 12916318) 6-Sep-18 10:37am

Thank you for the suggestion. I understood what you have explained to me. Let it be the code above what I have written. I have one method. In that, I have database calls and stored procedure by passing parameters as data table and string to save the data into the database. I would like to test that method by using the moq objects as per the requirement. There is no any other possibility to do like that by read your comments from the above. It won't possible right.Can you please suggest me

F-ES Sitecore 6-Sep-18 11:01am

It's possible if you have written your code in a particular way. You need a class that calls the SP (it implements IImintMSNRepository I assume) and you need other classes that use this class, and you test those classes by passing in a mocked copy of IImintMSNRepository rather than the actual class that accesses the database.

https://www.c-sharpcorner.com/UploadFile/dacca2/unit-test-using-mock-object-in-dependency-injection/

Sree(Member 12916318) 6-Sep-18 10:38am

Thank you for the suggestion. I understood what you have explained to me. Let it be the code above what I have written. I have one method. In that, I have database calls and stored procedure by passing parameters as data table and string to save the data into the database. I would like to test that method by using the moq objects as per the requirement. There is no any other possibility to do like that by read your comments from the above. It won't possible right.Can you please suggest me