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.

DELETE FROM BookDetail WHERE BookID=5

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.

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)

        MyBase.OnNavigatedTo(e)

        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

    ToastNotificationManager.CreateToastNotifier.RemoveFromSchedule(task.First)

End If

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

ToastNotificationManager.CreateToastNotifier().AddToSchedule(notification)

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

            ToastNotificationManager.CreateToastNotifier.RemoveFromSchedule(task.First)

        End If

        ToastNotificationManager.CreateToastNotifier().AddToSchedule(notification)

    End Sub

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
   
   
   http://msdn.microsoft.com/en-us/library/windows/hardware/ff565396(v=vs.85).aspx
   
   
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
   
   http://msdn.microsoft.com/en-us/library/windows/hardware/ff564805(v=vs.85).aspx
   
   
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
   
   http://msdn.microsoft.com/en-us/library/windows/hardware/ff551943(v=vs.85).aspx
   
   
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
    
    http://msdn.microsoft.com/en-us/library/windows/hardware/ff562112(v=vs.85).aspx
    
    

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
http://blogs.msdn.com/b/wsdevsol/archive/2013/08/15/dump-diving-into-cert-failure_3a00_--how-to-get-a-good-callstack-from-triagedump.dmp.aspx

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.

XAML Code

<phone:PhoneApplicationPage

    x:Class="PhoneApp1.MainPage"

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

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

    xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"

    xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"

    FontFamily="{StaticResource PhoneFontFamilyNormal}"

    FontSize="{StaticResource PhoneFontSizeNormal}"

    Foreground="{StaticResource PhoneForegroundBrush}"

    SupportedOrientations="Portrait" Orientation="Portrait"

    shell:SystemTray.IsVisible="True">

    <!--LayoutRoot is the root grid where all page content is placed-->

    <Grid x:Name="LayoutRoot" Background="Transparent">

        <Grid.RowDefinitions>

            <RowDefinition Height="Auto"/>

            <RowDefinition Height="*"/>

        </Grid.RowDefinitions>

        <!--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}"/>

        </StackPanel>

        <!--ContentPanel - place additional content here-->

        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">

            <Grid.RowDefinitions>

                <RowDefinition Height="Auto"/>

                <RowDefinition Height="*"/>

            </Grid.RowDefinitions>

             

            <TextBox Grid.Row="0" Name="txtSearchText"/>

            <ListBox Grid.Row="1" Background="LightGray" Name="lstData" Margin="12,0"/>

        </Grid>

    </Grid>

  

</phone:PhoneApplicationPage>

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

       Else

           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

       Else

           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()

        InitializeComponent()

        FillListBox()

    End Sub

    Private Sub FillListBox()

        mTechnologies.Add("HTML")

        mTechnologies.Add("Silverlight")

        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")

        mTechnologies.Add("XML")

        mTechnologies.Add("XAML")

    End Sub

    Private Sub MainPage_Loaded(sender As Object, e As RoutedEventArgs) Handles Me.Loaded

        BindListBox()

    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

        Else

            e.Accepted = False

        End If

    End Sub

    Private Sub txtSearchText_TextChanged(sender As Object, e As TextChangedEventArgs) Handles txtSearchText.TextChanged

        mTechnologiesCollectionSource.View.Refresh()

    End Sub

End Class

You can also refer following links to know more about CollectionViewSource

http://msdn.microsoft.com/en-us/library/system.windows.data.collectionview.aspx
http://msdn.microsoft.com/en-us/library/ms752348.aspx

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>

56.</Style>

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

05. 

06.    Public Sub New()

07.        Me.DefaultStyleKey = GetType(EditableButton)

08.        AddHandler Me.Loaded, New RoutedEventHandler(AddressOf EditableButton_Loaded)

09.    End Sub

10. 

11.    Private Sub EditableButton_Loaded(sender As Object, e As RoutedEventArgs)

12. 

13.        Me.OnApplyTemplate()

14.    End Sub

15. 

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

23. 

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

32. 

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))

35. 

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)

42. 

43.        itm = New MenuItem()

44.        itm.Header = "Cancel"

45.        AddHandler itm.Click, New RoutedEventHandler(AddressOf itmCancel_Click)

46.        menu.Items.Add(itm)

47. 

48.        ContextMenuService.SetContextMenu(mButtonTextBox, menu)

49.    End Sub

50. 

51. 

52. 

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)

59. 

60.        ContextMenuService.SetContextMenu(mButtonContentPresenter, menu)

61.    End Sub

62. 

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

68. 

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

75. 

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"

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

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

04.    xmlns:my="clr-namespace:WpfApplication1"

05.    Title="MainWindow" Height="350" Width="525">

06.   

07.    <Grid>

08.        <my:EditableButton x:Name="MyButton" Width="100" Height="25" Content="Edit Me"/>

09.    </Grid>

10.</Window>

Hope the above control helps you in your development.