C#.Net- How to Convert SQL to XML String and render output using XslCompiledTransform in Asp.net

Here I will explain how to convert datatable to xml string in asp.net using C#

Code for faching data from sql database

using (SqlConnection objcon = new SqlConnection(ConfigurationManager.ConnectionStrings[“TestDB”].ConnectionString))
SqlDataAdapter da = new SqlDataAdapter(“select * from Emp_Info”, objcon);
DataTable dt = new DataTable(“empInfo”);

using (TextWriter writer = new StringWriter())
Response.Write(new XMLHelper().GetValue(@”C:\Tushar\SQLToXMLExample\XSLT\Demo.xslt”, writer.ToString()));
XMLHelper that generate Output string using XSLT file and XML String

public class XMLHelper
public XMLHelper()
{ }
public string GetValue(string templatePath, string xmlString)
XDocument xmlObj = XDocument.Parse(xmlString);
XDocument result = GetResultXml(templatePath, xmlObj);

return (result == null) ? string.Empty : result.Document.ToString();
public XDocument GetResultXml(string templatePath, XDocument xmlObj)
XDocument result = new XDocument();
using (XmlWriter writer = result.CreateWriter())
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Transform(xmlObj.CreateReader(), writer);
return result;

XSLT File — Sample Demo

<?xml version=”1.0″ encoding=”utf-8″?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221;
xmlns:msxsl=”urn:schemas-microsoft-com:xslt” exclude-result-prefixes=”msxsl”
<xsl:output method=”html” omit-xml-declaration=”yes” indent=”yes”/>

