SQL Query to create Parameters for Stored Procedure

QUERY :

SELECT +'@'+COLUMN_NAME+' '+DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH as varchar)+')'+',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='yourTableName'


Table contains the columns must replace the 'YourTableName' in above query.

This Query Concatenates the Column Name , Datatype and size with the hard coded symbols like '@' ',' ('  and ')'

This Gives the Output in table form, You can copy amd paste it in stored procedure.


OUTPUT:

@ItemId nvarchar(100),
@GlobalId nvarchar(100),
@ProductId nvarchar(100),
@CategoryId nvarchar(100),
@CategoryName varchar(200),
@Location varchar(200),
@Title varchar(500),
@Price nvarchar(200),
@SellingState varchar(100),
@TimeLeft nvarchar(100),
@ShippingType varchar(200),
@Currency varchar(100),
@ShippingServiceCost nvarchar(200),
@ShipToLocations varchar(200),
@OneDayShippingAvail varchar(50),
@ReturnAccept varchar(50),
@ItemUrl nvarchar(200),
@ListingType varchar(200),
@BidCount varchar(200),
@BuyItNowAvail varchar(50),
@BuyItNowPrice nvarchar(200),
@BestOfferEnable varchar(50),
@GalleryUrl varchar(100),
@PaymentMode varchar(200),

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>

Pass xml as a array type parameter to the xslt.

 Method
 -----------------------
public void one1()
    {
        StringWriter sw = new StringWriter();
        XmlTextWriter w = new XmlTextWriter(sw);
        w.WriteStartElement("root");
        for (int i = 0; i < 10; i++)
            w.WriteElementString("val", "value" + i);
        w.WriteEndElement();
        w.Close();
        //XmlNodeWriter should be used instead of temporary string
        XPathDocument doc = new XPathDocument(new StringReader(sw.ToString()));
        XPathNavigator nav = doc.CreateNavigator();
        XsltArgumentList args = new XsltArgumentList();
        args.AddParam("array", "", nav.Select("/root/*"));

        XPathDocument input = new XPathDocument(MapPath("xmls//xml1.xml"));
        XslTransform xslt = new XslTransform();
        xslt.Load(MapPath("xslts//array1.xslt"));
        StringBuilder htmlOutput = new StringBuilder();
        TextWriter htmlWriter = new StringWriter(htmlOutput);
        xslt.Transform(input, args, htmlWriter);
        string str = htmlOutput.ToString();
    }


xml1.xml
---------------

<?xml version="1.0" encoding="utf-8" ?>
<Users>
  <User Id="1">
    <Name>Prateek</Name>
    <City>Bareilly</City>
  </User>
  <User Id="2">
    <Name>Ankit</Name>
    <City>Delhi</City>
  </User>
  <User Id="3">
    <Name>Navin</Name>
    <City>Gorakhpur</City>
  </User>
  <User Id="4">
    <Name>Ankit</Name>
    <City>Lucknow</City>
  </User>
</Users>



array1.xslt
----------------------

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" indent="yes"/>
  <xsl:param name="array"/>
  <xsl:template match="/">
      <xsl:for-each select="$array">
        <xsl:copy-of select="$array"/>
        <!--<xsl:copy-of select="$array[2]"/>-->
      </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>