Setting the Silverlight (Telerik) RadGrid Culture

I have this date column and it has DateConverter that i created to apply the current thread culture. The problem that I have is
-On load it display the date format perfectly based on the current culture
-The problem is when i double click the cell/edit mode then it applies different format but then when i exit edit mode then it shows the right formatting

on load the cell display 07/08/2010 (07th August 2010) but when I go to edit mode it shows as 08/07/2010 (08th July 2010) but again when I finish editing then it shows 07/08/2010

so basically my current thread date format is dd/mm/yyyy but in edit mode it applies the us formatting which is mm/dd/yyyy. Finally I found out how to set the grid culture

Dim _LocaleLanguage as String = .String.Empty

_LocaleLanguage = CultureInfo.CreateSpecificCulture(System.Web.HttpContext.Current.Request.UserLanguages(0)).Name.ToString()

dgResults.Language = Windows.Markup.XmlLanguage.GetLanguage(_LocaleLanguage)

Viola!! and it solves my culture problem

Read and Update XML Node of XML datatype in SQL Server

Select Node value

This is a simple explanation on how to select a particular node from xml data type of a record in SQL server.

Sample XML

  Brodie's HomeSton

So what I want to do is basically to get the value of ChangedColumns node

select  xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
from tblReferenceHistory
WHERE intReferenceHistoryID = 125

Update Existing Node value

Sample XML

  Brodie's HomeSton

Changed column has no value in it and you want to update it. Basically you can’t edit the existing node value so what you need to do is to readd it and remove the first instance of that node

DECLARE @ChangedColumns VARCHAR(255)

SELECT @ChangedColumns = xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
FROM tblReferenceHistory
WHERE intReferenceHistoryID = 125

-- first update - add a new ... node
UPDATE tblReferenceHistory
SET xmlDetail.modify('insert {sql:variable("@ChangedColumns")} as last into (/iReference)[1]')
WHERE intReferenceHistoryID = 124

-- second update - remove the empty  node
UPDATE dbo.tblReferenceHistory
SET xmlDetail.modify('delete (/iReference[1]/ChangedColumns[1])[1]')
WHERE intReferenceHistoryID = 124

SELECT xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
FROM tblReferenceHistory
WHERE intReferenceHistoryID = 124

How to set variable from Dynamic SQL

Here is the scenario, You have a stored procedure that builds dynamic insert SQL which means the “INSERT” statement is being composed on the fly based on the table name but at the same time you need to get SCOPE_IDENTITY or the last identity number inserted for further processing (e.g History table)

	DECLARE @pvcTableName	VARCHAR(255)
	DECLARE @pvcColumn		VARCHAR(255)

	--This is used to actually get the primary key for identity record
	DECLARE @dvcPrimaryKey VARCHAR(255)
	SET @dvcPrimaryKey = ''

	DECLARE @dintPrimaryKey INT

	-- Only execute when there is a @pvcValue.
	IF @pvcValue  ''
		SELECT @nvcSQL = 'INSERT INTO ' + @pvcTableName + '(' + @pvcColumn+ ') VALUES (' + @pvcValue + ')'
		SELECT @nvcSQL = @nvcSQL + ';' + 'SELECT @dintPrimaryKey = SCOPE_IDENTITY()'
		--PRINT @nvcSQL

		EXEC sp_executesql @query = @nvcSQL, @params = N'@dintPrimaryKey INT OUTPUT', @dintPrimaryKey = @dintPrimaryKey OUTPUT
		--EXEC (@nvcSQL)

	IF @dvcPrimaryKey  ''
		SELECT (@dvcPrimaryKey + ' = ' + CAST(@dintPrimaryKey AS VARCHAR(10)))  AS PrimaryKey
		SELECT '' AS PrimaryKey

Question: why do we need to use sp_executesql instead of EXEC?because EXEC just execute a SQL command without returning/expecting any result from it

Telerik SL Gridview Cell Foreground color dynamically based on multiple binding

