I’ve been living a lie. For 20 years of professional software development, I had convinced myself that I didn’t enjoy database development.
I was wrong.
It turned out that I was getting stuck on the repetitive nature of database code and query generation, instead of the core problems we solve when building database applications.
In this article I’ll discuss why I hated working with databases as a C# developer, and the tools and libraries I found that proved me wrong.
What’s Wrong with Database Development?
It’s not that I thought databases weren’t important – they are critically important to the majority of professional software applications. Instead, I simply thought that databases were boring.
Specifically, I thought that the C# code I needed to write to parse results out of the database was tedious, brittle, and repetitive. I also felt that the exercise of listing individual columns in stored procedures was incredibly tedious. I felt like most of database development was tediously typing out in very granular detail exactly what I wanted to have happen and then double checking my logic for incorrect names.
In short, database development felt less like programming and more like copying and pasting and adapting to match the area’s syntax.
Fixing Database Development
So, what changed?
For me, the key thing I was missing was finding the right tools.
In this section I’ll walk you through one commercial tool (with a free version available) and two C# libraries that have moved database development from something I dreaded to something I love.
Eliminating Boilerplate Code with Dapper
When most people learn how to work with databases in C#, they wind up seeing code that looks something like this:
This stuff is just boring to write, and if you get the name or type of the column wrong, you get a runtime error. If you don’t handle nulls from the database correctly, you’ll get different errors.
As a result, you start to dread this type of boilerplate code.
Dapper makes this a lot better. Just follow the instructions to install Dapper and add a relevant using statement and then your code becomes much more manageable:
Here Dapper takes care of intelligently mapping from SQL data to individual objects based on the types and names of properties on the class and columns in the SQL result. This process is also customizable through Dapper’s more advanced features.
Dapper has a lot more to offer than what I’ve shown, and all of it is just fun code to write that keeps you focused on the SQL and how things flow together instead of trying to map every column manually.
A Note on Entity Framework
Many of the things I’m talking about with C# code, including working with the database and performing database migrations can be handled via Entity Framework, and Entity Framework is very closely related to LINQ which is a near and dear topic to my heart.
However, for whatever reason, my mind has trouble internalizing how Entity Framework translates to SQL – particularly on joins and migrations and that makes me nervous. While many love Entity Framework and I could grow to love it as well, for now I prefer Dapper because it keeps me close to the relevant SQL.
My issue with SQL isn’t with the actual language or its syntax, but rather with the repetitive nature of some of the tasks associated with it.
So, let’s talk about how to improve that process.
Simplifying SQL Code Generation with dbForge
Generating SQL queries can suck sometimes. The joins are fun, but remembering the exact column names and formatting your query in a standard manner can be enough of a chore that it slows down your pace and takes the fun out of SQL development.
dbForge changed that for me by giving me a dedicated integrated development environment that has very helpful code formatting and SQL statement generation features in addition to some more advanced capabilities (more on some of these later).
Instead of opening up SQL Server Management Studio (SSMS), I open up dbForge and can type in just a few words and select the exact columns I want from a list without having to slow down.
Note: If you don’t want to give up SQL Server Management Studio, devart also offers a separate product called SQL Tools that integrates with SSMS, as well as dedicated options for databases other than SQL Server.
I struggle sometimes to remember the exact syntax for creating and altering tables, and dbForge’s graphical editors help with that too so I can focus on my intent while also seeing the relevant SQL.
dbForge comes in a variety of editions (including a free one) and offers advanced features such as comparing database schemas and data, analyzing queries, indexes, and fragmentation, and generating migration scripts.
Beyond that, dbForge’s highest editions offer tools for documenting, versioning, and testing databases, though I have not played with these features as my usage has centered on the professional edition.
Automating Deployment with DbUp
While not specifically a huge pain, deploying database updates when code reaches an environment for the first time is a fairly standard need (which is part of why it’s baked into Entity Framework).
DbUp is a small database maintenance library that you can use to have .NET code automatically execute change scripts located in files or embedded in an assembly.
You can use this utility to easily write console applications to migrate databases on new releases or, if you’re more daring, you can bake this capability right into an ASP .NET application.
I use DbUp to check on server start if there are any database migrations included in the current application that have not been applied against that environment’s database. DbUp takes care of tracking what scripts have and have not been run against a database by managing a SchemaVersions table (pictured below) which lists all the relevant scripts a database has had run against it.
If a script has not been executed, DbUp executes it and adds an entry to SchemaVersions. DbUp also manages error handling on script execution and gives you opportunities to take further action on failures.
The code for this is relatively easy to read:
That’s it. Just call this (or something like it) in your Startup.cs’ ConfigureServices method and your application will automatically perform database migrations as needed on startup on any .SQL resource included in my project with a build action of Embedded Resource. Easy peasy.
Of course, this is not for everyone and my use case is more oriented for side projects than massive organizations, but it’s just darn fun to be able to automatically run new scripts like this as soon as the relevant code gets to the new environment.
Putting it All Together
Alright, so now that I’ve shown you my key tools, let me introduce you to my main flow for building database features.
Define Schema Changes in dbForge
I’ll start with whatever stored procedures or table alterations I need in dbForge.
In this example, I’m keeping it simple and saying that a new table I was about to add to my side project shouldn’t require a categoryId column after all.
I make a change in dbForge targeting a local database I use for drafting things.
I also use dbForge’s smart completion and formatting to quickly create a new stored procedure I’ll use later on:
Generating Change Scripts
Next, I use dbForge Professional Edition’s schema comparison features to compare this database with an integration testing database by going to the Comparison menu and selecting New Schema Comparisons and selecting the two databases to compare.
I compare the two databases and select the relevant operations I want to include in my scripts:
Once satisfied, I can choose to synchronize the target database to the source one either by generating a script or directly executing the changes against the database. In my case, I want a script and I choose to save it in a SQL folder inside my web application’s project.
Making the Script Discoverable to DbUp
Next, I move into Visual Studio and change the Build Action on the newly added script to Embedded Resource. This allows DbUp to detect the file at run time and carry out the database migration.
Defining My Data Model
I need a class for Dapper to be able to translate SQL query results into, so I make a new class to represent that data:
Querying Data with Dapper
From there, it’s not a lot of code to build a method that calls my new stored procedure and translates the results into my new class:
In Summary
So, here we have a fairly streamlined flow where I’m using dbForge’s streamlined capabilities to take the tedium out of building queries and generating changescripts, using Dapper to keep C# / SQL interactions minimal, and using DbUp to automate getting my changes out into the real world.
While not perfect for every project and environment, I’m finding myself having actual real-life fun working with SQL, and I wasn’t really sure that was possible at the beginning of this year.
What have you tried to make SQL fun recently? What are your tips and tricks?
