Kürzlich habe ich ein Beispiel für den Zugriff auf FoxPro-Tabellen mit LINQ gefunden. Das Beispiel ist leider in VB geschrieben, was sich ja aber leicht umstellen lässt….
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
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
- 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
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
