- 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); }
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/2304201512386.html
来源: http://www.codesnippet.cn/detail/2304201512386.html