如何正确使用多种形式的sqlite连接？ [英] How to properly use a sqlite connection in multiple forms?

问题描述

我的C＃应用​​程序中有两个类。

我在main.cs中启动并打开数据库连接

 public static void dbConnect（）
 {
 string dbPath = Application.StartupPath +/ database.db; 
 
 SQLiteConnection conn = new SQLiteConnection（Data Source =+ dbPath）; 
 
 conn.Open（）; 
 
} 
 

我有另一个表单Add.cs，我尝试填充一个组合框来自数据库。一旦我这样做，组合框填充，但在我关闭Add.cs并且main.cs中的函数尝试使用conn对象后，它会给我错误

无法访问已处置的对象对象名称：'sqliteconnection'

我尝试了什么：



这是我尝试使用第二种形式访问数据库的方式

 private void AddStore_Load （对象发送者，EventArgs e）
 {
使用（frmMain.conn）
 {
尝试
 {
 string query =SELECT * FROM routes; ; 
 SQLiteDataAdapter da = new SQLiteDataAdapter（query，frmMain.conn）; 
 DataSet ds = new DataSet（）; 
 da.Fill（ds，routes）; 
 cmbRoute.DisplayMember =route_name; 
 cmbRoute.ValueMember =route_id; 
 cmbRoute.DataSource = ds.Tables [routes]; 
} 
 catch（Exception ex）
 {
 MessageBox.Show（Error+ ex）; 
} 
} 
} 
 

由于这不起作用，我尝试创建一个具有相同细节的新连接字符串并重新连接到数据库并且工作原理

 private void AddStore_Load（object sender，EventArgs e）
 {
 string dbPath = Application.StartupPath +/ database.db; 
 using（SQLiteConnection conn = new SQLiteConnection（Data Source =+ dbPath））
 {
 try 
 {
 string query =SELECT * FROM routes; ; 
 SQLiteDataAdapter da = new SQLiteDataAdapter（query，frmMain.conn）; 
 DataSet ds = new DataSet（）; 
 da.Fill（ds，routes）; 
 cmbRoute.DisplayMember =route_name; 
 cmbRoute.ValueMember =route_id; 
 cmbRoute.DataSource = ds.Tables [routes]; 
} 
 catch（Exception ex）
 {
 MessageBox.Show（Error+ ex）; 
} 
} 
} 

但我对此方法持怀疑态度，因为这会产生多个连接你需要在需要时创建连接并在不需要时关闭它们，因为这样可以将ado.net连接池连接到同一个数据库。

解决方案

工作。因此，您更新的示例很好，但您可以通过在app config的connectionStrings部分中保存连接字符串来简化它，并使用配置类来访问它，而不是每次使用时对字符串进行硬编码。或者您可以创建一个静态帮助函数，它将返回代码连接

 public static class ConnectionHelper 
 {
 public static SQLiteConnection GetConnection（）
 {
 ...创建并返回连接对象
} 
} 

无论何时需要连接，只需致电



ConnectionHelper.GetConnection



和当你不再需要它时，调用.Close和\或.Dispose方法。这种类型的解决方案在这些日子里不受欢迎，但因为它不适合可测试的代码，所以最好使用配置管理器来获取连接字符串（googlec＃manage connection strings获取更多信息）。

问题是，一旦一个对象超出范围，它就不再可用，并且可能随时被垃圾收集器处理。如果你使用标题创建它作为的一部分，那么当它超出范围时它会被显式处理。



所以当你这样做时：

  public   static   void  dbConnect（）
 {
  string  dbPath = Application。 StartupPath +   / database.db; 
 
 SQLiteConnection conn =  new  SQLiteConnection（  Data Source = + dbPath）; 
 
 conn.Open（）; 
 
} 

对象 conn 仅在该方法的上下文中可用，除非您在其中保存对它的引用一个外部变量。

当你这样做时：

  private  < span class =code-keyword> void  AddStore_Load（ object  sender，EventArgs e）
 {
  string  dbPath = Application.StartupPath +   / database.db ; 
 使用（SQLiteConnection conn =  new  SQLiteConnection（  数据源= + dbPath））
 {
 ... 
} 
} 

conn 对象在结尾使用块显式处理，即使你这样做保留对它的引用。



