C#面向对象的数据库操作类DbHelper

 
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
  
public static class Db
{
    private static ConnectionStringSettings ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"];
    public static DbProviderFactory Factory = DbProviderFactories.GetFactory(ConnectionString.ProviderName);
  
    public static DbConnection CreateConnection()
    {
        DbConnection con = Factory.CreateConnection();
        con.ConnectionString = ConnectionString.ConnectionString;
        return con;
    }
  
    #region 参数
  
    public static DbParameter CreateParameter(DbParameter param)
    {
        return CreateParameter(param.ParameterName,param.Value,param.DbType,param.Size,param.Direction,param.SourceColumn,param.SourceColumnNullMapping,param.SourceVersion);
    }
  
    public static DbParameter CreateParameter(string ParameterName,object Value,DbType? DbType = null,int? Size = null,ParameterDirection? Direction = null,string SourceColumn = null,bool? SourceColumnNullMapping = null,DataRowVersion? SourceVersion = null)
    {
        DbParameter param = Factory.CreateParameter();
  
        param.ParameterName = ParameterName;
        param.Value = Value;
  
        if (DbType != null)
            param.DbType = DbType.Value;
        if (Size != null)
            param.Size = Size.Value;
        if (Direction != null)
            param.Direction = Direction.Value;
        if (SourceColumn != null)
            param.SourceColumn = SourceColumn;
        if (SourceColumnNullMapping != null)
            param.SourceColumnNullMapping = SourceColumnNullMapping.Value;
        if (SourceVersion != null)
            param.SourceVersion = SourceVersion.Value;
  
        return param;
    }
  
    private static DbParameter[] ConvertParameters(object[] parameters)
    {
        List<DbParameter> paramList = new List<DbParameter>();
  
        for (int i = 0; i < parameters.Length; i++)
        {
            if (parameters[i] is DbParameterCollection)
                foreach (DbParameter item in parameters[i] as DbParameterCollection) paramList.Add(CreateParameter(item));
            else if (parameters[i] is DbParameter)
                paramList.Add(parameters[i] as DbParameter);
            else
                paramList.Add(CreateParameter("@" + i,parameters[i]));
        }
  
        return paramList.ToArray();
    }
  
    #endregion
  
    public static Query Query(string query,params object[] parameters)
    {
        return new Query(query,ConvertParameters(parameters));
    }
  
