How do I structure a C# console application to efficiently use IDisposable database resources?

Question

Here's my proposed (very simplified to illustrate the problem space) design for a C# console application. The database connections implement IDisposable, and this solution doesn't allow for using the database connection objects. Can someone propose a more correct structure for a console application? This is a problem I need to solve often.

class Program 
{
    SQLiteConnection sourceConnection;
    SQLiteConnection destinationConnection;

    static void Main(string[] args)
    {
        Program shell = new Program();

        // get connection strings from command line arguments
        string sourceConnectionString = shell.getConnectionString(args);
        string destinationConnectionString = shell.getConnectionString(args);

        // call non-static methods that use
        shell.setUpConnections(sourceConnectionString, destinationConnectionString);

        shell.doDatabaseWork();
    }

    private void setUpConnections(string sourceConnectionString, string destinationConnectionString)
    {
        sourceConnection = new SQLiteConnection(sourceConnectionString);
        destinationConnection = new SQLiteConnection(destinationConnectionString);
    }

    private void doDatabaseWork()
    {
        // use the connections here
    }
}

Edit:

Some people can't figure out why I'd want them as member variables. Here's my use case (a little psuedocoded) of what would go in doDatabaseWork:

foreach (Row sourceRow in DBResultSet)
{
  string sourceXml = sourceRow.Columns["MyColumnName"].Value;
  string destinationXML = transformUsingXSLT(sourceXml);
  writeToDestination(destinationXml);
}

See how I'd want to keep these connections open for the life of this loop?

Answer 1

How about writing a class that implements IDisposable.

Inside your class constructor, you can instantiate your DB connections.

Then inside your IDisposable.Dispose Method, you write your tear down code for closing your DB connections.

Here is a code sample to demonstrate what I mean:

public class DBWrapper : IDisposable
{
    public SqlConnection Connection1 { get; set; }
    public SqlConnection Connection2 { get; set; }

    public DBWrapper()
    {
        Connection1 = new SqlConnection();
        Connection1.Open();
        Connection2 = new SqlConnection();
        Connection2.Open();
    }
    public void DoWork()
    {
        // Make your DB Calls here
    }

    public void Dispose()
    {
        if (Connection1 != null)
        {
            Connection1.Dispose();
        }
        if (Connection2 != null)
        {
            Connection2.Dispose();
        }
    }
}

And then, from within your main method of your Program class:

class Program
{
    static void Main(string[] args)
    {
        using (DBWrapper wrapper = new DBWrapper())
        {
            wrapper.DoWork();
        }
    }
}