Graeme Hill on .NET development

Josh Smith just made a blog post about XAML DataContext comments when using the MVVM pattern. He makes a great point, which is that in many cases it is not immediately obvious what the DataContext of a view is intended to be. A simple comment as Josh suggests will go a long way, but the downside is that comments create a maintainability issue. If you rename a model view, or refactor code so that a page, window or user control expects a different DataContext you also need to update the comment. Here’s an example:

<!-- DataContext = SampleModelView -->
<UserControl x:Class="SampleUserControl"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Width="300" Height="300">
    <Grid>
 
    </Grid>
</UserControl>

But now what if I change my mind and decide that instead of SampleModelView, this UserControl will have AlternateModelView as its DataContext? If I forget to update the comment then it is now a source of misinformation. What I would really like to do is somehow specify the expected data context type for a given UI element, so I created an attached property called ExpectedDataContextType. When the element is loaded, it will fail at runtime if the DataContext is not of the desired type. It looks like this:

<UserControl x:Class="TestControl"
    xmlns:local="clr-namespace:ExpectedDataContextType"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Width="300" Height="300"
    local:DataContextHelper.ExpectedDataContextType="{x:Type local:TestModelView}">
    <Grid>
        <TextBlock>Hello</TextBlock>
    </Grid>
</UserControl>

The code is extremely simple. It just attaches a handler to the Loaded event and then checks the type. When the types do not match up it gives you a warning via a message box if you are debugging. I chose not to throw an exception because the exception gets covered up and just ends up as a tiny message on Visual Studio immediate window. When I am debugging and a control has the wrong DataContext I want to make sure I find out about it, hence the message box. You can always replace the message box code with something else though.

Option Strict On
 
Public Class DataContextHelper
 
    Public Shared Function GetExpectedDataContextType(ByVal element As DependencyObject) As Type
        If element Is Nothing Then
            Throw New ArgumentNullException("element")
        End If
 
        Return DirectCast(element.GetValue(ExpectedDataContextTypeProperty), Type)
    End Function
 
    Public Shared Sub SetExpectedDataContextType(ByVal element As DependencyObject, ByVal value As Type)
        If element Is Nothing Then
            Throw New ArgumentNullException("element")
        End If
 
        element.SetValue(ExpectedDataContextTypeProperty, value)
    End Sub
 
    Public Shared ReadOnly ExpectedDataContextTypeProperty As  _
                           DependencyProperty = DependencyProperty.RegisterAttached("ExpectedDataContextType", _
                           GetType(Type), GetType(DataContextHelper), _
                           New FrameworkPropertyMetadata(Nothing, AddressOf OnExpectedDataContextTypeChanged))
 
    Private Shared Sub OnExpectedDataContextTypeChanged(ByVal obj As DependencyObject, ByVal args As DependencyPropertyChangedEventArgs)
        Dim element = DirectCast(obj, FrameworkElement)
        AddHandler element.Loaded, AddressOf OnElementLoaded
    End Sub
 
    Private Shared Sub OnElementLoaded(ByVal sender As Object, ByVal args As RoutedEventArgs)
        Dim element = DirectCast(sender, FrameworkElement)
 
        RemoveHandler element.Loaded, AddressOf OnElementLoaded
 
        ' Compare the expected type to the actual type
        Dim expectedDataContextType = GetExpectedDataContextType(element)
        Dim actualDataContextType = element.DataContext.GetType
        If expectedDataContextType IsNot actualDataContextType Then
 
#If DEBUG Then
            ' The types don't match and debug mode is on so notify the developer that the element
            ' has the wrong data context
            MessageBox.Show(String.Format("DataContext has type {0}. Expected {1}.", _
                                          actualDataContextType.ToString, _
                                          expectedDataContextType.ToString))
#End If
 
        End If
    End Sub
 
End Class

The end result is that you are still specifying what the type of your DataContext should be, but now it is actually enforced. The best part is that if the you rename your model view without updating the ExpectedDataContextType property you will get a compile error because the type no longer exists. If the type still exists then you have to settle for a runtime error.

