首先,我们来分析SELECT 语法:
[[AS] Column_Name] [, [Alias.] Select_Item [[AS] Column_Name] ...]
FROM [FORCE] [DatabaseName!] Table [[AS] Local_Alias]
[ [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN DatabaseName!]
Table [[AS] Local_Alias] [ON JoinCondition ...]
[[INTO Destination] | [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]]
[PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT]
[WHERE JoinCondition [AND JoinCondition ...] [AND | OR FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]] [HAVING FilterCondition] [UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]
根据以上的语法,我的分析:
Skip、 Top 一定要支持
Percent 很少用,可以不支持
[LEFT|RIGHT|FULL] JOIN ON 坚决不支持,用IN查询替换,然后在内存中JOIN
WHERE 多个条件组合
要支持的条件有: =, >, >=, <, <=, <>, IN, NOT IN, IS NULL, IS NOT NULL, LIKE, NOT LIKE 共12种条件
先不支持 BETWEEN, EXISTS, NOT EXSITS(都可用其他的条件替换)
Group 会影响结果集(配合各种聚合函数使用)---复杂,不实现,GROUP对表的扫描是和没GROUP一样的,只是影响网络传输而已
Having 多个条件组合---复杂,不实现,只是影响网络传输而已
Order By 支持,在分页的时候需要
注意,我们不支持OR,只支持AND,以为,OR条件可以被UNION代替,而UNION应该在内存中进行,而不是在数据库!
根据以上的分析,我写了以下的代码:
{
Equal,
NotEqual,
Bigger,
NotBigger,
Smaller,
NotSmaller,
Like,
NotLike
}
public interface IWhereCondition { }
public abstract class ConditionBase
{
public object Column { get; set; }
}
public abstract class WhereConditionBase : ConditionBase, IWhereCondition
{
public WhereConditionType Type { get; set; }
public object ObjValue { get; set; }
}
public abstract class WhereInConditionBase : ConditionBase, IWhereCondition
{
public bool InOrNot { get; set; }
public IEnumerable<object> ObjValueList { get; set; }
}
public class WhereCondition<T> : WhereConditionBase
{
private T _value;
public T Value
{
get { return _value; }
set
{
ObjValue = value;
_value = value;
}
}
}
public class WhereLikeCondition : WhereCondition<string>
{
}
public class WhereInCondition<T> : WhereInConditionBase
{
private IEnumerable<T> _valueList;
public IEnumerable<T> ValueList
{
get { return _valueList; }
set
{
ObjValueList = value.Cast<object>();
_valueList = value;
}
}
}
public class WhereNullCondition : ConditionBase, IWhereCondition
{
public bool NullOrNot { get; set; }
}
public class OrderByCondition
{
public object Column { get; set; }
public bool AscOrNot { get; set; }
}
以上实现WHERE条件和ORDER BY.
然后我们写了对应的扩展方法,以及两个委托:
{
public static IWhereCondition SqlEqual<T>(this object column, T value)
{
return new WhereCondition<T>()
{
Column = column,
Value = value,
Type = WhereConditionType.Equal
};
}
public static IWhereCondition SqlNotEqual<T>(this object column, T value)
{
return new WhereCondition<T>()
{
Column = column,
Value = value,
Type = WhereConditionType.NotEqual
};
}
public static IWhereCondition SqlBigger<T>(this object column, T value)
{
return new WhereCondition<T>()
{
Column = column,
Value = value,
Type = WhereConditionType.Bigger
};
}
public static IWhereCondition SqlNotBigger<T>(this object column, T value)
{
return new WhereCondition<T>()
{
Column = column,
Value = value,
Type = WhereConditionType.NotBigger
};
}
public static IWhereCondition SqlSmaller<T>(this object column, T value)
{
return new WhereCondition<T>()
{
Column = column,
Value = value,
Type = WhereConditionType.Smaller
};
}
public static IWhereCondition SqlNotSmaller<T>(this object column, T value)
{
return new WhereCondition<T>()
{
Column = column,
Value = value,
Type = WhereConditionType.NotSmaller
};
}
public static IWhereCondition SqlLike(this object column, string value)
{
return new WhereLikeCondition
{
Column = column,
Value = value,
Type = WhereConditionType.Like
};
}
public static IWhereCondition SqlNotLike(this object column, string value)
{
return new WhereLikeCondition
{
Column = column,
Value = value,
Type = WhereConditionType.NotLike
};
}
public static IWhereCondition SqlIn<T>(this object column, IEnumerable<T> valueList)
{
return new WhereInCondition<T>
{
Column = column,
ValueList = valueList,
InOrNot = true
};
}
public static IWhereCondition SqlNotIn<T>(this object column, IEnumerable<T> valueList)
{
return new WhereInCondition<T>
{
Column = column,
ValueList = valueList,
InOrNot = false
};
}
public static IWhereCondition SqlNull(this object column)
{
return new WhereNullCondition
{
Column = column,
NullOrNot = true
};
}
public static IWhereCondition SqlNotNull(this object column)
{
return new WhereNullCondition
{
Column = column,
NullOrNot = false
};
}
public static OrderByCondition SqlOrderBy(this object column)
{
return new OrderByCondition
{
Column = column,
AscOrNot = true
};
}
public static OrderByCondition SqlOrderByDesc(this object column)
{
return new OrderByCondition
{
Column = column,
AscOrNot = false
};
}
}
public delegate IWhereCondition WhereDelegate<TK>(TK p);
public delegate OrderByCondition OrderByDelegate<TK>(TK p);
好吧,现在让我们一个类解决问题吧:
{
private bool _isDistinct;
private int _skipNum = -1;
private int _tackNum = -1;
private string _cmdAlians;
private IList<IWhereCondition> _whereConditions = new List<IWhereCondition>();
private IList<OrderByCondition> _orderByConditions = new List<OrderByCondition>();
public LinqDAC<T> SqlDistinct()
{
_isDistinct = true;
return this;
}
public LinqDAC<T> SqlSkip(int skipNum)
{
_skipNum = skipNum;
return this;
}
public LinqDAC<T> SqlTack(int tackNum)
{
_tackNum = tackNum;
return this;
}
public LinqDAC<T> SqlSelect(string cmdAlians)
{
_cmdAlians = cmdAlians;
return this;
}
public LinqDAC<T> SqlWhere(Expression<WhereDelegate<T>> whereDelegate)
{
var expStr = whereDelegate.Body.ToString();
var conditionBase = whereCondition as ConditionBase;
var dotIndex = expStr.IndexOf('.');
var endIndex = expStr.IndexOf('.', dotIndex + 1);
var endIndex2 = expStr.IndexOf(')');
endIndex = endIndex < endIndex2 ? endIndex : endIndex2;
if (conditionBase != null) conditionBase.Column = expStr.Substring(dotIndex + 1, endIndex - dotIndex - 1);
_whereConditions.Add(whereCondition);
return this;
}
public LinqDAC<T> SqlOrderBy(Expression<OrderByDelegate<T>> orderByDelegate)
{
var expStr = orderByDelegate.Body.ToString();
var dotIndex = expStr.IndexOf('.');
var endIndex = expStr.IndexOf('.', dotIndex + 1);
var endIndex2 = expStr.IndexOf(')');
endIndex = endIndex < endIndex2 ? endIndex : endIndex2;
var orderByCondition = orderByDelegate.Compile()(new T());
if (orderByCondition != null) orderByCondition.Column = expStr.Substring(dotIndex + 1, endIndex - dotIndex - 1);
_orderByConditions.Add(orderByCondition);
return this;
}
public IEnumerator<T> GetEnumerator()
{
//得到语句并且查询
return ToList().GetEnumerator();
}
IEnumerator IEnumerable.GetEnumerator()
{
return GetEnumerator();
}
public IList<T> ToList()
{
string providerAlians = DataBaseInstance.ProviderAlians;
string connAlians = DataBaseInstance.ConnAlians;
AdoHelper ado = AdoHelper.CreateHelper(providerAlians);
string conStr = DbConnectionStore.TheInstance.GetConnection(connAlians);
if (string.IsNullOrEmpty(_cmdAlians))
_cmdAlians = "Select" + typeof(T).Name;
var com = AdoHelper.GetCommandInfo(connAlians, _cmdAlians);
var comtext = com.RealCommandText(typeof (T), ado, _whereConditions, _orderByConditions, _isDistinct, _skipNum, _tackNum);
List<IDataParameter> outP;
return ado.QuerySqlForList<T, string, T>(conStr, comtext, null, null, out outP,
connAlians, _cmdAlians);
}
public void SqlDelete(string cmdAlians = null)
{
_cmdAlians = cmdAlians;
string providerAlians = DataBaseInstance.ProviderAlians;
string connAlians = DataBaseInstance.ConnAlians;
AdoHelper ado = AdoHelper.CreateHelper(providerAlians);
string conStr = DbConnectionStore.TheInstance.GetConnection(connAlians);
if (string.IsNullOrEmpty(_cmdAlians))
_cmdAlians = "Delete" + typeof(T).Name;
var com = AdoHelper.GetCommandInfo(connAlians, _cmdAlians);
var comtext = com.RealCommandText(typeof(T), ado, _whereConditions, _orderByConditions);
List<IDataParameter> outP;
ado.ExecuteNonQuery(conStr, comtext, null, CommandType.Text, out outP,
connAlians, _cmdAlians);
}
public void SqlUpdate(string cmdAlians = null)
{
_cmdAlians = cmdAlians;
string providerAlians = DataBaseInstance.ProviderAlians;
string connAlians = DataBaseInstance.ConnAlians;
AdoHelper ado = AdoHelper.CreateHelper(providerAlians);
string conStr = DbConnectionStore.TheInstance.GetConnection(connAlians);
if (string.IsNullOrEmpty(_cmdAlians))
_cmdAlians = "Update" + typeof(T).Name;
var com = AdoHelper.GetCommandInfo(connAlians, _cmdAlians);
var comtext = com.RealCommandText(typeof(T), ado, _whereConditions, _orderByConditions);
List<IDataParameter> outP;
ado.ExecuteNonQuery(conStr, comtext, null, CommandType.Text, out outP,
connAlians, _cmdAlians);
}
public void Clear()
{
_whereConditions = new List<IWhereCondition>();
_orderByConditions = new List<OrderByCondition>();
_isDistinct = false;
_skipNum = -1;
_tackNum = -1;
_cmdAlians = null;
}
}
注意到了么?我们继承了之前的DACBase<T>类, 还实现了IEnumerable<T>接口。支持延迟查询哦!
SqlSkip 跳过多少记录
SqlTack 返回多少记录
SqlSkip + SqlTack 完美支持分页哦
SqlSelect 指定一个命令执行,默认的是Select + T的类型名
SqlWhere 加上各种Where 条件(支持12种条件)
SqlOrderBy 加上排序,支持倒序
GetEnumerator 返回迭代器,支持延迟查询
ToList 立即查询
SqlDelete 执行删除
SqlUpdate 执行更新
Clear 把类置于初始状态,已执行下一个命令
本组件的缺点,很明显,在上面的分析部分已经讲明白了,不支持的就是不支持,我不打算去做数据库关系的MAPPING。
但是你说这个东西不能用,DAAB都能做的事它都能做,并且比DAAB好多了吧。
那么,我们看看它的使用方式吧:
var retList = new List<ShopInfo>();
var list = dac.SqlWhere(p => p.ShopID.SqlEqual(context.InnerRequest))
//.SqlDistinct().SqlTack(1).SqlSkip(0)
.ToList()
和EF差不多吧。
具体使用指南将在专门的使用指南里介绍,稍后我将上传全部源码。
回过都来,我们来看看它的短板的替代方案:
第一,JOIN 查询不支持, 没关系,前面以前说了,用IN查询来限定第二个结果集的大小,然后在内存里对两个结果集进行JOIN
第二,Group BY, 直接使用内存里的Group BY。
来看看丰富的集合操作的扩展方法吧:
对以上扩展方法的分析:
Aggregate 加上闭包传进列表长度能得到平均值
Aggregate 在RROUP BY 中特别好用
ANY, ALL 返回BOOL
CONTAINS 指定一个比较器, 默认的构造器模板,传进一个委托返回BOOL 与ANY用法有相同之处
Avg只能返回一个DECIMAL,用 Aggregate 更好
SUM
COUNT可带条件, 用 Aggregate 可实现
LongCount
MIN/MAX
CANCAT 不带条件
Distinct 带比较器
UNION 带比较器 = CANCAT + Distinct
Except 带比较器
DefaultOrEmpty在实现LEFT Join的时候好用啊!!!
First带条件
FirstORDefault可实现DefaultIFEmpty,但更强大
Last
LastOrDefault
Single 不止一个就报错
SingleOrDefault
ElementAt
ElementAtDefault
ToDictnary
ToLookUp
ToArray
ToList
WHERE
Tack
TackWhile 和WHERE一样
Skip
SkipWhile
CAST
OfType很有用,当然也能用WHERE + CAST实现。
Reverse
SequenceEqual在自动化测试中很好用,带比较器
ORDERBY 正序和倒序
GROUPBY
GROUPJOIN
JOIN,条件为TRUE可实现笛卡尔乘积
INTERSECT 取交集
SELECT
SELECTMANY 可实现LEFT JOIN,笛卡尔乘积,LEFT JOIN 和 RIGHT join 加起来UNION就是 FULL JOIN
可见,集合操作的扩展方法是极其丰富的,没有问题。
好,接下来的问题是,用IN 查询实现 限定JOIN查询的 第二个记录集的大小有没有性能问题。
我们知道一个in后面只能有1000个值
如果我们JION两张表,一张是15万的数据,另一张是2亿的数据
我们就要用150次IN查询 ,并且把结果UNION起来,好在我已经给你做了,你不需要写一个 for(var i=0, i <150, i++){}
现在只是需要确认一下性能和原本的JOIN查询差距有多大~~
一会出性能测试报告,敬请期待 。
PS: 测试结果出来的, IN查询的效率肯定是比JOIN 低的。究竟低多少呢?
6.2万 join 2亿, JOIN是0.015秒,
IN查询是0.46秒,
大数据量,差的比较多了,不过,小数据量就完全没区别了。
大数据量的情况下,查询结果其实都没有返回的,网络不行啊!
上面的黄色高亮是本组件的短板,也就是缺点了~~
好了,终于写完了,算是对自己大约一个星期工作量的总结,写完了就对得起自己的熬夜了,也对得起公司,对得起大家了。
我真TM是个偏执的人!哈哈
PS, 上一篇中我可能犯了两个错误,以个人感受概全了大家的感受,以偏概全了:
一,也许我错了,让销售录入信息可能是只有我这样的懒销售才感到头痛,大部分销售是不会头痛的,他们可以一边谈客户一边录入,可以在家打开笔记本录入~~
二,也许我又错了,一个系统里应该可以存在两个以及以上的数据访问组件,然后优势互补
本系列四篇博文,只是做自己的工作总结,和发表自己的个人看法~不带其他感情色彩哦~~读者,切记切记