使用VB.Net写一个简单的数据访问层：TSql函数的支持。

为了支持不同的数据源，数据访问层，就不能简单的创建不同的Command,Conection,DataAdapter对象。根据不同的数据源进行不同的t-sql语句转换，下面我们就来看看数据层对函数的支持。

Abs

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(item._库存量.Abs).From(item)
Console.WriteLine("ABS语句：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("绝对值：{0}", value)

红色部份就是对应的Sql语句。获取库存量的绝对值，并返回第一行第一列的值。

AsName,Sum

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(o.SQL.AsName(item._库存金额.Sum, "库存总金额")).From(item)
Console.WriteLine("ASName语句：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)

对库存金额求和，返回的列名，是库存总金额。

Avg

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(item._库存量.Avg).From(item)
Console.WriteLine("Avg语句：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("平均值：{0}", value)

对库存量求平均值

Bracket，Count

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(item._库存量.Count).From(item). _
    WHERE(o.SQL.Bracket(item._物品型号.EqualTo("aa").And(item._物品名称.EqualTo("bb")). _
            Or(o.SQL.Bracket(item._生产厂家.EqualTo("cc").And(item._单位.EqualTo("11"))) _
               ) _
          ) _
       )
Console.WriteLine("Bracket：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("Bracket：{0}", value)

这个主要针对条件优先级的调整。相当于sqlserver,access中的括号，上面原始的sql语句是。

select count(库存量) from item where (物品型号='aa' and 物品名称='bb')

or (生产厂家='cc' and 单位='11')

唯一不好的地方，是感觉括号太多了，不知道有没有什么办法。

Day

Dim o As New Common.进仓_Operate
Dim item As New Common.进仓
Dim command As ICommand = o.SQL.Saclar(item._日期.Day).From(item)
Console.WriteLine("Day：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("Day：{0}", value)

Exists

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim j As New Common.进仓
Dim command As ICommand = o.SQL.Saclar(item._物品型号.Count()).From(item). _
    WHERE(o.SQL.Exists(item._ID.NotEqualTo(j._主表ID), j))
Console.WriteLine("Exists：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("Exists：{0}", value)

针对Exists语句的支持。原始的t-sql语句是:

select count(物品型号) from item 

where exists(select 1 from 进仓 where item.id<>进仓.主表id)

IsNUll

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim pars As New ParameterCollection
pars.Add(o.DataProvider.DataHelper.GetNewPar("@A", System.DBNull.Value))
Dim command As ICommand = o.SQL.Saclar(o.SQL.IsNull( _
    New At.Data.CustomExpression("@A", pars), "zqonlie")). _
    From(item). _
    OrderBy(item._物品型号.OrderBy(SortDirection.DESC))
Console.WriteLine("IsNUll：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("IsNUll：{0}", value)

这里根据sqlserver里的isnull(expression,value)函数而来,测试表达式是否为null，如果是则用

指定的值替代。目前已知access里的isnull函数与sqlserver的含义不一样，这里MsAccessSqlBuilder

类重写了，iif(isnull(expression), value,expression),结合iif与isnull完成。

CustomExpression：自定义表达式对象，根据针对数据源编写表达式如：针对sqlserver可以写

case 语句的支持。

OrderBy：是对查询排序的支持

原型t-sql:select isnull(@a,zqonline) from item order by 物品型号 desc

Left

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(item._物品型号.Left(10)).From(item). _
OrderBy(item._物品型号.OrderBy(SortDirection.DESC))
Console.WriteLine("Left：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("Left：{0}", value)

Len

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(o.SQL.Len(item._物品型号.Max)).From(item)
Console.WriteLine("Len：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("Len：{0}", value)

Lower

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(o.SQL.AsName(o.SQL.Lower(item._物品型号.Max), "转换为小写"))

.From(item)
Console.WriteLine("Lower：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("Lower：{0}", value)

ltrim,max,min,Month,Right,Replace,Round,Rtrim,SubString,Sum,Upper,Year其中MsAccess不支持

Replace函数

以上这此函数调用方式都类似。这里就不一一举例了。

Not

Dim o As New Common.主表_Operate
Dim item As New Common.主表
Dim command As ICommand = o.SQL.Saclar(item._物品型号.Left(10)). _
From(item).WHERE(o.SQL.Not(item._物品型号.EqualTo(1))). _
OrderBy(item._物品型号.OrderBy(SortDirection.DESC))
Console.WriteLine("[Not]：{0}", command.CommandText)
Dim value As Object = o.ExecuteScalar(command)
Console.WriteLine("[Not]：{0}", value)

原型t-sql:select left(物品型号,10) from item where not 物品型号='1' order by 物品型号 desc

如果感兴趣，请继续关注。