Fransiscus Setiawan | EV Charging & Azure Solution Architect | Sydney

Technical Insights: Azure, .NET, Dynamics 365 & EV Charging Architecture

Tag Replacement using Regex in .NET

This snippet of code shows how to do Synchronisation or HTML tag replacement

-My problem is i want to synchronise of tags between 2 HTML document

Issue

-Source.htm

my source.htm has this tag

<xml id=STATIC_CRITERIA_>

  <fields>

    <field fieldID=OPEN_FLAG displayType=checkboxGroup searchType=checkboxGroup underlyingType=int />

    <field fieldID=PARTITION displayType=dropdown searchType=profile profileID=SU_PARTITIONS underlyingType=int />

    <field fieldID=TEMPLATE_IND displayType=checkbox searchType=checkbox underlyingType=int />

    <field fieldID=INCLUDE_DELETED displayType=checkbox searchType=checkbox underlyingType=string />

  </fields>

</xml>

 -Target.htm has this tag

<xml id=STATIC_CRITERIA_>

</xml>

So now the problem is how do I fill the gap in XML tag in target.htm with the value from mySource.htm. We can do this using regex and it’s very simple

 

  string originalDocument = Load(“c:\\MySource.htm”);

                string syncDocument = Load(“c:\\Target.htm”);

 

                MatchCollection mc = Regex.Matches(originalDocument, “<xml([ ]?.*?)>(.*?)</xml>”, RegexOptions.Singleline | RegexOptions.IgnoreCase);

 

                foreach (Match m in mc)

                {

                    string token = “<xml{0}>”;

                    syncDocument = Regex.Replace(syncDocument, String.Format(token, m.Groups[1].Value) + “(.*?)</xml>”,

                                                               String.Format(token, m.Groups[1].Value) + m.Groups[2].Value + “</xml>”,

                                                               RegexOptions.Singleline | RegexOptions.IgnoreCase);

                }

 MatchCollection is used to return all the instances of that regular expression (e.g you might have multiple XML tags with different ID)

What does this tag means

“<xml([ ]?.*?)>(.*?)</xml>”

 ([ ]?.*) means that I don’t care whatever after it (e.g it can be ID or attributes etc). this is the first parameter that we stored on variable

(.*?) means that whatever inside the tag are captured into the second parameter that we stored on variable

You can access the first variable (e.g any attributes after the XML) by doing

m.Groups[1].Value

you can access the value inside the xml bracket by using

m.Groups[2].Value

so what  contains in

m.Groups[0].Value

it contains the whole xml tag that we extracted using regex

 then we can use regex.replace method to replace the tag in target html. When you replace the tag you need to replace the whole xml tag. You can’t just replace the inner part of it

   foreach (Match m in mc)

                {

                    string token = “<xml{0}>”;

                    syncDocument = Regex.Replace(syncDocument, String.Format(token, m.Groups[1].Value) + “(.*?)</xml>”,

                                                               String.Format(token, m.Groups[1].Value) + m.Groups[2].Value + “</xml>”,

                                                               RegexOptions.Singleline | RegexOptions.IgnoreCase);

                }

Recovering a corrupt iTunes DB on iphone

