Tuesday, June 28, 2016

Update Sequence After Deleting Table Row

Often we have an autoincrement column in a table where next insert into the table will be one more than the previous one. In SQL Server we can have an identity property on a column which can help in generating value based on current seed & increment. But in many cases we need to have auto increment depending on another column. Say for example in below image we have books for different languages and we have sequence number based on Book Type. 

Now if we delete a row from the table, the sequence number of subsequent rows will not change or get updated which will create a gap between two sequence number. This is one of the most common issues faced by developers and one of the most common question in various forums.

Solution 1

When we delete a row from the table, we can update the sequence number of subsequent rows by decrementing them by 1.

UPDATE BookDetail SET SequenceNo = SequenceNo - 1 WHERE BookID > 5

But decrementing by 1 will not work when we have deleted multiple row. For example 

DELETE FROM BookDetail WHERE SequenceNo <= 3

Solution 2

When we have update multiple rows in SQL with auto increment sequence, we can use Local Variable in SQL Server which will get incremented on each update. 

DECLARE @SequenceNumber INT = 0
UPDATE BookDetail SET @SequenceNumber = @SequenceNumber + 1, SequenceNo = @SequenceNumber
WHERE BookType = '.NET'

In this example, we have declared a SequenceNumber variable which will get incremented for each row, and we set that number in SequenceNo. Based on where condition in query, sequence number will get updated, and we will have sequence numbers without any gap.

Friday, August 15, 2014

Create Scheduled Toast Notification in Windows Phone Silverlight 8.1

Windows Phone 8.1 has a new feature called Toast Notification where we can show notifications to user and also show it in action center. We can also schedule the toast notification to appear at specific time. We can have scheduled toast notification in both Windows 8.1 Runtime app and Windows 8.1 Silverlight app.

Unlike Windows 8, Windows Phone only has one template for toast notification i.e. “toastText02”. It has application icon on left and accepts two text string with first with bold text and another with normal text. It is advisable to have very short strings to avoid truncation. We can use XML functions to set above two text.

 Dim toastTemplate As ToastTemplateType = ToastTemplateType.ToastImageAndText02
 Dim toastXml As XmlDocument = ToastNotificationManager.GetTemplateContent(toastTemplate)
 Dim toastTextElements As XmlNodeList = toastXml.GetElementsByTagName("text")
 toastTextElements(0).AppendChild(toastXml.CreateTextNode("File Downloaded"))
 toastTextElements(1).AppendChild(toastXml.CreateTextNode("Content.txt file downloaded from server"))

When user clicks on toast notification, the app gets launched. We can set launch attribute of toast element containing parameters which we can use to decide navigation page when the app is launched. We can write following code to set launch parameter.

Dim toastNode = toastXml.SelectSingleNode("/toast")
CType(toastNode, XmlElement).SetAttribute("launch", "?FileID=1")

So if our launch page is MainPage.xaml then uri would be “MainPage.xaml?FileID=1”. We can read the QueryString value in MainPage.xaml and do operation as per project requirement.

Protected Overrides Sub OnNavigatedTo(e As NavigationEventArgs)
        If NavigationContext.QueryString.ContainsKey("FileID") = True Then
            Dim fileID As String = NavigationContext.QueryString("FileID")
            'Perform operation using fileID
       End If
 End Sub

Now we can create a ScheduledToastNotification where we can pass XmlDocument object containing information about toast and scheduled time when the toast notification should be displayed. We can optional also set “ID” property for ScheduledToastNotification so that we can cancel it in future if required.

Dim notification As New ScheduledToastNotification(toastXml, dueTime)
notification.Id = notificationID

We can also set MaximumSnoozeCount parameter to set maximum number of times notification should be displayed and SnoozeInterval parameter to set time interval between notifications. SnoozeInterval should be between 1 to 60 minutes and MaximumSnoozeCount should be between 1 to 5.

Dim notification As New ScheduledToastNotification(toastXml, dueTime, TimeSpan.FromMinutes(5), 3)
If we don’t want to show ToastNotification popup and only show the details in action center then we can set SuppressPopup property.

notification.SuppressPopup = True

It is advisable to check if any toast notification with similar ID is already created or not. If already created then you can notify the user or delete it.

Dim task = ToastNotificationManager.CreateToastNotifier.GetScheduledToastNotifications().Where(Function(f) f.Id = notificationID)
If task.Count > 0 Then
End If

