Why does Connection.Open cause a Winforms program to stop

Question

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.

Answer 1

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
}