Suppose we need to populate and filter a Datagrid with the data coming from an Access table. It could be of course a table from Sql Server also.
Form dataGridSelect contains two buttons and a DataGridView. Clicking the first button will populate the DataGridView the data comming from a Sql Server database. The second button does the same comming the data from an Access database. Nothing new, but once the data is fetched a Linq filters through a Regex pattern.
The pattern "[^d|D]ata" tells to select the rows where the field Title has the sequence 'ata', not those [^d|D] preceeded by a capital or lower 'd'.
For ex. if present the word 'catalogue' will be selected. Not those with the word 'database' or 'Database'. If both words present also the row will be selected.
Note: in some internet pages (included a MS example) I've seen CopyToDataTable() called from an instance, while it's a shared function (as MS states here )
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.DataTableExtensions
Imports System.Linq
Imports System.Text.RegularExpressions
Public Class dataGridSelect
Dim sConn As String = String.Empty
Private Sub btnFromSqlServer_Click(sender As System.Object, e As System.EventArgs) Handles btnFromSqlServer.Click
Try
CreateDataSetFromSqlServer()
Catch ex As Exception
End Try
End Sub
Private Sub btnFromAccess_Click(sender As System.Object, e As System.EventArgs) Handles btnFromAccess.Click
Try
CreateDataSetFromAccess()
Catch ex As Exception
End Try
End Sub
Sub CreateDataSetFromSqlServer()
' https://sudeeptaganguly.wordpress.com/2010/04/20/how-to-enable-sa-account-in-sql-server/
'
sConn += "Server=DESKTOP-KD8CVG6\SQLEXPRESS;"
sConn += "Database=biblio;"
sConn += "User Id=sa;"
sConn += "Password=password;"
Dim cn As New SqlClient.SqlConnection(sConn)
cn.Open()
Dim dsBiblio As New DataSet
Dim daTitles As New SqlClient.SqlDataAdapter("SELECT * FROM biblio.Titles", cn)
daTitles.Fill(dsBiblio, "Titles")
Dim dt As DataTable = dsBiblio.Tables("Titles")
Dim q() As DataRow = _
(From r In dt.AsEnumerable _
Where Regex.IsMatch(r.Item("Title"), "[^d|D]ata") _
Select r).ToArray
DataGridView1.DataSource = CopyToDataTable(q)
cn.Close()
End Sub
Sub CreateDataSetFromAccess()
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=C:\Users\Public\biblio.accdb;"
Dim cn As New OleDbConnection(sConn)
Dim dsBiblio As New DataSet
Dim daTitles As New OleDbDataAdapter("SELECT * FROM Titles", cn)
daTitles.Fill(dsBiblio, "Titles")
Dim dt As DataTable = dsBiblio.Tables("Titles")
Dim q() As DataRow = _
(From r In dt.AsEnumerable _
Where Regex.IsMatch(r.Item("Title"), "[^d|D]ata") _
Select r).ToArray
DataGridView1.DataSource = CopyToDataTable(q)
cn.Close()
End Sub
End Class