705 logics: 2012

Step 1: Umbraco Site

Created blank umbraco site.
Created a clear XSLT file.

Step2: Creating solution

· Created a new project in visual web developer with an empty web application [XsltExtension.aspx].

XsltExtension.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="XsltExtension.aspx.cs" Inherits="XsltExtension.XsltExtension" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:Xml ID="Xml1" runat="server"></asp:Xml>

</div>

</form>

</body>

</html>

Add a class file to the project [student.cs].
In student.cs add a public class. [SqlXSLTExtension]
Declare a static function [GetDataSet()] with type XPathNodeIterator.
Here Create sql connection and select data from database.
Read the data in to a list.
Convert the data in the list to XML format using StringBuilder.
Create an XmlDocument and add this XML data to it.
Return the XmlDocument.

· StudentClass.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace XsltExtension

{

public class Student_Class

{

public int Student_ID { get; set; }

public string Student_Name { get; set; }

public DateTime Student_DOB { get; set; }

public string Student_Gender { get; set; }

public string Student_Address { get; set; }

}

· Student.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Xml.XPath;

using System.Data;

using System.Xml;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Configuration;

using System.Text;

using XsltExtension;

namespace XsltExtension

{

public class SqlXSLTExtension

{

public static XPathNodeIterator GetDataSet()

{

string connString = WebConfigurationManager.ConnectionStrings["connection"].ToString();

SqlConnection con = new SqlConnection(connString);

SqlCommand cmd1 = new SqlCommand("SELECT [Student_ID],[Student_Name],[Student_DOB],[Student_Gender],[Student_Address] FROM [school].[dbo].[Student] ", con);

con.Open();

SqlDataReader reader = cmd1.ExecuteReader();

List<Student_Class> students = new List<Student_Class>();

while (reader.Read())

{

students.Add(

new Student_Class()

{

Student_ID = Convert.ToInt32(reader["Student_ID"]),

Student_Name = reader["Student_Name"].ToString(),

Student_DOB = Convert.ToDateTime(reader["Student_DOB"]),

Student_Gender = reader["Student_Gender"].ToString(),

Student_Address = reader["Student_Address"].ToString(),

}

);

}

con.Close();

con.Dispose();

StringBuilder NewStringBuilder = new StringBuilder();

XmlDocument XMLDoc = new XmlDocument();

NewStringBuilder.Append("<?xml version=\"1.0\"?>");

NewStringBuilder.Append("<Students>");

foreach (Student_Class NewStudent in students)

{

NewStringBuilder.Append("<Students>");

NewStringBuilder.AppendFormat("<Student_ID>{0}</Student_ID>", NewStudent.Student_ID);

NewStringBuilder.AppendFormat("<Student_Name>{0}</Student_Name>", NewStudent.Student_Name);

NewStringBuilder.AppendFormat("<Student_DOB>{0}</Student_DOB>", NewStudent.Student_DOB);

NewStringBuilder.AppendFormat("<Student_Gender>{0}</Student_Gender>", NewStudent.Student_Gender);

NewStringBuilder.AppendFormat("<Student_Address>{0}</Student_Address>", NewStudent.Student_Address);

NewStringBuilder.Append("</Students>");

}

NewStringBuilder.Append("</Students>");

XmlDocument studentsXmlDoc = new XmlDocument();

studentsXmlDoc.LoadXml(NewStringBuilder.ToString());

return studentsXmlDoc.CreateNavigator().Select(".");

}

Compile the solution.
Go to C:\Users\arundas\Documents\Visual Studio 2010\Projects\XsltExtension\XsltExtension\bin and copy the .dll file [XsltExtension.dll].
paste it into bin of umbraco ( D:\umbraco_4711\build\bin).

Step3: Creating Xslt extensions in umbraco.

Go to D:\umbraco_4711\build\config
Open the xsltExtention file.
Add <ext assembly="<projectname>"type="<projectname>.<className>" alias="sql" ></ext> to it.

· Eg: <ext assembly="XsltExtension" type="XsltExtension.SqlXSLTExtension" alias="sql" >

</ext>

In the XSLT created ,call the method using alias name as prefix.

<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:param name="currentPage"/>

<xsl:template match="/">

<xsl:copy-of select="sql:GetDataSet()"/>

</xsl:template>

</xsl:stylesheet>

Add the connection string for data base connection in the .config file of umbraco.
Create a macro with the above Xslt extension.
Insert the macro into the template of our umbraco site.
Publish the site.
Open the site.