LINQ to ADO.NET - VB.NET
要使用 LINQ 操作 SQL 資料庫可以使用O/R Designer來簡化造資料表的一些動作，首先新增一個LINQ To SQL類別。
從資料庫將資料表拖曳進設計畫面。
接著就可以開始寫程式啦，查詢的語法同一般LINQ語法。
Dim enumData = From v In context.DEMO Where v.TP_RELEASE = True
For Each row In enumData
MsgBox(row.TP_NAME)
Next
新增、更新、刪除。
' 新增
'***********************************************************************************
Dim newRow As New DEMO
newRow.TP_NAME = "DEMO1"
newRow.TP_RELEASE = "True"
context.DEMO.InsertOnSubmit(newRow)
context.SubmitChanges()
Dim newRow2 As New DEMO
newRow2.TP_NAME = "DEMO2"
newRow2.TP_RELEASE = "False"
context.DEMO.InsertOnSubmit(newRow2)
context.SubmitChanges()
' 更新
'***********************************************************************************
Dim enumUpdate = From v In context.DEMO Where v.TP_NAME = "DEMO2"
For Each row In enumUpdate
row.TP_RELEASE = True
Next
context.SubmitChanges()
' 刪除
'***********************************************************************************
Dim enumDelete = From v In context.DEMO Where v.TP_NAME = "DEMO2"
context.DEMO.DeleteOnSubmit(enumDelete.First)
context.SubmitChanges()
如果是要對ADO.NET的物件查詢記得要呼叫AsEnumerable。
Dim sql As String = "SELECT * FROM DEMO"
Dim ds As New DataSet
Using conn
Dim adapter As New SqlDataAdapter(sql, conn)
adapter.Fill(ds, "DEMO")
End Using
Dim dt As DataTable = ds.Tables("DEMO")
Dim enumTable = From table In dt.AsEnumerable _
Where table.Field(Of Boolean)("TP_RELEASE") = True
For Each row In enumTable
MsgBox(row("TP_NAME"))
Next
還可以將資料庫查詢出來的資料直接轉為XML。
Dim enumXML As New XElement("DEMO", _
From v In context.DEMO Where v.TP_RELEASE = True _
Select New XElement("Row", New XElement("TP_NAME", v.TP_NAME), _
New XElement("TP_RELEASE", v.TP_RELEASE)))
Dim xDoc As New XDocument(New XDeclaration("1.0", "UTF-8", "yes"))
xDoc.Add(enumXML)
xDoc.Save(".\TEST.xml")