C#使用OleDb操作Excel和數(shù)據(jù)庫的策略
前言
在C#編程中,使用OleDb可以方便地實(shí)現(xiàn)對Excel文件和數(shù)據(jù)庫的操作。本文探討了在C#中使用OleDb技術(shù)操作Excel和數(shù)據(jù)庫的策略。文章詳述了OleDb的定義、配置環(huán)境的步驟,并通過實(shí)際代碼示例演示了如何高效讀寫Excel文件和交互數(shù)據(jù)庫。文中還評估了OleDb技術(shù)的優(yōu)缺點(diǎn),為需要處理多種數(shù)據(jù)源的開發(fā)者提供了一種有效的數(shù)據(jù)操作方案。
一、OleDb簡介
OleDb(Object Linking and Embedding, Database)是微軟的一套數(shù)據(jù)庫訪問接口,支持對多種數(shù)據(jù)源的訪問,包括SQL Server、Access以及Excel等。它提供了一種統(tǒng)一的方法來訪問不同的數(shù)據(jù)源。在.NET環(huán)境下,OleDb可以通過System.Data.OleDb命名空間提供的類來使用。
二、OleDb安裝前準(zhǔn)備
1. 環(huán)境準(zhǔn)備
.NET Framework: 確保您的開發(fā)環(huán)境中安裝了.NET Framework,因?yàn)镺leDb類庫是基于.NET Framework的。
引用設(shè)置: 在Visual Studio中,需要添加對System.Data.dll的引用,以確保項(xiàng)目可以使用System.Data.OleDb命名空間。
2. 安裝相應(yīng)的驅(qū)動
使用 OLE DB 操作數(shù)據(jù)庫或 Excel 時,你需要確保安裝了適當(dāng)?shù)?OLE DB 驅(qū)動程序,這取決于你要連接的具體數(shù)據(jù)庫系統(tǒng)。每種數(shù)據(jù)庫系統(tǒng)都可能有一個或多個專用的 OLE DB 驅(qū)動,這些驅(qū)動允許應(yīng)用程序通過 OLE DB 接口與數(shù)據(jù)庫通信。
Excel 的 OLE DB 驅(qū)動:
對于Excel文件的數(shù)據(jù)操作,你可以使用Microsoft提供的OLE DB驅(qū)動來進(jìn)行讀取和寫入操作。這些驅(qū)動允許通過OLE DB接口與Excel文件進(jìn)行交互。具體使用的驅(qū)動取決于Excel文件的版本和你的系統(tǒng)類型(32位或64位)。
①M(fèi)icrosoft Access Database Engine OLE DB Provider
- 適用版本:Excel 2007 及更高版本的文件(.xlsx)。
- 驅(qū)動名稱:
- 對于舊版本或需要兼容性的,可以使用
Microsoft.ACE.OLEDB.12.0
。 - 對于更高版本的Office,可以使用
Microsoft.ACE.OLEDB.16.0
。
- 對于舊版本或需要兼容性的,可以使用
②Microsoft Jet OLE DB Provider
- 適用版本:Excel 2003 及更早版本的文件(.xls)。
- 驅(qū)動名稱:
Microsoft.Jet.OLEDB.4.0
。 - 注意:這個驅(qū)動僅支持32位系統(tǒng)。
驅(qū)動下載地址
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);
已經(jīng)絕版的Microsoft.Jet.OLEDB.4.0(2007 Office system 驅(qū)動程序)下載地址:microsoft.ace.oledb.12.0 | Microsoft Learn
安裝流程
未安裝Microsoft.ACE.OLEDB.12.0驅(qū)動報錯的截圖。
下載Microsoft.ACE.OLEDB.12.0驅(qū)動安裝程序。
點(diǎn)擊安裝。
選擇安裝路徑。
安裝完成。
常見數(shù)據(jù)庫的 OLE DB 驅(qū)動:
①M(fèi)icrosoft SQL Server:
Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL):是當(dāng)前推薦的驅(qū)動,支持最新的SQL Server功能。SQL Server Native Client (SQLNCLI):舊版本的驅(qū)動,依然廣泛使用,但可能不支持最新的數(shù)據(jù)庫功能。
②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 驅(qū)動。
④MySQL:
MySQL 官方?jīng)]有直接提供專用的 OLE DB 驅(qū)動,通常推薦使用 ODBC 驅(qū)動或第三方提供的 OLE DB 驅(qū)動。
⑤PostgreSQL:
類似于 MySQL,PostgreSQL 官方也沒有提供 OLE DB 驅(qū)動。推薦使用 ODBC 驅(qū)動或第三方提供的 OLE DB 驅(qū)動。
確保驅(qū)動安裝正確并且連接字符串配置適當(dāng)是成功使用 OLE DB 的關(guān)鍵。不正確的驅(qū)動安裝或配置可能導(dǎo)致連接失敗或數(shù)據(jù)訪問錯誤。
三、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. 讀取數(shù)據(jù)
使用OleDb讀取Excel文件通常涉及以下步驟:
- 創(chuàng)建OleDbConnection對象來建立到Excel文件的連接。
- 創(chuàng)建OleDbCommand對象來定義要執(zhí)行的SQL查詢。
- 創(chuàng)建OleDbDataAdapter和DataSet,用于接收查詢結(jié)果。
- 執(zhí)行查詢并填充DataSet,之后可以通過DataTable對象來操作數(shù)據(jù)。
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]; // 數(shù)據(jù)現(xiàn)在存儲在data中,可以進(jìn)行進(jìn)一步處理 }
3. 寫入數(shù)據(jù)
要向Excel文件寫入數(shù)據(jù),可以使用OleDbConnection
和OleDbCommand
對象。
下面示例展示了如何打開一個連接,單條寫入數(shù)據(jù)到Excel表格中。
using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); // 打開連接 // 構(gòu)建插入命令 OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$] (Column1, Column2) VALUES ('Value1', 'Value2')", conn); // 執(zhí)行插入命令 cmd.ExecuteNonQuery(); // 關(guān)閉連接 conn.Close(); }
如果需要向Excel文件批量寫入數(shù)據(jù),可以構(gòu)建多個INSERT語句,或使用OleDbDataAdapter
和DataTable
,通過調(diào)整DataTable
中的數(shù)據(jù)然后調(diào)用Update()
方法來實(shí)現(xiàn)。
DataTable dt = new DataTable(); // 假設(shè)dt已經(jīng)被填充了數(shù)據(jù) 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(); }
這種方法特別適合處理大量數(shù)據(jù),可以顯著提高數(shù)據(jù)插入的效率。
四、OleDb操作數(shù)據(jù)庫
使用OLE DB進(jìn)行數(shù)據(jù)庫訪問(包括增加、刪除、修改和查詢操作)涉及到幾個關(guān)鍵的.NET類,主要是OleDbConnection
、OleDbCommand
、OleDbDataAdapter
和OleDbDataReader
。下面是一個使用OLE DB來執(zhí)行增刪改查(CRUD:Create, Read, Update, Delete)操作的基本示例,這里假設(shè)數(shù)據(jù)庫是一個簡單的Microsoft Access數(shù)據(jù)庫或其他支持OLE DB的數(shù)據(jù)庫。
1. OleDb實(shí)現(xiàn)數(shù)據(jù)增刪改查
代碼示例
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;"; // 創(chuàng)建一個新的OleDbConnection,并將其封裝在using語句中以確保資源的釋放 using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); // 創(chuàng)建(Create) using (OleDbCommand command = new OleDbCommand("INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe')", connection)) { command.ExecuteNonQuery(); } // 讀?。≧ead) 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(); } } } }
解釋
- 連接字符串 (
connectionString
): 指定了OLE DB提供者、數(shù)據(jù)庫文件路徑等信息。 - OleDbConnection: 用于建立與數(shù)據(jù)庫的連接。
- OleDbCommand: 用于執(zhí)行SQL命令。
- OleDbDataReader: 用于讀取從數(shù)據(jù)庫返回的結(jié)果。
注意事項(xiàng)
- 確保修改連接字符串以匹配你的數(shù)據(jù)庫設(shè)置。
- 這個例子中使用的是Microsoft Access的OLE DB提供者 (
Microsoft.Jet.OLEDB.4.0
),你可能需要根據(jù)你的數(shù)據(jù)庫類型調(diào)整提供者。 - 確保數(shù)據(jù)庫路徑、SQL語句和數(shù)據(jù)庫表結(jié)構(gòu)與示例代碼中的相符。
2. OleDb實(shí)現(xiàn)事務(wù)管理
OLE DB 允許你通過OleDbTransaction
來控制事務(wù),這樣可以確保數(shù)據(jù)的完整性和一致性。在一個事務(wù)中,你可以執(zhí)行多個操作,要么全部成功,要么全部失敗,從而避免數(shù)據(jù)不一致的情況出現(xiàn)。
代碼示例
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(); // 提交事務(wù) transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { // 回滾事務(wù) transaction.Rollback(); Console.WriteLine("There was an error, transaction was rolled back."); Console.WriteLine(ex.Message); } } } }
這個示例展示了如何使用事務(wù)來確保數(shù)據(jù)的一致性,只有當(dāng)所有操作都成功時才提交事務(wù),否則回滾所有操作。這是數(shù)據(jù)庫操作中一個非常重要的高級功能。
3. OleDb實(shí)現(xiàn)異步操作
在.NET中,使用OLE DB進(jìn)行異步數(shù)據(jù)庫操作可以通過多種方式實(shí)現(xiàn),比如使用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
方法被用來異步打開連接、執(zhí)行查詢和讀取結(jié)果。這種方法非常適合用在需要維護(hù)應(yīng)用程序響應(yīng)性的場景中,比如GUI應(yīng)用程序或者高負(fù)載Web應(yīng)用程序。
4. OleDb調(diào)用存儲過程
代碼示例
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; // 添加輸入?yún)?shù) command.Parameters.AddWithValue("@EmployeeId", 1); // 如果存儲過程有返回值 command.Parameters.Add(new OleDbParameter("@ReturnValue", OleDbType.VarChar, 100)); command.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; command.ExecuteNonQuery(); // 讀取輸出參數(shù)的值 string result = command.Parameters["@ReturnValue"].Value.ToString(); Console.WriteLine("Result: " + result); } } } }
這個示例展示了如何調(diào)用名為"GetEmployeeDetails"的存儲過程,它假定此存儲過程接收一個名為@EmployeeId
的輸入?yún)?shù),并返回一些數(shù)據(jù)。在實(shí)際應(yīng)用中,你需要根據(jù)實(shí)際存儲過程的定義來調(diào)整參數(shù)的名稱和類型。
五、OleDb操作總結(jié)
1. OleDb和其他數(shù)據(jù)庫操作方式的對比
特性 | OleDb | ADO.NET(SqlClient等) | Entity Framework |
---|---|---|---|
數(shù)據(jù)庫支持 | 支持多種數(shù)據(jù)庫,包括SQL Server、Oracle等 | 特定于數(shù)據(jù)庫(如SqlClient針對SQL Server) | 支持多種數(shù)據(jù)庫 |
性能 | 通常較慢,因?yàn)樗峁┝艘粚映橄?/td> | 直接與數(shù)據(jù)庫通信,性能較高 | 高級抽象,性能優(yōu)化可變 |
操作方式 | 低級,需要手動編寫SQL語句和管理連接 | 低級,同樣需要手動處理SQL和連接 | 高級抽象,自動處理SQL |
代碼復(fù)雜性 | 較高,需要處理更多的細(xì)節(jié) | 較高,類似OleDb | 較低,簡化數(shù)據(jù)操作 |
平臺依賴性 | 較高,依賴于安裝的OLE DB提供程序 | 較低,通常內(nèi)置支持 | 較低,內(nèi)置支持 |
設(shè)計目的 | 數(shù)據(jù)訪問的通用解決方案,兼容性好 | 針對特定數(shù)據(jù)庫的優(yōu)化訪問 | 全面的ORM解決方案 |
2. OleDb和其他Excel操作方式的對比
特性 | OleDb | Excel Interop | EPPlus等第三方庫 |
---|---|---|---|
系統(tǒng)要求 | 需要安裝OLE DB驅(qū)動 | 需要安裝Microsoft Office | 無額外安裝要求 |
性能 | 中等,適合中小規(guī)模數(shù)據(jù)處理 | 較慢,尤其是大量數(shù)據(jù)時 | 較快,針對大數(shù)據(jù)進(jìn)行了優(yōu)化 |
操作復(fù)雜性 | 高,需要編寫SQL語句 | 高,需要使用Office COM API | 低,簡潔的API |
功能豐富性 | 適合簡單的數(shù)據(jù)讀寫 | 功能豐富,可以操作Excel的幾乎所有特性 | 功能豐富,專注于數(shù)據(jù)處理 |
編程模型 | 通過SQL語句訪問數(shù)據(jù) | 直接操作Excel對象模型 | 使用類似Excel的對象模型 |
處理復(fù)雜文檔能力 | 限制較多,不適合處理復(fù)雜的Excel文件格式 | 非常適合處理復(fù)雜文檔 | 較適合處理數(shù)據(jù)密集型文檔 |
3. OleDb操作總結(jié)
- 通用性:OleDb提供了一個廣泛的數(shù)據(jù)庫訪問解決方案,支持多種數(shù)據(jù)庫系統(tǒng)。這使得它非常適合需要與多種數(shù)據(jù)源交互的應(yīng)用程序。
- 性能考慮:由于OleDb提供了一個通用的接口,可能在性能上不如專用的數(shù)據(jù)庫訪問技術(shù),如直接使用SqlClient等。
- 適用場景:OleDb非常適合那些不需要高性能數(shù)據(jù)庫交互,但需要與多種數(shù)據(jù)庫兼容的應(yīng)用程序。對于簡單的Excel數(shù)據(jù)操作也很有用,尤其是在沒有安裝Excel的服務(wù)器環(huán)境中。
- 編程復(fù)雜性:使用OleDb進(jìn)行數(shù)據(jù)庫操作通常需要較為復(fù)雜的代碼來管理連接、執(zhí)行SQL命令等,不如某些現(xiàn)代ORM框架(如Entity Framework)提供的抽象級別高。
總的來說,OleDb是一個非常強(qiáng)大的工具,特別是在需要操作多種數(shù)據(jù)源的情況下。然而,對于特定的應(yīng)用需求,使用更現(xiàn)代和專用的庫可能會更加高效和簡潔。
以上就是C#使用OleDb操作Excel和數(shù)據(jù)庫的策略的詳細(xì)內(nèi)容,更多關(guān)于C# OleDb操作Excel和數(shù)據(jù)庫的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
快速學(xué)習(xí)C# 設(shè)計模式之職責(zé)鏈模式
這篇文章主要介紹了C# 設(shè)計模式之職責(zé)鏈模式的的相關(guān)資料,文中代碼非常細(xì)致,幫助大家更好的理解和學(xué)習(xí),感興趣的朋友可以了解下2020-06-06C# List集合中獲取重復(fù)值及集合運(yùn)算詳解
這篇文章主要介紹了C# List集合中獲取重復(fù)值及集合運(yùn)算詳解,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12基于NPOI用C#開發(fā)的Excel以及表格設(shè)置
這篇文章主要為大家詳細(xì)介紹了基于NPOI用C#開發(fā)的Excel以及表格設(shè)置,文中示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2022-02-02