Now we can create ToastNotifier object and add scheduled notification using following code


For ToastNotification to work correctly we have to set Notification Service as “WNS” in WMAppManifest.xml file. This is one of the most important steps as your app could be reject in Certification process if “WNS” is not selected in Notification Service.

Below is complete code to create ScheduledToastNotification in Windows Phone Silverlight 8.1 app.

Private Sub CreateScheduleNotification(ByVal dueTime As Date, ByVal notificationID As Integer)
        Dim toastTemplate As ToastTemplateType = ToastTemplateType.ToastImageAndText02
        Dim toastXml As XmlDocument = ToastNotificationManager.GetTemplateContent(toastTemplate)
        Dim toastTextElements As XmlNodeList = toastXml.GetElementsByTagName("text")
        toastTextElements(0).AppendChild(toastXml.CreateTextNode("File Downloaded"))
        toastTextElements(1).AppendChild(toastXml.CreateTextNode("Content.txt file downloaded from server"))
        Dim toastNode = toastXml.SelectSingleNode("/toast")
        CType(toastNode, XmlElement).SetAttribute("launch", "?FileID=1")
        'Dim notification As New ScheduledToastNotification(toastXml, dueTime)
        Dim notification As New ScheduledToastNotification(toastXml, dueTime, TimeSpan.FromMinutes(5), 3)
        notification.Id = notificationID
        'notification.SuppressPopup =True 'To show notification only in action center
        Dim task = ToastNotificationManager.CreateToastNotifier.GetScheduledToastNotifications().Where(Function(f) f.Id = notificationID)
        If task.Count > 0 Then
        End If
    End Sub

Wednesday, April 2, 2014

Use WinDBG to see crash dump details

Recently one of my Windows Phone app had a crash and it was available for download in crash count on WP Dev Center. Crash count had an excel file containing links for .CAB file which can be downloaded and we can extract the .CAB file to get the dump file containing stack traces.
We have to use WinDBG which is a free debugging tool from Microsoft. Following are the steps which need to be performed to get the exception details and stack traces.
  1. Download Cab File and extract mdmp file
  2. Open command prompt and set _NT_Symbol_Path and _NT_ALT_Symbol_Path environment variable as below.

  3. As you can see in above image we have set symbol path as environment variable. We have set “http://msdl.microsoft.com/download/symbols” path which is a link for Microsoft Symbol Server.
  4. Open WinDBG using "WinDBG -y" command
  5. Open crash dump file "minidump.mdmp” file from “File -> Open Crash Dump…” (or Ctrl + D key)

  6. As you can see in above image you get a command line at bottom of screen with text “0.000>”.
  7. Turn on the noisy symbol loading mode using “!sym noisy” command. This command instructs the debugger to display information about its search for symbols.
    0.000> !sym noisy
    You can find more about this command from following link
  8. Use .reload command to delete all symbol information for the specified module and reload these symbols as needed. Using “/f” parameter with .reload command will force the debugger to load the symbols immediately.
    0.000> .reload /f
    You can know more about this command from following link


  9. Use “KV” command to get stack frame information. It displays frame pointer omission (FPO) information.
    0.000> kv
    You can find more about this command from following link


  10. Use “!analyze –v” to get information about current exception or bug check with verbose output.
    0.000> !analyze -v
    You can find more about this command from following link



Hope above steps helps you finding exception information from dump file available in Dev Center.

Special thanks to Bret Bentzinger and Eric Dunaway for providing lots of valuable input on the debugging process

Other Useful link

Wednesday, October 16, 2013

Searching in Windows Phone ListBox

One of the most common way of searching in ListBox is to type text in TextBox and filter the ListBox items. In this article we will see how we can do this in Windows Phone application.

As you can see in above image, we have a TextBox where user can type, and a ListBox which get filtered based on text entered.


    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
    <!--LayoutRoot is the root grid where all page content is placed-->
    <Grid x:Name="LayoutRoot" Background="Transparent">
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        <!--TitlePanel contains the name of the application and page title-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12">
            <TextBlock x:Name="ApplicationTitle" TextAlignment="Center"  Text="SEARCHABLE LISTBOX" Style="{StaticResource PhoneTextNormalStyle}"/>
        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
                <RowDefinition Height="Auto"/>
                <RowDefinition Height="*"/>
            <TextBox Grid.Row="0" Name="txtSearchText"/>
            <ListBox Grid.Row="1" Background="LightGray" Name="lstData" Margin="12,0"/>

