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}]=@{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); }
    }
}