数据层之我的查询设计.

虽然现在网络上很多很好的orm都不错,并起也是开源的.我想走走弯路也是有必要的.让自己对这个理解更深刻.

我不想把他搞得太复杂,太复杂了学习曲线也比较高,潜在的BUG也更多(当然是个人水平的原因.)

我的要求和目的:
1.所有的表和视图都映射成一个实体.系统不负责一对多或多对多关系的处理.
2.目前只是针对SQLServer设计的,因为mysql...等其它数据库我也没有使用过.不过尽量对其进行抽象.等以后水平到了在进行重构.
3.我不会要求使用了我的组件后,系统中不出现t-sql语句.那个方便就用那个.
4.效率不能太低,不能不稳定,一定要比不使用开发的效率更高.

最后一点目前对我来说是最重要的.

实体类:
实体类的属性对应着数据库的每一个字段,对于字段描述我是用属性来描述的.
实体类不具备update,delete,insert等操作,但具备Retrieve操作.
我为什么不让实体具有操作的原因:之前我也设计了一个数据层,实体就具有这些操作,如果是在事务中时,对同一个数据连接控制太丑陋了.

实体操作适配器
实现在对实体的查询,删除,更新,插入四个操作.四个操作分别对应四个命令对象DeleteCommand,SelectCommand,UpdateCommand,InsertCommand 分别用于构造select,update,delete,insert语句.这样我想以后我需要扩展数据源支持,我只需花很少的代价.就可以按需要生成sql语句了.

条件语句的产生.
CustomExpression                自定义表达式      (select 1+1) as number
FieldExpression                    字段表达式          tablea.id=tableb.id
ValueExpression                   字段与值表达式  tablea.id=123
SqlFun                                  t-sql函数定义
ExpressionGroup                  条件分组   tablea.id=tableb.id or (tablea.name=tableb.name)

定义实体类
Public Class t
    Inherits lily.PersistenceLayer.EntityBase

    Public Const _id = "id"
    Public Const _name = "name"

    Public Sub New()
        MyBase.new()
        Me.md_AttributeList.add(_id)
        Me.md_AttributeList.add(_name)
    End Sub
    Private md_id As Integer
    <lily.PersistenceLayer.Field("t", "id", 4, DbType.Int32, True, False, "id", False)> _
    Public Overridable Property id() As Integer
        Get
            Return md_id
        End Get
        Set(ByVal Value As Integer)
            md_id = Value
        End Set
    End Property

    Private md_name As String = String.Empty
    <lily.PersistenceLayer.Field("t", "name", 100, DbType.String, False, False, "name", False)> _
    Public Overridable Property name() As String
        Get
            Return md_name
        End Get
        Set(ByVal Value As String)
            md_name = Value
        End Set
    End Property

    Public Overrides ReadOnly Property Table_Source() As String
        Get
            Return "t"
        End Get
    End Property
    Public Overrides ReadOnly Property IsView() As Boolean
        Get
            Return False
        End Get
    End Property

End Class

查询:
SELECT a.name,b.name  as  姓名 FROM t as a ,t as b  WHERE a.name=b.name ORDER BY a.id DESC
生成这条语句的代码为:
 '实体操作类
        Dim ea As New Lily.PersistenceLayer.EntityAdapter
        '实体查询对象
        Dim sc As New Lily.PersistenceLayer.SelectCommand

        '实例化实体
        Dim t As New t
        Dim fca As Lily.PersistenceLayer.FromSourceManager.From = sc.FromSoure.AddFrom(t, "a")
        Dim fcb As Lily.PersistenceLayer.FromSourceManager.From = sc.FromSoure.AddFrom(t, "b")
        sc.ConditionExpression = New Lily.PersistenceLayer.FieldExpression(fca.ToFieldName(t._name), Lily.PersistenceLayer.Match.等于, fcb.ToFieldName(t._name))

        sc.AddSelectField(fca.ToFieldName(t._name))
        sc.AddSelectField(fcb.ToFieldName(t._name), " 姓名")

        sc.AddOrderBy(fca.ToFieldName(t._id), Lily.PersistenceLayer.OrderByType.DESC)

        '查询对象添加到操作适配器中
        ea.AddEntityCommand(sc)

        '获取结果
        Me.DataGrid1.DataSource = ea.GetDataTable


删除:
 DELETE t WHERE name = @name
相应的代码:
      Dim ea As New Lily.PersistenceLayer.EntityAdapter
        Dim t As New t
        t.name = "zqonline" 
       Dim dc As New Lily.PersistenceLayer.DeleteCommand(t, New Lily.PersistenceLayer.ValueExpression(t._name, Lily.PersistenceLayer.Match.等于, t.name))
        ea.AddEntityCommand(dc)
        ea.Update()

插入:
INSERT INTO t (name ) VALUES  (@name )
代码为:
      Dim ea As New Lily.PersistenceLayer.EntityAdapter
        Dim t As New t
        t.name = zqonline
        Dim ic As New Lily.PersistenceLayer.InsertCommand(t)
        ea.AddEntityCommand(ic)
        ea.Update()
更新:
UPDATE t SET name=@name FROM t as b  WHERE b.name = @bname
代码为
        Dim ea As New Lily.PersistenceLayer.EntityAdapter
        Dim name As String = "luzhou"
        Dim t As New t
        Dim uc As New Lily.PersistenceLayer.UpdateCommand(New t)

        Dim fc As Lily.PersistenceLayer.FromSourceManager.From = uc.FromSoure.AddFrom(t, "b")
        uc.ConditionExpression = New Lily.PersistenceLayer.ValueExpression(fc.ToFieldName(t._name), Lily.PersistenceLayer.Match.等于, name)

        uc.AddForUpdateField(t._name, "zqonline")
        ea.AddEntityCommand(uc)
        ea.Update()

水平有限,我现在连类图都不会画,所以看起来肯定比较乱,条理也比较混乱,我相信有经验了一定会做得更好.