文章目錄
- 一,C# Database 庫
- 二,使用樣本
- 三,注意事項
[C#]ADO訪問多資料庫的C#庫
羅朝輝 (http://www.cnblogs.com/kesalin/)
本文遵循“署名-非商業用途-保持一致”創作公用協議
一,C# Database 庫
整了一個支援通過ADO方式訪問多種資料庫(OLE,MySQL,SQL Server,Oracle)的 C# 庫 Database。實現相當簡單,用Factory 方法建立各種資料庫訪問的 wrapper 類即可。
源碼下載:點此下載
或訪問 Github:https://github.com/kesalin/CSharpSnippet/tree/master/Database
類圖如下:
IDatabase 是對外公開的介面類,其中定義了一堆操作資料庫的介面方法;
DatabaseFactory 是視窗資料庫的工廠類;
DatabaseType 是一個資料庫類型的枚舉;
DatabaseHelper 封裝一些和資料庫相關的常用的小工具方法,如便捷地建立 connection string,從 dataset 中讀取值等。
二,使用樣本
範例程式碼如下:
View Code
internal class Program { private IDatabase _db; private const DatabaseType _dbType = DatabaseType.MySQL; #region Database related // You need to create a MySQL database named "sample" with columns // id(int), Name(varchar(45)), Address(varchar(45)), Age(int) for this test. // private void CreateDatabase() { if (_db == null) { // Setup you database information here. // var connStr = DatabaseHelper.CreateConnectionString(_dbType, "localhost", "sample", "root", "123456"); _db = DatabaseFactory.CreateDatabase(_dbType, connStr); if (_db == null) Console.WriteLine(" >> Failed to create database with connection string {0}.", connStr); else Console.WriteLine(" >> Created database."); } } private void CloseDatabase() { if (_db != null) { _db.Dispose(); _db = null; } } public void TestInsert() { if (_db == null) return; const string sqlCmd = "insert into customer (id, Name,Address,Age) values (0,'飄飄白雲','上海張江高科',28)"; try { _db.Open(); _db.ExcuteSql(sqlCmd); Console.WriteLine(" >> Succeed. {0}", sqlCmd); } catch (Exception ex) { Console.WriteLine(" >> Failed to {0}. {1}", sqlCmd, ex.Message); } finally { _db.Close(); } } public void TestFind() { if (_db == null) return; const string sqlCmd = "select Name,Address,Age from customer where Name='飄飄白雲'"; try { _db.Open(); var dataSet = _db.ExcuteSqlForDataSet(sqlCmd); var recordCount = DatabaseHelper.GetRowCount(dataSet); Console.WriteLine(" >> Excuted {0}", sqlCmd); Console.WriteLine(" >> Found {0} record.", recordCount); for (int i = 0; i < recordCount; i++) { var name = DatabaseHelper.GetValue(dataSet, i, 0) as string; var address = DatabaseHelper.GetValue(dataSet, i, 1) as string; var age = DatabaseHelper.GetIntValue(dataSet, i, 2); Console.WriteLine(" >> Record {0}, Name:{1}, Address:{2}, Age:{3}", i + 1, name, address, age); } } catch (Exception ex) { Console.WriteLine(" >> Failed to {0}. {1}", sqlCmd, ex.Message); } finally { _db.Close(); } } public void TestUpdate() { if (_db == null) return; const string sqlCmd = "update customer set Address='張江高科' where Name='飄飄白雲'"; try { _db.Open(); _db.ExcuteSql(sqlCmd); Console.WriteLine(" >> Succeed. {0}", sqlCmd); } catch (Exception ex) { Console.WriteLine(" >> Failed to {0}. {1}", sqlCmd, ex.Message); } finally { _db.Close(); } } public void TestDelete() { if (_db == null) return; const string sqlCmd = "delete from customer where Name='飄飄白雲'"; try { _db.Open(); _db.ExcuteSql(sqlCmd); Console.WriteLine(" >> Succeed. {0}", sqlCmd); } catch (Exception ex) { Console.WriteLine(" >> Failed to {0}. {1}", sqlCmd, ex.Message); } finally { _db.Close(); } } #endregion static void Main(string[] args) { var runner = new Program(); runner.CreateDatabase(); runner.TestInsert(); runner.TestFind(); runner.TestUpdate(); runner.TestFind(); runner.TestDelete(); runner.TestFind(); runner.CloseDatabase(); Console.ReadLine(); } }
運行輸出結果:
三,注意事項
如果你對常用的資料庫命令文法還不太瞭解,可以參考如下連結:
// SQL syntax
//
Select : http://en.wikipedia.org/wiki/Select_(SQL)
Insert : http://en.wikipedia.org/wiki/Insert_(SQL)
Delete : http://en.wikipedia.org/wiki/Delete_(SQL)
Update : http://en.wikipedia.org/wiki/Update_(SQL)
Truncate : http://en.wikipedia.org/wiki/Truncate_(SQL)
由於各個資料庫廠商有不同的資料庫實現,導致資料庫命令文法有一些細微的差別,因此需要特別注意。以下就列出一些常見的不同之處:
1,最大查詢記錄數
對於 SQL Server 使用 top 關鍵字。如:
select top 100 * from customer
對於 MySQL 使用 limit 關鍵字。如:
select * from customer limit 100
對於 Oracle 使用 rownum <=。如:
select * from customer where rownum <= 100
2,命令中出現的逸出字元(詳見 DatabaseHelper 類的 Validate 方法)
對於 SQL Server,單引號 ' 要用兩個單引號 '' 替換;雙引號 " 要用兩個雙引號 "" 替換;
對於 MySQL,單引號 ' 要用 \' 替換;反斜線 \ 用於 \\ 替換。