如何构建 C# 控制台应用程序以有效使用 IDisposable 数据库资源？

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

提问by danieltalsky

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 usingthe database connection objects. Can someone propose a more correct structure for a console application? This is a problem I need to solve often.

这是我为 C# 控制台应用程序提出的（非常简化以说明问题空间）设计。数据库连接实现了 IDisposable，该解决方案不允许using数据库连接对象。有人可以为控制台应用程序提出更正确的结构吗？这是我需要经常解决的问题。

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:

有些人不明白为什么我想要它们作为成员变量。这是我将在 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?

看看我想如何在这个循环的生命周期中保持这些连接打开？

采纳答案by Dmitrii Lobanov

I think that the best solution is to extract main logic from Program class. The Program class is some kind of starter for primary work. And providing wrappers for SqlConnections is not a good idea indeed, because they are managed resources already, wrapping them is redundant. Thus my solution looks like this:

我认为最好的解决方案是从 Program 类中提取主要逻辑。Program 类是初级工作的某种启动器。并且为 SqlConnections 提供包装器确实不是一个好主意，因为它们已经是托管资源，包装它们是多余的。因此，我的解决方案如下所示：

class ProgramCore : IDisposable
{
    internal ProgramCore(string sourceConnectionString, string destinationConnectionString)
    {
        setUpConnections(sourceConnectionString, destinationConnectionString);
    }

    internal void Execute()
    {
        // do whatever you want
        doDatabaseWork();
        // do whatever you want
    }

    public void Dispose()
    {
        if (_sourceConnection != null)
            _sourceConnection.Dispose();
        if (_destinationConnection != null)
            _destinationConnection.Dispose();
    }

    private void setUpConnections(string sourceConnectionString, string destinationConnectionString)
    {
        _sourceConnection = new SQLiteConnection(sourceConnectionString);
        _destinationConnection = new SQLiteConnection(destinationConnectionString);
    }

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

    private SQLiteConnection _sourceConnection;
    private SQLiteConnection _destinationConnection;
}

class Program
{
    static void Main(string[] args)
    {
        // get connection strings from command line arguments
        string sourceConnectionString = GetConnectionString(args);
        string destinationConnectionString = GetConnectionString(args);

        using (ProgramCore core = new ProgramCore(sourceConnectionString, destinationConnectionString))
        {
            core.Execute();
        }
    }

    static string GetConnectionString(string[] args)
    {
        // provide parsing here
    }
}

回答by Scott Ferguson

How about writing a class that implements IDisposable.

编写一个实现 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.

然后在 IDisposable.Dispose 方法中，编写用于关闭数据库连接的拆卸代码。

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:

然后，从您的 Program 类的 main 方法中：

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

回答by Matthew Flaschen

Scott's answer is one way to do it. You could also consider using try{} finally instead?

斯科特的答案是一种方法。您也可以考虑使用 try{} finally 来代替？

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);
    try
    {
      shell.doDatabaseWork();
    }
    finally
    {
      if(sourceConnection != null)
        sourceConnection.Dispose();
      if(destinationConnection != null)
        destinationConnection.Dispose();
    }
}

回答by Chad Grant

Personally, I think you are over thinking this and the code samples in this thread are overly complex imho. I have no idea why people are implementing IDisposable on their Program class either since it's disposed when it exits.

就我个人而言，我认为你想多了，这个线程中的代码示例过于复杂恕我直言。我不知道为什么人们在他们的 Program 类上实现 IDisposable ，因为它在退出时被处理。

I can't think of a single reason to not use or why you cannot use the using(){} statement.

我想不出一个不使用的原因，或者为什么不能使用 using(){} 语句。

You want to open a Connection and hold it? Why? All the real connections are behind the scenes in .net connection pooling, so new'ing Connection objects is not a big deal. Just open and close as you need them and connection pooling handles all that behind the scenes.

你想打开一个连接并保持它吗？为什么？所有真正的连接都在 .net 连接池的幕后，所以新的 Connection 对象并不是什么大问题。只需根据需要打开和关闭它们，连接池会在幕后处理所有这些。

I edited my example to wrap it in a class so you can have your encapsulation as well.

我编辑了我的示例以将其包装在一个类中，以便您也可以进行封装。

class Program 
{
    static void Main(string[] args)
    {
        DBWorker worker = new DBWorker();
        worker.DoDatabaseWork();
    }
}

public class DBWorker 
{

    private void DoDatabaseWork()
    {
        using (SQLiteConnection sourceDB = new SQLiteConnection( GetConnectionString() ))
        {
            sourceDB.Open();
            using (SQLiteConnection destDB = new SQLiteConnection( GetConnectionString() ))
            {
                destDB.Open();
            }
        }
    }

}

回答by Joel Lucsy

Hmm, I see no one has mentioned doing it this way. You don't have to have the variables that are used in the usingdeclared locally.

嗯，我看没有人提到过这样做。您不必拥有在using本地声明中使用的变量。

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);

        using (sourceConnection = new SQLiteConnection(sourceConnectionString))
        using (destinationConnection = new SQLiteConnection(destinationConnectionString))
        {
            shell.doDatabaseWork();
        }
    }

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