小北山

博客园 首页 联系 订阅 管理

C#数据库操作通用类

在C#中数据库是十分简便的，但是为了达到低耦合的效果，一般还需将数据库的访问操作的基本方法写一公共类，在此我提供一种方式：分三个类实现，一个是生产操作数据库对象的类（DBParaFactory），该类是一个抽象类，然后就是一个继承自DBParaFactory类个性化类（本例子中是SQLiteDBParaFactory），还有一个类就是数据库操作的公共类，这样的话如果要调换数据库的话只需修改DBParaFactory类得个性化类即可。

下面以一个简单的数据库——SQLite为例：

A.数据库操作对象工厂类
 
  
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Xml;

namespace DBHelper
{
    public abstract class DBParaFactory
    {
        private static string connString = "";

        public static string ConnString
        {
            get { return DBParaFactory.connString; }
            set { DBParaFactory.connString = value; }
        }

        public abstract string GetDbConnectionString();

        public abstract DbCommand GetDbCommand();

        public abstract DbConnection GetDbConnection();

        public abstract DbDataAdapter GetDbDataAdapter();

        public abstract DbParameter GetDbParameter();
    }
}

B.工厂类重写
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SQLite;
using System.Xml;

namespace DBHelper
{
    class SQLiteDBParaFactory : DBParaFactory
    {
        private static DbCommand _GetDbCommand()
        {
            return new SQLiteCommand();
        }
        private static DbConnection _GetDbConnection()
        {
            return new SQLiteConnection(ConnString);
        }
        private static DbDataAdapter _GetDbDataAdapter()
        {
            return new SQLiteDataAdapter();
        }
        private static DbParameter _GetDbParameter()
        {
            return new SQLiteParameter();
        }

        public override DbCommand GetDbCommand()
        {
            return _GetDbCommand();
        }

        public override DbConnection GetDbConnection()
        {
            GetDbConnectionString();
            return _GetDbConnection();
        }

        public override DbDataAdapter GetDbDataAdapter()
        {
            return _GetDbDataAdapter();
        }

        public override DbParameter GetDbParameter()
        {
            return _GetDbParameter();
        }

        public override string GetDbConnectionString()
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load("UserSetting.xml");
                XmlNode node = xmlDoc.SelectSingleNode("Infos/SQLiteDBConnection");
                ConnString = node.Attributes[0].Value;
                return ConnString;
            }
            catch (System.Exception ex)
            {
                return null;
            }
        }
    }
}
 
C.数据库公用类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;

namespace DBHelpers
{
    public class DBHelper
    {
        private static DBParaFactory dbParaFactory;

        public static DBParaFactory DbParaFactory
        {
            get 
            { 
                if (DBHelper.dbParaFactory == null)
                {
                    return new SQLiteDBParaFactory();
                }
                return DBHelper.dbParaFactory; 
            }
            set { DBHelper.dbParaFactory = value; }
        }

        private static DbConnection _Connection;
        /// <summary>
        /// 
        /// </summary>
        public static DbConnection Connection
        {
            get
            {
                if (_Connection == null)
                {
                    _Connection = DbParaFactory.GetDbConnection();
                    _Connection.Open();
                }
                else if (_Connection.State == ConnectionState.Closed)
                {
                    _Connection.Open();
                }
                else if (_Connection.State == ConnectionState.Broken)
                {
                    _Connection.Close();
                    _Connection.Open();
                }
                return _Connection;
            }
        }
        public static bool Exists(string safeSql)
        {
            return (ExecuteCommand(safeSql) > 0);
        }
        public static bool Exists(string Sql, params DbParameter[] values)
        {
            return (ExecuteCommand(Sql, values) > 0);
        }
        /// <summary>
        /// 执行并返回影响行数
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string safeSql)
        {
            DbCommand cmd = cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = safeSql;

            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 执行并返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, params DbParameter[] values)
        {
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;

            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static object GetScalar(string safeSql)
        {
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = safeSql;

            object result = cmd.ExecuteScalar();
            return result;
        }
        /// <summary>
        /// 执行并返回执行结果中的第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static object GetScalar(string sql, params DbParameter[] values)
        {
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddRange(values);
            cmd.CommandText = sql;

            object result = cmd.ExecuteScalar();
            return result;
        }
        /// <summary>
        /// 根据sql语句获得datareader
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static DbDataReader GetReader(string safeSql)
        {
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = safeSql;

            DbDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 根据sql语句获得datareader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DbDataReader GetReader(string sql, params DbParameter[] values)
        {
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(values);

            DbDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 根据sql语句获得DataTable
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = safeSql;

            DbDataAdapter da = DbParaFactory.GetDbDataAdapter();
            da.SelectCommand = cmd;

            da.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 根据sql语句获得DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, params DbParameter[] values)
        {
            DataSet ds = new DataSet();
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(values);

            DbDataAdapter da = DbParaFactory.GetDbDataAdapter();
            da.SelectCommand = cmd;

            da.Fill(ds);
            return ds;
        }
        public static DataAdapter GetDataAdapter(string safeSql)
        {
            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = safeSql;

            DbDataAdapter da = DbParaFactory.GetDbDataAdapter();
            da.SelectCommand = cmd;

            return da;
        }
        /// <summary>
        /// 根据sql语句获得DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataAdapter GetDataAdapter(string sql, params DbParameter[] values)
        {

            DbCommand cmd = DbParaFactory.GetDbCommand();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(values);

            DbDataAdapter da = DbParaFactory.GetDbDataAdapter();
            da.SelectCommand = cmd;

            return da;
        }
        /// <summary> 
        /// DataSet装换为泛型集合 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="ds">DataSet</param> 
        /// <param name="tableIndex">待转换数据表索引</param> 
        /// <returns></returns> 
        public static IList<T> DataSetToIList<T>(DataSet ds, int tableIndex)
        {
            if (ds == null || ds.Tables.Count < 0)
                return null;
            if (tableIndex > ds.Tables.Count - 1)
                return null;
            if (tableIndex < 0)
                tableIndex = 0;

            DataTable dt = ds.Tables[tableIndex];
            // 返回值初始化 
            IList<T> result = new List<T>();
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        if (pi.Name.Equals(dt.Columns[i].ColumnName))
                        {
                            // 数据库NULL值单独处理 
                            if (dt.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, dt.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }

        /// <summary> 
        /// DataSet装换为泛型集合 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="ds">DataSet</param> 
        /// <param name="tableName">待转换数据表名称</param> 
        /// <returns></returns> 
        /// 2008-08-01 22:47 HPDV2806 
        public static IList<T> DataSetToIList<T>(DataSet ds, string tableName)
        {
            int _TableIndex = 0;
            if (ds == null || ds.Tables.Count < 0)
                return null;
            if (string.IsNullOrEmpty(tableName))
                return null;
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                // 获取Table名称在Tables集合中的索引值 
                if (ds.Tables[i].TableName.Equals(tableName))
                {
                    _TableIndex = i;
                    break;
                }
            }
            return DataSetToIList<T>(ds, _TableIndex);
        }

    }
}