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 paramList = new List();
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 paramList = new List();
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 paramList = new List();
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(Func 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(Func function, T exValue = default(T))
{
if (IsException)
return ExecuteCommon(function);
try
{
return ExecuteCommon(function);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return exValue;
}
}
public void Execute(Action 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()
{
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 ToList()
{
return Execute(cmd =>
{
List list = new List();
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());
}
public override string ToString()
{
return Scalar();
}
#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 recordCount)
{
recordCount(RecordCountQuery.Scalar());
return PagerResultQuery(primaryKey, pageIndex, pageSize).ToDataTable();
}
public DataTable ToPager(int pageIndex, int pageSize, Action recordCount)
{
return ToPager("Id", pageIndex, pageSize, recordCount);
}
public List ToPager(string primaryKey, int pageIndex, int pageSize, Action recordCount)
{
recordCount(RecordCountQuery.Scalar());
return PagerResultQuery(primaryKey, pageIndex, pageSize).ToList();
}
public List ToPager(int pageIndex, int pageSize, Action recordCount)
{
return ToPager("Id", pageIndex, pageSize, recordCount);
}
#endregion
}
public class Single : Dictionary
{
public new object this[string name]
{
get { return base[name.ToLower()]; }
set { Add(name.ToLower(), value); }
}
}
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点!
本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。