【导读】作为C#开发者，是否曾为创建Access数据库时必须添加COM引用而烦恼？本文将详细介绍如何使用CATALOG_FILL函数无需任何COM组件即可轻松创建Access数据库，并提供完整的代码示例和详细解析。

创建Access数据库的传统困境

在C#开发中，创建Access数据库通常需要引用COM组件如ADOX、DAO或ADODB，这带来了一系列问题：

• 需要在项目中添加额外的COM引用
• 可能导致32位/64位兼容性问题
• 部署时需要确保目标机器上有相应的COM组件
• 引用COM组件可能使应用程序变得笨重

很多开发者不知道的是，实际上可以完全不使用COM引用，仅通过ADO.NET就能创建Access数据库，这就是本文要介绍的CATALOG_FILL技术。

CATALOG_FILL技术原理

CATALOG_FILL并不是一个显式的函数，而是我们利用Access数据库引擎的一个特性：当连接到一个空文件但使用正确的连接字符串时，Access引擎会自动将该文件"填充"为有效的数据库结构。

这一技术的核心步骤是：

1. 创建一个空文件
2. 使用正确的OleDb连接字符串尝试连接该文件
3. 首次连接会失败，但会将空文件转换为数据库结构
4. 再次连接即可成功并进行后续操作

完整代码示例：创建Access数据库

下面是一个完整的示例，展示如何使用CATALOG_FILL技术创建Access 2007及更高版本(.accdb)的数据库：

C#
using System.Data;
using System.Data.OleDb;

namespace AppAccessDb
{
    class Program
    {
        static void Main(string[] args)
        {
            // 记录开始时间，用于计算执行时间
            DateTime startTime = DateTime.Now;

            try
            {
                // 定义数据库文件路径
                string dbPath = @".\MyNewDatabase.accdb";

                // 确保目录存在
                string directory = Path.GetDirectoryName(dbPath);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                    Console.WriteLine($"已创建目录: {directory}");
                }

                // 如果数据库文件已存在，则删除
                if (File.Exists(dbPath))
                {
                    File.Delete(dbPath);
                    Console.WriteLine($"已删除现有数据库文件: {dbPath}");
                }

                // Access 2007及以上版本的连接字符串
                string connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbPath};";

                Console.WriteLine("开始创建数据库...");

                // 创建数据库文件
                CreateEmptyAccessDatabase(dbPath, connString);

                // 创建表结构
                CreateDatabaseSchema(connString);

                // 插入示例数据
                InsertSampleData(connString);

                // 查询验证数据
                QueryAndDisplayData(connString);

                // 计算并显示执行时间
                TimeSpan duration = DateTime.Now - startTime;
                Console.WriteLine($"\n数据库创建和初始化完成！耗时: {duration.TotalSeconds:F2} 秒");
                Console.WriteLine($"数据库路径: {dbPath}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"错误: {ex.Message}");
                if (ex.InnerException != null)
                {
                    Console.WriteLine($"内部错误: {ex.InnerException.Message}");
                }
            }

            Console.WriteLine("\n按任意键退出...");
            Console.ReadKey();
        }

