Use LINQ to Foxpro or any SQL data and display it in a WPF form using DataSetExtensions and DataRowExtensions


In the last post Create your own media browser: Display your pictures, music, movies in a XAML tooltip, we created a query of your local media and displayed it.


 


 


Today, we’ll make a query to my Foxpro picture database of 28,000 pictures/movies. We’ll make the query result structure to be similar to the prior post, so the same code will show the picture or movie in a tooltip. The query is for “wheel”, which includes pictures of cartwheels, Ferris Wheels, wheelbarrows.


 


We can use the System.Data.DataRowExtensions to access the DataTable filled by the OledbDataAdapter without using DLINQ


 


 


Change the query statement from the last post to the snippet below


 


 


<Snippet>


        Dim Query


        If True Then    ‘ if you want the prior sample, just change this to false


            Dim dt As New DataTable


            _rootFolder = “e:\Pictures”


            Dim da = New OleDbDataAdapter( _


                “Select fullname as FileName,Notes from mypix where ‘wheel’$lower(notes)”, _


                “Provider=VFPOLEDB.1;Data Source=” + _rootFolder + “\mypix.dbf”)


            da.Fill(dt)


            Query = From picrow In dt.AsEnumerable _


                        Select FileName = picrow.Field(Of String)(“FileName”), _


                        Description = picrow.Field(Of String)(“Notes”) _


                        Select FileName, _


                            Description, _


                            Type = IO.Path.GetExtension(FileName).Substring(1), _


                            Size = (New IO.FileInfo(_rootFolder + IO.Path.DirectorySeparatorChar + FileName)).Length


        Else


            Query = From file In IO.Directory.GetFiles(_rootFolder, _


                                “*.*”, IO.SearchOption.AllDirectories) _


                                Select file, Ext = IO.Path.GetExtension(file).ToLower _


                                Where Ext.Length > 0 AndAlso “.avi .jpg .mid .mpg .wma .wmv”.Contains(Ext) _


                         Select FileName = file.Substring(_rootFolder.Length + 1), _


                             Type = Ext.Substring(1), _


                             Size = (New IO.FileInfo(file)).Length


        End If


 


</Snippet>


 


For the full working sample:


 


Start Visual Studio 2008


 


Choose File->New Project->Visual Basic->WPF Application


 


Double click the form designer to bring up the Window1.xaml.vb file. Replace the contents with the code below.


Change the oledb provider connection string to your data


 


Hit F5 to run the program


 


 


 


<Sample Code>


Imports System.Windows.Controls.Primitives  ‘ for Popup


Imports System.Data


Imports System.Data.OleDb


Imports System.Data.DataTableExtensions


 


