C#进阶-OleDb操作Excel和数据库

在C#编程中，使用OleDb可以方便地实现对Excel文件和数据库的操作。本文探讨了在C#中使用OleDb技术操作Excel和数据库的策略。文章详述了OleDb的定义、配置环境的步骤，并通过实际代码示例演示了如何高效读写Excel文件和交互数据库。文中还评估了OleDb技术的优缺点，为需要处理多种数据源的开发者提供了一种有效的数据操作方案。

一、OleDb简介

OleDb（Object Linking and Embedding, Database）是微软的一套数据库访问接口，支持对多种数据源的访问，包括SQL Server、Access以及Excel等。它提供了一种统一的方法来访问不同的数据源。在.NET环境下，OleDb可以通过System.Data.OleDb命名空间提供的类来使用。

二、OleDb安装前准备

1. 环境准备

.NET Framework: 确保您的开发环境中安装了.NET Framework，因为OleDb类库是基于.NET Framework的。

引用设置: 在Visual Studio中，需要添加对System.Data.dll的引用，以确保项目可以使用System.Data.OleDb命名空间。

2. 安装相应的驱动

使用 OLE DB 操作数据库或 Excel 时，你需要确保安装了适当的 OLE DB 驱动程序，这取决于你要连接的具体数据库系统。每种数据库系统都可能有一个或多个专用的 OLE DB 驱动，这些驱动允许应用程序通过 OLE DB 接口与数据库通信。

Excel 的 OLE DB 驱动：

对于Excel文件的数据操作，你可以使用Microsoft提供的OLE DB驱动来进行读取和写入操作。这些驱动允许通过OLE DB接口与Excel文件进行交互。具体使用的驱动取决于Excel文件的版本和你的系统类型（32位或64位）。

① 🎯Microsoft Access Database Engine OLE DB Provider