        /// <summary>
        /// 使用CATALOG_FILL技术创建空的Access数据库
        /// </summary>
        /// <param name="dbPath">数据库文件路径</param>
        /// <param name="connString">连接字符串</param>
        static void CreateEmptyAccessDatabase(string dbPath, string connString)
        {
            try
            {
                // 确保目录存在
                string directory = Path.GetDirectoryName(dbPath);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                }

                // 如果文件已存在，则删除
                if (File.Exists(dbPath))
                {
                    File.Delete(dbPath);
                }

                // 动态创建ADOX.Catalog实例
                Type catalogType = Type.GetTypeFromProgID("ADOX.Catalog");
                if (catalogType == null)
                    throw new Exception("找不到ADOX.Catalog类型，请确保MDAC和ACE OLEDB已正确安装");

                dynamic catalog = Activator.CreateInstance(catalogType);
                catalog.Create(connString);

                Console.WriteLine("数据库创建成功！");
            }
            catch (Exception ex)
            {
                throw new Exception($"创建数据库时出错: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 创建数据库表结构
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        static void CreateDatabaseSchema(string connString)
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(connString))
                {
                    connection.Open();
                    Console.WriteLine("\n开始创建表结构...");

                    // 创建用户表
                    string createUserTableSql = @"
                        CREATE TABLE [用户表] (
                            [用户ID] AUTOINCREMENT PRIMARY KEY, 
                            [用户名] TEXT(50) NOT NULL, 
                            [密码] TEXT(50) NOT NULL, 
                            [邮箱] TEXT(100), 
                            [手机号] TEXT(20),
                            [注册日期] DATETIME DEFAULT NOW(),
                            [最后登录] DATETIME,
                            [用户状态] INTEGER DEFAULT 1,
                            [备注] MEMO
                        )";
                    ExecuteNonQuery(connection, createUserTableSql, "用户表");

                    // 创建产品分类表
                    string createCategoryTableSql = @"
                        CREATE TABLE [产品分类表] (
                            [分类ID] AUTOINCREMENT PRIMARY KEY, 
                            [分类名称] TEXT(50) NOT NULL, 
                            [父分类ID] INTEGER,
                            [分类描述] TEXT(255),
                            [排序号] INTEGER DEFAULT 0,
                            [创建时间] DATETIME DEFAULT NOW()
                        )";
                    ExecuteNonQuery(connection, createCategoryTableSql, "产品分类表");

                    // 创建产品表
                    string createProductTableSql = @"
                        CREATE TABLE [产品表] (
                            [产品ID] AUTOINCREMENT PRIMARY KEY, 
                            [产品编码] TEXT(30) NOT NULL,
                            [产品名称] TEXT(100) NOT NULL, 
                            [分类ID] TEXT(255),
                            [产品描述] TEXT(255), 
                            [规格] TEXT(50),
                            [单位] TEXT(10),
                            [价格] CURRENCY, 
                            [成本] CURRENCY,
                            [库存数量] INTEGER DEFAULT 0, 
                            [上架状态] BIT DEFAULT TRUE,
                            [上架日期] DATETIME,
                            [创建时间] DATETIME DEFAULT NOW(),
                            [更新时间] DATETIME
                        )";
                    ExecuteNonQuery(connection, createProductTableSql, "产品表");

                    // 创建订单表
                    string createOrderTableSql = @"
                        CREATE TABLE [订单表] (
                            [订单ID] AUTOINCREMENT PRIMARY KEY, 
                            [订单编号] TEXT(30) NOT NULL,
                            [用户ID] INTEGER, 
                            [订单日期] DATETIME DEFAULT NOW(), 
                            [收货人] TEXT(50),
                            [联系电话] TEXT(20),
                            [收货地址] TEXT(255),
                            [订单状态] INTEGER DEFAULT 0, 
                            [支付状态] INTEGER DEFAULT 0,
                            [支付方式] INTEGER,
                            [支付时间] DATETIME,
                            [发货时间] DATETIME,
                            [完成时间] DATETIME,
                            [订单金额] CURRENCY,
                            [实付金额] CURRENCY,
                            [备注] TEXT(255)
                        )";
                    ExecuteNonQuery(connection, createOrderTableSql, "订单表");

                    // 创建订单明细表
                    string createOrderDetailTableSql = @"
                        CREATE TABLE [订单明细表] (
                            [明细ID] AUTOINCREMENT PRIMARY KEY, 
                            [订单ID] INTEGER, 
                            [产品ID] INTEGER, 
                            [产品名称] TEXT(100),
                            [数量] INTEGER, 
                            [单价] CURRENCY,
                            [小计] CURRENCY,
                            [备注] TEXT(255)
                        )";
                    ExecuteNonQuery(connection, createOrderDetailTableSql, "订单明细表");

                    // 创建日志表
                    string createLogTableSql = @"
                        CREATE TABLE [系统日志表] (
                            [日志ID] AUTOINCREMENT PRIMARY KEY,
                            [用户ID] INTEGER,
                            [用户名] TEXT(50),
                            [操作类型] INTEGER,
                            [操作描述] TEXT(255),
                            [操作时间] DATETIME DEFAULT NOW(),
                            [IP地址] TEXT(50),
                            [设备信息] TEXT(255)
                        )";
                    ExecuteNonQuery(connection, createLogTableSql, "系统日志表");

                    // 创建设置表
                    string createSettingsTableSql = @"
                        CREATE TABLE [系统设置表] (
                            [设置ID] AUTOINCREMENT PRIMARY KEY,
                            [设置项] TEXT(50) NOT NULL,
                            [设置值] TEXT(255),
                            [设置描述] TEXT(255),
                            [更新时间] DATETIME DEFAULT NOW()
                        )";
                    ExecuteNonQuery(connection, createSettingsTableSql, "系统设置表");

                    // 创建用户表唯一索引
                    string createUserIndexSql = @"
                        CREATE UNIQUE INDEX [IDX_用户名] ON [用户表] ([用户名])";
                    ExecuteNonQuery(connection, createUserIndexSql, "用户名唯一索引");

                    // 创建产品表索引
                    string createProductIndexSql = @"
                        CREATE INDEX [IDX_产品_分类] ON [产品表] ([分类ID])";
                    ExecuteNonQuery(connection, createProductIndexSql, "产品分类索引");

                    // 创建订单索引
                    string createOrderIndexSql = @"
                        CREATE UNIQUE INDEX [IDX_订单编号] ON [订单表] ([订单编号])";
                    ExecuteNonQuery(connection, createOrderIndexSql, "订单编号索引");

                    Console.WriteLine("所有表结构创建完成！");
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"创建表结构时出错: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 执行非查询SQL语句
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="objectName">对象名称（用于日志显示）</param>
        static void ExecuteNonQuery(OleDbConnection connection, string sql, string objectName)
        {
            try
            {
                using (OleDbCommand command = new OleDbCommand(sql, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine($"- 已创建{objectName}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"- 创建{objectName}失败: {ex.Message}");
                throw;
            }
        }

        static void InsertSampleData(string connString)
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(connString))
                {
                    connection.Open();
                    Console.WriteLine("\n开始插入示例数据...");

                    // 插入用户数据
                    int userCount = InsertUsers(connection);
                    Console.WriteLine($"成功插入 {userCount} 条用户数据");

                    // 插入产品分类数据
                    int categoryCount = InsertCategories(connection);
                    Console.WriteLine($"成功插入 {categoryCount} 条产品分类数据");

                    // 插入产品数据
                    int productCount = InsertProducts(connection);
                    Console.WriteLine($"成功插入 {productCount} 条产品数据");

                    // 插入订单数据
                    int orderCount = InsertOrders(connection);
                    Console.WriteLine($"成功插入 {orderCount} 条订单数据");

                    // 插入订单明细数据
                    int detailCount = InsertOrderDetails(connection);
                    Console.WriteLine($"成功插入 {detailCount} 条订单明细数据");

                    // 插入系统设置数据
                    int settingsCount = InsertSettings(connection);
                    Console.WriteLine($"成功插入 {settingsCount} 条系统设置数据");

                    // 插入系统日志数据
                    int logCount = InsertLogs(connection);
                    Console.WriteLine($"成功插入 {logCount} 条系统日志数据");

                    Console.WriteLine($"示例数据插入完成！总共插入：{userCount}个用户、{categoryCount}个分类、{productCount}个产品、{orderCount}个订单、{detailCount}条订单明细、{settingsCount}条系统设置、{logCount}条日志");
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"插入示例数据时出错: {ex.Message}", ex);
            }
        }

        // 用户数据插入方法
        static int InsertUsers(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 用户数据
                var users = new[]
                {
            new { Username = "admin", Password = "e10adc3949ba59abbe56e057f20f883e", Email = "admin@example.com", Phone = "13800138000", RegisterDate = new DateTime(2023, 1, 1, 8, 30, 0), Status = 1 },
            new { Username = "zhangsan", Password = "e10adc3949ba59abbe56e057f20f883e", Email = "zhangsan@example.com", Phone = "13900139000", RegisterDate = new DateTime(2023, 1, 1, 8, 30, 0), Status = 1 },
            new { Username = "lisi", Password = "e10adc3949ba59abbe56e057f20f883e", Email = "lisi@example.com", Phone = "13700137000", RegisterDate = new DateTime(2023, 1, 1, 8, 30, 0), Status = 1 },
            new { Username = "wangwu", Password = "e10adc3949ba59abbe56e057f20f883e", Email = "wangwu@example.com", Phone = "13600136000", RegisterDate = new DateTime(2023, 1, 1, 8, 30, 0), Status = 0 },
            new { Username = "test", Password = "e10adc3949ba59abbe56e057f20f883e", Email = "test@example.com", Phone = "13500135000", RegisterDate = new DateTime(2023, 1, 1, 8, 30, 0), Status = 1 }
        };

                string sql = @"INSERT INTO [用户表] ([用户名], [密码], [邮箱], [手机号], [注册日期], [用户状态]) VALUES (?, ?, ?, ?, ?, ?)";

                foreach (var user in users)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", user.Username);
                        cmd.Parameters.AddWithValue("?", user.Password);
                        cmd.Parameters.AddWithValue("?", user.Email);
                        cmd.Parameters.AddWithValue("?", user.Phone);
                        cmd.Parameters.AddWithValue("?", user.RegisterDate);
                        cmd.Parameters.AddWithValue("?", user.Status);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入用户数据时出错: {ex.Message}");
                return count;
            }
        }

        // 产品分类数据插入方法
        static int InsertCategories(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 产品分类数据
                var categories = new dynamic[]
                {
                new { Name = "电子产品", ParentId = (object)DBNull.Value, Description = "各类电子产品", SortOrder = 1 },
                new { Name = "手机", ParentId = (object)1, Description = "智能手机及配件", SortOrder = 1 },
                new { Name = "电脑", ParentId = (object)1, Description = "笔记本电脑、台式机等", SortOrder = 2 },
                new { Name = "家用电器", ParentId = (object)DBNull.Value, Description = "家用电器产品", SortOrder = 2 },
                new { Name = "厨房电器", ParentId = (object)4, Description = "厨房使用的电器产品", SortOrder = 1 },
                new { Name = "生活电器", ParentId = (object)4, Description = "日常生活使用的电器", SortOrder = 2 }
                };

                string sql = @"INSERT INTO [产品分类表] ([分类名称], [父分类ID], [分类描述], [排序号]) VALUES (?, ?, ?, ?)";

                foreach (var category in categories)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", category.Name);
                        cmd.Parameters.AddWithValue("?", category.ParentId);
                        cmd.Parameters.AddWithValue("?", category.Description);
                        cmd.Parameters.AddWithValue("?", category.SortOrder);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入产品分类数据时出错: {ex.Message}");
                return count;
            }
        }