Windows Phone has a class called CollectionView which allows grouping, sorting, filtering and navigation in a data collection. To create a collection view for a collection that implements IEnumerable, we can create a CollectionViewSource object, and add collection to the Source property. To filter the data based on TextBox text, we have to add event handler for Filter event.

Private Sub BindListBox()
       mTechnologiesCollectionSource.Source = mTechnologies
       Dim b As New Binding
       AddHandler mTechnologiesCollectionSource.Filter, AddressOf mTechnologiesCollectionSource_Filter
       b.Source = mTechnologiesCollectionSource
       lstData.SetBinding(ListBox.ItemsSourceProperty, b)
End Sub
Private Sub mTechnologiesCollectionSource_Filter(sender As Object, e As FilterEventArgs)
       Dim str As String = e.Item
       If String.IsNullOrWhiteSpace(txtSearchText.Text) = True OrElse str.ToUpperInvariant.StartsWith(txtSearchText.Text.ToUpperInvariant) = True Then
           e.Accepted = True
           e.Accepted = False
       End If
End Sub

In the above code, we are using "StartsWith" function to filter the Collection, and show the items which starts with text typed in TextBox. We can also use "Contains" function instead of "StartsWith" to show all the items which contains text typed in TextBox.

Private Sub mTechnologiesCollectionSource_Filter(sender As Object, e As FilterEventArgs)
       Dim str As String = e.Item
       If String.IsNullOrWhiteSpace(txtSearchText.Text) = True OrElse str.ToUpperInvariant.Contains(txtSearchText.Text.ToUpperInvariant) = True Then
           e.Accepted = True
           e.Accepted = False
       End If
End Sub

On TextChanged event on TextBox to reapply the filter we have to refresh the view using "CollectionViewSource.View.Refresh" function.

Imports System.Collections.ObjectModel
Imports System.Windows.Data
Partial Public Class MainPage
    Inherits PhoneApplicationPage
    Dim mTechnologies As New ObservableCollection(Of String)
    Dim mTechnologiesCollectionSource As New CollectionViewSource
    ' Constructor
    Public Sub New()
    End Sub
    Private Sub FillListBox()
        mTechnologies.Add("Visual Basic")
        mTechnologies.Add("Visual C#")
        mTechnologies.Add("Visual F#")
        mTechnologies.Add("Window Communication Foundation")
        mTechnologies.Add("Window Presentation Foundation")
        mTechnologies.Add("Window Phone")
    End Sub
    Private Sub MainPage_Loaded(sender As Object, e As RoutedEventArgs) Handles Me.Loaded
    End Sub
    Private Sub BindListBox()
        mTechnologiesCollectionSource.Source = mTechnologies
        Dim b As New Binding
        AddHandler mTechnologiesCollectionSource.Filter, AddressOf mTechnologiesCollectionSource_Filter
        b.Source = mTechnologiesCollectionSource
        lstData.SetBinding(ListBox.ItemsSourceProperty, b)
    End Sub
    Private Sub mTechnologiesCollectionSource_Filter(sender As Object, e As FilterEventArgs)
        Dim str As String = e.Item
        If String.IsNullOrWhiteSpace(txtSearchText.Text) = True OrElse str.ToUpperInvariant.StartsWith(txtSearchText.Text.ToUpperInvariant) = True Then
            e.Accepted = True
            e.Accepted = False
        End If
    End Sub
    Private Sub txtSearchText_TextChanged(sender As Object, e As TextChangedEventArgs) Handles txtSearchText.TextChanged
    End Sub
End Class

You can also refer following links to know more about CollectionViewSource


Friday, July 26, 2013

Editable Button in WPF

Today I am going to share a code snippet to create an Editable Button in WPF, where we can update the text of a Button at runtime. In this control the user would right-click a button and then click 'Edit' to make to control Editable, and later right-click it to save/cancel the change.

First step to make the Button editable is to change the Style of Button and add a TextBox inside it, which we will make visible when the user clicks on 'Edit'.

