简单的数据库操作类

需要操作很多数据库，现在是需要两种：MSSQL和SQLITE。预计以后会用到MYSQL，以便在首页显示Discuz论坛博客之类的内容。但是单单MSSQL，服务器有好几个。每次写程序，都要单个配置；然后生成SQLITE(而且是2.8的低版本)，发布到一个特殊的PHP环境当中。

把数据库连接封装到一个类里的想法已经很久了。中间版本无数，很多都是临时赶进度，加一点改进，匆忙上机。直到去年年底，以面向接口方式写了两个类，将数据库连接和数据库操作分开，才慢慢把封装的思路理清楚了，复用性有了很大改进，而且跨数据库类型的时候，改写也不用费太大的气力。不过前两天在做SQLITE应用的时候，总觉得基类里有些部分封装还是不够，代码还是太多。最终，今天上午的一版，又把简单的数据库操作和连接重新写到了一起。

代码部分不是很难，因为我不喜欢存储过程或数据库函数的缘故，所有的数据库查询都是依靠语句来完成的。这样移植起来会非常容易，对环境的依赖也自然很低。但是效率相对会低一些。（其实去年一年，我的很大一部分工作都是在维护我们那个糟糕的数据库，写了无数的程序去缓存各种页面。年底的时候，按照第三范式要求，重新设计了数据库，而后的工作，以至于今天，都是在为新的网站、新的后台准备着。）正是因为完全使用语句，所以我的数据库操作能简单很多，无非是填充个DataSet，执行个Command等等。如果数据库设计上没有问题，完全使用语句，我想还是没有问题的。

最根源的约定:ISQL

    public interface ISQL : IDisposable
    {
        void Open();
        void Close();
        IDbConnection Connection { get; }

        DataTable DT(string selectSQL);
        DataSet DSLimit(string selectSQL, int startRecord, int maxRecords, string srcTable);
        int Command(string sql);
        object CommandReturn(string sql);
    }

 继承IDisposable接口，是想使用using来及时释放资源。空行下面的，就是我需要的四种数据库操作。