        // 产品数据插入方法
        static int InsertProducts(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 产品数据
                var products = new[]
                {
            new { Code = "SP001", Name = "智能手机X1", CategoryId = 2, Description = "新一代智能手机，搭载最新处理器", Spec = "6.5英寸", Unit = "台", Price = 3999, Cost = 2800, Stock = 100, Status = true, ListDate = new DateTime(2023, 2, 1) },
            new { Code = "SP002", Name = "智能手机X2 Pro", CategoryId = 2, Description = "旗舰智能手机，高清摄像头", Spec = "6.7英寸", Unit = "台", Price = 5999, Cost = 3800, Stock = 50, Status = true, ListDate = new DateTime(2023, 2, 5) },
            new { Code = "NB001", Name = "轻薄笔记本Pro", CategoryId = 3, Description = "轻薄商务笔记本，长续航", Spec = "14英寸", Unit = "台", Price = 6999, Cost = 5200, Stock = 30, Status = true, ListDate = new DateTime(2023, 2, 10) },
            new { Code = "NB002", Name = "游戏本GTX", CategoryId = 3, Description = "高性能游戏笔记本，独立显卡", Spec = "15.6英寸", Unit = "台", Price = 8999, Cost = 6800, Stock = 20, Status = true, ListDate = new DateTime(2023, 2, 15) },
            new { Code = "KA001", Name = "智能电饭煲", CategoryId = 5, Description = "多功能智能电饭煲，可预约", Spec = "5L", Unit = "个", Price = 699, Cost = 320, Stock = 200, Status = true, ListDate = new DateTime(2023, 2, 20) },
            new { Code = "KA002", Name = "破壁料理机", CategoryId = 5, Description = "多功能破壁料理机，可做各种食谱", Spec = "2.5L", Unit = "台", Price = 1299, Cost = 650, Stock = 150, Status = true, ListDate = new DateTime(2023, 2, 25) },
            new { Code = "LA001", Name = "智能扫地机器人", CategoryId = 6, Description = "全自动智能扫地机器人，激光导航", Spec = "标准", Unit = "台", Price = 1999, Cost = 1100, Stock = 80, Status = true, ListDate = new DateTime(2023, 3, 1) },
            new { Code = "LA002", Name = "空气净化器", CategoryId = 6, Description = "高效空气净化器，HEPA滤网", Spec = "适用30㎡", Unit = "台", Price = 1599, Cost = 800, Stock = 60, Status = true, ListDate = new DateTime(2023, 3, 5) },
            new { Code = "LA003", Name = "无线吸尘器", CategoryId = 6, Description = "大吸力无线吸尘器，续航长", Spec = "标准", Unit = "台", Price = 2299, Cost = 1200, Stock = 40, Status = false, ListDate = new DateTime(2023, 3, 10) },
            new { Code = "SP003", Name = "智能手表", CategoryId = 2, Description = "智能手表，健康监测功能", Spec = "标准", Unit = "个", Price = 1299, Cost = 600, Stock = 120, Status = true, ListDate = new DateTime(2023, 3, 15) }
        };

                string sql = @"INSERT INTO [产品表] ([产品编码], [产品名称], [分类ID], [产品描述], [规格], [单位], [价格], [成本], [库存数量], [上架状态], [上架日期]) 
                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                foreach (var product in products)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", product.Code);
                        cmd.Parameters.AddWithValue("?", product.Name);
                        cmd.Parameters.AddWithValue("?", product.CategoryId);
                        cmd.Parameters.AddWithValue("?", product.Description);
                        cmd.Parameters.AddWithValue("?", product.Spec);
                        cmd.Parameters.AddWithValue("?", product.Unit);
                        cmd.Parameters.AddWithValue("?", product.Price);
                        cmd.Parameters.AddWithValue("?", product.Cost);
                        cmd.Parameters.AddWithValue("?", product.Stock);
                        cmd.Parameters.AddWithValue("?", product.Status);
                        cmd.Parameters.AddWithValue("?", product.ListDate);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入产品数据时出错: {ex.Message}");
                return count;
            }
        }

        // 订单数据插入方法
        static int InsertOrders(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 订单数据
                var orders = new[]
                {
            new { OrderNo = "ORD20230305001", UserId = 2, OrderDate = new DateTime(2023, 3, 5, 10, 20, 30), Receiver = "张三", Phone = "13900139000", Address = "北京市海淀区XX路XX号", Status = 3, PayStatus = 1, PayMethod = 1, PayTime = new DateTime(2023, 3, 5, 10, 25, 40), Amount = 4698, ActualAmount = 4698, Remark = "" },
            new { OrderNo = "ORD20230310002", UserId = 3, OrderDate = new DateTime(2023, 3, 10, 14, 30, 22), Receiver = "李四", Phone = "13700137000", Address = "上海市浦东新区XX路XX号", Status = 3, PayStatus = 1, PayMethod = 2, PayTime = new DateTime(2023, 3, 10, 14, 35, 12), Amount = 6999, ActualAmount = 6999, Remark = "工作日送货" },
            new { OrderNo = "ORD20230315003", UserId = 2, OrderDate = new DateTime(2023, 3, 15, 9, 10, 5), Receiver = "张三", Phone = "13900139000", Address = "北京市海淀区XX路XX号", Status = 2, PayStatus = 1, PayMethod = 1, PayTime = new DateTime(2023, 3, 15, 9, 15, 20), Amount = 3598, ActualAmount = 3598, Remark = "" },
            new { OrderNo = "ORD20230320004", UserId = 5, OrderDate = new DateTime(2023, 3, 20, 16, 40, 18), Receiver = "测试用户", Phone = "13500135000", Address = "广州市天河区XX路XX号", Status = 1, PayStatus = 1, PayMethod = 3, PayTime = new DateTime(2023, 3, 20, 16, 45, 30), Amount = 8999, ActualAmount = 8699, Remark = "优惠300元" }
        };

                string sql = @"INSERT INTO [订单表] ([订单编号], [用户ID], [订单日期], [收货人], [联系电话], [收货地址], [订单状态], [支付状态], [支付方式], [支付时间], [订单金额], [实付金额], [备注]) 
                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                foreach (var order in orders)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", order.OrderNo);
                        cmd.Parameters.AddWithValue("?", order.UserId);
                        cmd.Parameters.AddWithValue("?", order.OrderDate);
                        cmd.Parameters.AddWithValue("?", order.Receiver);
                        cmd.Parameters.AddWithValue("?", order.Phone);
                        cmd.Parameters.AddWithValue("?", order.Address);
                        cmd.Parameters.AddWithValue("?", order.Status);
                        cmd.Parameters.AddWithValue("?", order.PayStatus);
                        cmd.Parameters.AddWithValue("?", order.PayMethod);
                        cmd.Parameters.AddWithValue("?", order.PayTime);
                        cmd.Parameters.AddWithValue("?", order.Amount);
                        cmd.Parameters.AddWithValue("?", order.ActualAmount);
                        cmd.Parameters.AddWithValue("?", order.Remark);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入订单数据时出错: {ex.Message}");
                return count;
            }
        }

        // 订单明细数据插入方法
        static int InsertOrderDetails(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 订单明细数据
                var details = new[]
                {
            new { OrderId = 1, ProductId = 1, ProductName = "智能手机X1", Quantity = 1, Price = 3999, Subtotal = 3999, Remark = "" },
            new { OrderId = 1, ProductId = 5, ProductName = "智能电饭煲", Quantity = 1, Price = 699, Subtotal = 699, Remark = "" },
            new { OrderId = 2, ProductId = 3, ProductName = "轻薄笔记本Pro", Quantity = 1, Price = 6999, Subtotal = 6999, Remark = "" },
            new { OrderId = 3, ProductId = 7, ProductName = "智能扫地机器人", Quantity = 1, Price = 1999, Subtotal = 1999, Remark = "" },
            new { OrderId = 3, ProductId = 5, ProductName = "智能电饭煲", Quantity = 1, Price = 699, Subtotal = 699, Remark = "" },
            new { OrderId = 3, ProductId = 10, ProductName = "智能手表", Quantity = 1, Price = 1299, Subtotal = 899, Remark = "促销价" },
            new { OrderId = 4, ProductId = 4, ProductName = "游戏本GTX", Quantity = 1, Price = 8999, Subtotal = 8699, Remark = "优惠300元" }
        };

                string sql = @"INSERT INTO [订单明细表] ([订单ID], [产品ID], [产品名称], [数量], [单价], [小计], [备注]) VALUES (?, ?, ?, ?, ?, ?, ?)";

                foreach (var detail in details)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", detail.OrderId);
                        cmd.Parameters.AddWithValue("?", detail.ProductId);
                        cmd.Parameters.AddWithValue("?", detail.ProductName);
                        cmd.Parameters.AddWithValue("?", detail.Quantity);
                        cmd.Parameters.AddWithValue("?", detail.Price);
                        cmd.Parameters.AddWithValue("?", detail.Subtotal);
                        cmd.Parameters.AddWithValue("?", detail.Remark);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入订单明细数据时出错: {ex.Message}");
                return count;
            }
        }

        // 系统设置数据插入方法
        static int InsertSettings(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 系统设置数据
                var settings = new[]
                {
            new { Key = "系统名称", Value = "企业进销存管理系统", Description = "系统显示名称" },
            new { Key = "公司名称", Value = "XX科技有限公司", Description = "公司名称" },
            new { Key = "联系电话", Value = "400-123-4567", Description = "客服电话" },
            new { Key = "电子邮箱", Value = "service@example.com", Description = "客服邮箱" },
            new { Key = "系统版本", Value = "v1.0.0", Description = "当前系统版本号" }
        };

                string sql = @"INSERT INTO [系统设置表] ([设置项], [设置值], [设置描述]) VALUES (?, ?, ?)";

                foreach (var setting in settings)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", setting.Key);
                        cmd.Parameters.AddWithValue("?", setting.Value);
                        cmd.Parameters.AddWithValue("?", setting.Description);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入系统设置数据时出错: {ex.Message}");
                return count;
            }
        }

        // 系统日志数据插入方法
        static int InsertLogs(OleDbConnection connection)
        {
            int count = 0;

            try
            {
                // 系统日志数据
                var logs = new[]
                {
            new { UserId = 1, Username = "admin", OperationType = 1, Description = "管理员登录系统", OperationTime = new DateTime(2023, 3, 25, 8, 30, 0), IpAddress = "192.168.1.100" },
            new { UserId = 1, Username = "admin", OperationType = 2, Description = "添加新产品：智能手表", OperationTime = new DateTime(2023, 3, 25, 9, 15, 20), IpAddress = "192.168.1.100" },
            new { UserId = 1, Username = "admin", OperationType = 3, Description = "修改产品价格：智能手机X1", OperationTime = new DateTime(2023, 3, 25, 10, 20, 30), IpAddress = "192.168.1.100" },
            new { UserId = 2, Username = "zhangsan", OperationType = 1, Description = "用户登录系统", OperationTime = new DateTime(2023, 3, 25, 13, 40, 50), IpAddress = "192.168.1.101" },
            new { UserId = 2, Username = "zhangsan", OperationType = 4, Description = "提交新订单：ORD20230325005", OperationTime = new DateTime(2023, 3, 25, 14, 5, 10), IpAddress = "192.168.1.101" }
        };

                string sql = @"INSERT INTO [系统日志表] ([用户ID], [用户名], [操作类型], [操作描述], [操作时间], [IP地址]) VALUES (?, ?, ?, ?, ?, ?)";

                foreach (var log in logs)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("?", log.UserId);
                        cmd.Parameters.AddWithValue("?", log.Username);
                        cmd.Parameters.AddWithValue("?", log.OperationType);
                        cmd.Parameters.AddWithValue("?", log.Description);
                        cmd.Parameters.AddWithValue("?", log.OperationTime);
                        cmd.Parameters.AddWithValue("?", log.IpAddress);

                        count += cmd.ExecuteNonQuery();
                    }
                }

                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入系统日志数据时出错: {ex.Message}");
                return count;
            }
        }

        /// <summary>
        /// 查询并显示数据验证结果
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        static void QueryAndDisplayData(string connString)
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(connString))
                {
                    connection.Open();
                    Console.WriteLine("\n验证数据库数据...");

                    // 获取数据库中的表列表
                    DataTables tables = GetTablesList(connection);
                    Console.WriteLine($"数据库中共有 {tables.Count} 个表");
                    foreach (var table in tables)
                    {
                        Console.WriteLine($"- 表名: {table.TableName}，记录数: {table.RecordCount}");
                    }

                    // 显示部分产品数据
                    Console.WriteLine("\n产品表示例数据:");
                    string productQuery = "SELECT TOP 3 [产品ID], [产品编码], [产品名称], [价格], [库存数量] FROM [产品表] ORDER BY [产品ID]";
                    using (OleDbCommand command = new OleDbCommand(productQuery, connection))
                    {
                        using (OleDbDataReader reader = command.ExecuteReader())
                        {
                            Console.WriteLine("ID  | 编码   | 产品名称        | 价格  | 库存");
                            Console.WriteLine("----|--------|-----------------|-------|------");

                            while (reader.Read())
                            {
                                Console.WriteLine(
                                    $"{reader["产品ID"],-4}| " +
                                    $"{reader["产品编码"],-8}| " +
                                    $"{reader["产品名称"],-16}| " +
                                    $"{reader["价格"]:C,-7}| " +
                                    $"{reader["库存数量"]}");
                            }
                        }
                    }

                    Console.WriteLine("\n用户订单汇总:");
                    string orderSummaryQuery = @"
                SELECT u.[用户名], 
                       COUNT(o.[订单ID]) AS [订单数], 
                       SUM(o.[实付金额]) AS [总金额]
                FROM [用户表] u
                LEFT JOIN [订单表] o ON u.[用户ID] = o.[用户ID]
                GROUP BY u.[用户ID], u.[用户名]
                ORDER BY SUM(o.[实付金额]) DESC";

                    using (OleDbCommand command = new OleDbCommand(orderSummaryQuery, connection))
                    {
                        try
                        {
                            using (OleDbDataReader reader = command.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    Console.WriteLine("用户名  | 订单数 | 总金额");
                                    Console.WriteLine("--------|--------|----------");

                                    while (reader.Read())
                                    {
                                        string username = reader["用户名"].ToString();
                                        int orderCount = reader["订单数"] is DBNull ? 0 : Convert.ToInt32(reader["订单数"]);
                                        decimal totalAmount = reader["总金额"] is DBNull ? 0 : Convert.ToDecimal(reader["总金额"]);

                                        Console.WriteLine(
                                            $"{username,-8}| " +
                                            $"{orderCount,-8}| " +
                                            $"{totalAmount:C}");
                                    }
                                }
                                else
                                {
                                    Console.WriteLine("没有找到任何订单数据");
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"查询订单汇总时出错: {ex.Message}");
                            Console.WriteLine($"SQL语句: {orderSummaryQuery}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询数据时出错: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取数据库中的表列表及记录数
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <returns>表信息列表</returns>
        static DataTables GetTablesList(OleDbConnection connection)
        {
            DataTables tables = new DataTables();

            // 获取所有用户表（排除系统表）
            DataTable schemasTable = connection.GetSchema("Tables", new string[] { null, null, null, "TABLE" });

            foreach (DataRow row in schemasTable.Rows)
            {
                string tableName = row["TABLE_NAME"].ToString();

                // 获取表中的记录数
                string countQuery = $"SELECT COUNT(*) FROM [{tableName}]";
                using (OleDbCommand cmd = new OleDbCommand(countQuery, connection))
                {
                    int recordCount = Convert.ToInt32(cmd.ExecuteScalar());
                    tables.Add(new DTable { TableName = tableName, RecordCount = recordCount });
                }
            }

            return tables;
        }
    }

    /// <summary>
    /// 表信息集合
    /// </summary>
    class DataTables : List<DTable>
    {
    }

    /// <summary>
    /// 表信息类
    /// </summary>
    class DTable
    {
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 记录数
        /// </summary>
        public int RecordCount { get; set; }
    }
}

使用须知与注意事项

数据库引擎要求

虽然不需要COM引用，但是仍然需要在运行环境中安装Microsoft Access Database Engine：

• 对于Access 2007及以上版本(.accdb)，需要安装Microsoft Access Database Engine 2010或更高版本
• 对于Access 2003及以下版本(.mdb)，需要安装Microsoft Jet 4.0 Engine

安装链接：Microsoft Access Database Engine 2016 Redistributable

32位/64位兼容性

Access数据库引擎有32位和64位两个版本，需要与应用程序的位数匹配：

• 如果你的应用是32位的，需要安装32位的Access数据库引擎
• 如果你的应用是64位的，需要安装64位的Access数据库引擎

不匹配会导致"提供程序无法加载"错误。

不同版本Access的连接字符串

针对不同版本的Access数据库，连接字符串也不同：

C#
// Access 2007-2019 (.accdb)
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabase.accdb;";

// Access 2000-2003 (.mdb)
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb;";

总结

使用CATALOG_FILL技术无需COM引用创建Access数据库是一种简单高效的方法，特别适合轻量级应用程序。它的核心优势在于：

1. 简化开发：无需添加复杂的COM引用
2. 降低依赖：减少外部组件依赖
3. 提高兼容性：减少版本冲突问题
4. 易于部署：简化部署过程

虽然Access数据库在企业级应用中已逐渐被其他数据库系统替代，但在小型应用、桌面工具、教学示例等场景中仍有其价值。掌握这种无需COM引用创建Access数据库的技术，可以让C#开发者快速实现数据存储需求，提高开发效率。

【关键词】C# Access数据库 CATALOG_FILL 无需COM引用 ADO.NET OleDb 数据库创建 进销存系统 数据库设计

如果您觉得这篇文章有帮助，欢迎点赞、收藏和分享！也欢迎关注我们的微信公众号，获取更多实用的C#开发技巧和解决方案。