vb.net 手动写sql并执行

1，定义数据库

Dim ctx = KikanContext.Current()

2，写sql语句

1 Dim sql1 = "SELECT (MS_TKM.S_E * 10 + MS_TKM.S_B) AS sitenid,count(*) AS count " & _
2                    "FROM (SELECT TOKUSHIN_OYA_CODE,Max(SAISHINSEI_STATUS) AS P1,Max(SAISHINSEI_KBN) AS P2,Max(DELETE_FLG) AS P3 " & _
3                     "FROM T_HHININ GROUP BY TOKUISAKI_CODE, SHOHIN_CODE, TEIBAN_TANKA, KIKAN_TANKA,MAKER_TANKA ,SHINSEI_NENGETU,TOKUSHIN_OYA_CODE) " & _
4                    "JOIN MS_TKM@togo ON TOKUSHIN_OYA_CODE = MS_TKM.TOKCD1 * 1000 + MS_TKM.TOKCD2 " & _
5                    "WHERE P1 = 0 AND P3 = 0 AND P2 = 0 GROUP BY MS_TKM.S_E * 10 + MS_TKM.S_B ORDER BY MS_TKM.S_E * 10 + MS_TKM.S_B"

3，执行

Dim rList1 = (ctx.Database.SqlQuery(Of HSY0230ViewModel)(sql1)).ToList()

其中HSY0230ViewModel是自建的接受数据类型的模型类

Imports System.ComponentModel.DataAnnotations

Public Class HSY0230ViewModel
  
    <Required>
    <Display(Name:="件数")>
    Public Property count As Integer

    <Required>
    Public Property sitenid As Integer


End Class

 

posted @ 2017-03-30 08:25  追风小伙  阅读(2081)  评论(0)  编辑  收藏  举报