As almost anyone who tries to unit test a database application will quickly discover, databases present a huge problem for unit testing. Strictly speaking, if you are testing your C# or VB code and you actually hit a real database, then it isn’t really a unit test. It is actually an integration test. However, I have found that it doesn’t really matter what you call it, the end result is that your tests are much more useful if they actually hit a real database. You don’t have to worry about whether the test failed because you screwed up your mock object or if the actual application is buggy and you get better code coverage because even broken SQL will lead to a failed test.

There are several methods that can be used to prevent your unit tests from actually using a real SQL Server database, but they all have their problems:

• Using an in-memory provider like SQLite

  There is an Entity Framework provider for SQLite that allows you to interact with a database without using a network or even going to your file system. This could certainly increase the execution speed of your unit tests and makes it easy to prevent cross contamination in your tests, but they are still integration tests. The only difference is that you are now testing whether your code works on SQLite, rather than the DBMS that you will actually use in production. The problem is that all database systems have different behaviors and feature sets, so your tests are no longer valid if you use a different DBMS for testing. There is also currently no system in place to automatically generate the SQLite schema from your entity data model, so you will need to find your own way of doing that, or you have to manually maintain a separate SQLite schema. Gross. If you are going to use another provider, it needs to be specially designed to behave exactly like your production database (ie: a mock SQL Server provider) but to my knowledge no such providers exists (if I’m wrong, please let me know!).

• Mock the Entity Framework ObjectContext

  If all you want to do is read data, then this works well and is easy to implement. Unfortunately, in the vast majority of cases, we also need to write data and that’s where this method gets tricky. Your mock ObjectContext needs to be able to track changes and save them to an in-memory repository. And again, you have to make sure that it behaves exactly like your production database. Because this method often involves either a huge wrapper or major alterations to auto-generated code (which means you also need to make your own generator or you’ll lose maintainability) the mock object itself is extremely complicated, leaving a high likelihood that it will have errors. Since the mock is so complicated one could argue that you are again doing integration tests, not unit tests. But this time instead of testing your code and the database, you are testing your code and the mock ObjectContext. Just like the SQLite example, this is much worse because you are testing whether your code integrates with something you will not use in production. If you are going to do integration tests anyways, then you might as well integrate with the real thing. This method could lead to faster executing tests, but don’t forget that a local SQL Server instance is actually extremely fast and might be just as good.

• Encapsulate your data access layer and then mock it

  I see this response on message boards all the time. Whenever someone asks how they unit test their data access code someone will respond “You’re doing it wrong, put all of your data access code into a separate module that you can mock”. There are a couple problems with this. First of all, you still need to test the code in the data access layer. If you have a function in your DAL that executes a complicated LINQ to Entites query, then you want to test that query. Without using one of the techniques mentioned above, this requires hitting the database. Secondly, making your client code completely unaware of the data access layer’s implementation leads to some issues. Let’s pretend that my data access layer looks like this:

  Public Interface IUsersModel
   
      Function GetUsers() As IEnumerable(Of Users)
      Sub Save()
   
  End Interface
   
  Public Class UsersModel
      Implements IUsersModel
   
      Private _context As New DataTestEntities
   
      Public Function GetUsers() As IEnumerable(Of Users) Implements IUsersModel.GetUsers
          Return _context.Users
      End Function
   
      Public Sub Save() Implements IUsersModel.Save
          _context.SaveChanges()
      End Sub
   
  End Class

  It’s pretty simple, the code just allows you to get a collection of users and save any changes you make. UsersModel correctly implements the interface using the Entity Framework. Then we also have a controller that accesses the DAL. It looks like this:

  Public Class UsersController
   
      Private _usersModel As IUsersModel
   
      Public Sub New(ByVal usersModel As IUsersModel)
          _usersModel = usersModel
      End Sub
   
      Public Sub ChangeFirstUserNameToFoobar()
          _usersModel.GetUsers().First.userName = "foobar"
          _usersModel.Save()
      End Sub
   
  End Class

  UsersController has a dependency on IUsersModel, so when unit testing the ChangeFirstUserNameToFoobar method, we pass in a mock implementation of IUsersModel, but we cannot simply verify that Save() was called, we also need to know what is going to happen when Save is called. Specifically, we need some way of checking that the first user’s username was changed to “foobar”. This means that a mocking framework like RhinoMocks or Moq will not be sufficient. There must be a fake implementation of IUsersModel that keeps track of the changes that have been made. Now we are getting back into “mock the ObjectContext” territory because that’s basically what we will have done.