SQL连接和命令对象是稀缺资源，您应该创建 - 使用 - 将它们视为理所当然 - 您不应该尝试使用连接从一个不同的形式，因为你完全不知道它处于什么状态 - 你甚至不知道是否已经显示该表格（除非你的代码非常有条理，在这种情况下，这是你的问题最少）。 br />


我要做的改变是有一个包含静态属性或方法的静态类，它返回一个有效的连接字符串（而不是硬编码它，它可以从一个配置文件）并在每次需要连接时使用该字符串。



（我更进一步，并有一个包含它们的外部存储：实例存储 - 在应用程序之间共享配置数据的简单方法 [ ^ ] - 但这可能对您的应用程序来说太过分了。）

I have two classes in my C# Application.
I initiate and open a db connection in main.cs

public static void dbConnect()
       {
           string dbPath = Application.StartupPath + "/database.db";

           SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath);

           conn.Open();

       }

I have another form Add.cs where I try to populate a combobox from the database. Once I do so, the combobox populates, but after I close Add.cs and a function in the main.cs tries to use the conn object, it gives me the error

Cannot access a disposed object object name: 'sqliteconnection'

What I have tried:

This is how I tried to access the db using the second form

private void AddStore_Load(object sender, EventArgs e)
       {
           using (frmMain.conn)
           {
               try
               {
                   string query = "SELECT * FROM routes;";
                   SQLiteDataAdapter da = new SQLiteDataAdapter(query, frmMain.conn);
                   DataSet ds = new DataSet();
                   da.Fill(ds, "routes");
                   cmbRoute.DisplayMember = "route_name";
                   cmbRoute.ValueMember = "route_id";
                   cmbRoute.DataSource = ds.Tables["routes"];
               }
               catch (Exception ex)
               {
                   MessageBox.Show("Error "+ex);
               }
           }
       }

Since this didn't work, I tried creating a new connection string with the same details and reconnecting to the db and that works

private void AddStore_Load(object sender, EventArgs e)
        {
            string dbPath = Application.StartupPath + "/database.db";
            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))
            {
                try
                {
                    string query = "SELECT * FROM routes;";
                    SQLiteDataAdapter da = new SQLiteDataAdapter(query, frmMain.conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds, "routes");
                    cmbRoute.DisplayMember = "route_name";
                    cmbRoute.ValueMember = "route_id";
                    cmbRoute.DataSource = ds.Tables["routes"];
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error "+ex);
                }
            }
        }

But I am skeptical about this method because this creates multiple connections to the same db.

解决方案

You're supposed to create connections when you need them and close them when you don't as this allows ado.net connection pooling to work. So your updated example is fine, however you can simplify it by holding the connection string in the connectionStrings section of the app config and use the configuration classes to access it rather than hard-coded the string every time you use it. Or you could create a static helper function that will return the code a connection

public static class ConnectionHelper
{
    public static SQLiteConnection GetConnection()
    {
       ... create and return the connection object
    }
}

Whenever you need a connection just call

ConnectionHelper.GetConnection

and when you no longer need it call the .Close and\or .Dispose method on it. This type of solution is frowned on these days however as it doesn't lend itself to testable code, you're better using the configuration manager to get the connection string instead (google "c# manage connection strings" for more info).

The problem is that once an object goes out of scope, it is no longer available, and may be Disposed by the garbage collector at any time. And if you create it as part of a using header then it is explicitly Disposed when it goes out of scope.

So when you do this:

public static void dbConnect()
       {
           string dbPath = Application.StartupPath + "/database.db";

           SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath);

           conn.Open();

       }

The object connis only available within the context of that method, unless you save a reference to it in an external variable.
And when you do this:

private void AddStore_Load(object sender, EventArgs e)
        {
            string dbPath = Application.StartupPath + "/database.db";
            using (SQLiteConnection conn = new SQLiteConnection("Data Source =" + dbPath))
            {
...
            }
        }

The conn object is explicitly Disposed at the end of the using block, even if you do retain a reference to it.

SQL connection and command objects are scarce resources, and you should create-use-Dispose them as a matter of course - you shouldn't be trying to use a connection from a different form because you have absolutely no idea what state it is in - you don't even know if that form has been displayed (unless your code is very baldy organised, in which case this is the least of your problems).

The change I would make is to have a static class containing a static property or method which returns a valid connections string (instead of hardcoding it, it can be read from a config file) and use that string each time you need a connection.

(I go further, and have an external store which contains them: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] - but that may be overkill for your application.)

这篇关于如何正确使用多种形式的sqlite连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！