db in c#-how does I do again?

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!

Database in c#— how does I do again?

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

Connection string

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:

.net core console application IOptions configuration

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:

"ConnectionStrings": {
    "SomethingAwesomeDatabase": "Server=MyLegitServer;Database=SomethingAwesome;User Id=MyDude;password=Totes$ecurePassw0rd;"
  }

IDbConnection

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?

public interface IDbConnectionFactory
{
 IDbConnection Get(string connectionString);
}

Note that in the above, IDbConnection is a built in type from the namespace System.Data.

And an implementation:

public class SqlDbConnectionFactory : IDbConnectionFactory
{
 public IDbConnection Get(string connectionString)
 {
  return new SqlConnection(connectionString);
 }
}

Simple enough — new up and return a SqlConnection using the connectionString given as a parameter.

IDbCommand

That’s pretty much all there is setup wise before we can start using this thing.

How to do that?

public interface ISuperService
{
 void DoStuff();
}

public class MySuperService : ISuperService
{
 private readonly IDbConnectionFactory _dbConnectionFactory;
 
 public MySuperService(IDbConnectionFactory dbConnectionFactory)
 {
  _dbConnectionFactory = dbConnectionFactory;
 }
 
 public void DoStuff()
 {
  using (var conn = _connectionFactory.Get("mySecretConnectionString"))
  {
   conn.Open();

   using (var cmd = conn.CreateCommand())
   {
    cmd.CommandText = "mySuperAwesomeStoredProc";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.AddParameter("someParam", 42);

    using (var reader = cmd.ExecuteReader())
    {
     while (reader.Read())
     {
      Console.WriteLine("Some record was returned");
     }
    }
   }
  }
 }
}

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:

public static class IDbCommandExtensions
{
 public static void AddParameter(this IDbCommand command, string parameterName, object value)
 {
  var param = command.CreateParameter();
  param.ParameterName = parameterName;
  param.Value = value ?? DBNull.Value;

  command.Parameters.Add(param);
 }
}

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:

gist

Related:

• .net core console application IOptions configuration
• Your favorite/most useful extension methods?
• Photo by @ikukevk on Unsplash.