/// <summary>
/// 执行Insert操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="columns"></param>
public virtual void DoInsert <TSource>(LinqDLR2Sql <TSource> source, object columns)
{
if (columns == null)
{
throw new ArgumentNullException("columns");
}
if (CurrentOperation == LinqDLR2SQLOperation.SelectJoin ||
CurrentOperation == LinqDLR2SQLOperation.SelectMany)
{
throw new NotSupportedException("当前模式不支持INSERT操作");
}
FrameDLRObject columnsobj = FrameDLRObject.CreateInstance(columns);
var columnsstr = "";
var valuestr = "";
foreach (var key in columnsobj.Keys)
{
columnsstr += $"{string.Format(SqlFlags.Column_Quatation, key)},";
valuestr += $"{Convert2Express(columnsobj.GetValue(key))},";
}
columnsstr = columnsstr == "" ? "" : columnsstr.Substring(0, columnsstr.Length - 1);
valuestr = valuestr == "" ? "" : valuestr.Substring(0, valuestr.Length - 1);
var sql = "INSERT INTO {tables}({columns})VALUES({values})";
var alianname = string.IsNullOrEmpty(source.AliasName) ? "" : $"{source.AliasName}";
var tables = source.Table;
CurrentSQL = sql.Replace("{tables}", tables).Replace("{columns}", columnsstr).Replace("{values}", valuestr);
}
/// <summary>
/// 执行update操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
public virtual void DoUpdate <TSource>(LinqDLR2Sql <TSource> source, object columns)
{
if (columns == null)
{
throw new ArgumentNullException("columns");
}
if (CurrentOperation == LinqDLR2SQLOperation.SelectJoin ||
CurrentOperation == LinqDLR2SQLOperation.SelectMany)
{
throw new NotSupportedException("当前模式不支持Update操作");
}
FrameDLRObject columnsobj = FrameDLRObject.CreateInstance(columns);
var columnsstr = "";
foreach (var key in columnsobj.Keys)
{
columnsstr += $"{string.Format(SqlFlags.Column_Quatation, key)}={Convert2Express(columnsobj.GetValue(key))},";
}
columnsstr = columnsstr == "" ? "" : columnsstr.Substring(0, columnsstr.Length - 1);
var sql = "UPDATE {tables} SET {columns} {where}";
var alianname = string.IsNullOrEmpty(source.AliasName) ? "" : $"{source.AliasName}";
var tables = source.Table;
var where = (Lastwhere == null || string.IsNullOrEmpty(Lastwhere.Result)) ? "" : $"WHERE {Lastwhere.Result.Replace($"{alianname}.", "")}";
CurrentSQL = sql.Replace("{tables}", tables).Replace("{columns}", columnsstr).Replace("{where}", where);
}
/// <summary>
/// 直接执行union query操作，并返回结果
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="order_by"></param>
/// <param name="is_all"></param>
/// <param name="express"></param>
/// <returns></returns>
public static UnitDataCollection UnionQuery <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up, string order_by, bool is_all = false, params LinqDLR2Sql <TSource>[] express)
{
var sql = source.ToSql();
var data = source.SQLGenerator.ConditionValues;
if (express != null)
{
foreach (var e in express)
{
sql += $@" {(is_all?"union all":"union")}
{e.ToSql()}";
foreach (var item in e.SQLGenerator.ConditionValues)
{
data.Add(item.Key, item.Value);
}
}
}
sql = $"select * from ({sql}) t";
if (!string.IsNullOrEmpty(order_by))
{
sql += $" order by {order_by}";
}
up.SetValue("sql", sql);
foreach (var item in data)
{
up.SetValue(item.Key, item.Value);
}
return(DBUnitProxy.Query <LamdaExpressUnit>(up, ""));
}
/// <summary>
/// 直接执行query操作，并返回结果
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <returns></returns>
public static UnitDataCollection Query <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up)
{
var sql = source.ToSql();
var data = source.SQLGenerator.ConditionValues;
up.SetValue("sql", sql);
foreach (var item in data)
{
up.SetValue(item.Key, item.Value);
}
return(DBUnitProxy.Query <LamdaExpressUnit>(up, ""));
}
/// <summary>
/// 执行Delete操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
public virtual void DoDelete <TSource>(LinqDLR2Sql <TSource> source)
{
if (CurrentOperation == LinqDLR2SQLOperation.SelectJoin ||
CurrentOperation == LinqDLR2SQLOperation.SelectMany)
{
throw new NotSupportedException("当前模式不支持Delete操作");
}
var sql = "DELETE FROM {tables} {where}";
var alianname = string.IsNullOrEmpty(source.AliasName) ? "" : $"{source.AliasName}";
var tables = source.Table;
var where = (Lastwhere == null || string.IsNullOrEmpty(Lastwhere.Result)) ? "" : $"WHERE {Lastwhere.Result.Replace($"{alianname}.","")}";
CurrentSQL = sql.Replace("{tables}", tables).Replace("{where}", where);
}
/// <summary>
/// 直接执行Insert操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="columns"></param>
public static void Insert <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up, object columns)
{
if (source.SQLGenerator is DaoSqlGenerator)
{
((DaoSqlGenerator)source.SQLGenerator).DoInsert(source, columns);
var sql = ((DaoSqlGenerator)source.SQLGenerator).CurrentSQL;
var data = source.SQLGenerator.ConditionValues;
up.SetValue("sql", sql);
foreach (var item in data)
{
up.SetValue(item.Key, item.Value);
}
DBUnitProxy.NonQuery <LamdaExpressUnit>(up, "nonquery");
}
else
{
throw new NotSupportedException("当前对象不支持Insert操作");
}
}
public virtual UnitDataCollection ExcuteLamda <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable)
{
var sql = ltable.ToSql();
var data = ltable.SQLGenerator.ConditionValues;
//GlobalCommon.Logger.WriteLog(LoggerLevel.DEBUG, $"LamdaExpress解析后的sql为:{sql}");
p.SetValue("sql", sql);
foreach (var item in data)
{
p.SetValue(item.Key, item.Value);
}
if ((ltable.CurrentOperationType & LinqDLR2SQLOperation.Select) == LinqDLR2SQLOperation.Select ||
(ltable.CurrentOperationType & LinqDLR2SQLOperation.SelectJoin) == LinqDLR2SQLOperation.SelectJoin ||
(ltable.CurrentOperationType & LinqDLR2SQLOperation.SelectMany) == LinqDLR2SQLOperation.SelectMany)
{
return(Query <LamdaExpressUnit>(p, ""));
}
else
{
NonQuery <LamdaExpressUnit>(p, "");
return(new UnitDataCollection());
}
}
/// <summary>
/// 直接执行query操作，并返回结果集
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <returns></returns>
public static List <FrameDLRObject> GetQueryList <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up)
{
return(source.Query(up).QueryData <FrameDLRObject>());
}
/// <summary>
/// 直接执行union query操作，并返回结果集
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="order_by"></param>
/// <param name="is_all"></param>
/// <param name="express"></param>
/// <returns></returns>
public static List <FrameDLRObject> GetUnionQueryList <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up, string order_by, bool is_all = false, params LinqDLR2Sql <TSource>[] express)
{
return(source.UnionQuery(up, order_by, is_all, express).QueryData <FrameDLRObject>());
}
/// <summary>
/// 执行Distinct操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <returns></returns>
public static UnitDataCollection Distinct <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up)
{
return(source.Distinct().Query(up));
}
/// <summary>
/// 判断资料是否存在
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <returns></returns>
public static bool IsExists <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up)
{
return(source.Count(up) > 0);
}
/// <summary>
/// 执行Avg操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="selector"></param>
/// <returns></returns>
public static object Avg <TSource, TResult>(this LinqDLR2Sql <TSource> source, UnitParameter up, Func <TSource, TResult> selector)
{
return(source.Avg(selector).Query(up).QueryTable[0, 0]);
}
/// <summary>
/// 执行Avg操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="column"></param>
/// <returns></returns>
public static object Avg <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up, string column)
{
return(source.Avg(column).Query(up).QueryTable[0, 0]);
}
public virtual UnitDataCollection ExcuteLamda(UnitParameter p, LinqDLR2Sql <dynamic> ltable)
{
return(ExcuteLamda <dynamic>(p, ltable));
}
/// <summary>
/// 直接执行count的DB操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <returns></returns>
public static int Count <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up)
{
var result = source.Count().Query(up);
return(result.QueryTable.RowLength > 0 ? IntStd.IsNotIntThen(result.QueryTable[0, 0]) : 0);
}
public virtual UnitDataCollection LamdaQueryByPage <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable, string orderbyexpress)
{
var sql = ltable.ToSql();
var data = ltable.SQLGenerator.ConditionValues;
//GlobalCommon.Logger.WriteLog(LoggerLevel.DEBUG, $"LamdaExpress解析后的sql为:{sql}");
p.SetValue("sql", sql);
p.SetValue("orderby", orderbyexpress);
foreach (var item in data)
{
p.SetValue(item.Key, item.Value);
}
if (ltable.CurrentOperationType == LinqDLR2SQLOperation.Select ||
ltable.CurrentOperationType == LinqDLR2SQLOperation.SelectJoin ||
ltable.CurrentOperationType == LinqDLR2SQLOperation.SelectMany)
{
return(QueryByPage <LamdaExpressUnit>(p, ""));
}
else
{
throw new FrameException("LamdaQueryByPage只适用于select类型的操作");
}
}
public virtual UnitDataCollection LamdaQueryByPage(UnitParameter p, LinqDLR2Sql <dynamic> ltable, string orderbyexpress)
{
return(LamdaQueryByPage <dynamic>(p, ltable, orderbyexpress));
}
public virtual int Count <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable)
{
var result = ExcuteLamda <TSource>(p, ltable.Count());
return(result.QueryTable.RowLength > 0 ? IntStd.IsNotIntThen(result.QueryTable[0, 0]) : 0);
}
public virtual object Sum <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable, string sumColumn)
{
var result = ExcuteLamda <TSource>(p, ltable.Sum(sumColumn));
return(result.QueryTable.RowLength > 0 ? result.QueryTable[0, 0] : null);
}
public virtual bool IsExists <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable)
{
return(Count <TSource>(p, ltable) > 0);
}