• 适用版本：Excel 2007 及更高版本的文件（.xlsx）。
• 驱动名称：
  • 对于旧版本或需要兼容性的，可以使用 Microsoft.ACE.OLEDB.12.0。
  • 对于更高版本的Office，可以使用 Microsoft.ACE.OLEDB.16.0。

    ② 🔥Microsoft Jet OLE DB Provider

    • 适用版本：Excel 2003 及更早版本的文件（.xls）。
    • 驱动名称：Microsoft.Jet.OLEDB.4.0。
    • 注意：这个驱动仅支持32位系统。

      驱动下载地址

      Access Database Engine最新版本下载地址：(https://learn.microsoft.com/zh-cn/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16)；

      Access Database Engine历史版本(16及之后)下载地址：(https://learn.microsoft.com/zh-cn/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver16#previous-releases)；

      已经绝版的Microsoft.ACE.OLEDB.12.0(Microsoft Access 2010 数据库引擎)下载地址：(https://download.csdn.net/download/weixin_41793160/89286174) (注意只支持32位，.NET程序目标生成不能位X64)；

      已经绝版的Microsoft.Jet.OLEDB.4.0(2007 Office system 驱动程序)下载地址：

      同Microsoft.ACE.OLEDB.12.0。

      安装流程

      未安装Microsoft.ACE.OLEDB.12.0驱动报错的截图。

      

      下载Microsoft.ACE.OLEDB.12.0驱动安装程序。

      

      点击安装。

      

      选择安装路径。

      

      安装完成。

      

      ---

      常见数据库的 OLE DB 驱动：

      ① 🌈Microsoft SQL Server：

      • Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)：是当前推荐的驱动，支持最新的SQL Server功能。
      • SQL Server Native Client (SQLNCLI)：旧版本的驱动，依然广泛使用，但可能不支持最新的数据库功能。

        ② ✨Microsoft Access：

        • Microsoft Access Database Engine OLE DB Provider (ACEOLEDB)：适用于 Access 2007 及以上版本。
        • Microsoft Jet OLE DB Provider (Jet.OLEDB)：用于 Access 2003 及更早版本。

          ③ 🚀Oracle：

          • Oracle Provider for OLE DB：Oracle 官方提供的 OLE DB 驱动。

            ④ 🍀MySQL：

            • MySQL 官方没有直接提供专用的 OLE DB 驱动，通常推荐使用 ODBC 驱动或第三方提供的 OLE DB 驱动。

              ⑤ 🧩PostgreSQL：

              • 类似于 MySQL，PostgreSQL 官方也没有提供 OLE DB 驱动。推荐使用 ODBC 驱动或第三方提供的 OLE DB 驱动。

                确保驱动安装正确并且连接字符串配置适当是成功使用 OLE DB 的关键。不正确的驱动安装或配置可能导致连接失败或数据访问错误。

                ---

                三、OleDb操作EXCEL

                1. 连接字符串

                连接Excel文件时，您的连接字符串会略有不同，这取决于Excel文件的版本（例如Excel 97-2003工作簿.xls与Excel 工作簿.xlsx）：

                • 对于.xls文件：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=路径;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
                • 对于.xlsx文件：Provider=Microsoft.ACE.OLEDB.12.0;Data Source=路径;Extended Properties="Excel 12.0 Xml;HDR=Yes;IMEX=1";

                  ---

                  2. 读取数据

                  使用OleDb读取Excel文件通常涉及以下步骤：

                  1. 创建OleDbConnection对象来建立到Excel文件的连接。
                  2. 创建OleDbCommand对象来定义要执行的SQL查询。
                  3. 创建OleDbDataAdapter和DataSet，用于接收查询结果。
                  4. 执行查询并填充DataSet，之后可以通过DataTable对象来操作数据。

                  string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\path\\to\\file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                  using(OleDbConnection conn = new OleDbConnection(connectionString))
                  {
                      conn.Open();
                      OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
                      OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                      DataSet ds = new DataSet();
                      adapter.Fill(ds);
                      DataTable data = ds.Tables[0];
                      // 数据现在存储在data中，可以进行进一步处理
                  }
                  

                  ---

                  3. 写入数据

                  要向Excel文件写入数据，可以使用OleDbConnection和OleDbCommand对象。

                  下面示例展示了如何打开一个连接，单条写入数据到Excel表格中。

                  using (OleDbConnection conn = new OleDbConnection(connectionString))
                  {
                      conn.Open();  // 打开连接
                      // 构建插入命令
                      OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$] (Column1, Column2) VALUES ('Value1', 'Value2')", conn);
                      // 执行插入命令
                      cmd.ExecuteNonQuery();
                      // 关闭连接
                      conn.Close();
                  }
                  

                  如果需要向Excel文件批量写入数据，可以构建多个INSERT语句，或使用OleDbDataAdapter和DataTable，通过调整DataTable中的数据然后调用Update()方法来实现。

                  DataTable dt = new DataTable();
                  // 假设dt已经被填充了数据
                  using (OleDbConnection conn = new OleDbConnection(connectionString))
                  {
                      OleDbDataAdapter adapter = new OleDbDataAdapter();
                      adapter.InsertCommand = new OleDbCommand("INSERT INTO [Sheet1$] (Column1, Column2) VALUES (?, ?)", conn);
                      adapter.InsertCommand.Parameters.Add("@Column1", OleDbType.VarChar, 255, "Column1");
                      adapter.InsertCommand.Parameters.Add("@Column2", OleDbType.VarChar, 255, "Column2");
                      conn.Open();
                      adapter.Update(dt);  // 更新Excel文件
                      conn.Close();
                  }
                  

                  这种方法特别适合处理大量数据，可以显著提高数据插入的效率。

                  ---

                  四、OleDb操作数据库

                  使用OLE DB进行数据库访问（包括增加、删除、修改和查询操作）涉及到几个关键的.NET类，主要是OleDbConnection、OleDbCommand、OleDbDataAdapter和OleDbDataReader。下面是一个使用OLE DB来执行增删改查（CRUD：Create, Read, Update, Delete）操作的基本示例，这里假设数据库是一个简单的Microsoft Access数据库或其他支持OLE DB的数据库。

                  1. OleDb实现数据增删改查

                  代码示例

                  using System;
                  using System.Data;
                  using System.Data.OleDb;
                  class Program
                  {
                      static void Main()
                      {
                          string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\yourdatabase.mdb;";
                          // 创建一个新的OleDbConnection，并将其封装在using语句中以确保资源的释放
                          using (OleDbConnection connection = new OleDbConnection(connectionString))
                          {
                              connection.Open();
                              // 创建（Create）
                              using (OleDbCommand command = new OleDbCommand("INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe')", connection))
                              {
                                  command.ExecuteNonQuery();
                              }
                              // 读取（Read）
                              using (OleDbCommand command = new OleDbCommand("SELECT FirstName, LastName FROM Employees", connection))
                              using (OleDbDataReader reader = command.ExecuteReader())
                              {
                                  while (reader.Read())
                                  {
                                      Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
                                  }
                              }
                              // 更新（Update）
                              using (OleDbCommand command = new OleDbCommand("UPDATE Employees SET LastName = 'Smith' WHERE FirstName = 'John'", connection))
                              {
                                  command.ExecuteNonQuery();
                              }
                              // 删除（Delete）
                              using (OleDbCommand command = new OleDbCommand("DELETE FROM Employees WHERE FirstName = 'John'", connection))
                              {
                                  command.ExecuteNonQuery();
                              }
                          }
                      }
                  }
                  

                  解释

                  1. 连接字符串 (connectionString): 指定了OLE DB提供者、数据库文件路径等信息。
                  2. OleDbConnection: 用于建立与数据库的连接。
                  3. OleDbCommand: 用于执行SQL命令。
                  4. OleDbDataReader: 用于读取从数据库返回的结果。

                  注意事项

                  • 确保修改连接字符串以匹配你的数据库设置。
                  • 这个例子中使用的是Microsoft Access的OLE DB提供者 (Microsoft.Jet.OLEDB.4.0)，你可能需要根据你的数据库类型调整提供者。
                  • 确保数据库路径、SQL语句和数据库表结构与示例代码中的相符。

                    ---

                    2. OleDb实现事务管理

                    OLE DB 允许你通过OleDbTransaction来控制事务，这样可以确保数据的完整性和一致性。在一个事务中，你可以执行多个操作，要么全部成功，要么全部失败，从而避免数据不一致的情况出现。

                    代码示例

                    using System;
                    using System.Data;
                    using System.Data.OleDb;
                    class Program
                    {
                        static void Main()
                        {
                            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\yourdatabase.mdb;";
                            using (OleDbConnection connection = new OleDbConnection(connectionString))
                            {
                                connection.Open();
                                OleDbTransaction transaction = connection.BeginTransaction();
                                try
                                {
                                    OleDbCommand command = connection.CreateCommand();
                                    command.Transaction = transaction;
                                    command.CommandText = "INSERT INTO Table1 (Column1) VALUES ('Value1')";
                                    command.ExecuteNonQuery();
                                    command.CommandText = "INSERT INTO Table2 (Column2) VALUES ('Value2')";
                                    command.ExecuteNonQuery();
                                    // 提交事务
                                    transaction.Commit();
                                    Console.WriteLine("Both records are written to database.");
                                }
                                catch (Exception ex)
                                {
                                    // 回滚事务
                                    transaction.Rollback();
                                    Console.WriteLine("There was an error, transaction was rolled back.");
                                    Console.WriteLine(ex.Message);
                                }
                            }
                        }
                    }
                    

                    这个示例展示了如何使用事务来确保数据的一致性，只有当所有操作都成功时才提交事务，否则回滚所有操作。这是数据库操作中一个非常重要的高级功能。

                    ---

                    3. OleDb实现异步操作

                    在.NET中，使用OLE DB进行异步数据库操作可以通过多种方式实现，比如使用Task和async/await模式。

                    代码示例

                    using System;
                    using System.Data;
                    using System.Data.OleDb;
                    using System.Threading.Tasks;
                    class Program
                    {
                        static async Task Main()
                        {
                            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\yourdatabase.mdb;";
                            using (OleDbConnection connection = new OleDbConnection(connectionString))
                            {
                                await connection.OpenAsync();
                                using (OleDbCommand command = new OleDbCommand("SELECT * FROM Employees", connection))
                                {
                                    using (OleDbDataReader reader = (OleDbDataReader)await command.ExecuteReaderAsync())
                                    {
                                        while (await reader.ReadAsync())
                                        {
                                            Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
                                        }
                                    }
                                }
                            }
                        }
                    }
                    

                    在这个例子中，OpenAsync, ExecuteReaderAsync, 和 ReadAsync 方法被用来异步打开连接、执行查询和读取结果。这种方法非常适合用在需要维护应用程序响应性的场景中，比如GUI应用程序或者高负载Web应用程序。

                    ---

                    4. OleDb调用存储过程

                    代码示例

                    using System;
                    using System.Data;
                    using System.Data.OleDb;
                    class Program
                    {
                        static void Main()
                        {
                            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\yourdatabase.mdb;";
                            using (OleDbConnection connection = new OleDbConnection(connectionString))
                            {
                                connection.Open();
                                using (OleDbCommand command = new OleDbCommand("GetEmployeeDetails", connection))
                                {
                                    command.CommandType = CommandType.StoredProcedure;
                                    // 添加输入参数
                                    command.Parameters.AddWithValue("@EmployeeId", 1);
                                    // 如果存储过程有返回值
                                    command.Parameters.Add(new OleDbParameter("@ReturnValue", OleDbType.VarChar, 100));
                                    command.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
                                    command.ExecuteNonQuery();
                                    // 读取输出参数的值
                                    string result = command.Parameters["@ReturnValue"].Value.ToString();
                                    Console.WriteLine("Result: " + result);
                                }
                            }
                        }
                    }
                    

                    这个示例展示了如何调用名为"GetEmployeeDetails"的存储过程，它假定此存储过程接收一个名为@EmployeeId的输入参数，并返回一些数据。在实际应用中，你需要根据实际存储过程的定义来调整参数的名称和类型。

                    ---

                    五、OleDb操作总结

                    1. OleDb和其他数据库操作方式的对比

                    特性	OleDb	ADO.NET（SqlClient等）	Entity Framework
                    数据库支持	支持多种数据库，包括SQL Server、Oracle等	特定于数据库（如SqlClient针对SQL Server）	支持多种数据库
                    性能	通常较慢，因为它提供了一层抽象	直接与数据库通信，性能较高	高级抽象，性能优化可变
                    操作方式	低级，需要手动编写SQL语句和管理连接	低级，同样需要手动处理SQL和连接	高级抽象，自动处理SQL
                    代码复杂性	较高，需要处理更多的细节	较高，类似OleDb	较低，简化数据操作
                    平台依赖性	较高，依赖于安装的OLE DB提供程序	较低，通常内置支持	较低，内置支持
                    设计目的	数据访问的通用解决方案，兼容性好	针对特定数据库的优化访问	全面的ORM解决方案

                    2. OleDb和其他Excel操作方式的对比

                    特性	OleDb	Excel Interop	EPPlus等第三方库
                    系统要求	需要安装OLE DB驱动	需要安装Microsoft Office	无额外安装要求
                    性能	中等，适合中小规模数据处理	较慢，尤其是大量数据时	较快，针对大数据进行了优化
                    操作复杂性	高，需要编写SQL语句	高，需要使用Office COM API	低，简洁的API
                    功能丰富性	适合简单的数据读写	功能丰富，可以操作Excel的几乎所有特性	功能丰富，专注于数据处理
                    编程模型	通过SQL语句访问数据	直接操作Excel对象模型	使用类似Excel的对象模型
                    处理复杂文档能力	限制较多，不适合处理复杂的Excel文件格式	非常适合处理复杂文档	较适合处理数据密集型文档

                    3. OleDb操作总结

                    • 通用性：OleDb提供了一个广泛的数据库访问解决方案，支持多种数据库系统。这使得它非常适合需要与多种数据源交互的应用程序。
                    • 性能考虑：由于OleDb提供了一个通用的接口，可能在性能上不如专用的数据库访问技术，如直接使用SqlClient等。
                    • 适用场景：OleDb非常适合那些不需要高性能数据库交互，但需要与多种数据库兼容的应用程序。对于简单的Excel数据操作也很有用，尤其是在没有安装Excel的服务器环境中。
                    • 编程复杂性：使用OleDb进行数据库操作通常需要较为复杂的代码来管理连接、执行SQL命令等，不如某些现代ORM框架（如Entity Framework）提供的抽象级别高。

                      总的来说，OleDb是一个非常强大的工具，特别是在需要操作多种数据源的情况下。然而，对于特定的应用需求，使用更现代和专用的库可能会更加高效和简洁。