Partial Public Class Window1


    Dim _rootFolder As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)


    Private Sub Window1_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded


        Me.Width = 800 : Me.Height = 800


        Dim Query


        If True Then    ‘ if you want the prior sample, just change this to false


            Dim dt As New DataTable


            _rootFolder = “e:\Pictures”


            Dim da = New OleDbDataAdapter( _


                “Select fullname as FileName,Notes from mypix where ‘wheel’$lower(notes)”, _


                “Provider=VFPOLEDB.1;Data Source=” + _rootFolder + “\mypix.dbf”)


            da.Fill(dt)


            Query = From picrow In dt.AsEnumerable _


                        Select FileName = picrow.Field(Of String)(“FileName”), _


                        Description = picrow.Field(Of String)(“Notes”) _


                        Select FileName, _


                            Description, _


                            Type = IO.Path.GetExtension(FileName).Substring(1), _


                            Size = (New IO.FileInfo(_rootFolder + IO.Path.DirectorySeparatorChar + FileName)).Length


        Else


            Query = From file In IO.Directory.GetFiles(_rootFolder, _


                                “*.*”, IO.SearchOption.AllDirectories) _


                                Select file, Ext = IO.Path.GetExtension(file).ToLower _


                                Where Ext.Length > 0 AndAlso “.avi .jpg .mid .mpg .wma .wmv”.Contains(Ext) _


                         Select FileName = file.Substring(_rootFolder.Length + 1), _


                             Type = Ext.Substring(1), _


                             Size = (New IO.FileInfo(file)).Length


        End If


        Me.Content = New BrowseWithMedia(Query, Me)


        Me.Title = _rootFolder + ” #Items = “ + _


            CType(Me.Content, BrowseWithMedia).Items.Count.ToString


    End Sub


    Private Sub Window1_Deactivated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Deactivated


        Dim pop = CType(Me.Content, BrowseWithMedia)._lvPopUp ‘ no popup if user changes active window (Alt-Tab)


        If Not pop Is Nothing AndAlso pop.IsOpen Then


            pop.IsOpen = False


        End If


    End Sub


    Private Class BrowseWithMedia : Inherits Browse


        Dim _Parent As Window1      ‘ reference to container


        Friend _lvPopUp As Popup    ‘ popup window to show tip with movie


        Dim WithEvents _lvTimer As New System.Windows.Threading.DispatcherTimer ‘ timer: after delay, show tip


        Sub New(ByVal Query As Object, Optional ByVal Parent As Object = Nothing)


            MyBase.new(Query, Parent)


            _Parent = Parent


        End Sub


        Sub HandleWheel(ByVal sp As StackPanel, ByVal e As System.Windows.Input.MouseWheelEventArgs) ‘Handles Me.MouseWheel


            Dim transform = CType(sp.RenderTransform, ScaleTransform)


            Dim melem As MediaElement = sp.Children(1)


            If e.Delta > 0 Then


                melem.Height *= 1.1


                melem.Width *= 1.1


                ‘transform.ScaleX *= 1.1    ‘ to scale tip text too


                ‘transform.ScaleY *= 1.1


            Else


                ‘transform.ScaleX /= 1.1


                ‘transform.ScaleY /= 1.1


                melem.Height /= 1.1


                melem.Width /= 1.1


 


            End If


        End Sub


        Dim _mdownPt As Point


        Sub HandleClick(ByVal sp As StackPanel, ByVal e As MouseButtonEventArgs)


            If sp.IsMouseCaptured Then


                sp.ReleaseMouseCapture()


            End If


            If e.LeftButton = MouseButtonState.Pressed Then


                _mdownPt = Mouse.GetPosition(sp)    ‘ record mouse pos relative to sp


                sp.CaptureMouse()


            End If


        End Sub


        Sub HandleMouseMove(ByVal sp As StackPanel, ByVal e As MouseEventArgs)


            If Mouse.LeftButton = MouseButtonState.Pressed Then


                If sp.CaptureMouse Then


                    Dim curpt As Point = sp.PointToScreen(Mouse.GetPosition(sp))


                    _lvPopUp.HorizontalOffset = curpt.X – _mdownPt.X    ‘ subtract mouse pos rel to sp


                    _lvPopUp.VerticalOffset = curpt.Y – _mdownPt.Y


                    _lvPopUp.Placement = PlacementMode.Absolute ‘ PlacementMode.Mouse


                End If


            End If


        End Sub


 


        Sub HandlePopupTimerTick() Handles _lvTimer.Tick    ‘ show the popup


            _lvTimer.IsEnabled = False  ‘ enabled in HandleRowSelected


            Dim lbi As ListBoxItem = Me.ItemContainerGenerator.ContainerFromIndex(Me.SelectedIndex)


            If Not lbi Is Nothing Then


                Dim sp As New StackPanel


                Dim cSrcFile = _Parent._rootFolder + IO.Path.DirectorySeparatorChar + lbi.Content.FileName.ToString


                Dim XAMLPopup = _


                <Popup


                    xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation


                    xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml


                    Placement=Right


                    IsOpen=False


                    >


                    <StackPanel Orientation=Vertical>


                        <StackPanel.RenderTransform>


                            <ScaleTransform ScaleX=1 ScaleY=1/>


                        </StackPanel.RenderTransform>


                        <TextBlock Foreground=Black Background=LightYellow>


                            <%= cSrcFile + ” “ + CType(lbi.Content.size / 1024, Int32).ToString(“n0”) + “K” %>


                        </TextBlock>


                        <MediaElement


                            xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation


                            xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml


                            Name=MyVid Height=250>


                            <MediaElement.Triggers>


                                <EventTrigger RoutedEvent=MediaElement.Loaded>


                                    <EventTrigger.Actions>


                                        <BeginStoryboard>


                                            <Storyboard>


                                                <MediaTimeline Source=<%= cSrcFile %>


                                                    Storyboard.TargetName=MyVid


                                                    RepeatBehavior=Forever/>


                                            </Storyboard>


                                        </BeginStoryboard>


                                    </EventTrigger.Actions>


                                </EventTrigger>


                            </MediaElement.Triggers>


                        </MediaElement>


                    </StackPanel>


                </Popup>


                _lvPopUp = System.Windows.Markup.XamlReader.Load(XAMLPopup.CreateReader)


                _lvPopUp.PlacementTarget = lbi


                _lvPopUp.IsOpen = True


                Dim stackpanel As StackPanel = _lvPopUp.Child ‘ only child is the stackpanel


                AddHandler stackpanel.MouseWheel, AddressOf HandleWheel ‘ mouse wheel will zoom in/out


                AddHandler stackpanel.MouseDown, AddressOf HandleClick  ‘ mouse click/drag will move picture/movie


                AddHandler stackpanel.MouseUp, AddressOf HandleClick


                AddHandler stackpanel.MouseMove, AddressOf HandleMouseMove


 


            End If


        End Sub


        Sub HandleRowSelected(ByVal sender As Object, ByVal e As RoutedEventArgs) Handles Me.SelectionChanged


            Dim lb = CType(sender, ListBox)


            If Not lb Is Nothing AndAlso (lb.SelectedIndex >= 0) Then


                Dim lbi As ListBoxItem = lb.ItemContainerGenerator.ContainerFromIndex(lb.SelectedIndex)


                If Not _lvPopUp Is Nothing AndAlso _lvPopUp.IsOpen Then


                    _lvPopUp.IsOpen = False


                End If


                _lvTimer.Stop()  ‘ stop prior timer, if any


                _lvTimer.Interval = TimeSpan.FromMilliseconds(500)


                _lvTimer.Start()


            End If


        End Sub


    End Class


