两个类管理数据库连接和基本操作DBManager------DBHelper

DBManager类:

     管理数据库连接字符串,数据库类型(供应商),创建线程内唯一的数据库连接。

技术分享
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Common
{
    /// <summary>
    /// 数据库管理类
    /// </summary>
    public class DBManager
    {
        //配置文件中的数据库连接字符串
        private static readonly string connectionStr = ConfigurationManager.AppSettings["conStr"];

        /// <summary>
        /// 数据库类型 默认支持sqlserver数据库
        /// </summary>
        public static readonly string dbProviderName = string.IsNullOrEmpty(ConfigurationManager.AppSettings["dbProviderName"])
                                                    ? "System.Data.SqlClient" : ConfigurationManager.AppSettings["dbProviderName"];

        [ThreadStatic]
        public static DBHelper helper;

        /// <summary>
        /// 创建数据库访问类,且线程内唯一
        /// </summary>
        /// <returns></returns>
        public static DBHelper Instace()
        {
            if (helper == null)
            {
                helper = new DBHelper(connectionStr, dbProviderName);
                return helper;
            }
            return helper;
        }

    }
}
View Code

DBHelper类:

   工厂类创建各类数据库,以及基本操作方法。

技术分享
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;


namespace Common
{
    /// <summary>
    /// 数据库工具类
    /// </summary>
    public class DBHelper
    {
        //数据库连接字符串
        private string connectionStr = string.Empty;
        //数据库工厂,用于创建不同数据库的连接
        private DbProviderFactory factory;
        //数据库连接
        private DbConnection _connection;

        public DbConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }
        /// <summary>
        /// 构造方法获得一个工厂。
        /// </summary>
        /// <param name="connectionStr">数据库连接字符串</param>
        /// <param name="dbProviderName">数据库类型</param>
        public DBHelper(string connectionStr, string dbProviderName)
        {
            this.connectionStr = connectionStr;
            factory = DbProviderFactories.GetFactory(dbProviderName);
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (Connection == null)
            {
                Connection = factory.CreateConnection();
                Connection.ConnectionString = connectionStr;
            }
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
        }

        /// <summary>
        /// 得到Command对象
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        private DbCommand CreateCmd(CommandType cmdType, string cmdText, params DbParameter[] pars)
        {
            DbCommand cmd = factory.CreateCommand();
            cmd.Connection = Connection;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;
            if (pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }

            return cmd;
        }
        /// <summary>
        /// 得到reader对象
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] pars)
        {
            OpenConnection();
            DbCommand cmd = CreateCmd(cmdType, cmdText, pars);
            return cmd.ExecuteReader();
        }
        /// <summary>
        /// 执行非查询操作,返回影响行数
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] pars)
        {
            OpenConnection();
            DbCommand cmd = CreateCmd(cmdType, cmdText, pars);
            int count = cmd.ExecuteNonQuery();
            Connection.Close();
            if (count > 0)
            {
                return count;
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 返回查询的第一行第一列
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public object ExecuteScal(CommandType cmdType, string cmdText, params DbParameter[] pars)
        {
            OpenConnection();
            DbCommand cmd = CreateCmd(cmdType, cmdText, pars);
            object obj = cmd.ExecuteScalar();
            Connection.Close();
            return obj;
        }

        /// <summary>
        /// 根据查询获取数据集
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] pars)
        {
            OpenConnection();
            DbCommand cmd = CreateCmd(cmdType, cmdText, pars);
            DbDataAdapter da = factory.CreateDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            Connection.Close();
            return ds;
        }

        #region 创建类的对象,返回泛型集合
        public static IList<T> ToList<T>(DbDataReader reader)
        {
            Type type = typeof(T);
            IList<T> list = null;
            if (type.IsValueType || type == typeof(string))
                list = CreateValue<T>(reader, type);
            else
                list = CreateObject<T>(reader, type);
            reader.Dispose();
            reader.Close();
            return list;
        }
        private static IList<T> CreateObject<T>(DbDataReader reader, Type type)
        {
            IList<T> list = new List<T>();
            PropertyInfo[] properties = type.GetProperties();
            string name = string.Empty;
            while (reader.Read())
            {
                T local = Activator.CreateInstance<T>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    name = reader.GetName(i);
                    foreach (PropertyInfo info in properties)
                    {
                        if (name.Equals(info.Name)) { info.SetValue(local, Convert.ChangeType(reader[info.Name], info.PropertyType), null); break; }
                    }
                }
                list.Add(local);
            }
            return list;
        }
        private static IList<T> CreateValue<T>(DbDataReader reader, Type type)
        {
            IList<T> list = new List<T>();
            while (reader.Read())
            {
                T local = (T)Convert.ChangeType(reader[0], type, null);
                list.Add(local);
            }
            return list;
        }
        #endregion

    }
}
View Code

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。