如何用DbHelper连接数据库

发布网友 发布时间：2022-04-07 17:09

我来回答

共3个回答

懂视网 时间：2022-04-07 21:30

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

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;
 }

 }
}

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

 }
}

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

标签：

热心网友 时间：2022-04-07 18:38

热心网友 时间：2022-04-07 19:56