loading...

db in c#— how does I do again?

kritner profile image Russ Hammett Originally published at Medium on ・4 min read

Database in c#— how does I do again?

Photo by Kevin Ku on Unsplash

It’s been a fair amount of time since I’ve done anything in the database, as at my job I’m working strictly in c# — no database, no front-end. With the government shut down (again! well oiled machine!) 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!

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:

Related:

Posted on by:

kritner profile

Russ Hammett

@kritner

Dad, programmer, faux-chef, gamer, book reader

Discussion

markdown guide
 

Great refresher! It's been so long since I started a new C# project with a DB that I forgot what the the setup is like.

It's too easy to just for the last place I queried and follow the pattern.

Also Dapper has spoiled me rotten, and I'm thankful for it!

 

It was indeed refreshing to get hands dirty with low level code to see how it works under the hood as working in higher abstraction sometimes make us forget.

& Yes, Dapper spoiled us much 😉

 

Seems like basic ADO.Net. Or did I miss something?

 

I think the goal of this article is to show the basic steps for querying in .NET.

I don't often setup new projects, so been it's nice to see everything in one place.