Write C# in-built function in XSLT

First we have to add namespaces, then write the code.

XSLT File :  (datetime.xslt)

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"  xmlns:myExtension="urn:myExtension"
                exclude-result-prefixes="msxsl myExtension">

  <xsl:output method="xml" indent="yes"/>

  <msxsl:script implements-prefix="myExtension" language="C#">
    <![CDATA[
     public string FormatDateTime(string xsdDateTime, string format)
      {
          DateTime date = DateTime.Parse(xsdDateTime);
          return date.ToString(format);
      }
     ]]>
  </msxsl:script>
  <xsl:template match="Date">
    <formattedDate>
      <xsl:value-of select="myExtension:FormatDateTime(self::node(), 'd')"/>
    </formattedDate>
  </xsl:template>
</xsl:stylesheet>


XML File:  (datetime.xml)

<?xml version="1.0" encoding="utf-8" ?>
<Date>2007-11-14T12:01:00</Date>


CALL on  program.aspx.cs page

 Xml1.DocumentSource = MapPath("datetime.xml");
 Xml1.TransformSource = MapPath("datetime.xslt");


OUTPUT:

11/14/2007

Call ASP.net functions from XSLT by passing object of the class as a parameter to the Xslt

In this blog, u will learn how to pass the reference (object) of the class to the xslt as a argument, to powered the xslt to use the functionalities of the aspx methods.

Here, we will use two methods 'Circumference' and 'FullName'
of class 'myClass' by passing their object reference 'obj' to the xslt.

Lets have a look..

cls.xml
-------------------

<?xml version="1.0" encoding="utf-8" ?>
<data>
  <circle>
    <radius>12</radius>
    <first>Ankit</first>
    <last>Jaiswal</last>
  </circle>
  <circle>
    <radius>5</radius>
    <first>prateek</first>
    <last>jaiswal</last>
  </circle>
</data>

cls.xslt
----------------------

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ankit="obj_reference">

  <xsl:template match="data">
    <circles>
      <xsl:for-each select="circle">
        <circle>
          <circumference>
            <xsl:value-of select="ankit:Circumference(radius)"/>
          </circumference>
          <FullName>
            <xsl:value-of select="ankit:FullName(first,last)"></xsl:value-of>
          </FullName>
        </circle>
      </xsl:for-each>
    </circles>
  </xsl:template>
</xsl:stylesheet>


program.aspx.cs
-----------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.Xml;
using System.Text;
using System.IO;
public partial class program: System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            Method();
        }
        catch (Exception ex)
        {
        }
    }
    private void Method()
    {
        XslTransform xslt = new XslTransform();
        xslt.Load(MapPath("cls.xslt"));
        XPathDocument doc = new XPathDocument(MapPath("cls.xml"));
        XsltArgumentList xslArg = new XsltArgumentList();
        myClass obj = new myClass();      // create object
        xslArg.AddExtensionObject("obj_reference", obj);
        StringBuilder htmlOutput = new StringBuilder();
        TextWriter htmlWriter = new StringWriter(htmlOutput);
        xslt.Transform(doc, xslArg, htmlWriter);
        string str = htmlOutput.ToString();               // get result in ''str"
    }
}
public class myClass
{
    private double circ = 0;
    public double Circumference(double radius)
    {
        circ = Math.PI * 2 * radius;
        return circ;
    }
    public string FullName(string FirstName, string LastName)
    {
        return FirstName + " " + LastName;
    }
}


OUTPUT:

<?xml version="1.0" encoding="utf-16"?>
<circles xmlns:ankit="obj_reference">
  <circle>
    <circumference>75.398223686155035</circumference>
    <FullName>Ankit Jaiswal</FullName>
  </circle>
  <circle>
    <circumference>31.415926535897931</circumference>
    <FullName>prateek jaiswal</FullName>
  </circle>
</circles>

Replicate function in SQL server

Replicate functions is used to replicate the string of given no. of times, it takes 2 arguments, first argument takes string and second argument is an integer defines no. of times to replicate string.

REPLICATE(arg1,arg2)

arg1=> string to repeat (ex. 'ankit')
arg2=> no. of times to repeat (ex. 5)

Query: 

SELECT REPLICATE('ankit',5)


output will be: ankitankitankitankitankit

SQL function to Remove all Special Characters except the characters which you wants.

Step 1: Write Script for function

CREATE FUNCTION [dbo].[RemoveSpecialChars] (@string VARCHAR(1000)) 
RETURNS varchar(1000) 
AS 
BEGIN 

DECLARE @validValues varchar(200)='@/,.[](/) ''{}'     -- enter characters which you wants
DECLARE @validTable table(validkeywords varchar(200))
DECLARE @ids INT=1
WHILE @ids<=DATALENGTH(@validValues)
BEGIN
INSERT INTO @validTable VALUES(LTRIM(RTRIM(ASCII(SUBSTRING(@validValues,@ids,1)))))
SET @ids=@ids+1
END

DECLARE @short varchar(100)
DECLARE @index INT =1
DECLARE @str varchar(1000)=''
WHILE @index <= DATALENGTH(@string) 
BEGIN 
       SET @short=ASCII(SUBSTRING(LOWER(@string), @index, 1))
       IF (@short BETWEEN 97 AND 122) OR (@short BETWEEN 48 AND 57)  OR (@short IN (SELECT DISTINCT * FROM @validTable))
       BEGIN 
       SET @str=@str+SUBSTRING(@string, @index, 1)
       END
       SET @index =@index+1
END 
RETURN @str
END

Step 2: Execute function to create it (press F5 to execute)

Step 3: After create the function,  pass string as a parameter to get result

select dbo.RemoveSpecialChars('sds{///}\||+-()*. dsdsSWE.s@3223')


Output will be: sds{///}(). dsdsSWE.s@3223

SQL function to Split the String by Delimiter and insert into a table variable

CREATE FUNCTION [dbo].[Split] (@String varchar(MAX), @Delimiter char(1))      
returns @temptable TABLE (items varchar(MAX))      
as      
begin     
    declare @idx int      
    declare @slice varchar(max)      

    select @idx = 1      
        if len(@String)<1 or @String is null  return      

    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      

        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      

        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return
end;






































USE THIS FUNCTION HERE:

SET @Values=(select top 1 ALLkeywords from @temp)

SELECT LTRIM(RTRIM(items)) FROM [dbo].[Split] (@Values, ',') 

@Values contains the keywords with commas