Private Sub DataGridView1_CellEnter(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEnter
Me.List2TableAdapter.Fill(Me.Database1DataSet.List2)
List2BindingSource.Filter = " TagID like '" & DataGridView1.Item(1, DataGridView1.CurrentRow.Index).Value.ToString.Trim & "%'"
End Sub
Private Sub btnDeleteData_Click(sender As Object, e As EventArgs) Handles btnDeleteData.Click
'Dim strSQLSelect As String = "Select * From Products Where ProductID In ({0})"
Dim strSQLDelete As String = "Delete From TableProducts Where ProductID In ({0})"
'strSQLDelete == "Delete From TableProducts Where ProductID In(@P1, @P2, @P3, ...)"
Dim valueArray As String() = {"1, 2, 3, 4, 5, 6, 7, 8"} 'วนลูปอ่านค่า Keys จากตาราง DataGridView Filter
Dim params = valueArray.Select(Function(s, i) "@P" & i).ToArray()
Dim Inclause = String.Join(", ", params).TrimEnd(" ") 'Remove Last Blank space.
Dim connStr As String = "Data Source=yourPCName;Initial Catalog=yourDBName;User ID=sa;Password=หำ providerName=""System.Data.SqlClient"""
Dim con = New System.Data.SqlClient.SqlConnection(connStr)
'Dim cmd1 = New System.Data.SqlClient.SqlCommand(String.Format(strSQLSelect, Inclause), con)
Dim cmd2 = New System.Data.SqlClient.SqlCommand(String.Format(strSQLDelete, Inclause), con)
For i As Integer = 0 To valueArray.Length - 1
'cmd1.Parameters.AddWithValue(params(i), valueArray(i))
cmd2.Parameters.AddWithValue(params(i), valueArray(i))
Next
con.Open()
'Dim objReturn1 = cmd1.ExecuteNonQuery()
Dim objReturn2 = cmd2.ExecuteNonQuery()
End Sub
Public Module LINQ_LAMPDA_Extxt
'Returns only A where B does NOT exists for A.
<System.Runtime.CompilerServices.Extension> _
Public Function LeftExcludingJoin(Of TSource, TInner, TKey, TResult)(source As IEnumerable(Of TSource),
inner As IEnumerable(Of TInner),
pk As Func(Of TSource, TKey),
fk As Func(Of TInner, TKey),
result As Func(Of TSource, TInner, TResult)) As IEnumerable(Of TResult)
Dim _result As IEnumerable(Of TResult) = Enumerable.Empty(Of TResult)()
_result = From s In source Group Join i In inner On pk(s) Equals fk(i) Into joinData = Group
From left In joinData.DefaultIfEmpty() Where left Is Nothing
Select result(s, left)
Return _result
End Function
End Module
ตัวอย่างการใช้งาน
Code (VB.NET)
Dim leftList = {
New With {.ID = 1, .Name = "John", .Changed = False},
New With {.ID = 2, .Name = "Obama", .Changed = False},
New With {.ID = 3, .Name = "Kinton", .Changed = False}
}
Dim rightList = {
New With {.ID = 1, .Name = "John", .Changed = False},
New With {.ID = 3, .Name = "Kinton", .Changed = True},
New With {.ID = 4, .Name = "Payboy", .Changed = False},
New With {.ID = 5, .Name = "หำ", .Changed = False}
}
'ผลลัพธ์คือ {.ID = 2, .Name = "Obama", .Changed = False},
Dim resultLeftExcludingJoin = leftList.LeftExcludingJoin(rightList, Function(p) p.ID, Function(a) a.ID, _
Function(p, a) New With {Key .MyPerson = p, Key .MyAddress = a}) _
.Select(Function(a) New With { _
Key .LeftID = (If(a.MyPerson IsNot Nothing, a.MyPerson.ID & " Left", "Null-Value")), _
Key .RightID = (If(a.MyAddress IsNot Nothing, a.MyAddress.ID & " Right", "Null-Value")), _
Key .LeftName = (If(a.MyPerson IsNot Nothing, a.MyPerson.Name & " Left", "Null-Value")), _
Key .RightName = (If(a.MyAddress IsNot Nothing, a.MyAddress.Name & " Right", "Null-Value")), _
Key .LeftChanged = (If(a.MyPerson IsNot Nothing, a.MyPerson.Changed & " Left", -1)), _
Key .RightChanged = (If(a.MyAddress IsNot Nothing, a.MyAddress.Changed & " Left", -1))
}).ToList()