为什么Connection.Open会导致Winforms程序停止

kuhbmx9i 于 2023-06-24 发布在其他

关注(0)|答案(1)|浏览(114)

我做了一个简单的控制台应用程序来执行sql查询到access数据库。我也在学习如何使用类，所以我决定尝试把程序作为一个类的函数。在这里。

using System.Data.OleDb;

namespace table
{
    class Program
    {
        static public string query;
        static public string path;
        static public table table1 = new table();
        static void Main(string[] args)
        {
            Console.WriteLine("Enter databse file location: ");
            path = Console.ReadLine();
            Console.WriteLine("Enter query: ");
            query = Console.ReadLine();
            table1.SqlRead (query, path);
            for (int i = 0; i < table1.data.Count; i += 1)
            {
                for (int j = 0; j< table1.data[i].Length; j += 1)
                {
                    Console .WriteLine (table1.data[i][j]);
                }
                Console.WriteLine();
            }
            Console.ReadKey();
        }

    }
    class table
    {
        List<string> tempDataList = new List<string>();
        public List<string[]> data = new List<string[]>(); // both dimensions zero-based
        public void SqlRead(string q, string p)
        {
            string connectionSting = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + p + "; User Id=admin;Password=;";
            using (OleDbConnection connection = new OleDbConnection(connectionSting))
            {
                OleDbCommand command = new OleDbCommand(q, connection);
                connection.Open();
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i += 1)
                        {
                            tempDataList.Add(reader[i].ToString());
                        }
                        string[] tempDataArray = tempDataList.ToArray();
                        tempDataList.Clear();
                        data.Add(tempDataArray);
                    }
                }
            }
        }
    }
   
}

这个控制台程序工作正常。这可能不是最好的解决方案，但它确实有效。下一步我想做的是得到一个带有某种UI的表单。我已经从一个按钮开始，一个用于查询的输入框，我将类复制到表单代码中。当我尝试使用table1时，我得到了“不一致的可访问性”。我通过将表类公开来解决这个问题。程序运行，但OleDb连接不工作。我在控制台版本中使用连接字符串中的Provider时遇到了一些问题，但我通过下载access数据库引擎并将Provider更改为Microsoft.ACE.OleDB.12.0来修复了这个问题。确保表格上的内容一致。当窗体运行时，我可以使用断点来判断它到达了行连接. open（），此时调试停止。表单关闭，一切都结束了，没有解释。我已经在这一行做了try catch，没有抛出任何异常。
这对我来说是一个奇怪的问题，我找不到其他人有同样的问题。我能想到的这两个程序之间的唯一区别是，对于控制台，我将测试数据库存储在解决方案文件夹中。我用的表格也是一样的。因此，我在表单解决方案文件夹中复制了一份数据库-没有更改。我注意到的另一个区别是，控制台程序的解决方案资源管理器列出了Dependencies、Properties、Program.cs、database.accdb;但表单版本列出了Dependencies、database.accdb、Properties、Program.cs。我不认为这意味着什么，但我不知道出了什么问题。
这两个程序都在VS2022中。targeting. net 7.0.
谢谢你的帮助

winforms

来源：https://stackoverflow.com/questions/76477962/why-does-connection-open-cause-a-winforms-program-to-stop

1条答案

按热度按时间

js5cn81o1#

数据类有一个巨大的问题，因为它迫使你构建容易受到SQL注入问题影响的代码。此外，它在结果和数组之间花费了大量时间和内存复制，而这可以更有效地完成。让我建议一个更好的模式：

public static class DB
{
    private static string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MyDB.accdb; User Id=admin;Password=;";

    // this is PRIVATE! Can't call it directly
    private IEnumerable<T> GetData(string sql, Action<OleDbParameterCollection> addParams, Func<IDataRecord, T> translate)
    {
        // Note the "using" statements here.
        // This means we don't have to call .Close() ourselves,
        //  and in fact even makes the code *safer*, so the connection
        //  is closed *even if an exception is thrown*. The original
        //  could leave connections hanging open in that situation.
        // It is also important to understand that, thanks to 
        // ADO.Net connection pooling, it really is better to
        // create a new connection object pretty much every time you
        // run a query.
        using var cn = new OleDbConnection(ConnectionString);
        using var cmd = new OleDbCommand(sql, cn);

        if (addParams is object) addParams(cmd.Parameters);

        cn.Open();
        using var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            yield return translate(rdr);
        }
    }

    // Each query in the app is listed in its own method below, with strongly-typed arguments.
    
    // As an example, say you have an Employee class and table, and want to get all employees with a user-provided last name. That would look like this:
    public static IEnumerable<Employee> GetEmployeesByLastName(string LastName)
    {
         return GetData("SELECT * FROM Employee WHERE LastName = ?", 
              p => p.Add("?", OleDbType.VarWChar, 30).Value = LastName,
              r => new Employee() {
                 EmployeeID = r["EmployeeID"],
                 FirstName = r["FirstName"],
                 LastName = r["LastName"],
                 Salary = r["Salary"]
              });
    }

    // As an app grows, you might move the queries into 
    // separate `internal` classes that can still use 
    // the `GetData()` method, or even into their own 
    // class library project in Visual Studio
}

赞(0）回复(0）举报 2023-06-24

我来回答

为什么Connection.Open会导致Winforms程序停止

1条答案

相关问题

热门标签

最新问答