用一个基类实现所有功能。方法都用虚函数，方便子类的改写。其中附加了一个简单的SQL字符串过滤方法，用于防止SQL注入攻击。

    public abstract class SQL
    {
        public static string SQLStringFilter(string input)
        {
            if (input.Split('\'').Length % 2 == 0) input += "'";
            return input;
        }

        protected System.Data.Common.DbConnectionStringBuilder _ConnectStringBuilder;
        protected IDbConnection _Connection;
        protected DbDataAdapter _Adapter;
        protected virtual void _Init()
        {
            _Connection.ConnectionString = _ConnectStringBuilder.ConnectionString;
        }
        public virtual IDbConnection Connection
        {
            get { return _Connection; }
        }
        public virtual void Open()
        {
            lock (_Connection)
            {
                _Init();
                switch (_Connection.State)
                {
                    case ConnectionState.Open:
                        return;
                    case ConnectionState.Closed:
                        _Connection.Open();
                        return;
                    case ConnectionState.Broken:
                        _Connection.Close();
                        _Connection.Open();
                        return;
                    case ConnectionState.Connecting:
                    case ConnectionState.Executing:
                    case ConnectionState.Fetching:
                        throw new Exception(String.Format("未处理的情况:{0}", _Connection.State));
                }
            }
        }
        public virtual void Close()
        {
            lock (_Connection)
            {
                switch (_Connection.State)
                {
                    case ConnectionState.Closed:
                        return;
                    case ConnectionState.Open:
                    case ConnectionState.Broken:
                        _Connection.Close();
                        return;
                    case ConnectionState.Connecting:
                    case ConnectionState.Executing:
                    case ConnectionState.Fetching:
                        throw new Exception(String.Format("未处理的情况:{0}", _Connection.State));
                }
            }            
        }
        public virtual void Dispose()
        {
            _Adapter.Dispose();
            _Connection.Dispose();
            _Connection = null;
        }

        public DataTable DT(string selectSQL)
        {
            IDbCommand dc = _Connection.CreateCommand();
            dc.CommandText = selectSQL;
            _Adapter.SelectCommand = dc as DbCommand;

            DataTable dt = new DataTable();
            _Adapter.Fill(dt);

            dc.Dispose();
            _Adapter.SelectCommand = null;

            return dt;
        }
        public DataSet DSLimit(string selectSQL, int startRecord, int maxRecords, string srcTable)
        {
            IDbCommand dc = _Connection.CreateCommand();
            dc.CommandText = selectSQL;
            _Adapter.SelectCommand = dc as DbCommand;

            DataSet ds = new DataSet();
            _Adapter.Fill(ds, startRecord, maxRecords, srcTable);

            dc.Dispose();
            _Adapter.SelectCommand = null;

            return ds;
        }

        public int Command(string sql)
        {
            IDbCommand dc = _Connection.CreateCommand();
            dc.CommandText = sql;
            int i = dc.ExecuteNonQuery();
            dc.Dispose();
            return i;
        }
        public object CommandReturn(string sql)
        {
            IDbCommand dc = _Connection.CreateCommand();
            dc.CommandText = sql;
            object o = dc.ExecuteScalar();
            dc.Dispose();
            return o;
        }

        public SQL() { _ConnectStringBuilder = new System.Data.Common.DbConnectionStringBuilder(); }

基类里的变量用的是System.Data.Common下的对象。这些对象可以帮助实现各个接口。

下面定义一个IMSSQL接口，以实现MSSQL连接的一些特殊要求。

        interface IMSSQL : ISQL
        {
            string UserID { get; set; }
            string DataSource { get; set; }
            string InitialCatalog { get; set; }
            string Password { set; }
        }

这四个属性是连接mssql必需的参数。在继承ISQL的前提下，约束这四个作为MSSQL连接的规范。下面定义一个MSSQL连接的通用类：

 

        public class MSSQL : SQL, IMSSQL
        {
            protected string _UserID;
            public string UserID
            { get { return _UserID; } set { _UserID = value; } }

            protected string _DataSource;
            public string DataSource
            { get { return _DataSource; } set { _DataSource = value; } }

            protected string _InitialCatalog;
            public string InitialCatalog
            { get { return _InitialCatalog; } set { _InitialCatalog = value; } }

            protected string _Password;
            public string Password
            { set { _Password = value; } }

            protected override void _Init()
            {
                SqlConnectionStringBuilder _scsb = _ConnectStringBuilder as SqlConnectionStringBuilder;
                _scsb.UserID = _UserID;
                _scsb.DataSource = _DataSource;
                if (_InitialCatalog != null) _scsb.InitialCatalog = _InitialCatalog;
                _scsb.Password = _Password;
                base._Init();
            }
            public MSSQL()
            {
                _ConnectStringBuilder = new SqlConnectionStringBuilder();
                _Connection = new SqlConnection() as IDbConnection;
                _Adapter = new SqlDataAdapter() as DbDataAdapter;
            }

        }

 

 终于到最后的实现的，首先做个连接到本机数据库的类，方便调试：

 

        public class MSSQL_LOCAL : MSSQL, IMSSQL
        {
            public MSSQL_LOCAL(string initialCatalog, string userID, string password)
                : base()
            {
                _InitialCatalog = initialCatalog;
                _UserID = userID;
                _Password = password;
                _DataSource = "127.0.0.1";
            }
            public MSSQL_LOCAL() : this(String.Empty, String.Empty, String.Empty) { }
        }

 其他的连接mssql的类，按照MSSQL_LOCAL进行封装就可以了。

下面是连接SQLITE的类，用的是System.Data.Sqlite组件。

和MSSQL一样，首先是ISQLITE个性接口：

        public interface ISQLITE : ISQL
        {
            void Vacuum();
            bool CreateNew { get; set; }
            string DataSource { get; set; }
            string Password { set; }
        }

通用的SQLITE连接类。因为连接参数只要DataSource和Password，所以一个通用的类基本就够用了。

 

        public class SQLITE3 : SQL, ISQLITE
        {
            protected string _DataSource;
            public string DataSource
            { get { return _DataSource; } set { _DataSource = value; } }

            protected string _Password;
            public string Password
            { set { _Password = value; } }

            protected bool _CreateNew;
            public bool CreateNew
            { get { return _CreateNew; } set { _CreateNew = value; } }

            protected override void _Init()
            {
                if (_CreateNew) System.IO.File.Delete(_DataSource);
                System.Data.SQLite.SQLiteConnectionStringBuilder _scsb
                    = _ConnectStringBuilder as System.Data.SQLite.SQLiteConnectionStringBuilder;
                _scsb.DataSource = _DataSource;
                _scsb.Password = _Password;
                _scsb.FailIfMissing = false;
                base._Init();
            }
            public override void Dispose()
            {
                _Adapter.Dispose();
                _Connection = null;
            }
            void ISQLITE.Vacuum() { throw new Exception("no"); }
            public SQLITE3(string datasource, string password) 
            {
                _DataSource = datasource;
                _ConnectStringBuilder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                _Connection = new System.Data.SQLite.SQLiteConnection() as IDbConnection;
                _CreateNew = false;
                _Adapter = new System.Data.SQLite.SQLiteDataAdapter() as DbDataAdapter;
            }
            public SQLITE3(string datasource) : this(datasource, String.Empty) { }
            public SQLITE3() : this(String.Empty) { }
        }

 啊...忘记了，Vacuum方法还没实现。这是个压缩数据库的方法。

 下面是一个具体应用的例子：

 

        string sql = String.Empty;
        string name = SQLITE.SQLStringFilter(TextBox1.Text.Trim());
        string comment = SQLITE.SQLStringFilter(TextBox2.Text.Trim());
        if (Request["id"] == null)
            sql = String.Format("INSERT INTO WebSite(Name,Comment)VALUES('{0}','{1}')"
                , name
                , comment);
        else
            sql = String.Format("UPDATE WebSite SET Name='{0}',Comment='{1}' WHERE ID={2}"
                , name
                , comment
                , SQLITE.SQLStringFilter(Request["id"]));

        using (SQLITE s = new SQLITE())
        {
            s.Open();
            s.Command(sql);
            s.Close();
            s.Dispose();
        }

 这只是一个简单的操作的例子。当进行更复杂的操作的时候，接口将给你带来无尽的益处。