管理数据库连接字符串，数据库类型（供应商），创建线程内唯一的数据库连接。
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
两个类管理数据库连接和基本操作DBManager------DBHelper
标签：
查看更多关于两个类管理数据库连接和基本操作DBManager------DBHelper的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did119071