End Class


 


Class Browse


    Inherits ListView


    Sub New(ByVal Query As Object, Optional ByVal Parent As Object = Nothing)


        Dim gv As New GridView


        Me.View = gv


        Me.ItemsSource = Query


        If Not Parent Is Nothing Then


            If Parent.GetType.BaseType Is GetType(Window) Then


                CType(Parent, Window).Title = “# items = “ + Me.Items.Count.ToString


            End If


        End If


        Me.AddHandler(GridViewColumnHeader.ClickEvent, New RoutedEventHandler(AddressOf HandleHeaderClick))


        For Each mem In From mbr In _


                        Query.GetType().GetInterface(GetType(IEnumerable(Of )).FullName) _


                            .GetGenericArguments()(0).GetMembers _


                        Where mbr.MemberType = Reflection.MemberTypes.Property


            Dim coltype = CType(mem, Reflection.PropertyInfo).PropertyType.Name


            Select Case coltype


                Case “Int32”, “String”, “Int64”


                    Dim gvc As New GridViewColumn


                    gvc.Header = mem.Name


                    gv.Columns.Add(gvc)


                    If coltype <> “String” Then


                        gvc.Width = 80


                        Dim XAMLdt = _


                            <DataTemplate


                                xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation


                                xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml


                                >


                                <StackPanel Orientation=Horizontal>


                                    <TextBlock Name=tb


                                        Text=<%= “{Binding Path=” + mem.Name + “}” %>


                                        Foreground=Black


                                        FontWeight=Bold


                                        Background=SpringGreen>


                                    </TextBlock>


                                </StackPanel>


                            </DataTemplate>


                        gvc.CellTemplate = System.Windows.Markup.XamlReader.Load(XAMLdt.CreateReader)


                    Else


                        gvc.DisplayMemberBinding = New Binding(mem.Name)


                        gvc.Width = 180


                    End If


            End Select


        Next


        Dim XAMLlbStyle = _


            <Style


                xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation


                xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml


                TargetType=ListBoxItem>


                <Setter Property=Foreground Value=Blue/>


                <Style.Triggers>


                    <Trigger Property=IsSelected Value=True>


                        <Setter Property=Foreground Value=White/>


                        <Setter Property=Background Value=Aquamarine/>


                    </Trigger>


                    <Trigger Property=IsMouseOver Value=True>


                        <Setter Property=Foreground Value=Red/>


                    </Trigger>


                </Style.Triggers>


            </Style>


        Me.ItemContainerStyle = Windows.Markup.XamlReader.Load(XAMLlbStyle.CreateReader)


    End Sub


    Dim _Lastdir As System.ComponentModel.ListSortDirection = ComponentModel.ListSortDirection.Ascending


    Dim _LastHeaderClicked As GridViewColumnHeader = Nothing


    Sub HandleHeaderClick(ByVal sender As Object, ByVal e As RoutedEventArgs)


        If e.OriginalSource.GetType Is GetType(GridViewColumnHeader) Then


            Dim gvh = CType(e.OriginalSource, GridViewColumnHeader)


            Dim dir As System.ComponentModel.ListSortDirection = ComponentModel.ListSortDirection.Ascending


            If Not gvh Is Nothing AndAlso Not gvh.Column Is Nothing Then


                Dim hdr = gvh.Column.Header


                If gvh Is _LastHeaderClicked Then


                    If _Lastdir = ComponentModel.ListSortDirection.Ascending Then


                        dir = ComponentModel.ListSortDirection.Descending


                    End If


                End If


                Sort(hdr, dir)


                _LastHeaderClicked = gvh


                _Lastdir = dir


            End If


        End If


    End Sub


    Sub Sort(ByVal sortby As String, ByVal dir As System.ComponentModel.ListSortDirection)


        Me.Items.SortDescriptions.Clear()


        Dim sd = New System.ComponentModel.SortDescription(sortby, dir)


        Me.Items.SortDescriptions.Add(sd)


        Me.Items.Refresh()


    End Sub


End Class


 


 


</Sample Code>