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

05-13 1078阅读

在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驱动报错的截图。

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

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

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

        点击安装。

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

        选择安装路径。

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

        安装完成。

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


        常见数据库的 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和其他数据库操作方式的对比

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

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

                      特性OleDbExcel InteropEPPlus等第三方库
                      系统要求需要安装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是一个非常强大的工具,特别是在需要操作多种数据源的情况下。然而,对于特定的应用需求,使用更现代和专用的库可能会更加高效和简洁。

VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]