1

Brief background

I'm currently designing a COM DLL in .NET for use in MS Access by a few other developers. The Access application is hooked up to a SQL-Server & is supposed to just act as a front-end, but currently it pretty much does everything. Ideally we would like to separate the business logic into a separate entity to Access can be restricted to acting as just the front-end.

If the developers are working on a method that is supposed to perform some basic CRUD operations on the data they switch their ODBC Data Source over to test (just a localhost server) whilst the production application is hooked up to a live connection.

What I've done in my DLL so far

So in designing / testing my DLL I have worked exlusively in my own personal test server (localhost). So one of the requirements has been for the DLL to be able to connect to the same server as the local ODBC Data Source that the user has. I've managed to configure that fairly simply.

The way I do this is by Access writing a connection string to a local file the DLL can access whenever a class is initialised in Access.

I've been advised to add some unit testing for all of my CRUD operations against the database that are being performed in the DLL. However, these unit tests fail when being completed in Azure DevOps during my build event as the localhost isn't available to use.

I was thinking about adding a 3rd database to the mix, a code first SQLite database that will be created & torn down during the unit testing process.

My original thought was to create this using a series of SQL statements to be executed but it was getting very messy very quickly so I've put that branch on hold for now.

Am I overthinking this? I've never designed anything like this & I'd like to try & work to some strong guidelines from an early point in time of the development on this project to avoid to much re-work later on.

Would this approach be considered a good practice? If not, what would you suggest / what should I be searching for? My knowledge on this is quite limited & I feel that I'm probably searching for the wrong thing.

|improve this question
  • Are you not keeping database <connectionStrings> in your Web.config or App.config files? This is precisely the problem this feature solves. – Greg Burghardt Apr 4 at 16:01
  • What's the point of unit testing the CRUD operations? If it is to test some business logic in there, you could mock the DB and just ignore it. If the point is to make sure stuff saves, then don't bother. You don't need to be testing the database to make sure it behaves like a database. – Becuzz Apr 4 at 16:37
  • @GregBurghardt I'm not sure how me having the connection string in app.config solves this. The database doesn't exist when the solution is being built in Azure DevOps (mentioned this in my post) – Webbarr Apr 4 at 20:11
  • @Becuzz it's to test the business logic is being applied correctly rather than the CRUD operations themselves. Part of the deployment process is to build it in Azure DevOps but the unit tests fail due to not having the database there! Eventually one of the ideas I had was to mock it with SQLite during the unit testing process, but as I mentioned in my question, as I'm very new to this type of thing I'm just wondering if this is good practice or should I be doing something else – Webbarr Apr 4 at 20:16
  • 1
    @AdamWells the main problem I've had with attempting to use app.config for this is that when Azure builds my dll, it's done on a remote server that doesn't have any access to my dev / production environments. I can't see how to get around that, which is why I was thinking about using a mock db during testing – Webbarr Apr 5 at 20:45
0

You can use LocalDb for this, with a connection string like:

"Data Source = (localdb)\MSSqlLocalDB; Integrated Security = SSPI; Initial Catalog = YourDbName"

Then if you're using code first you can run your migrations, and populate the database however you want. If you need to tear down the database, I've found running this sql makes sure to kill it absolutely:

if db_id(YourDbName) is not null
begin
    ALTER DATABASE [YourDbName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [YourDbName]
end

I've used this in Azure Dev Ops to run automated tests that build and teardown a database before and after each test. You can try using sqlite, but it can be a lot more forgiving, so you may find things that work fine in your tests, fail when connecting to the "real" database. May as well use the real deal in your tests.

|improve this answer
  • Thanks for that, I'll be sure to test that out. I've not much experience using SQLite, completely forgot about LocalDb, I've used it briefly with some .NET Core stuff I've worked on in the past, bit more familiar with it. And thank you for the code snippet, I'll do some testing with it Monday morning! – Webbarr Apr 5 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.