英文:
Why does Connection.Open cause a Winforms program to stop
问题
我已经翻译了您提供的文本，以下是翻译好的部分：
我已经创建了一个简单的控制台应用程序来执行针对Access数据库的SQL查询。我还在学习如何使用类等，所以我决定尝试将程序作为类的功能来实现。以下是代码示例。
```csharp
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 database 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 connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + p + "; User Id=admin;Password=;";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
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时，出现了“不一致的可访问性”错误。我通过将table类设置为public来解决了这个问题。程序可以运行，但是OleDb连接不起作用。在控制台版本中，连接字符串的Provider部分曾经困扰过我，但通过下载Access数据库引擎并将提供程序更改为Microsoft.ACE.OleDB.12.0来解决了这个问题。我确保在窗体中也使用了相同的提供程序。
当窗体运行时，使用断点我可以确定它到达了connection.Open()这一行，此时调试会突然停止。窗体关闭，一切都没有任何解释。我已经尝试在这一行上使用try-catch，但没有引发异常。
对我来说，这是一个奇怪的问题，我找不到有相同问题的其他人。我能想到这两个程序之间唯一的区别是，在控制台中，我将测试数据库存储在解决方案文件夹中。而对于窗体，我使用了相同的数据库。所以我在窗体解决方案文件夹中复制了数据库 - 但没有变化。我注意到的另一个区别是，控制台程序的解决方案资源管理器列出了Dependencies、Properties、Program.cs、database.accdb；而窗体版本列出了Dependencies、database.accdb、Properties、Program.cs。我不认为这有什么意义，但我不知道到底出了什么问题。
这两个程序都在VS2022中编写，目标是.NET 7.0。
感谢您的任何帮助。
如果您需要更多帮助或有其他问题，请随时提出。
<details>
<summary>英文:</summary>
I have made a simple console app to execute sql queries to an access database. I'm also just learning how to use classes ect so I decided to try and make the program as the function of a class. Here it is.
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);
}
}
}
}
}
}
This console program works fine. Might not be the best solution but it does work. The next step I wanted to do was to get a form with some kind of UI. I have started with a button, a input box for the query, and I copied the class into the form code. When I tried to use table1 I was given 'Inconsistent accessibility'. I worked around that by making the table class public. The program runs, but the OleDb connection does not work. I had some trouble with the Provider in the connection string in the console version but I fixed that by downloading the access database engine and changing the provider to Microsoft.ACE.OleDB.12.0 . Made sure that was the same in the forms.
When the form runs, I can tell using breakpoints that it reaches the line connection.Open(), at which point debugging just stops. the form closes, everything ends with no explanation. I have done try catch on the line and no exception is thrown.
Its such a strange problem to me and i can't find anyone else with the same. The only differences I can think of between the two programs is that for the console I had stored my test database in the solution folder. And for the forms I was using the same one. So I made a copy of the database in the form solution folder- no change. The one other difference I notice is that the solution explorer for the console program lists Dependencies, Properties, Program.cs, database.accdb ; but the forms version lists Dependencies, database.accdb, Properties, Program.cs. Don't think that means anything but I have no idea what is going wrong.
Both programs in Vs2022. Targeting .net 7.0.
Thanks for any help.
</details>
# 答案1
**得分**: 1
以下是您要求的中文翻译：
有一个与数据类相关的巨大问题，即它会强制您构建容易受到 SQL 注入问题影响的代码。此外，它会花费大量时间和内存在结果和数组之间进行复制，而这可以更高效地完成。让我提出一种更好的模式：
```csharp
public static class DB
{
private static string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MyDB.accdb; User Id=admin;Password=;";
// 这是私有的！不能直接调用它
private IEnumerable<T> GetData(string sql, Action<OleDbParameterCollection> addParams, Func<IDataRecord, T> translate)
{
// 注意这里的 "using" 语句。
// 这意味着我们不必自己调用 .Close()，
// 事实上，甚至使代码更加安全，因此连接
// 即使在抛出异常时也会被关闭。原始代码可能会在那种情况下保留连接处于打开状态。
// 还值得注意的是，由于
// ADO.Net 连接池的原因，实际上最好
// 几乎每次运行查询都创建一个新的连接对象。
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);
}
}
// 应用程序中的每个查询都在下面的自己的方法中列出，具有强类型参数。
// 例如，假设您有一个 Employee 类和表，并且想要获取所有具有用户提供的姓氏的员工。那将如下所示：
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"]
});
}
// 随着应用程序的增长，您可能会将查询移动到
// 单独的 `internal` 类中，仍然可以使用
// `GetData()` 方法，甚至将其移动到他们自己的
// 在 Visual Studio 中的类库项目中
}
英文:
There is a HUGE problem with the data class, in that it forces you to build code that is susceptible to sql injection issues. Additionally, it spends a lot of time and memory copying between the results and arrays that can be done more efficiently. Let me suggest a better pattern:
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
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论