数据层之我的查询设计.
虽然现在网络上很多很好的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()
水平有限,我现在连类图都不会画,所以看起来肯定比较乱,条理也比较混乱,我相信有经验了一定会做得更好.
