[C#]ADO訪問多數(shù)據(jù)庫的C#庫

整了一個支持通過ADO方式訪問多種數(shù)據(jù)庫(OLE，MySQL，SQL Server，Oracle)的 C# 庫 Database。實現(xiàn)相當簡單，用工廠方法創(chuàng)建各種數(shù)據(jù)庫訪問的 wrapper 類即可。

羅朝輝 (http://www.rzrgm.cn/kesalin/)

本文遵循“署名-非商業(yè)用途-保持一致”創(chuàng)作公用協(xié)議

一，C# Database 庫

整了一個支持通過ADO方式訪問多種數(shù)據(jù)庫(OLE，MySQL，SQL Server，Oracle)的 C# 庫 Database。實現(xiàn)相當簡單，用工廠方法創(chuàng)建各種數(shù)據(jù)庫訪問的 wrapper 類即可。

源碼下載：點此下載

或訪問 Github:https://github.com/kesalin/CSharpSnippet/tree/master/Database

類圖如下：

IDatabase 是對外公開的接口類，其中定義了一堆操作數(shù)據(jù)庫的接口方法；
DatabaseFactory 是窗口數(shù)據(jù)庫的工廠類；
DatabaseType 是一個數(shù)據(jù)庫類型的枚舉；
DatabaseHelper 封裝一些和數(shù)據(jù)庫相關的常用的小工具方法，如便捷地創(chuàng)建 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();
        }
    }