将平面 MDB 转换为 SQL 链接的 mdb 后代码停止工作

Question

我修改了代码以计算 mdb 文件内每个表中的记录。它完美地工作，直到它使用指向 SQL 服务器的链接表命中 MDB 文件。代码不能修改为直接指向SQL Server，它适用于就地软件升级场景。

如何修改此代码以使用常规表和链接表？

using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Collections;
using System.Data.Common;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            string[] databases = new string[3];
            databases[0] = "data.mdb";
            databases[1] = "chunk.mdb";
            databases[2] = "transactions.mdb";

            DateTime dt = DateTime.Now;
            string filename = "Results-" + dt.Hour + "_" + dt.Minute + "_" + dt.Second + ".txt";
            System.IO.StreamWriter file = new System.IO.StreamWriter(filename);



        foreach(string db in databases)
        {
                    file.WriteLine("##########BEGIN " + db + "##########\r\n");
                    Console.Write("Processing " + db + " Database . . . ");

                    string strAccessConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\financial\" + db;

                    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                    DataTable userTables = null;
                    DataSet myDataSet = new DataSet();
                    OleDbConnection conn = new OleDbConnection(strAccessConn);

                    using (DbConnection connection = factory.CreateConnection())
                    {
                        connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\financial\DATA\" + db;
                        string[] restrictions = new string[4];
                        restrictions[3] = "Table"; 

                        try
                        {
                            connection.Open();
                        }
                        catch
                        {
                            Console.WriteLine("Error opening MDB file. Please ensure it is in the correct location");
                        }

                        userTables = connection.GetSchema("Tables", restrictions);
                    }


                   ArrayList tables = new ArrayList();
                   for (int i=0; i < userTables.Rows.Count; i++)
                        tables.Add(userTables.Rows[i][2].ToString());


                   foreach (string tbl in tables)
                   {
                            string queryString = "SELECT COUNT(*) FROM " + "[" + tbl + "]";
                            OleDbCommand command = new OleDbCommand(queryString, conn);
                            command.Connection.Open();

                            try
                            {
                                int records = (int)command.ExecuteScalar();
                                file.WriteLine("{0,-45}" + records,tbl);
                            }
                            catch (OleDbException e)
                            {
                                Console.WriteLine(e + "OLEDB Exception Occured.");
                            }

                            command.Connection.Close();
                   }


                    file.WriteLine("\r\n##########END " + db + "##########");
                    Console.WriteLine("Done!\n");
         }

            file.Close();
            Console.WriteLine(@"All Databases Complete. Press any key to continue...");
            Console.Read();  // Press any key to continue...
            System.Diagnostics.Process.Start("notepad.exe",filename);

        }
    }
}

Answer 1

你好，我相信 ado.net 无法做到这一点，几年前我在 ado.net 1 中也遇到过同样的情况。你必须使用 ADOX。

这是有用的 ADOX 示例列表
http://allenbrowne.com/func-adox.html

ADOX 不会区分表和链接表。我会看一下我的硬盘，稍后我可能会发布一个示例。

您应该使用 ADOX 执行查询，这样就不会遇到与 ADO.net 相同的问题

Answer 2

您也可以使用 DAO 来完成此操作。