With the government shut down (again!) I’m not working at the moment, so onto learning on stuff! I wanted to do something with a database, and I’ve gone done and forgotten how to work with them it’s been so long! At my job I’m working strictly in c# — no database, no front-end - so here we go!
I’ve used EF previously, but in this case I wanted to go a bit old-school. I threw together a little abstraction, which… seems… to work…? Maybe I can improve on this as I go, or if nothing else help someone else with it, or myself down the line. Feedback appreciated! :)
A few things are needed for working with a db connection:
- Connection string — the thing that specifies where and how to connect to the database
- DB Connection — the actual database connection
- DB Command — the command(s) to run against the database (include parameters and returns)
- There’s probably more, but that should be sufficient for my need
There’s a fair amount to connection strings — keeping them in the code, in configuration files, in environment variables, probably more. In my case I wanted to store them in a configuration file that can be swapped in and out based on the environment. For more information on how this swapping config files based on environment is done, see:
For our connection string we’ll need a few things:
- Server in which to connect
- database to use
- authentication method (integrated or user id/password)
We’ll put our example connection string in appsettings.json:
For our database connection, ideally we’d make use of a method that creates the connection for us, as to avoid some newing up of classes within our actual services. The only thing we actually need for newing up a db connection is the connection string. This makes our factory method’s signature very simple. Note I’m using the term factory here, I’m pretty sure this is a factory, but not positive, can someone correct me if wrong?
Note that in the above,
IDbConnection is a built in type from the namespace
And an implementation:
Simple enough — new up and return a
SqlConnection using the
connectionString given as a parameter.
That’s pretty much all there is setup wise before we can start using this thing.
How to do that?
It’s unfortunate how “nested” the code gets when working with using blocks, but using blocks are considered best practice (at least last I checked) when working with objects that implement
IDisposable (which they MUST to be used within a using block). The using block ensures the code is “disposed of” more effectively, and doesn’t leave it up to the consumer to remember to have to close connections and things of that nature.
One thing I like about the above, is we’re working with everything’s abstraction, rather than concrete — which again, helps (forces?) us to write more easily testable code.
Finally, we’re using an extension method
AddParameter since the interface does not provide a very “pretty” method of adding one.
The extension method looks like:
This helps us avoid a “few lines” for each parameter added — as they’re now condensed into a single line call. I don’t remember the specifics on when the
DbType needs to be specified on the
IDbDataParameter (the thing
command.CreateParameter returns) — but this should work, until it doesn’t. At that point you could throw together a few more extension methods to handle the additional properties that need setting on the parameter.
There’s a bit more to it when projecting the reader’s returned values into an object, more so than what you get from EF, but it’s pretty straight forward. Perhaps I’ll slap that in here at some point.
Gist of code: