Partager via

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

  • Article

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="https://schemas.microsoft.com/winfx/2006/xaml/presentation"

                xmlns:x="https://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="https://schemas.microsoft.com/winfx/2006/xaml/presentation"

                            xmlns:x="https://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="https://schemas.microsoft.com/winfx/2006/xaml/presentation"

                                xmlns:x="https://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="https://schemas.microsoft.com/winfx/2006/xaml/presentation"

                xmlns:x="https://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>

Comments