Handeling passing of Sql connection used in multiple classes

Question

I am using a Sql-connection to create a temporary table and perform DML operations on it. The temporary table act as a staging area, for preparation of data to be loaded into a data warehouse. I am using a using statement, to properly dispose of the Sql-connection after the program finishes (or fails), like the following:

using IDbConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
sqlCon.Execute("SELECT 5 AS Number INTO ##TempTable");
sqlCon.Execute("INSERT INTO ##TempTable SELECT 10 AS Number");       

In reality my queries are more complex than this and takes some effort to prepare. I would like to divide the associated work into more than one class, to work in an object-oriented fashion. That could be done by creating the following classing :

public class TableCreator
{
    private IDbConnection SqlCon { get; set; }
    
    public TableCreator(IDbConnection conn)
    {
        SqlCon = conn;
    }

    public void CreateTable()
    {
        sqlCon.Execute("SELECT 5 AS Number INTO ##TempTable");
    }
}

public class DataInserter
{
    private IDbConnection SqlCon { get; set; }
    
    public DataInserter(IDbConnection conn)
    {
        SqlCon = conn;
    }

    public void InsertData()
    {
        sqlCon.Execute("INSERT INTO ##TempTable SELECT 10 AS Number");
    }
}

In the main method I can then simply instantiate these classes and run their methods like this:

using IDbConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
TableCreator creator = new TableCreator(sqlCon);
stager.CreateTable();
DataInserter inserter = new DataInserter (sqlCon);
inserter.InsertData();

This makes the code clean, and easy to read. But it's probably not a good idea to be passing open connections into other classes or methods. There probably exists a better way to achieve the same.

I hope that any of you can give me some advice on this challenge. Is there a way that I can split the execute commands into separate classes, without passing the connection around as a parameter?

Answer 1

I ended up creating a static class to hold the Sql-connection, since it can be reached from everywhere.

public static class SqlConnector
{
    public static IDbConnection SqlCon { get; set; }
}

Then I can rewrite the TableCreator class, to longer hold a Sql-connection like this:

public class TableCreator
{
    public void CreateTable()
    {
        SqlConnector.SqlCon.Execute("SELECT 5 AS Number INTO ##TempTable");
    }
}

And I can rewrite the main method like this:

TableCreator creator = new TableCreator();
DataInserter inserter = new DataInserter();

SqlConnector.SqlCon = new SqlConnection(connectionString);
using IDbConnection SqlCon = SqlConnector.SqlCon;
SqlConnector.SqlCon.Open();

creator.CreateTable();
inserter.InsertData();