<xsl:template match=”@* | node()”>
<xsl:for-each select=”/*/node()”>
<xsl:if test=”position()=1″>
<xsl:for-each select=”*”>
<xsl:value-of select=”local-name()”/>
<xsl:for-each select=”*”>
<xsl:for-each select=”*”>
<xsl:value-of select=”.”/>


Inherit Textbox control -c#

how to create a class which inherits textBox control in c# . the new class contains a new property like “Windowtype” and more.

[CustomAttributes(Browsable = true)]
public class Textbox_tushar : System.Web.UI.WebControls.TextBox

        public enum Size

        [CustomAttributes(Browsable = true)]
        public override string ID
                return base.ID;
                base.ID = value;

        private Size _size;
        public Size WindowType
            get { return _size; }
            set { _size = value; }

        protected override void CreateChildControls()
        protected override void OnTextChanged(EventArgs e)


C# Partial

Partial classes span multiple files. How can you use the partial modifier on a C# class declaration? With partial, you can physically separate a class into multiple files. This is often done by code generators.



With normal C# classes, you cannot declare a class in two separate files in the same project. But with the partial modifier, you can. This is useful if one file is commonly edited and the other is machine-generated or rarely edited.

Program that uses partial class: C#

class Program
    static void Main()

Contents of file A1.cs: C#

using System;

partial class A
    public static void A1()

Contents of file A2.cs: C#

using System;

partial class A
    public static void A2()



Partial is required here. If you remove the partial modifier, you will get an error containing this text: [The namespace ‘<global namespace>’ already contains a definition for ‘A’].


Tip: To fix this, you can either use the partial keyword, or change one of the class names.



How does the C# compiler deal with partial classes? If you disassemble the above program, you will see that the files A1.cs and A2.cs are eliminated. You will find that the class A is present.

IL Disassembler

So: Class A will contain the methods A1 and A2 in the same code block. The two classes were merged into one.


Tip: Partial classes are precisely equivalent to a single class with all the members.

Compiled result of A1.cs and A2.cs: C#

internal class A
    // Methods
    public static void A1()

    public static void A2()

Clustered and Non-Clustered Index in SQL 2005

1. Introduction

We all know that data entered in the tables are persisted in the physical drive in the form of database files. Think about a table, say Customer (For any leading bank India), that has around 16 million records. When we try to retrieve records for two or three customers based on their customer id, all 16 million records are taken and comparison is made to get a match on the supplied customer ids. Think about how much time that will take if it is a web application and there are 25 to 30 customers that want to access their data through internet. Does the database server do 16 million x 30 searches? The answer is no because all modern databases use the concept of index.

2. What is an Index

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations.

In this article, we will see creating the Index. The below two sections are taken from my previous article as it is required here. If your database has changes for the next two sections, you can directly go to section 5.

3. First Create Two Tables

To explain these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window, then execute it.

Collapse | Copy Code
CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);

Note that there are no constraints at present on these tables. We will add the constraints one by one.

4. Primary Key Constraint

A table column with this constraint is called as the key column for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of the Student table as primary key. Follow these steps:

  1. Right click the student table and click on the modify button.
  2. From the displayed layout, select the StudId row by clicking the Small Square like button on the left side of the row.
  3. Click on the Set Primary Key toolbar button to set the StudId column as primary key column.


Now this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate on the primary key column. Then, the uniqueness is considered among all the participant columns by combining their values.

5. Clustered Index

The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand the Indexes. You will see the following index created for you when the primary key is created:


With the use of the binary tree, now the search for the student based on the studid decreases the number of comparisons to a large amount. Let us assume that you had entered the following data in the table student:


The index will form the below specified binary tree. Note that for a given parent, there are only one or two Childs. The left side will always have a lesser value and the right side will always have a greater value when compared to parent. The tree can be constructed in the reverse way also. That is, left side higher and right side lower.


Now let us assume that we had written a query like below:

Collapse | Copy Code
Select * from student where studid = 103;
Select * from student where studid = 107;

Execution without index will return value for the first query after third comparison.
Execution without index will return value for the second query at eights comparison.

Execution of first query with index will return value at first comparison.
Execution of second query with index will return the value at the third comparison. Look below:

  1. Compare 107 vs 103 : Move to right node
  2. Compare 107 vs 106 : Move to right node
  3. Compare 107 vs 107 : Matched, return the record

If numbers of records are less, you cannot see a different one. Now apply this technique with a Yahoo email user accounts stored in a table called say YahooLogin. Let us assume there are 33 million users around the world that have Yahoo email id and that is stored in the YahooLogin. When a user logs in by giving the user name and password, the comparison required is 1 to 25, with the binary tree that is clustered index.

Look at the above picture and guess yourself how fast you will reach into the level 25. Without Clustered index, the comparison required is 1 to 33 millions.

Got the usage of Clustered index? Let us move to Non-Clustered index.

6. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, AccountType column of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.

Follow the steps below to create a Non-clustered index on our table Student based on the column class.

  1. After expanding the Student table, right click on the Indexes. And click on the New Index.Pic05.jpg
  2. From the displayed dialog, type the index name as shown below and then click on the Add button to select the column(s) that participate in the index. Make sure the Index type is Non-Clustered.Pic06.jpg
  3. In the select column dialog, place a check mark for the column class. This tells that we need a non-clustered index for the column Student.Class. You can also combine more than one column to create the Index. Once the column is selected, click on the OK button. You will return the dialog shown above with the selected column marked in blue. Our index has only one column. If you selected more than one column, using the MoveUp and MoveDown button, you can change order of the indexed columns. When you are using the combination of columns, always use the highly repeated column first and more unique columns down in the list. For example, let use assume the correct order for creating the Non-clustered index is: Class, DateOfBirth, PlaceOfBirth.Pic07.jpg
  4. Click on the Index folder on the right side and you will see the non-clustered index based on the column class is created for you.Pic08.jpg

7. How Does a Non-Clustered Index Work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column on the database.

This can be easily explained with the concept of a book and its index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says all about C#. When you glanced at the book, it has all beautiful color pages and shiny papers. But, that is not only the eligibility for a good book right? One you are impressed, you want to see your favorite topic of Regular Expressions and how it is explained in the book. What will you do? I just peeped at you from behind and recorded what you did as below:

  1. You went to the Index page (it has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on page Number 17.
  2. Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.
  3. Your first target is Page 407. You opened a page in the middle, the page is greater than 500.
  4. Then you moved to a somewhat lower page. But it still reads 310.
  5. Then you moved to a higher page. You are very lucky you exactly got page 407. [Yes man you got it. Otherwise I need to write more. OK?]
  6. That’s all, you started exploring what is written about Regular expression on that page, keeping in mind that you need to find page 816 also.

In the above scenario, the Index page is Non-Clustered index and the page numbers are clustered index arranged in a binary tree. See how you came to the page 407 very quickly. Your mind actually traversed the binary tree way left and right to reach the page 407 quickly.

Here, the class column with distinct values 1,2,3..12 will store the clustered index columns value along with it. Say for example; Let us take only class value of 1. The Index goes like this:

wpf treeview custom template round corners Seletion


<Window x:Class=”TreeViewExample.MainWindow”

Title=”MainWindow” Height=”350″ Width=”525″ Loaded=”Window_Loaded”>

<Style TargetType=”TreeViewItem”>
<LinearGradientBrush x:Key=”ItemAreaBrush” StartPoint=”0.5, 0″ EndPoint=”0.5, 1″>
<GradientStop Color=”#66000000″ Offset=”0″ />
<GradientStop Color=”#22000000″ Offset=”1″ />
<LinearGradientBrush x:Key=”SelectedItemAreaBrush” StartPoint=”0.5, 0″ EndPoint=”0.5, 1″>
<GradientStop Color=”Orange” Offset=”0″ />
<GradientStop Color=”OrangeRed” Offset=”1″ />
<LinearGradientBrush x:Key=”ItemBorderBrush” StartPoint=”0.5, 0″ EndPoint=”0.5, 1″>
<GradientStop Color=”LightGray” Offset=”0″ />
<GradientStop Color=”Gray” Offset=”1″ />
<LinearGradientBrush x:Key=”SelectedItemBorderBrush” StartPoint=”0.5, 0″ EndPoint=”0.5, 1″>
<GradientStop Color=”Yellow” Offset=”0″ />
<GradientStop Color=”Black” Offset=”1″ />
<DropShadowBitmapEffect x:Key=”DropShadowEffect” />
<Setter Property=”Template”>
<ControlTemplate TargetType=”TreeViewItem”>
<Grid Margin=”5″>
<RowDefinition Height=”*”></RowDefinition>

<!– This Border and ContentPresenter displays the content of the TreeViewItem. –>
<Border Name=”Bd”
Background=”{StaticResource ItemAreaBrush}”
BorderBrush=”{StaticResource ItemBorderBrush}”
<ColumnDefinition Width=”30″ />
<ColumnDefinition Width=”100*” />
<Image Grid.Column=”0″ Source=”Image/ChangeUserIcon.png” Width=”20″ Height=”20″ Margin=”5,0,0,0″></Image>
<ContentPresenter Grid.Column=”1″ Name=”PART_Header”
VerticalAlignment=”Center” />

<!–When the item is selected in the TreeView, use the
“selected” colors and give it a drop shadow. –>
<Trigger Property=”IsSelected” Value=”True”>
Value=”{StaticResource SelectedItemAreaBrush}” />
Value=”{StaticResource SelectedItemBorderBrush}” />
{x:Static SystemColors.HighlightTextBrushKey}}” />
Value=”{StaticResource DropShadowEffect}” />

<!– Make each TreeViewItem show it’s children
in a horizontal StackPanel. –>
<!–<Setter Property=”ItemsPanel”>
Orientation=”Vertical”  />

<LinearGradientBrush  x:Key=”RibbonGroupBorderStyle” EndPoint=”0.5,1″ StartPoint=”0.5,0″>
<GradientStop Color=”#FF6590C1″ Offset=”0″ />
<GradientStop Color=”#FF6590C1″ Offset=”1″ />
<TreeView Name=”tree”>


private void Window_Loaded(object sender, RoutedEventArgs e)
TreeViewItem objTreeViewItem = new TreeViewItem();
objTreeViewItem.Header = “Hello”;
objTreeViewItem.Name = “Item1”;
TreeViewItem objTreeViewItem1 = new TreeViewItem();
objTreeViewItem1.Header = “Hello2”;
objTreeViewItem1.Name = “Item2”;

INI Files for Windows Application (http://www.codeproject.com/Articles/20120/INI-Files)

Example INI File.
; for 16-bit app support [fonts] [extensions] [mci extensions] [files] [Mail] MAPI=1 CMC=1 CMCDLLNAME32=mapi32.dll CMCDLLNAME=mapi.dll MAPIX=1 MAPIXVER= OLEMessaging=1 [MCI Extensions.BAK] aif=MPEGVideo aifc=MPEGVideo aiff=MPEGVideo asf=MPEGVideo asx=MPEGVideo au=MPEGVideo m1v=MPEGVideo ...

(My win.ini)


It is strange to write a parser of INI files each time it is needed. Why not use an existing one, especially if it is good? I give you a class library that has the following features:

  • Parses INI files using given format directions.
  • Edits them easily using one single class.
  • Automatically finds out a formatting convention.
  • Adds commentaries to the sections and values just by setting a property Comment.
  • Writes a file to the disc, preserving its original format.
  • Enjoys richly documented code.


INI [=initialization] files are used to store settings that define the start-up state of some programs. Alternatively, they can just contain configuration data. Currently they can be found in special folders (desktop.ini) like “My Documents.” A lot of them exist in the Windows root folder, e.g. C:\Windows\win.ini. In this article, I will use the following terminology:

 Collapse | Copy Code
[Abcd]  <- "Section"
Number=188  <- "Key" (vel "Value name") and "Value"

Using the Code

I will present three main usage guides of my library: User-Mode, Hardcore-Mode and Light-Mode.


The IniFile namespace contains one class: IniFile. It represents an object model of an INI file, which stores a whole structure in memory. Usage of IniFile is extremely simple.

 Collapse | Copy Code
IniFile file = new IniFile();
// Adds a new section with one value

file["LastUser"]["Name"] = "Gajatko";
// Save to disc


This will give the following output:

 Collapse | Copy Code

The indexer of IniFile returns an object that also has an indexer. However, it also has many other features. For example, it has a Comment property:

 Collapse | Copy Code
file["LastUser"].Comment =
@"This section contains information about user
which logged in at previous program run";
file["LastUser"].SetComment("Name", "Name of user");


 Collapse | Copy Code
;This section contains information about user
;which has logged in at previous program run
;Name of user

We can freely manipulate values and sections:

 Collapse | Copy Code
// Rename sections and keys

file["LastUser"].Name = "RecentUser";
file["RecentUser"].RenameKey("Name", "Login");
// Get names of all section:

string[] sections = file.GetSectionNames();
// Set existing values:

file["RecentUser"]["Login"] = "Chuck";
// Override existing comment:

file["RecentUser"].Comment = "New Comment";


 Collapse | Copy Code
;New Comment
;Name of user

Also, inline comments are supported:

 Collapse | Copy Code
file["RecentUser"].InlineComment = "An inline thing";


 Collapse | Copy Code
[RecentUser]   ;An inline thing

We can save all changes to a file and read from it, as well:

 Collapse | Copy Code
file = IniFile.FromFile("test.txt");

More methods and properties will appear in the auto-completion service in Visual Studio. They are all commented using Summary blocks. Files edited with the IniFile class will preserve their formatting.

Concluding: the User-Mode is cool, isn’t it?



Actually, this library contains 10 classes. The first two are implementations of StreamReader and StreamWriter:

  • IniFileReader – Reads and parses a text file.
  • IniFileWriter – Writes INI file elements to a disc.

The second group of classes is used by User-Mode:

  • IniFile – Contains the whole INI file in memory and enables easy access to it.
  • IniFileSection – Represents a section and its values.

An INI file’s Elements are the core objects of the IniFiles namespace. Usually they represent single lines of a file, but sometimes more.

  • IniFileSectionStart – Represents a beginning line of a section, like “[LastUser]”.
  • IniFileValue – Represents a single line with a value, like “Name=Gajatko”.
  • IniFileBlankLine – Represents one or more blank lines. The actual number of them is specified in theAmount property.
  • IniFileComment – Represents one or more commentary lines.

The class IniFileElement is a base class for them all. It has standard properties that are used by Reader andWriter:

  • Line – An actual representation of the object in the INI file. The name is a little confusing because sometimes the Line contains more than one line.
  • Content – Similar to Line, but without indentation.
  • Indentation – A string of all white spaces (tabs, spaces) preceding a meaningful content.
  • Formatting – A formatting string specific to a particular type of INI file element.

IniFileSettigs is a static class that contains all format settings used by other parts of the library. Sample settings are:

  • PreserveFormatting – Default true. If set to false, the Format() method will be called for all INI file elements while writing a file. The demo shows how it works.
  • CommentChars – Array of strings that begin a comment line, by default { ";", "#" }. When adding new comments, the first string in the array is used.
  • GroupElements – As said before, when the parser runs into a multiline commentary or blank lines, it will group them into one object. Setting this property to false will prevent this practice.
  • QuoteChar – A nullable char. Default value is null, but can be set to e.g. ” (double qoutation mark). Then all values will be surrounded by quotes. However, either calling the Format() method or settingPreserveFormatting to false will be necessary.
  • DefaultValueFormatting – A spectacular property that defines a default appearance of values. The default is “?=$ ;” which causes values look like this: “Key=Value ;comment”.
  • … and much more (totally 16 properties).


What can you do with the Hardcore-Mode? What can you gain?

  • Be faster.
  • Read only the values that you need.
  • Feel like a Hardcore Boss.

Sample scenario #1: You want to extract a single section from an INI file instead of loading a whole file into a dummyConfigFile class from User-Mode. We will use IniFileReader here. Our goal is to extract the “Mail” section fromwin.ini to the separate file without loading all other sections.

 Collapse | Copy Code
IniFileReader reader = new IniFileReader("win.ini");
// Find the section without parsing lines before it.

// Get section's content

List<IniFileElement> sectionElements = reader.ReadSection();
// Do not load the rest. Close the stream.


// Write the section.

IniFileWriter writer = new IniFileWriter("mail.ini");

A very good idea is to create an IniFile object from the extracted collection:

 Collapse | Copy Code
IniFile file = IniFile.FromElements(sectionElements)

Such an IniFile would contain one section: the extracted “Mail.” This enables a whole set of methods specific to User-Mode, including lazy commenting entries and easy modifying values.

Sample scenario #2: You want to get just ONE value from a file. Let’s say that this value is “OLEMessaging” from the “Mail” section in win.ini.

 Collapse | Copy Code
IniFileReader reader = new IniFileReader("win.ini");
// I do not remember how olemessaging's characters exactly look,

// so I set case-sensitivity to false (default true).

IniFileSettings.CaseSensitive = false;
IniFileValue val = reader.GotoValue("olemessaging");
string oleMsg = val.Value;
// ... (do something with oleMsg).

…and so on.


IniFileReader and IniFileWriter are fast. IniFile is a bit slower, although the main slowdown appears while loading a file, not performing operations. For yet higher performance, I have written an additional class in a separate namespace: IniFiles.Light.IniFileLight. It enables parsing and writing of INI files. However, it totally ignores formatting and blank lines. Everything will be written using the following pattern:

 Collapse | Copy Code

It enables commenting values, but only single-line. The usage is similar to the User-Mode:

 Collapse | Copy Code
// Create a new file
Gajatko.IniFiles.Light.IniFileLight light = 
    new Gajatko.IniFiles.Light.IniFileLight();
// Set some data
light.Sections.Add("Data", new Dictionary<string, string>());
light.Sections["Data"].Add("Name", "Mickey");
light.Sections["Data"].Add("Surname", "Mouse");
// Add comments
light.Comments.Add("Data.Surname", "This is a surname");
light.Comments.Add("Data", "This is a section");
// Set a footer
light.Comments.Add("", "Footer");
// Save to a file


 Collapse | Copy Code
;This is a section
;This is a surname

You know the limitations of such a solution, so it’s up to you which variant you use. I put the IniFileLight in a separate namespace because if somebody wants to use it, he probably wouldn’t like to have all my 10 classes in the auto-completion list.

Format Preserving

Nobody forces you to use this IniFiles‘s skill; it can run in the background and not bother you. However, if you want to make your INI files look as originally or you are parsing ones which look as originally, I tell you that my code will not loose any content of a file. How much does the parser preserve the format? Here are some samples.

 Collapse | Copy Code
    Key1=  Value1

Look how Key1 looks. After the equality sign there are two spaces. If you add a new value to Section1, it will preserve this appearance and indentation:

 Collapse | Copy Code
    Key1=  Value1
    NewKey=  NewValue

Section1 is a little indented. If you add a new section, it will be a little indented too.

 Collapse | Copy Code
    Key1=  Value1
    NewKey=  NewValue

If you now add a value to Section2, it will look the same as the last value in the previous section:

 Collapse | Copy Code
    Key1=  Value1
    NewKey=  NewValue
    FreshKey=  FreshValue

Let’s consider a section with such an appearance:

 Collapse | Copy Code
[ Section1 ]

Then a new added one will follow the template:

 Collapse | Copy Code
[ Section1 ]
[ New Section ]

Also, indentation of comments and blank lines are saved. There are situations when a conflict appears. Here is one of them: If quotes are on (IniFileSettings.QuoteChars = "\""), then we can imagine the following piece:

 Collapse | Copy Code
Key1="Value1" some text

It is obvious that the value of Key1 is ‘Value1’, not ‘Value1″ some text’ or something like that. The parser knows that, so it interprets ‘ some text’ as a TextOnTheRight. This also will be preserved. However, this behaviour can be changed through the IniFileSettings.AllowTextOnTheRight property.

Format preserving can be turned off by switching IniFileSettings.PreserveFormatting to false. However, you may want to save the indentation of elements. Then call the IniFile.Format(false) method to format everything excluding leading spaces, or IniFile[sectionName].Format(false) to format the specified section.