CODEWITHSUNDEEP
vb.netunit-testing
GDutton
GDutton

Reputation: 176

VB Unit Test with Database connection

I am new to the unit testing project and I am wondering how I can create and use a database connection. Can someone offer a VB.Net example? It seems most are in c#

This is as far as I have got! The function GetCorrectedTransactionEffectiveDate expects a connection object as it looks up some goodies in the database

So I am a little bewildered as to how this should be done in unit testing??

<TestClass()> Public Class UnitTest1

<DataSource ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\BServer\bucshared\Systems\ApplicationData\BOne\ENV_DEV\Database\BOne.accdb; Jet OLEDB:Database Password=password"), ]
<TestMethod()> Public Sub RecordedDateTest()

    Dim DateToday As New Date(2014, 6, 14)
    Dim TransactionDate As New Date(2014, 7, 15)
    Dim oUtil As New BerkleyOne.clsDbUtil({Database connection})

    Dim Res as date = oUtil.GetCorrectedTransactionEffectiveDate(TransactionDate)

End Sub

End Class

Upvotes: 0

Views: 893

Answers (1)

David
David

Reputation: 2272

TL;DR I don't think the DataSourceAttribute is what you want.

According to the class's documentation, its purpose is to identify a data store for inputs to the test method.

So in your case, the way you'd want to use this would be to:

  • have the test method accept a Date (or DateTime) parameter
  • pass that parameter to oUtil.GetCorrectedTransactionEffectiveDate()
  • store the set of input parameter values in a database
  • use the DataSourceAttribute would identify how to locate the test input values

... but (from what I can tell) this would be more of an integration test than a unit test.

To be able to unit test your code, your code should follow the Single Responsibility Principle. But it appears the GetCorrectedTransactionEffectiveDate() method is doing at least two things:

  • looking up information in a database (perhaps weekend days and holidays);
  • performing some sort of date calculations.

As I noted in a comment, in the world of automated testing -- which appears to be your goal -- a unit test is supposed to test code (naturally), and be able to do so quickly. Therefore, you don't want the code you're unit testing to be performing network, database, or even disk I/O.

A common approach would be to separate the date-calculation logic and database access as follows:

  • define an interface for the data-retrieval mechanism (GetFooById(), GetFooByName(), etc.) -- this is commonly referred to as a "repository";
  • implement the repository interface with a "real" class that fetches the values from a database / web service / whatever;
  • implement the interface with a mock class that (for example) always returns a hard-coded value, or collection of values;
  • better yet, create the mock implementation using a mocking framework -- this will allow you to construct the mock object and specify what it should return in your test method ("on the fly");
  • modify clsDbUtil to accept an IFooRepository as a constructor parameter (rather than a connection string) and delegate all the data-retrieval work to the repository.

Now you have a test for your date calculation that should run in at most a few milliseconds. So the next time a "Y2K" type calendar altering event occurs, you'll be able to quickly verify (regression test) any changes you have to make to the date calculations.
An additional benefit is that the repository interface is (or should be) media-agnostic. If you have to go from a database to XML or JSON files or a web service, none of the date calculation code should be affected.

I realize all this isn't trivial. And it's not always feasible to modify the source code for clsDbUtil or whatever. You can still test the method; again, it'll be more of an integration test, but (as comments have noted) that's still much better than no tests at all.

Automated testing (unit / integration / acceptance testing) is a large topic;I've only scratched the surface here.
Assuming I haven't frightened you away, I would recommend you read Roy Osherove's The Art of Unit Testing. If you're working with a code base that doesn't lend itself (easily) to automated testing, I'd also recommend Michael Feathers' Working Effectively with Legacy Code.

Upvotes: 1

Related Questions