C#数据库操作通用类

在C#中数据库是十分简便的，但是为了达到低耦合的效果，一般还需将数据库的访问操作的基本方法写一公共类，在此我提供一种方式：分两个类实现，一个是生产操作数据库对象的类，两一个类是数据库操作的公共类，这样的话如果要调换数据库的话只需修改数据库操作对象工厂类即可。

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

A.数据库操作对象工厂类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using System.Data.SQLite;

namespace DBUnity
{
    public class DBParaFactory
    {
        protected string connString = "Data Source=userInfo.db;Version=3;";

        public static DbCommand GetDbCommand()
        {
            return new SQLiteCommand();
        }
        public static DbConnection GetDbConnection()
        {
            return new SQLiteConnection(new DBParaFactory().connString);
        }
        public static DbDataAdapter getDbDataAdapter()
        {
            return new SQLiteDataAdapter();
        }
        public static DbParameter getDbParameter()
        {
            return new SQLiteParameter();
        }
    }
}
B.数据库公用类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;

namespace DBUnity
{
    public class DBHelper
    {
        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);
        }

    }
   
}