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}]=@{0},", item.Name);
            paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
        }
 
        paramList.AddRange(ConvertParameters(parameters));
 
        return Db.Query(string.Format("update [{0}] set {1}", table, 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", SqlQuery), 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 pageIndex, int pageSize, Action<int> recordCount)
    {
        recordCount(RecordCountQuery.Scalar<int>());
        return PagerResultQuery(primaryKey, pageIndex, pageSize).ToDataTable();
    }
 
    public DataTable ToPager(int pageIndex, int pageSize, Action<int> recordCount)
    {
        return ToPager("Id", pageIndex, pageSize, recordCount);
    }
 
    public List<T> ToPager<T>(string primaryKey, int pageIndex, int pageSize, Action<int> recordCount)
    {
        recordCount(RecordCountQuery.Scalar<int>());
        return PagerResultQuery(primaryKey, pageIndex, pageSize).ToList<T>();
    }
 
    public List<T> ToPager<T>(int pageIndex, int pageSize, Action<int> recordCount)
    {
        return ToPager<T>("Id", pageIndex, pageSize, recordCount);
    }
 
    #endregion
}
 
public class Single : Dictionary<string, object>
{
    public new object this[string name]
    {
        get { return base[name.ToLower()]; }
        set { Add(name.ToLower(), value); }
    }
}