Configurable SQL access method

Recently I was working on a lot of SQL server development activity. One such activity involved accessing 3 SQL server instances to compare and update data among them. One identity provider server acted at the top of the hierarchy. Two other services were there, one is a legacy system, and another one was a newly introduced service, which basically has to use the same data. The identity service handles the users and the authentication and authorization of the common entities these two services will be using.

All the common tables between the databases had very little differences in their schema. But regardless differences were there. There was a service implemented where a new entity is being introduced or an entity is changed, the other services would know of the change and then update them accordingly. But due to unforeseen circumstances, as often the case was in this scenario, the syncing mechanism would fail, ultimately leaving discrepancies between the data in the separate services. Therefore a separate syncing mechanism was needed. This mechanism, which materialized as a separate syncing tool, was to first compare the entries in the identity service tables and the client service tables to identify the disparities, and then perform the usual CRUD operations to match up the whole thing.

The requirements for the tool was roughly :

  1. It should not depend too much on the models of the separate programs.
  2. Should be expandable, in to other additional applications we add to the system in the future.
  3. Should be configurable.
  4. The data and the processing should be disjoint.

I really experienced the pains of not getting the requirements set and cleared early on while working on this. Lot of the requirements were communicated in different stages. So at first it was a simple husk of a program. I had to do a LOT of refactoring and polishing things up later on and adding more complexity in to the mix to make it more configurable.

Everything was simplified in each of the services with the use of Entity Framework. It’s really nice and easy to use, IF our models are kind of set and we work with a single data model. Besides even at the smallest of changes in the model, we have to do Entity Framework Migrations and rebuilding just to get it up and running again. Ain’t nobody got time for that for a simple tool like the one we built. So Entity Framework was not an option, So good old SQL transactions was the way to go. But additionally the tool was designed so that the data access method was also pluggable.

The perfect tool for the situation would have been a super tool where we only have to define the data sources for the databases and run the tool and it will sync. But achieving that superiority was blocked by the slight changes in the db schema. As I said the requirements for expanding the tool came later on. Before that a single tool which syncs the legacy application with the newly introduced identity provider was made. Then we had to introduce a common tool out of that. What I did was to introduce a common library while making separate tools for the client services, using the common library. While in the process it was my goal to get most of the common elements as possible to the common library.

Gradually I was able to make the common library as common as possible in a satisfactory level. All the logic for comparing and preparing the data for syncing was taken in to the common library. The separate tools should have their own sql queries to do the CRUD stuff.

Being the lazy guy I am, I didn’t want to put all the SQL queries in the code itself, and make it a hassle to rebuild the whole thing whenever there is a change in the schema and all. So I thought of a solution for that to read the sql queries from a separate file and make it easy to change the queries whenever possible. Also get all the queries to one place. A solution I used for this is to use resource strings. All the SQL queries was put in a resource file. This way all the queries are in one place and we can change them whenever we want, without having to rebuild the tool every time. Now I cannot argue it’s the best solution out there, that’s why SQL string provider was made pluggable. The method I used is just one of the methods from which to take the SQL queries. If a new better method comes along, one only needs to know about the query string keys used and use them to map in their way and introduce a new SQL string provider to use that method.

This way building the new separate tool was easy. I only had to introduce a new resource string file, which has all the necessary queries specific to those databases. That’s it. Now I’m thinking there would have been more to that, because I’m not really accepting myself that it was that much simple.

Anyway it worked. It seems like a simple solution that really made my day as a developer of those tools easier. I would love to discuss about the drawbacks and really know about a way that I would’ve done it better. So please feel free to point out the drawbacks and suggest better ways of doing that. Actually I’m looking forward to it. Thanks!

Advertisements
This entry was posted in .Net and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s