SQLite数据库未打开错误

Question

我有一个构造函数，它从SQL Server数据库中获取数据并将其放入本地SQLite数据库：

public ForemanController()
        {
            connectionString.DataSource = "dxdb02v";
            connectionString.InitialCatalog = "QTRAX4619410";
            connectionString.UserID = "tunnelld";
            connectionString.Password = "david";

            string queryString = "SELECT * FROM [QTRAXAdmin].[vwQT_Foreman]";

            List<Foreman> list;
            // Creates a SQL connection
            using (var connection = new SqlConnection(connectionString.ToString()))
            {
                using (var command = new SqlCommand(queryString, connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        list = new List<Foreman>();
                        while (reader.Read())
                        {
                            list.Add(new Foreman { ForeBadge = reader.GetString(0), ForeName = reader.GetString(1) });
                        }
                    }
                }
                connection.Close();
                allForeman = list.ToArray();
            }
            string deleteSQL = "DELETE FROM Foreman;";
            using (SQLiteConnection SQLconn1 = new SQLiteConnection(SQLiteConnectionString))
            {
                using (var command = new SQLiteCommand(deleteSQL, SQLconn1))
                {
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
            }

            using (SQLiteConnection SQLconn2 = new SQLiteConnection(SQLiteConnectionString))
            {
                SQLiteCommand cmd2 = SQLconn2.CreateCommand();
                foreach (Foreman row in allForeman)
                {
                    cmd2.CommandText = "INSERT INTO Foreman (ForeBadge, ForeName) VALUES (@param1, @param2);";
                    cmd2.Parameters.Add(new SQLiteParameter("@param1", row.ForeBadge));
                    cmd2.Parameters.Add(new SQLiteParameter("@param2", row.ForeName));
                    cmd2.ExecuteNonQuery();
                }
            }
        }

一切似乎都很正常，直到最后一个using语句：

            using (SQLiteConnection SQLconn2 = new SQLiteConnection(SQLiteConnectionString))
            {
                SQLiteCommand cmd2 = SQLconn2.CreateCommand();
                foreach (Foreman row in allForeman)
                {
                    cmd2.CommandText = "INSERT INTO Foreman (ForeBadge, ForeName) VALUES (@param1, @param2);";
                    cmd2.Parameters.Add(new SQLiteParameter("@param1", row.ForeBadge));
                    cmd2.Parameters.Add(new SQLiteParameter("@param2", row.ForeName));
                    cmd2.ExecuteNonQuery();
                }
            }

我遇到了这个错误：

- David Tunnell

---

很明显，你不能像打开其他东西一样打开它。你需要在某个地方使用SQLconn2.Open()或者cmd2.Connection.Open()。 - Ron Beyer

缺少SQLConn2.Open();。 - HadiRj

旁注 - 在类构造函数中做这么多事情是过度的。我会认真考虑将其作为一个单独的方法（或一组方法）来实现。构造函数应该轻量级，并且不应该抛出任何异常（因为它们很难调试）。 - D Stanley

Answer 1

那是因为你唯一忘记打开连接的地方。 添加这行代码：SQLconn2.Open();

- walther

---

这可能很明显，但为什么我不需要为DELETE语句打开连接？ - David Tunnell

你需要为DELETE语句打开它：command.Connection.Open(); - Tim

Answer 2

您忘记打开连接。

SQLConn2.Open();

- vmg