I had a problem before where I build dynamic system (light dataset) for doing CRUD (Create, Read, Update and Delete) and I need to store the history of previous record before Update/Delete and at the same time I also need to indicate the column(e.g changing the color of the column) that has been changed per record in history table. I’ve been struggling for a day in order to accomplish this.

1. You need to add reference and import System.Windows.Interactivity from SL3 SDK
2. Create a TableHistoryColorConverter.vb

Imports System.Windows.Data
Imports System.Globalization
Imports System.Reflection

Public Class TableHistoryColorConverter
    Implements IValueConverter

    Public Function Convert(ByVal value As Object, ByVal targetType As System.Type, ByVal parameter As Object, ByVal culture As System.Globalization.CultureInfo) As Object Implements System.Windows.Data.IValueConverter.Convert
        Dim cellColor As SolidColorBrush = New SolidColorBrush(Colors.Black)


            Dim columnList As String = value.ToString()
            Dim currentColumnName As String = parameter.ToString()
            'Dim changeColumnParam As String = parameter.ToString()
            Dim changedColumnList As String() = columnList.Split(New Char() {","c})

            If changedColumnList.Contains(currentColumnName) Then
                cellColor = New SolidColorBrush(Colors.Red)
            End If

        Catch ex As Exception

        End Try

        Return cellColor
    End Function

    Public Function ConvertBack(ByVal value As Object, ByVal targetType As System.Type, ByVal parameter As Object, ByVal culture As System.Globalization.CultureInfo) As Object Implements System.Windows.Data.IValueConverter.ConvertBack
        Throw New Exception("Not Implemented")
    End Function
End Class

3. Create a vb class file called HistoryRecordFormatting.vb

Imports System.Windows.Input
Imports System.Windows.Media
Imports System.Windows.Media.Animation
Imports System.Windows.Shapes
Imports System.Windows.Interactivity
Imports Telerik.Windows.Controls
Imports Telerik.Windows.Controls.GridView
Imports System.Windows.Data
Imports SLCoreLib

Public Class HistoryRecordFormatting
    Inherits Behavior(Of RadGridView)

    Protected Overrides Sub OnAttached()
        AddHandler AssociatedObject.RowLoaded, New EventHandler(Of Telerik.Windows.Controls.GridView.RowLoadedEventArgs)(AddressOf AssociatedObject_RowLoaded)
    End Sub

    Private Sub AssociatedObject_RowLoaded(ByVal sender As Object, ByVal e As Telerik.Windows.Controls.GridView.RowLoadedEventArgs)
        If (TypeOf e.Row Is GridViewHeaderRow) OrElse (TypeOf e.Row Is GridViewFooterRow) OrElse (TypeOf e.Row Is GridViewNewRow) Then
        End If

        For Each cell In e.Row.Cells
            'we want to apply logic to data rows only
            'ChangedColumns is the list of the column that has been edited (comma separated value) for that history record
            Dim colorBinding As New Binding("ChangedColumns") With { _
                .Converter = New TableHistoryColorConverter(), .ConverterParameter = cell.Column.UniqueName _

            cell.SetBinding(GridViewCell.ForegroundProperty, colorBinding)
        'e.Row.Cells(1).SetBinding(GridViewCell.ForegroundProperty, colorBinding)
        'e.Row.Cells[1].SetBinding(GridViewCell.BackgroundProperty, colorBinding);
    End Sub
End Class

4. Add behavior in your XAML (Interaction.Behaviors) tag and HistoryRecordFormatting.


Basically what it does is adding the converter on every single cell and use the column name to compare with the changed column. It uses Column.UniqueName to pass it to the ConverterParameter and check whether it’s on the list of changed column or not. The problem looks simple at the beginning but when it comes to silverlight then it’s different mechanism of applying the format compared with ASP.NET

The behavior and AssociatedObject_RowLoaded for me looks like itemdatabound event on repeater.