/// <summary>
/// 批量新增
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model"></param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static bool GTListAdd <T>(this List <T> Model, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
List <string> Listr = new List <string>();
string ModelName = string.Empty;
string ColumName = string.Empty;
string DbValue = string.Empty;
foreach (T item in Model)
{
PropertyInfo[] propertys = item.GetType().GetProperties();
ModelName = item.GetType().Name;
List <string> FileName = new List <string>();
List <string> Value = new List <string>();
foreach (PropertyInfo pi in propertys)
{
GDColoum objAttrs = pi.GetCustomAttribute <GDColoum>();
if (objAttrs != null)
{
object obj = pi.GetValue(item, null);
if (obj != null)
{
DbValue = pi.GetValue(item, null).DbTypeStr();
FileName.Add(pi.Name);
Value.Add(DbValue);
}
}
}
ColumName = string.Join(",", FileName);
Listr.Add("(" + string.Join(",", Value) + ")");
}
return(Helper.BsExecuteSQL("insert into " + ModelName + "(" + ColumName + ") values" + string.Join(",", Listr)));
}
/// <summary>
/// 新增返回自增Id
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model"></param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static int GTReturnAddId <T>(this T Model, string ConnectionName = null) where T : new()
{
List <SqlParameter> Parameter = new List <SqlParameter>();
PropertyInfo[] propertys = Model.GetType().GetProperties();
string ModelName = Model.GetType().Name;
List <string> FileName = new List <string>();
List <string> Value = new List <string>();
string DbValue = string.Empty;
foreach (PropertyInfo pi in propertys)
{
GDColoum objAttrs = pi.GetCustomAttribute <GDColoum>();
if (objAttrs != null)
{
object obj = pi.GetValue(Model, null);
if (obj != null)
{
Parameter.Add(new SqlParameter("@" + pi.Name, pi.GetValue(Model, null)));
FileName.Add(pi.Name);
Value.Add("@" + pi.Name);
}
}
}
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
return(Helper.GetAddGetId(string.Join(",", FileName), string.Join(",", Value), ModelName, Parameter.ToArray()));
}
/// <summary>
/// 根据条件进行更新
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="Model">对象</param>
/// <param name="UpCondition">更新条件</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static bool GTUpdata <T>(this T Model, Expression <Func <T, bool> > UpCondition = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
List <SqlParameter> ListParameter = new List <SqlParameter>();
PropertyInfo[] propertys = Model.GetType().GetProperties();
string ModelName = Model.GetType().Name;
string DbValue = string.Empty;
List <string> FileName = new List <string>();
foreach (PropertyInfo pi in propertys)
{
GDColoum objAttrs = pi.GetCustomAttribute <GDColoum>();
if (objAttrs != null)
{
object obj = pi.GetValue(Model, null);
if (obj != null)
{
if (!objAttrs.IsKey)
{
FileName.Add(pi.Name + "=@" + pi.Name);
ListParameter.Add(new SqlParameter("@" + pi.Name, obj));
}
}
}
}
return(Helper.BsExecuteSQL("update " + ModelName + " set " + string.Join(",", FileName) + (UpCondition == null ? "" : " where " + UpCondition.DealExpress()), ListParameter.ToArray()));
}
/// <summary>
/// 根据主键进行删除
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="Model">对象</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static bool GTDelKey <T>(this T Model, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
PropertyInfo[] propertys = Model.GetType().GetProperties();
string ModelName = Model.GetType().Name;
string KeyStr = " and 1=2";
foreach (PropertyInfo pi in propertys)
{
GDColoum objAttrs = pi.GetCustomAttribute <GDColoum>();
if (objAttrs != null)
{
object obj = pi.GetValue(Model, null);
if (obj != null)
{
if (objAttrs.IsKey)
{
KeyStr = " and " + pi.Name + "=" + obj.ToString();
break;
}
}
}
}
return(Helper.BsExecuteSQL("Delete from " + ModelName + " where 1=1 " + KeyStr));
}
/// <summary>
/// 根据主键进行更新
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="Model">对象</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static bool GTUpdateKey <T>(this T Model, string ConnectionName = null) where T : new()
{
PropertyInfo[] propertys = Model.GetType().GetProperties();
List <SqlParameter> ListParameter = new List <SqlParameter>();
string ModelName = Model.GetType().Name;
string KeyStr = " and 1=2";
List <string> FileName = new List <string>();
foreach (PropertyInfo pi in propertys)
{
GDColoum objAttrs = pi.GetCustomAttribute <GDColoum>();
if (objAttrs != null)
{
object obj = pi.GetValue(Model, null);
if (obj != null)
{
if (objAttrs.IsKey)
{
KeyStr = " and " + pi.Name + "=" + obj.ToString();
}
else
{
ListParameter.Add(new SqlParameter("@" + pi.Name, obj));
FileName.Add(pi.Name + "=@" + pi.Name);
}
}
}
}
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
return(Helper.BsExecuteSQL("update " + ModelName + " set " + string.Join(",", FileName) + " where 1=1" + KeyStr, ListParameter.ToArray()));
}
/// <summary>
/// 根据条件进行删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model"></param>
/// <param name="Condition">删除条件</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static bool GTDel <T>(this T Model, Expression <Func <T, bool> > Condition = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
string ModelName = Model.GetType().Name;
return(Helper.BsExecuteSQL(" delete from " + ModelName + " where 1=1 " + (Condition == null ? "" : " and " + Condition.DealExpress())));
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ThisPage">当前页数</param>
/// <param name="Storing">排序</param>
/// <param name="PageSize">分页大小</param>
/// <param name="source">查询条件</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static GDPaging <T> GTPaging <T>(this IEnumerable <T> source, int ThisPage = 1, int PageSize = 10, string Storing = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
GDPaging <T> GDModel = new GDPaging <T>();
List <T> lt = new List <T>();
string ModelName = typeof(T).Name;
List <string> Conditions = new List <string>();
string DbValue = string.Empty;
SqlParameter[] parameter =
{
new SqlParameter("@ThisPage", ThisPage),
new SqlParameter("@PageSize", PageSize),
new SqlParameter("@TableName", ModelName),
new SqlParameter("@Condition", source.ToString().Contains("Where")?(source.AsQueryable().Expression).DealExpress():""),
new SqlParameter("@Sorting", (string.IsNullOrEmpty(Storing)?(" Id desc"):Storing))
};
DataSet ds = Helper.MoreExecuteStorage("GT_Paging", parameter);
if (ds == null || ds.Tables.Count < 2 || ds.Tables[0].Rows.Count == 0)
{
GDModel.GDModel = new List <T>();
return(GDModel);
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
lt.Add(ds.Tables[0].Rows[i].ToModel <T>());
}
GDModel.GDModel = lt;
GDModel.Count = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
}
return(GDModel);
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model">对象</param>
/// <param name="ThisPage">当前页数</param>
/// <param name="Condition">条件</param>
/// <param name="Storing">排序</param>
/// <param name="PageSize">当前也分大小</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static GDPaging <T> GTPagingByCondition <T>(this T Model, int ThisPage, string Condition, int PageSize = 10, string Storing = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
GDPaging <T> GDModel = new GDPaging <T>();
List <T> lt = new List <T>();
string ModelName = Model.GetType().Name;
SqlParameter[] parameter =
{
new SqlParameter("@ThisPage", ThisPage),
new SqlParameter("@PageSize", PageSize),
new SqlParameter("@TableName", ModelName),
new SqlParameter("@Condition", Condition),
new SqlParameter("@Sorting", (string.IsNullOrEmpty(Storing)?(" Id desc"):Storing))
};
DataSet ds = Helper.MoreExecuteStorage("GT_Paging", parameter);
if (ds == null || ds.Tables.Count < 2 || ds.Tables[0].Rows.Count == 0)
{
return(GDModel);
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
lt.Add(ds.Tables[0].Rows[i].ToModel <T>());
}
GDModel.GDModel = lt;
GDModel.Count = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
}
return(GDModel);
}
/// <summary>
/// 执行多个表操作
/// </summary>
/// <param name="obj"></param>
/// <param name="Connection">数据库连接名字</param>
/// <returns></returns>
public static bool GTMergerExecute(string Connection, params object[] obj)
{
List <string> SQLStr = new List <string>();
foreach (var item in obj)
{
SQLStr.Add((item as GDModel.MergerModel).SQLStr);
}
SQLServerDBHeper Helper = GetDBHeper(Connection);
return(Helper.BsExecuteSQL(string.Join(";", SQLStr)));
}
/// <summary>
/// 查询一条信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model"></param>
/// <param name="expression">条件</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static T GTInfo <T>(this T Model, Expression <Func <T, bool> > expression = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
T t = new T();
List <SqlParameter> Parameter = new List <SqlParameter>();
string ModelName = Model.GetType().Name;
List <string> Conditions = new List <string>();
string DbValue = string.Empty;
DataRow dr = Helper.BsRows("select * from " + ModelName + " where " + (expression == null ? "" : expression.DealExpress()), Parameter.ToArray());
if (dr == null)
{
return(t);
}
return(dr.ToModel <T>());
}
/// <summary>
/// 泛型查询默认主键排倒叙
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source">条件</param>
/// <param name="Storing">排序</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static List <T> GTSearch <T>(this IEnumerable <T> source, string Storing = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
List <T> lt = new List <T>();
DataTable dt = Helper.GetDataTable((source.ToString().Contains("Where") ? (source.AsQueryable().Expression).DealExpress() : "") + " order by " + (string.IsNullOrEmpty(Storing) ? (" Id desc") : Storing), typeof(T).Name);
if (dt == null || dt.Rows.Count == 0)
{
return(lt);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lt.Add(dt.Rows[i].ToModel <T>());
}
}
return(lt);
}
/// <summary>
/// 泛型查询默认主键排倒叙
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model">对象</param>
/// <param name="Exp">条件</param>
/// <param name="Storing">排序</param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static List <T> GTSearch <T>(this T Model, Expression <Func <T, bool> > Exp = null, string Storing = null, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
List <T> lt = new List <T>();
string Condition = Exp.DealExpress();
DataTable dt = Helper.GetDataTable(string.IsNullOrEmpty(Condition) ? "" : " and " + Condition + " order by " + (string.IsNullOrEmpty(Storing) ? (" Id desc") : Storing), typeof(T).Name);
if (dt == null || dt.Rows.Count == 0)
{
return(lt);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lt.Add(dt.Rows[i].ToModel <T>());
}
}
return(lt);
}
/// <summary>
/// 泛型查询默认主键排倒叙
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model"></param>
/// <param name="Condition"></param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static List <T> GTSearchCondition <T>(this T Model, string Condition, string ConnectionName = null) where T : new()
{
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
List <T> lt = new List <T>();
PropertyInfo[] propertys = Model.GetType().GetProperties();
string ModelName = Model.GetType().Name;
DataTable dt = Helper.GetDataTable(Condition, ModelName);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lt.Add(dt.Rows[i].ToModel <T>());
}
}
return(lt);
}
/// <summary>
/// 返回DataSet结果存储过程
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Model"></param>
/// <param name="ConnectionName">数据库连接名字</param>
/// <returns></returns>
public static DataSet GTDsStorage <T>(this T Model, string ConnectionName = null) where T : new()
{
List <SqlParameter> Parameter = new List <SqlParameter>();
PropertyInfo[] propertys = Model.GetType().GetProperties();
string ModelName = Model.GetType().Name;
foreach (PropertyInfo pi in propertys)
{
GDColoum objAttrs = pi.GetCustomAttribute <GDColoum>();
if (objAttrs != null)
{
object obj = pi.GetValue(Model, null);
if (obj != null)
{
Parameter.Add(new SqlParameter("@" + pi.Name, pi.GetValue(Model, null)));
}
}
}
SQLServerDBHeper Helper = GetDBHeper(ConnectionName);
return(Helper.MoreExecuteStorage(ModelName, Parameter.ToArray()));
}
/// <summary>
/// 查询多个表;
/// </summary>
/// <param>调用：var f= objc[0].As<!--实体-->()</param>
/// <param name="Connection">数据库连接</param>
/// <param name="obj"></param>
public static List <List <object> > GTMergerSearch(string Connection, params object[] obj)
{
List <List <object> > objlist = new List <List <object> >();
List <GDModel.MergerModel> ListSQLStr = new List <GDModel.MergerModel>();
foreach (var item in obj)
{
ListSQLStr.Add((item as GDModel.MergerModel));
}
SQLServerDBHeper Helper = GetDBHeper(Connection);
var r = Helper.BsDataSet(string.Join(";", ListSQLStr.Select(s => s.SQLStr).ToArray()));
foreach (DataTable item in r.Tables)
{
var sl = ListSQLStr.Where(w => w.EntityName == item.TableName).Select(s => s.Entity).ToArray().First();
List <object> listable = new List <object>();
for (int i = 0; i < item.Rows.Count; i++)
{
listable.Add(item.Rows[i].ToModel(sl));
}
objlist.Add(listable);
}
return(objlist);
}
