Simple function to encode and decode UTF8 character for unicode (this is for IE)
Encode
utf8bytes= unescape(encodeURIComponent(unicodecharacters));
Decode
unicodecharacters= decodeURIComponent(escape(utf8bytes));
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);
}
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)
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
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
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
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
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
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
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