You need to explore the file in your iphone, you can use SSH or you can use this tool i-FunBox (it’s free at http://i-funbox.com/)

Please navigate to

/private/var/mobile/Media/iTunes_Control/iTunes

and rename folder iTunes_control to iTunes_control2 (or BAD)

When you connect to iTunes then it will ask you to configure your ipod then

I’ve renamed it, connect to iTunes and iTunes will recreate a new iTunes_Control database (hence I deleted iTunes_Control2 folder)

using BCP to transfer a large amount of data in SQL Server

I would like to transfer hundred millions of records between table in different database server. I tried to use DTS export/import and it’s not fast enough for my needs but i forget that there is a BCP command in SQL which is SQL Server format file.

The speed in my local machine is (22795.13 rows per sec)

Sample of the command: (You need to run it in command prompt), you can find bcp.exe in folder (C:\program files\Microsoft SQL Server\100\Tools\Binn) – The 100 is for SQL Server 2008, for SQL server 2005 it’s 90

Sample

1. You need to generate the BCP File from the source table

bcp MyDatabase.dbo.MyTable out C:\BCP_MyTable -n -S localhost -T -e[BCP_MyTable_ERROR]

*Using -T is for trusted connection

2. You need to import the BCP file to the destination table

bcp MyDatabase.dbo.MyTable in C:\BCP_MyTable -n -S localhost -T -e[BCP_MyTable_ERROR]

for further command line reference click here

*UPDATE:

you can configure your server to to reduce the amount of transaction log during bulk copy/insert transaction by executing

EXEC SP_DBOPTION MyDatabase, ‘SELECT INTO/BULKCOPY’, TRUE

bcp MyDatabase.dbo.MyTable in C:\BCP_MyTable /b 20000 -n -S localhost -T -e[BCP_MyTable_ERROR]

What I did was to add extra parameter of /b and the number 20000 (you should play around with the number to see the best one fit your situation) after it means the number of the records per transaction. Please be careful if you don’t put /b parameter, sql server by default will commit all the records at once. but if you put /b parameter then it will commit the transaction per x amount of records specified. If you transfer large data, you shouldn’t commit all at once because it will take sometime. In my case I transferred 18 millions of records

I’d recommend you to save the BCP file and copy the file over if it’s in different server because you need to be careful with the bandwidth of the network and the risk if the network connection is interrupted

Restore SQL Server database through network path

I had a problem where I need to restore database using shared folder on the network. I tried to copy it locally to my local harddrive but then the connection is not stable enough. Hence I decided to create a script

Single Media

USE MASTER
DECLARE @fromdatabase varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Multiple Media(Sometimes the backup source is stripped multiple files hence if you try to run it with above script you will get this error “The media set has 2 media families but only 1 are provided. All members must be provided.”)

USE MASTER
DECLARE @fromdatabase varchar(50)
DECLARE @fromdatabase2 varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
SET @fromdatabase2 = 'DailySupport-2'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ', DISK = N''' + @BackupPath + '' + @fromdatabase2 + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Enter button in TextBox ASP.NET

This article is demonstrating how to wire up an enter key into a textbox. For example you have a search text box where you press enter then it will click go button and at the same page you have another textbox where you want to do another button click when you press the enter which means it’ is not necessary to post the page. This java script is used to capture the key event of enter and execute the LinkButton and ASP.NET button Click Event on the server side. Please add this javascript to your javascript common library or add this to your master page. This piece of code works in Firefox as well


function ButtonKeyPress(evt, thisElementName)
{
    if(evt.which || evt.keyCode)
    {
        if ((evt.which == 13) || (evt.keyCode == 13))
        {
            // alert('post back href: ' +document.getElementById(thisElementName).href);

            if (typeof document.getElementById(thisElementName).href != 'undefined')
            {
                location = document.getElementById(thisElementName).href;
            }
            else
            {
                document.getElementById(thisElementName).click();
            }
            return false;
        }
    }
    else
    {
        return true;
    }
}

And add this to your .NET code behind on the page load

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
         If Not Page.IsPostBack Then


            If (User.Identity.IsAuthenticated) Then
                Response.Redirect("frmTimeMain.aspx")
            End If

            txtGUID.Attributes.Add("onkeydown", "ButtonKeyPress(event, '" + lnkSubmit.ClientID + "')")
            txtPassword.Attributes.Add("onkeydown", "ButtonKeyPress(event, '" + lnkSubmit.ClientID + "')")
          End If
    End Sub

ASP.NET Calendar selected day on click method

I’ve a case where I have an ASP.NET calendar that has a page load method that automatically select a date when it’s loaded. I also have an event defined for Selected_Changed which will be triggered only when the user select any other date other than pre-selected date on the load

Private Sub ctlCalendar_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ctlCalendar.SelectionChanged
        If (Not String.IsNullOrEmpty(CalendarPreviousPage)) Then
            BaseCalendar.SelectedDate = ctlCalendar.SelectedDate
            Response.Redirect(CalendarPreviousPage)
        End If
    End Sub

But how do I wire up an event or when the selecteddate being clicked?You can use DayRender event and attach it to a javascript in this case I want to go back to previous page. BaseCalendar.Selected date can be any date where you want to set up/wire up the logic

 Private Sub ctlCalendar_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles ctlCalendar.DayRender
        Dim d As CalendarDay
        Dim c As TableCell
        d = e.Day
        c = e.Cell

        If (BaseCalendar.SelectedDate.Value = d.Date) Then
            c.Attributes.Add("OnClick", "history.go(-1)")
        End If

    End Sub

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

e.g
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


  00003479
  Brodie's HomeSton
  NULL
  
  
  AU941170
  2010-07-22T09:42:20
  vcAuditorDesc,chUpdateStaffCode,dtUpdateDateTime,dtUpdateDateTime

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


  00003479
  Brodie's HomeSton
  NULL
  
  
  AU941170
  2010-07-22T09:42:20
  

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 @nvcSQL			NVARCHAR(MAX)
	DECLARE @pvcTableName	VARCHAR(255)
	DECLARE @pvcColumn		VARCHAR(255)
	DECLARE @pvcValue		NVARCHAR(MAX)

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

	DECLARE @dintPrimaryKey INT
	SELECT @dvcPrimaryKey = ISNULL(NAME,'') FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = @pvcTableName AND Is_Identity = 1


	-- Only execute when there is a @pvcValue.
	IF @pvcValue  ''
	BEGIN
		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)
	END

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

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)

        Try

            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()
        MyBase.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
            Return
        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)
        Next
        '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.

Page 10 of 19

Powered by WordPress & Theme by Anders Norén