There is a definite trend here: each of the above methods is complicated enough that you lose the benefits of isolating your tests from the database. They are all integration tests. In every case you are testing your client code, plus the repository. Since you have to test a repository, it might as well be the real one. Of course, this presents its own challenges. You will want to use a local instance of SQL Server (or whatever DBMS you use) to keep the tests fast (and isolated from other developers) and you will need to roll back changes after each test. In subsequent articles I will look at how to deal with these issues.

Update: I have posted the second article: Unit testing an Enitity Framework DAL part 2: Rolling back the test database

Download the code here: WpfNullableComboBox.zip

A few months ago I wrote this article on making a nullable combo box control in WPF. I had a bunch of requests to see an actual implementation, so here is a sample Visual Studio 2008 project with the source code for the user control. It basically uses the same technique I described in my previous blog post. All the combo box properties (even the obscure ones no one will ever use) are implemented.

As I described in this previous article raising the PropertyChanged event for classes that implement INotifyPropertyChanged can be a real pain. The biggest problem is that PropertyChangedEventArgs takes the name of the property that changed as a string and as we all know, strings are the root of all evil. Here I will show how to use a simple PostSharp attribute on your properties that need to raise the PropertyChanged event when they are changed so that you don’t manually need to do it and hard code the name of the property as a string. PostSharp is a framework for .NET that allows for aspect oriented programming. You can read all about it at the PostSharp website.

First of all, let’s assume that the classes implementing INotifyPropertyChanged are model view classes in the MVVM pattern. We will use a base class for all model views called BaseModelView that looks like this:

Imports System.ComponentModel
 
