On my last article we are talking about replacing special character for XML document which was working great but our testing team able to break it again. The single and double quote character are different from NOTEPAD to Microsoft Word .Here is how it looks and it's decimal/Hex value
| SINGLE QUOTE |
| NOTEPAD | Microsoft WORD |
| OPENING | CLOSING | OPENING | CLOSING |
| ' DEC ' HEX ' | ' DEC ' HEX ' | ‘ DEC ‘ HEX ‘ | ’ DEC ’ HEX ’ |
| DOUBLE QUOTE |
| NOTEPAD | Microsoft WORD |
| OPENING | CLOSING | OPENING | CLOSING |
| " DEC " HEX " | " DEC " HEX " | “ DEC “ HEX “ | ” DEC ” HEX ” |
Again, How do you replace in C# code ?
The best way it to write a Regular Expression but for quick solution use this code
newString = newString.Replace("'","'");
//" Double Quote Word oepning
newString = newString.Replace(""","“");
//" Double Quote Word Closing
newString = newString.Replace(""","”");
//‘ Single Quote Word oepning
newString = newString.Replace("‘","‘");
//’ Single Quote Word Closing
newString = newString.Replace("’","’");
How do you write or produce C# code for Microsoft Word Single Quote like this ‘ (not like ')?
Well, To be frank I did not found any keyboard letter to write this character ;) Use the traditional method like copy the character from Microsoft Word and paste in VS .Net C# editor. Just like above and this works for other characters too.
For example :
The string conversation might look like this
Before :
This’s testing for “Double quote from MS word” and ‘single quote from MS Word’ 'NOTEPAD' \"NOTEPAD\"
After :
This’s testing for “Double quote from MS word” and ‘single quote from MS Word’ 'NOTEPAD' \"NOTEPAD\"
Here is the link to convert character
Hope you enjoy this coding..Keep tune lots more fun part is coming up :)
Suresh Behera
Syntactical differences between Java and C#.
Syntactical differences between VB.NETand C#.
More reading
http://www.harding.edu/USER/fmccown/WWW/vbnet_csharp_comparison.html
Suresh Behera
I was having hard time to filer form input from a asp.net page. This page accept text as input and send to database in terms of xml formats.
All are ok when the text doest not include any of the following character. It gone for toss when you use these charactes
| Character Name | Entity Reference | Character Reference | Numeric Reference |
| Ampersand | & | & | & |
| Left angle bracket | < | < | &#60; |
| Right angle bracket | > | > | > |
| Straight quotation mark | " | " | ' |
| Apostrophe | ' | ' | " |
Microsoft has one suport article but it does not give sufficent help for this problem.
How to locate and replace special characters in an XML file with Visual C# .NET
I tried different way to workaround this and I could be following any of these
1 - You simple filter the text using c# or VB.Net Replace method
Like
// >
newString = newString.Replace(">",">");
// <
newString = newString.Replace("<","<");
//&
newString = newString.Replace("&","&");
//Double Quote " –This does not work here…
// newString = newString.Replace(ControlChars.Quote,""");
Here C# does not have any class called ControlChars
// newString = newString.Replace(CHR(32),""");
Replace method accept either both character or both string not one character and another string. So this also does not work.
//Single Quote '
newText = newText.Replace("'","'");
2. You can use Regular expression to filter all you special character.It might be something like this @"a1/}{]yryr23dsdhds%$#yytr^&uut887611oiuif():><?jfhgg";
3. You can write stored procedure to replace all these special characters.
Regular expression was good choice but it was killing lots of time to make the expression. I am not so good on regular expression or might not be wanted to waste much time for this simple problem. If somebody could write the expression it would be great and helpful for others
How To Locate and Replace Special Characters in an XML Document with Visual Basic
Some extra reading
XML Syntax and Parsing Concepts
Manipulating Strings in C#
Happy Coding
Suresh Behera
All list of Unicode with PDF download document. This is great help for Unicode developer. The Unicode Character Code Charts By Script
Thank you for all of your comments. I modified the links. I was using desktop client for first time so made mistake ;)
keep tuning lots of things are coming up..
Suresh Behera
This is awsome query.It saved my lots of time but again i got another error message " Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" .
Cascade Delete
CREATE Procedure spDeleteRows
/*
Recursive row delete procedure.
It deletes all rows in the table specified that conform to the criteria selected,
while also deleting any child/grandchild records and so on. This is designed to do the
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
table to find any child tables, then deletes the soon-to-be orphan records from them using
recursive calls to this procedure. Once all child records are gone, the rows are deleted
from the selected table. It is designed at this time to be run at the command line. It could
also be used in code, but the printed output will not be available.
*/
(
@cTableName varchar(50), /* name of the table where rows are to be deleted */
@cCriteria nvarchar(1000), /* criteria used to delete the rows required */
@iRowsAffected int OUTPUT /* number of records affected by the delete */
)
As
set nocount on
declare @cTab varchar(255), /* name of the child table */
@cCol varchar(255), /* name of the linking field on the child table */
@cRefTab varchar(255), /* name of the parent table */
@cRefCol varchar(255), /* name of the linking field in the parent table */
@cFKName varchar(255), /* name of the foreign key */
@cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
@cChildCriteria nvarchar(1000), /* criteria to be used to delete
records from the child table */
@iChildRows int /* number of rows deleted from the child table */
/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
SC1.name AS Col,
SO2.name AS RefTab,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
BEGIN
/* build the criteria to delete rows from the child table. As it uses the
criteria passed to this procedure, it gets progressively larger with
recursive calls */
SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
@cRefTab +'] WHERE ' + @cCriteria + ')'
print 'Deleting records from table ' + @cTab
/* call this procedure to delete the child rows */
EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
For more Information...
http://www.sqlteam.com/item.asp?ItemID=8595
Exceeding The Max Nest Level
http://www.sqlservercentral.com/columnists/rmarda/nestingstoredprocedures.asp
Cheers...
Suresh Behera
Build complex regular expressions by selecting components from a palette
Test expressions against real or sample input data
Display all matches in a tree structure, showing captured groups, and all captures within a group
Build replacement strings and test the match and replace functionality
Highlight matched text in the input data
Test automatically for syntax errors
Generate Visual Basic or C# code
Save and restore data in a project file
Maintain and expand a library of frequently used regular expressions
Here are some of the additional features available in Expresso 2.x
-
Expresso Analyzer interprets and diagrams a regular expression to aid in understanding and debugging
-
Analyzer produces an English description of the expression that can be automatically incorporated into comments in the generated code
-
Improvements to the user interface
-
New "Partial Match" and "Exclude Match" enable testing selected portions of a regular expression to aid in debugging
-
Code generation now supports Managed C++
-
More extensive options for customizing the generated code
-
Multithreaded operation allows interruption of time-consuming matches
-
Performance tester for timing optimization
-
Improvements and added features in the Expression Builder
-
More extensive Help features
-
Built-in regular expression tutorial
-
Improvements to the Expression Library
http://www.ultrapico.com/Expresso.htm
Suresh Behera