    public static bool Insert(string table,object model)
    {
        StringBuilder fields = new StringBuilder();
        StringBuilder values = new StringBuilder();
        List<DbParameter> paramList = new List<DbParameter>();
  
        foreach (var item in model.GetType().GetProperties())
        {
            fields.AppendFormat("[{0}],",item.Name);
            values.AppendFormat("@{0},item.Name);
            paramList.Add(CreateParameter("@" + item.Name,item.GetValue(model,null)));
        }
  
        return Db.Query(string.Format("insert into [{0}]({1}) values({2})",table,fields.ToString().TrimEnd(','),values.ToString().TrimEnd(',')),paramList.ToArray()).Execute() > 0;
    }
  
    public static bool Update(string table,object model,string where,params object[] parameters)
    {
        StringBuilder fieldsAndValues = new StringBuilder();
        List<DbParameter> paramList = new List<DbParameter>();
  
        foreach (var item in model.GetType().GetProperties())
        {
            fieldsAndValues.AppendFormat("[{0}][email?protected]{0},null)));
        }
  
        paramList.AddRange(ConvertParameters(parameters));
  
        return Db.Query(string.Format("update [{0}] set {1}",fieldsAndValues.ToString().TrimEnd(',') + ((where ?? "").Trim() == "" ? "" : " where " + where)),paramList.ToArray()).Execute() > 0;
    }
}
  
public class Query
{
    #region 构造方法
  
    public Query(string query,DbParameter[] parameters)
    {
        SqlQuery = query;
        Parameters = parameters;
    }
  
    public Query(string query,DbParameter[] parameters,bool isException)
        : this(query,parameters)
    {
        IsException = isException;
    }
  
    #endregion
  
    #region 属性/字段
  
    private bool IsException { get; set; }
    public string SqlQuery { get; set; }
    public DbParameter[] Parameters { get; set; }
  
    #endregion
  
    #region 执行基础
  
    private T ExecuteCommon<T>(Func<DbCommand,T> function)
    {
        using (DbConnection con = Db.CreateConnection())
        using (DbCommand cmd = con.CreateCommand())
        {
            cmd.CommandText = SqlQuery;
            cmd.Parameters.AddRange(Parameters);
            con.Open();
            T result = function(cmd);
            cmd.Parameters.Clear();
            return result;
        }
    }
  
    public T Execute<T>(Func<DbCommand,T> function,T exValue = default(T))
    {
        if (IsException)
            return ExecuteCommon<T>(function);
  
        try
        {
            return ExecuteCommon<T>(function);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
            return exValue;
        }
    }
  
    public void Execute(Action<DbCommand> action)
    {
        Execute(cmd => { action(cmd); return 0; });
    }
  
    #endregion
  
    #region 执行查询
  
    public int Execute()
    {
        return Execute(cmd => cmd.ExecuteNonQuery());
    }
  
    public object Scalar()
    {
        return Execute(cmd => cmd.ExecuteScalar());
    }
  
    public T Scalar<T>()
    {
        return Execute(cmd => (T)cmd.ExecuteScalar());
    }
  
    public Query Top(int count)
    {
        return Db.Query(string.Format("select top {0} * from ({1}) as t0",count,SqlQuery),Parameters);
    }
  
    public Single ToSingle()
    {
        return Execute(cmd =>
        {
            Single s = new Single();
  
            using (var dr = cmd.ExecuteReader())
            {
                if (dr.Read())
                {
                    string name = string.Empty;
  
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        name = dr.GetName(i);
                        s[name] = dr[name];
                    }
                }
                else
                {
                    throw new Exception("Not Find !!");
                }
            }
  
            return s;
        });
  
    }
  
    public DataTable ToDataTable()
    {
        return Execute(cmd =>
        {
            DbDataAdapter da = Db.Factory.CreateDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        });
    }
  
    public List<T> ToList<T>()
    {
        return Execute(cmd =>
        {
            List<T> list = new List<T>();
  
            using (var dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    Type t = typeof(T);
                    T s = default(T);
                    string name = string.Empty;
  
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        name = dr.GetName(i);
                        var pro = t.GetProperty(name);
  
                        if (pro != null)
                            pro.SetValue(s,dr[name],null);
                    }
  
                    list.Add(s);
                }
            }
  
            return list;
        },new List<T>());
    }
  
    public override string ToString()
    {
        return Scalar<string>();
    }
  
    #endregion
  
    #region 分页
  
    private Query RecordCountQuery
    {
        get { return Db.Query(string.Format("select count(*) from ({0}) as t0",Parameters); }
    }
  
    private Query PagerResultQuery(string primaryKey,int pageIndex,int pageSize)
    {
        return Db.Query(string.Format("select top {1} * from ({0}) as t0" +
            (pageIndex > 1 ? " where t0.{3} not in (select top {2} t1.{3} from ({0}) as t1)" : ""),SqlQuery,pageSize,pageIndex * pageSize,primaryKey),Parameters);
    }
  
    public DataTable ToPager(string primaryKey,int pageSize,Action<int> recordCount)
    {
        recordCount(RecordCountQuery.Scalar<int>());
        return PagerResultQuery(primaryKey,pageIndex,pageSize).ToDataTable();
    }
  
    public DataTable ToPager(int pageIndex,Action<int> recordCount)
    {
        return ToPager("Id",recordCount);
    }
  
    public List<T> ToPager<T>(string primaryKey,pageSize).ToList<T>();
    }
  
    public List<T> ToPager<T>(int pageIndex,Action<int> recordCount)
    {
        return ToPager<T>("Id",recordCount);
    }
  
    #endregion
}
  
public class Single : Dictionary<string,object>
{
    public new object this[string name]
    {
        get { return base[name.ToLower()]; }
        set { Add(name.ToLower(),value); }
    }
}