''' <summary>
''' Parent class for all model views
''' </summary>
Public Class BaseModelView
    Implements INotifyPropertyChanged
 
    Public Event PropertyChanged( _
        ByVal sender As Object, _
        ByVal e As System.ComponentModel.PropertyChangedEventArgs) _
        Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged
 
    ''' <summary>
    ''' Raises the <c>PropertyChanged</c> event for the property with the given name.
    ''' </summary>
    ''' <param name="propertyName">The name of the property that has changed.</param>
    ''' <remarks>If there is no property on this class with the given name, then an
    ''' exception will be thrown.</remarks>
    Public Sub OnPropertyChanged(ByVal propertyName As String)
 
        ' Throw an exception if the property doesn't exist
        If Me.GetType().GetProperty(propertyName) Is Nothing Then
            Throw New ArgumentException( _
                String.Format("The property {0} doesn't exist on type {1}.", _
                              propertyName, _
                              Me.GetType().Name))
        End If
 
        RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
 
    End Sub
 
End Class

This class is very important. There needs to be a method for property change notification (ie: OnPropertyChanged on BaseModelView) instead of just an event (ie: PropertyChanged on INotifyPropertyChanged) because the attribute cannot directly raise an event, but it can call a public method that raises the event. The PostSharp attribute looks like this:

Imports PostSharp.Laos
Imports System.ComponentModel
 
<Serializable()> _
Public Class NotifyAttribute
    Inherits OnMethodBoundaryAspect
 
    Public Overrides Sub OnExit(ByVal eventArgs As PostSharp.Laos.MethodExecutionEventArgs)
        ' Convert to BaseModelView
        Dim notifier = TryCast(eventArgs.Instance, BaseModelView)
 
        ' If the instance is the wrong type then throw an exception
        If notifier Is Nothing Then
            Throw New InvalidOperationException("Cannot raise PropertyChanged event unless instance implements INotifyPropertyChanged.")
        End If
 
        ' Ignore everything that's not a setter
        If eventArgs.Method.Name.StartsWith("set_") Then
            notifier.OnPropertyChanged(eventArgs.Method.Name.Substring(4))
        End If
    End Sub
 
End Class

Note that when you apply PostSharp attribute to a property, you are actually applying the attribute to the two methods that are generated for that property. For example, if you have a property called MyProperty then the compiler will actually generate two methods: get_MyProperty and set_MyProperty. Since OnExit() will actually get called for both of these methods when we apply the attribute to a property, the code has to check whether the getter or the setter was called. Using the attribute is very simple:

<Notify()> _
Public Property Text() As String
    Get
        Return _text
    End Get
    Set(ByVal value As String)
        _text = value
    End Set
End Property

The result is that the PropertyChanged event will automatically be raised after the setter finishes executing and there is no need to hard code any strings! Now you are free to change the name of your property and it won’t break any code.

Download the sample project here: WpfRenderingThreadSynchronization.zip

In most applications it is necessary to offload long running processes to an alternate thread so that the rest of the program does not lock up during that time. However, it’s not so simple when the long running process is the actual rendering. Separate windows can have their own UI threads (as explained here) but to my knowledge there is no way to use multiple rendering threads on a single window.

The second problem is that rendering is done in big chunks. For example, if you have an ItemsControl that is bound to an ObservableCollection and a loop that adds 1000 items to that collection, you will notice that the elements are not drawn one at a time. Instead the UI will stall for a moment and then every element will suddenly appear on screen. During the time that it is loading, the entire window will be completely unusable. Basically, what happens is that UI changes (like adding an element to an ItemsControl’s ObservableCollection) all get put into a queue and then the rendering thread deals with a whole bunch of them all at once.

There are two problems with this behaviour:

• The rest of the UI is unusable while this loading takes place
• It’s not obvious what is happening during the loading period. Since absolutely nothing is happening on screen, the user might think the app is broken.

It turns out that in cases like this where we have many small rendering operations that add up to a large amount of time, we can force the rendering thread to flush out the Windows message queue after each element is added to the collection. This will not only allow the user to see progress (ie: items appearing in the ItemsControl one at a time) but between each item being added other UI updates can take place giving the illusion that there are separate UI threads.

The included sample draws 1000 TextBoxes inside an ItemsControl, but after each element is added the Windows message queue is flushed out using the FlushWindowsMessageQueue function. All the functions does is tell the dispatcher to invoke a delegate that does nothing. The result is that the code blocks at that line until the specified delegate has been run. But since it is at the end of the queue, everything else has to be dealt with first. The function looks like this:

Private Sub FlushWindowsMessageQueue()
    Application.Current.Dispatcher.Invoke( _
        New Action(AddressOf DummySub), _
        DispatcherPriority.Background, _
        New Object() {})
End Sub
 
Private Sub DummySub()
End Sub

When the sample is run with the FlushWindowsMessageQueue() line commented out the whole UI will lock up for a couple of seconds after you click “Refresh data”. However, when the message queue is emptied after each element is added the UI never locks up, even when it is still drawing TextBoxes.

Unfortunately, there are some drawbacks to this method. The most obvious is that it makes the entire rendering operation actually take longer. The trade off is that the first items appear much earlier, but the last items appear later. The technique also cannot be used when the rendering cannot easily be split into many small chunks.

VB.NET has the sometimes useful feature of late binding, but this seems to lead to poor code. By default, late binding is enabled (ie: Option Strict is set to Off) allowing for implicit narrowing conversions (no cast). Although there are certainly cases where this is a useful feature that can cut down on the amount of reflection code left up to the programmer, I have found that it is more often a cause of less robust code and needless performance degradation.

With Option Strict Off we can write code like this:

Dim obj As Object = "Hello, World!"
Dim str As String = obj

In this case the code will run just fine, and it saved us the hassle of casting obj to String. However, we will obviously run into problems in a situation like this:

Dim obj As Object = "Hello, World!"
Dim int As Integer = obj

Even though int is an Integer this code will compile, but at runtime there will be an InvalidCastException. This is all pretty simple stuff, but the bottom line is that in this case, Option Strict Off gives a runtime error, while Option Strict On gives a compile error. The value of compile-time errors should not be taken lightly, and in my humble opinion they are a programmer’s best friend. With Option Strict On our first sample only needs a minor change:

Dim obj As Object = "Hello, World!"
Dim str As String = DirectCast(obj, String)

Was it really that difficult just to cast it? Type casting is not an inconvenience, but a necessary precaution requiring the programmer to say to the compiler: “Yes, I did intend to perform a narrowing conversion. It was not an accident”.

As a general rule of thumb, I like to set Option Strict On as the project default (go to Project -> Properties -> Compile) and then add Option Strict Off to code files that require it rather than the other way around.

Update: I have posted another article here that explains what I think is a better solution to this problem using a simple PostSharp attribute.

When you raise the PropertyChanged event you have to pass it a property name as a string. If there is no property with that name then nothing will happen. The listener will not be notified and no exception will be thrown making the problem very difficult to debug. You can change this behaviour and make the application fail at runtime by adding a simple check to your helper function for the event:

Public Sub NotifyPropertyChanged(ByVal propertyName As String)
    ' Throw an exception if the property doesn't exist
    If Me.GetType().GetProperty(propertyName) Is Nothing Then
        Throw New InvalidPropertyNameException()
    End If
 
    RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
End Sub

If you put this in a base class for all of your model views (or controllers, or presenters) then you will automatically get this functionality every time, preventing some potentially very annoying bugs.

This still isn’t the ultimate solution because you don’t find out that the property name doesn’t exist until runtime. Ideally, we would get a compile error when the property does not exist. What I would like to do is call the function like this:

NotifyPropertyChanged(AddressOf MyProperty)

This way you wouldn’t have to use a string at all and the compiler would tell you if MyProperty doesn’t exist. Unfortunately, .NET languages only have delegates for functions/subroutines so there is no way to make a strongly typed pointer to a property. Let’s hope they add that in one day, but until then, we’ll have to use strings.

By default, validation is not enforced on a binding until after the value has been changed once. Consider this situation: A form has a text box whose contents should not be empty, but its starting value is the empty string. The text box will not show an error when the form is loaded. Instead, it will only show its error style after the user types in some text and then deletes it. Some people would call this a feature, but I prefer to have the validation checked right away so that I immediately know what the required fields are, and which starting values are invalid.

A quick hack

One way to tackle this is to hook a loaded event to the control and then call UpdateSource() from code:

Private Sub myTextBox_Loaded(ByVal sender As System.Object, _
        ByVal e As System.Windows.RoutedEventArgs) Handles myTextBox.Loaded
 
    BindingOperations.GetBindingExpression(myTextBox, _
        TextBox.TextProperty).UpdateSource()
 
End Sub

Unfortunately, this really isn’t a desirable solution because it requires some pretty ugly code-behind (not that there’s any other kind) and needs to be done over again for every binding. What we really want is for this functionality to be a part of the binding markup extension.

Creating a wrapper around the binding markup extension

At first I thought it would be easy to just inherit from the Binding class and override ProvideValue, but of course, it is marked as NotOverridable (sealed in C#). Instead, I create a class called ValidationBinding that inherits directly from MarkupExtension and has the new class manage an instance of Binding. The code looks like this:

Imports System.Windows.Markup
 
Public Class ValidationBinding
    Inherits MarkupExtension
 
    Private _binding As New Binding
    Private _dependencyObject As DependencyObject
    Private _dependencyProperty As DependencyProperty
 
    Public Sub New()
        _binding.ValidatesOnDataErrors = True
        _binding.ValidatesOnExceptions = True
    End Sub
 
    Public Sub New(ByVal path As String)
        Me.New()
        _binding.Path = New PropertyPath(path)
    End Sub
 
    Public Overrides Function ProvideValue _
        (ByVal serviceProvider As System.IServiceProvider) As Object
 
        Dim valueTarget = _
            DirectCast(serviceProvider.GetService(GetType(IProvideValueTarget)),  _
            IProvideValueTarget)
 
        _dependencyObject = valueTarget.TargetObject
        _dependencyProperty = valueTarget.TargetProperty
 
        If TypeOf _dependencyObject Is FrameworkElement Then
            Dim element = DirectCast(_dependencyObject, FrameworkElement)
            If element.IsLoaded Then
                ForceValidation()
            Else
                AddHandler element.Loaded, AddressOf ElementLoaded
            End If
        Else
            ForceValidation()
        End If
 
        Return _binding.ProvideValue(serviceProvider)
 
    End Function
 
    Private Sub ForceValidation()
        BindingOperations.GetBindingExpression(_dependencyObject, _
                                               _dependencyProperty).UpdateSource()
    End Sub
 
    Private Sub ElementLoaded(ByVal sender As System.Object, _
                              ByVal e As System.Windows.RoutedEventArgs)
        ForceValidation()
    End Sub
 
    Public Property Path() As PropertyPath
        Get
            Return _binding.Path
        End Get
        Set(ByVal value As PropertyPath)
            _binding.Path = value
        End Set
    End Property
 
    ... the rest of the binding properties go here
 
End Class

The binding can then be used like this (where my is an imported namespace containing ValidationBinding):

<TextBox Margin="5" Text="{my:ValidationBinding Path=Text}" />

As an example, I have exposed the Binding’s Path property, but you actually have to do this for all of the public properties in Binding. A reference of all the properties that should be implemented can be found here.

ProvideValue returns the result of the Binding’s ProvideValue function, but first it checks whether the binding target has finished loading. If it has already finished loading then it forces validation by calling UpdateSource() on the target. In the much more likely scenario that the control has not yet loaded (this will be the case when you set your binding in XAML) it attaches a handler to the Loaded event so that the ForceValidation subroutine can be deferred until it is finished loading.

Also, notice that I set both ValidatesOnDataErrors and ValidatesOnExceptions to True in the constructor so that I didn’t need to specify it in the XAML. Chances are that whenever you use this markup extension you will want those enabled anyways.

It may seem like a lot of work, but it is a very reusable solution that gives you significantly more power.

The LINQ to SQL DataContext provides excellent functionality for managing a set of local changes to a database that can be pushed to the server with a single call to SubmitChanges(). Inevitably there will be situations where you want to discard the changes you have made, effectively allowing you to continue using the DataContext as though those changes had never been made. Unfortunately, there is no DataContext.DiscardChanges() method.

A little research reveals that this is by design and that you should simply recreate the DataContext in these cases, but of course, nothing is that simple. Every object you have that came from the original DataContext now needs to be reset to use the new one to guarantee predictable behaviour. For example, the last line of this snippet will set objectsAreEqual to False:

' Create two data contexts
Dim dc1 As New UsersDataContext
Dim dc2 As New UsersDataContext
 
' Grab the same record out of both the data contexts
Dim user1 = dc1.Users.First
Dim user2 = dc2.Users.First
 
' False, because the otherwise identical records come from different contexts
Dim objectsAreEqual = user1 = user2

Basically, this shows that it does matter where your object came from. If you just want to discard a few little changes and you don’t want to have to recreate all your variables, you can instead undo all the changes that you have made so far and continue to use the same DataContext.

Undoing inserts and deletes is easy, because we can just do the opposite to revert the change. Luckily there is even a nice function on the DataContext that will show us what we need to do called GetChangeSet:

Public Sub DiscardInsertsAndDeletes(ByVal data As DataContext)
    ' Get the changes
    Dim changes = data.GetChangeSet()
 
    ' Delete the insertions
    For Each insertion In changes.Inserts
        data.GetTable(insertion.GetType).DeleteOnSubmit(insertion)
    Next
 
    ' Insert the deletions
    For Each deletion In changes.Deletes
        data.GetTable(deletion.GetType).InsertOnSubmit(deletion)
    Next
End Sub

LINQ to SQL is smart enough to know that if a row is inserted and then deleted (or the other way around) nothing needs to be done. If you look at the ChangeSet after running this function, you will notice that the Inserts and Deletes collections are empty. Updates are a little more annoying. If you update a value, and then set it back to its original state then you will get the expected behaviour (ie: the ChangeSet will be empty because you reverted your change) but unless you write code to keep track of the original value yourself, there is no way to automatically put an object back into its start state without hitting the database. If hitting the database is acceptable, you can always use the Refresh function to get rid of the updates:

Public Sub DiscardUpdates(ByVal data As DataContext)
    ' Get the changes
    Dim changes = data.GetChangeSet()
 
    ' Refresh the tables with updates
    Dim updatedTables As New List(Of ITable)
    For Each update In changes.Updates
        Dim tbl = data.GetTable(update.GetType)
        ' Make sure not to refresh the same table twice
        If updatedTables.Contains(tbl) Then
            Continue For
        Else
            updatedTables.Add(tbl)
            data.Refresh(RefreshMode.OverwriteCurrentValues, tbl)
        End If
    Next
End Sub