SQL语句对象化,先看示例代码.
说明:当前只是针对SQLServer数据,其它的数据库我也没有用过,不过在基础上扩展重构工作量不是很大.
IN语句
'SELECT XSH_HT_合同.合同编号,XSH_HT_合同.合同名称 FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN (@p70798e19b6c748519ab37515e1884d8b,@pd0873785429445d8868c51dfd310b31f,@p7d84bb7aa5e340909e02527948ca63c7) GROUP BY XSH_HT_合同.合同编号,XSH_HT_合同.合同名称 ORDER BY XSH_HT_合同.合同编号 ASC
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(en._合同编号, en._合同名称). _
From(en).WHERE(en._合同编号.Filter._In("020482", "020503", "020539")). _
GroupBy(en._合同编号, en._合同名称). _
OrderBy(en._合同编号.OrderBy(SortDirection.DESC))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
IN语句
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@pc93cea79f9884d15ae19367bdcab5d0c)
'使用IN 语句返回所有[邓山]的合同.
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _
From(en). _
WHERE(en._合同编号.Filter._In( _
Lily.OQL.BuilderSql.Select(0, True, en._合同编号). _
From(en). _
WHERE(en._销售员.Filter.EqualTo("山"))) _
)
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
IN语句
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@pc93cea79f9884d15ae19367bdcab5d0c)
'使用IN 语句返回所有[邓山]的合同.
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _
From(en). _
WHERE(en._合同编号.Filter._In( _
Lily.OQL.BuilderSql.Select(0, True, en._合同编号). _
From(en). _
WHERE(en._销售员.Filter.EqualTo("山"))) _
)
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'左匹配
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@p382468e15c184543b73bbd0434f8440a) OR XSH_HT_合同.销售员 Like @p1b7b18f217a846e2ae7391320326e857
'使用IN 和 Like 语句
'查找销售员是邓和姓张的销售员合同
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _
From(en). _
WHERE(en._合同编号.Filter._In( _
Lily.OQL.BuilderSql.Select(en._合同编号). _
From(en). _
WHERE(en._销售员.Filter.EqualTo("邓")) _
). _
_Or _
(en._销售员.Filter.StartWith("张")) _
)
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'Like包含
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员 Like @pcb75950fbe764c2ba94112ecbaac7eb5
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.Include("山"))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'Like右匹配
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员 Like @p6ccb3db2ac984a4ea1d908c09dde5464
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.EndWith("公司"))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'Like字段左匹配
'SELECT XSH_HT_合同.乙方代表,XSH_HT_合同.销售员 FROM XSH_HT_合同 WHERE XSH_HT_合同.乙方代表 Like XSH_HT_合同.销售员+'%'
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(en._乙方代表, en._销售员).From(en).WHERE(en._乙方代表.Filter.StartWith(en._销售员))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'字段相等
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=XSH_HT_合同.乙方代表
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(en._销售员, en._乙方代表).From(en).WHERE(en._销售员.Filter.EqualTo(en._乙方代表))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'字段等于值
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@pe72d31922f434cd89abbadd51d2cf566
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.EqualTo("邓"))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
NOt IN
'SELECT * FROM XSH_HT_合同 WHERE NOT XSH_HT_合同.销售员 IN (@pe8b92721ff8c42548fb96965da962a9a)
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.NotIn("邓山"))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'SELECT * FROM XSH_HT_合同 WHERE NOT XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@p925776458a204a4d9999a1fae33470ec)
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _
From(en). _
WHERE(en._合同编号.Filter.NotIn( _
Lily.OQL.BuilderSql.Select(0, True, en._合同编号). _
From(en). _
WHERE(en._销售员.Filter.EqualTo("邓山"))) _
)
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
exists
'SELECT * FROM XSH_HT_工程信息 WHERE EXISTS ( SELECT * FROM XSH_HT_合同 WHERE XSH_HT_工程信息.合同编号=XSH_HT_合同.合同编号)
Dim cp As New XSH_HT_工程信息
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(cp).WHERE(cp.Exists(cp._合同编号.Filter.EqualTo(en._合同编号), en))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
'SELECT * FROM XSH_HT_工程信息 WHERE NOT EXISTS ( SELECT * FROM XSH_HT_合同 WHERE XSH_HT_工程信息.合同编号=XSH_HT_合同.合同编号)
Dim cp As New XSH_HT_工程信息
Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(cp).WHERE(cp.NotExists(cp._合同编号.Filter.EqualTo(en._合同编号), en))
Me.DataGrid1.DataSource = da.GetDataTable(cmd)
IN语句
'SELECT XSH_HT_合同.合同编号,XSH_HT_合同.合同名称 FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN (@p70798e19b6c748519ab37515e1884d8b,@pd0873785429445d8868c51dfd310b31f,@p7d84bb7aa5e340909e02527948ca63c7) GROUP BY XSH_HT_合同.合同编号,XSH_HT_合同.合同名称 ORDER BY XSH_HT_合同.合同编号 ASC Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(en._合同编号, en._合同名称). _ From(en).WHERE(en._合同编号.Filter._In("020482", "020503", "020539")). _ GroupBy(en._合同编号, en._合同名称). _ OrderBy(en._合同编号.OrderBy(SortDirection.DESC)) Me.DataGrid1.DataSource = da.GetDataTable(cmd)IN语句
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@pc93cea79f9884d15ae19367bdcab5d0c) '使用IN 语句返回所有[邓山]的合同. Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _ From(en). _ WHERE(en._合同编号.Filter._In( _ Lily.OQL.BuilderSql.Select(0, True, en._合同编号). _ From(en). _ WHERE(en._销售员.Filter.EqualTo("山"))) _ ) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@pc93cea79f9884d15ae19367bdcab5d0c) '使用IN 语句返回所有[邓山]的合同. Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _ From(en). _ WHERE(en._合同编号.Filter._In( _ Lily.OQL.BuilderSql.Select(0, True, en._合同编号). _ From(en). _ WHERE(en._销售员.Filter.EqualTo("山"))) _ ) Me.DataGrid1.DataSource = da.GetDataTable(cmd)'左匹配
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@p382468e15c184543b73bbd0434f8440a) OR XSH_HT_合同.销售员 Like @p1b7b18f217a846e2ae7391320326e857 '使用IN 和 Like 语句 '查找销售员是邓和姓张的销售员合同 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _ From(en). _ WHERE(en._合同编号.Filter._In( _ Lily.OQL.BuilderSql.Select(en._合同编号). _ From(en). _ WHERE(en._销售员.Filter.EqualTo("邓")) _ ). _ _Or _ (en._销售员.Filter.StartWith("张")) _ ) Me.DataGrid1.DataSource = da.GetDataTable(cmd)'Like包含
'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员 Like @pcb75950fbe764c2ba94112ecbaac7eb5 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.Include("山")) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员 Like @p6ccb3db2ac984a4ea1d908c09dde5464 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.EndWith("公司")) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT XSH_HT_合同.乙方代表,XSH_HT_合同.销售员 FROM XSH_HT_合同 WHERE XSH_HT_合同.乙方代表 Like XSH_HT_合同.销售员+'%' Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(en._乙方代表, en._销售员).From(en).WHERE(en._乙方代表.Filter.StartWith(en._销售员)) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=XSH_HT_合同.乙方代表 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(en._销售员, en._乙方代表).From(en).WHERE(en._销售员.Filter.EqualTo(en._乙方代表)) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT * FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@pe72d31922f434cd89abbadd51d2cf566 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.EqualTo("邓")) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT * FROM XSH_HT_合同 WHERE NOT XSH_HT_合同.销售员 IN (@pe8b92721ff8c42548fb96965da962a9a) Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(en).WHERE(en._销售员.Filter.NotIn("邓山")) Me.DataGrid1.DataSource = da.GetDataTable(cmd)not select
'SELECT * FROM XSH_HT_合同 WHERE NOT XSH_HT_合同.合同编号 IN ( SELECT DISTINCT XSH_HT_合同.合同编号 FROM XSH_HT_合同 WHERE XSH_HT_合同.销售员=@p925776458a204a4d9999a1fae33470ec) Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select(). _ From(en). _ WHERE(en._合同编号.Filter.NotIn( _ Lily.OQL.BuilderSql.Select(0, True, en._合同编号). _ From(en). _ WHERE(en._销售员.Filter.EqualTo("邓山"))) _ ) Me.DataGrid1.DataSource = da.GetDataTable(cmd) 'SELECT * FROM XSH_HT_工程信息 WHERE EXISTS ( SELECT * FROM XSH_HT_合同 WHERE XSH_HT_工程信息.合同编号=XSH_HT_合同.合同编号) Dim cp As New XSH_HT_工程信息 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(cp).WHERE(cp.Exists(cp._合同编号.Filter.EqualTo(en._合同编号), en)) Me.DataGrid1.DataSource = da.GetDataTable(cmd)not exists
'SELECT * FROM XSH_HT_工程信息 WHERE NOT EXISTS ( SELECT * FROM XSH_HT_合同 WHERE XSH_HT_工程信息.合同编号=XSH_HT_合同.合同编号) Dim cp As New XSH_HT_工程信息 Dim cmd As Lily.PL.PersistenceLayerCommandBase = Lily.OQL.BuilderSql.Select().From(cp).WHERE(cp.NotExists(cp._合同编号.Filter.EqualTo(en._合同编号), en)) Me.DataGrid1.DataSource = da.GetDataTable(cmd)欢迎各位拍砖,这样的查询方式,简便明了吗?
XSH_HT_工程信息 是实体对象
PersistenceLayerCommandBase 是命令对象所有的select,update,delete,insert into 语句的构造都是从此类派生.
da 是一个操作命令对象的适配器.