01.<Style TargetType="my:EditableButton">
02.    <Setter Property="FocusVisualStyle">
03.        <Setter.Value>
04.            <Style>
05.                <Setter Property="Control.Template">
06.                    <Setter.Value>
07.                        <ControlTemplate>
08.                            <Rectangle Margin="2" SnapsToDevicePixels="True" Stroke="{DynamicResource {x:Static SystemColors.ControlTextBrushKey}}" StrokeThickness="1" StrokeDashArray="1 2"/>
09.                        </ControlTemplate>
10.                    </Setter.Value>
11.                </Setter>
12.            </Style>
13.        </Setter.Value>
14.    </Setter>
15.    <Setter Property="Background" Value="#FFDDDDDD"/>
16.    <Setter Property="BorderBrush" Value="#FF707070"/>
17.    <Setter Property="Foreground" Value="{DynamicResource {x:Static SystemColors.ControlTextBrushKey}}"/>
18.    <Setter Property="BorderThickness" Value="1"/>
19.    <Setter Property="HorizontalContentAlignment" Value="Center"/>
20.    <Setter Property="VerticalContentAlignment" Value="Center"/>
21.    <Setter Property="Padding" Value="1"/>
22.    <Setter Property="Template">
23.        <Setter.Value>
24.            <ControlTemplate TargetType="{x:Type Button}">
25.                <Border x:Name="border" BorderBrush="{TemplateBinding BorderBrush}" BorderThickness="{TemplateBinding BorderThickness}" Background="{TemplateBinding Background}" SnapsToDevicePixels="True">
26.                    <Grid>
27.                        <ContentPresenter x:Name="tbkContent" ContentTemplate="{TemplateBinding ContentTemplate}" Content="{TemplateBinding Content}" ContentStringFormat="{TemplateBinding ContentStringFormat}" Focusable="False" HorizontalAlignment="{TemplateBinding HorizontalContentAlignment}" Margin="{TemplateBinding Padding}" RecognizesAccessKey="True" SnapsToDevicePixels="{TemplateBinding SnapsToDevicePixels}" VerticalAlignment="{TemplateBinding VerticalContentAlignment}"/>
28.                        <TextBox   Name="txtContent" Height="{TemplateBinding Height}" TextAlignment="Center" Visibility="Collapsed" Text="{TemplateBinding Content}" Padding="0" Background="Transparent" BorderBrush="Transparent" BorderThickness="0" HorizontalContentAlignment="{TemplateBinding HorizontalContentAlignment}"  VerticalContentAlignment="{TemplateBinding VerticalContentAlignment}" Margin="{TemplateBinding Padding}"/>
29.                    </Grid>
30.                </Border>
31.                <ControlTemplate.Triggers>
32.                    <Trigger Property="IsDefaulted" Value="True">
33.                        <Setter Property="BorderBrush" TargetName="border" Value="{DynamicResource {x:Static SystemColors.HighlightBrushKey}}"/>
34.                    </Trigger>
35.                    <Trigger Property="IsMouseOver" Value="True">
36.                        <Setter Property="Background" TargetName="border" Value="#FFBEE6FD"/>
37.                        <Setter Property="BorderBrush" TargetName="border" Value="#FF3C7FB1"/>
38.                    </Trigger>
39.                    <Trigger Property="IsPressed" Value="True">
40.                        <Setter Property="Background" TargetName="border" Value="#FFC4E5F6"/>
41.                        <Setter Property="BorderBrush" TargetName="border" Value="#FF2C628B"/>
42.                    </Trigger>
43.                    <Trigger Property="ToggleButton.IsChecked" Value="True">
44.                        <Setter Property="Background" TargetName="border" Value="#FFBCDDEE"/>
45.                        <Setter Property="BorderBrush" TargetName="border" Value="#FF245A83"/>
46.                    </Trigger>
47.                    <Trigger Property="IsEnabled" Value="False">
48.                        <Setter Property="Background" TargetName="border" Value="#FFF4F4F4"/>
49.                        <Setter Property="BorderBrush" TargetName="border" Value="#FFADB2B5"/>
50.                        <Setter Property="TextElement.Foreground" TargetName="tbkContent" Value="#FF838383"/>
51.                    </Trigger>
52.                </ControlTemplate.Triggers>
53.            </ControlTemplate>
54.        </Setter.Value>
55.    </Setter>

