c# mysql dbhelp,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 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

特别注意：本站所有转载文章言论不代表本站观点！

本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。