xrjf 178 Newbie Poster

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.