As you can see in the above code, I have added a "txtContent" textbox which is hidden. Also to make editing look like a part of the Button, the Background and BorderBrush properties of textbox are transparent

01.Public Class EditableButton
02.    Inherits Button
03.    Private mButtonContentPresenter As ContentPresenter
04.    Private mButtonTextBox As TextBox
06.    Public Sub New()
07.        Me.DefaultStyleKey = GetType(EditableButton)
08.        AddHandler Me.Loaded, New RoutedEventHandler(AddressOf EditableButton_Loaded)
09.    End Sub
11.    Private Sub EditableButton_Loaded(sender As Object, e As RoutedEventArgs)
13.        Me.OnApplyTemplate()
14.    End Sub
16.    Public Overrides Sub OnApplyTemplate()
17.        MyBase.OnApplyTemplate()
18.        mButtonContentPresenter = DirectCast(Me.GetTemplateChild("tbkContent"), ContentPresenter)
19.        mButtonTextBox = DirectCast(Me.GetTemplateChild("txtContent"), TextBox)
20.        CreateSaveContextMenu()
21.        CreateEditContextMenu()
22.    End Sub
24.    Public Property IsEditMode() As Boolean
25.        Get
26.            Return CBool(GetValue(IsEditModeProperty))
27.        End Get
28.        Private Set(value As Boolean)
29.            SetValue(IsEditModeProperty, value)
30.        End Set
31.    End Property
33.    ' Using a DependencyProperty as the backing store for IsEditMode.  This enables animation, styling, binding, etc...
34.    Public Shared ReadOnly IsEditModeProperty As DependencyProperty = DependencyProperty.Register("IsEditMode", GetType(Boolean), GetType(EditableButton), New PropertyMetadata(False))
36.    Private Sub CreateSaveContextMenu()
37.        Dim menu As New ContextMenu
38.        Dim itm As New MenuItem()
39.        itm.Header = "Save"
40.        AddHandler itm.Click, New RoutedEventHandler(AddressOf itmSave_Click)
41.        menu.Items.Add(itm)
43.        itm = New MenuItem()
44.        itm.Header = "Cancel"
45.        AddHandler itm.Click, New RoutedEventHandler(AddressOf itmCancel_Click)
46.        menu.Items.Add(itm)
48.        ContextMenuService.SetContextMenu(mButtonTextBox, menu)
49.    End Sub
53.    Private Sub CreateEditContextMenu()
54.        Dim menu As New ContextMenu
55.        Dim itm As New MenuItem
56.        itm.Header = "Edit"
57.        AddHandler itm.Click, New RoutedEventHandler(AddressOf itmEdit_Click)
58.        menu.Items.Add(itm)
60.        ContextMenuService.SetContextMenu(mButtonContentPresenter, menu)
61.    End Sub
63.    Private Sub itmEdit_Click(sender As Object, e As RoutedEventArgs)
64.        mButtonContentPresenter.Visibility = Visibility.Collapsed
65.        mButtonTextBox.Visibility = Visibility.Visible
66.        IsEditMode = True
67.    End Sub
69.    Private Sub itmSave_Click(sender As Object, e As RoutedEventArgs)
70.        mButtonContentPresenter.Visibility = Visibility.Visible
71.        mButtonTextBox.Visibility = Visibility.Collapsed
72.        Me.Content = mButtonTextBox.Text
73.        IsEditMode = False
74.    End Sub
76.    Private Sub itmCancel_Click(sender As Object, e As RoutedEventArgs)
77.        mButtonContentPresenter.Visibility = Visibility.Visible
78.        mButtonTextBox.Visibility = Visibility.Collapsed
79.        IsEditMode = False
80.    End Sub
81.End Class

In the above code we have an "IsEditMode" property which the user can use to know whether the control is in Edit Mode or not. Also, we have two different Context Menus, one to Edit the Control, and the another to Save/Cancel the changes. Based on the menuitems that are clicked, we are going to set the visibility of TextBox and ContentPresenter, defined in XAML.

Now we can use the control in our application as below

01.<Window x:Class="MainWindow"
04.    xmlns:my="clr-namespace:WpfApplication1"
05.    Title="MainWindow" Height="350" Width="525">
07.    <Grid>
08.        <my:EditableButton x:Name="MyButton" Width="100" Height="25" Content="Edit Me"/>
09.    </Grid>

Hope the above control helps you in your development.