Good link for XML serialization

http://msdn.microsoft.com/en-us/library/ms973893.aspx#objserializ_topic4

postion of INDEX using Charindex and Pathindex

We have two function in SQL Server to find out position of INDEX

CHARINDEX( )  and PATHINDEX( ) , Both function are used to find out index postion and both have two arguments

With PATINDEX, must include percent signs before and after the pattern, unless for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

Example of CHARINDEX:

Select CHARINDEX('Expresion',columnName) from TABLENAME



Examples of PATINDEX:


Select PATHINDEX('%Expresion%',columnName) from TABLENAME


Both return integer value , if not available , it returns Zero(0);
  

select max data from multiple column with respect id using PIVOT in SQL SERVER

Sometimes , we face a problem , how can retrive a max data from multiple column.
suppose ,  i have a table of student result.
In this table multiple column , ie, ROLLNUMBER , MATH,PHYSICS,CHEMISTRY ,TOTALMARKS.
it can be easilly calculated what is his  TOTALMARKS , but it cam be tuff , in which subject he got maximum marks??

Try some dummy code as follow
////////////////////TABLE CREATE////////////////////////////////////////////
CREATE TABLE [dbo].[TBLRESULT]([ROLLNUMBER] [bigint] NOT NULL,[MATH] [int] NULL,[PHYSICS] [int] NULL,[CHEMISTRY] [int]
)NULL ON [PRIMARY]

//////////////////////////////INSERT DATA    START QUERY////////////////////////////////////////
INSERT INTO TBLRESULT VALUES(ROLLNUMBER,MARKSOFmaths,MARKSOFphysics,MARKSOFchemistry)

 EXAMPLE --
  INSERT  INTO TBLRESULT VALUES(1004,12,0,9)

///////////////////////////////END QUERY///////////////////////////////////////////

SELECT *  FROM TBLRESULT

ROLLNUMBER	MATH	PHYSICS	CHEMISTRY
1001	10	12	14
1002	10	14	12
1003	14	12	0

Now use this query

////////////////////////////START QUERY//////////////////////////////////////////////////////////////

select row_number()over(partition by ROLLNUMBER order by MAXMARKS desc) as MAXMARKSID,ROLLNUMBER , SUBJECTS,MAXMARKSfrom (select ROLLNUMBER,SUBJECTS,MAXMARKS from (select ROLLNUMBER,MATH,PHYSICS,CHEMISTRY from TBLRESULT) ALIAS1unpivot
(MAXMARKS for SUBJECTS in(MATH,PHYSICS,CHEMISTRY)) AS ALIAS2 ) ALIAS3


////////////////////////////////END QUERY/////////////////////////////////////////////////////

Result like this
 

MAXMARKSID	ROLLNUMBER	MAXMARKS	MARKS
1	1001	14	CHEMISTRY
2	1001	12	PHYSICS
3	1001	10	MATH
1	1002	14	PHYSICS
2	1002	12	CHEMISTRY
3	1002	10	MATH
1	1003	14	MATH
2	1003	12	PHYSICS
3	1003	0	CHEMISTRY
1	1004	12	MATH
2	1004	9	CHEMISTRY
3	1004	0	PHYSICS

Now for Mximum obtained marks subjec can be obtained as


///////////////////////////START QUERY/////////////////////////////////////////////////////////////////////////////

WITH DUMMYTABLE as (select row_number()over(partition by ROLLNUMBER order by MAXMARKS desc) as MAXMARKSID,ROLLNUMBER , SUBJECTS,MAXMARKSfrom (select ROLLNUMBER,SUBJECTS,MAXMARKS from (select ROLLNUMBER,MATH,PHYSICS,CHEMISTRY from TBLRESULT) ALIAS1unpivot
(MAXMARKS for SUBJECTS in(MATH,PHYSICS,CHEMISTRY)) AS ALIAS2 ) ALIAS3)select ROLLNUMBER,SUBJECTS,MAXMARKS from DUMMYTABLE where DUMMYTABLE.MAXMARKSID =1

////////////////////////////////////////////END QUERY//////////////////////////////////////////////////////////////////
Result like this

ROLLNUMBER	SUBJECTS	MAXMARKS
1001	CHEMISTRY	14
1002	PHYSICS	14
1003	MATH	14

State Mangement in ASP.net

very useful link for State Mangement on MSDN

http://msdn.microsoft.com/en-us/library/75x4ha6s(v=VS.100).aspx

Anonymous Types and Anonymous Method in C#.

Anonymous Types

Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to first explicitly define a type. The type name is generated by the compiler and is not available at the source code level. The type of the properties is inferred by the compiler. The following example shows an anonymous type being initialized with two properties called Amount and Message.
var v = new { Amount = 108, Message = "Hello" };
Anonymous types are class types that consist of one or more public read-only properties. No other kinds of class members such as methods or events are allowed. An anonymous type cannot be cast to any interface or type except for object.
Anonymous types are reference types that derive directly from object. The compiler gives them a name although your application cannot access it. From the perspective of the common language runtime, an anonymous type is no different from any other reference type, except that it cannot be cast to any type except for object.
The most common scenario is to initialize an anonymous type with some properties from another type. In the following example, assume a class that is named Product that includes Color and Price properties together with several other properties that you are not interested in. Products is a collection of Product objects. The anonymous type declaration starts with the new keyword. It initializes a new type that uses only two properties from Product. This causes a smaller amount of data to be returned in the query.

If you do not specify member names in the anonymous type, the compiler gives the anonymous type members the same name as the property being used to initialize them. You must provide a name to a property that is being initialized with an expression.
var productQuery =
from prod in products
select new { prod.Color, prod.Price };

foreach (var v in productQuery)
{
Console.WriteLine("Color={0}, Price={1}", v.Color, v.Price);
}


Anonymous Method

Creating anonymous methods is essentially a way to pass a code block as a delegate parameter.
By using anonymous methods, you reduce the coding overhead in instantiating delegates by eliminating the need to create a separate method.
The scope of the parameters of an anonymous method is the anonymous-method-block.
Unlike local variables, the lifetime of the outer variable extends until the delegates that reference the anonymous methods are eligible for garbage collection.
A reference to n is captured at the time the delegate is created.
An anonymous method cannot access the ref or out parameters of an outer scope.
No unsafe code can be accessed within the anonymous-method-block.
It is an error to have a jump statement, such as goto, break, or continue, inside the anonymous method block whose target is outside the block. It is also an error to have a jump statement, such as goto, break, or continue, outside the anonymous method block whose target is inside the block.
The local variables and parameters whose scope contain an anonymous method declaration are called outer or captured variables of the anonymous method.

Animated bar chart in Jquery

useful link for animated bar chart using Jquery.

http://www.elated.com/articles/snazzy-animated-pie-chart-html5-jquery/
http://www.highoncoding.com/Articles/440_Creating_Animated_Bar_Charts_Using_JQuery_Animation_Effects.aspx

How can data retrive from Excel file using C#?

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.IO;
public partial class export : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //Always use single xls file in xlsfilepath  
       
        string ExcelPath = " D:\\xlfile";
        if (Directory.Exists(ExcelPath))
        {
            DirectoryInfo GetExeclFiles = new DirectoryInfo(ExcelPath);
            FileInfo[] Files = GetExeclFiles.GetFiles("*.xls");
            if (Files.Length > 0)
            {
                DataTable dtnewtable = new DataTable();
                DataTable dttable = new DataTable();
                string FilePath, excelConnectionString, sqlConnectionString;
                for (int k = 0; k < Files.Length; k++)
                {
                    FilePath = Files[k].FullName.ToString();
                    excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
                    // Create Connection to Excel Workbook
                    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                    {
                        if (connection.State == ConnectionState.Open)
                            connection.Close();
                        if (connection.State == ConnectionState.Closed)
                            connection.Open();

                        //  here in query you should pass sheet name here relatedproducts is sheet name
                        OleDbCommand command = new OleDbCommand("Select * FROM [RelatedProducts$]", connection);
                        // Create DbDataReader to Data Worksheet
                        using (System.Data.Common.DbDataReader dr = command.ExecuteReader())
                        {
                            dttable.Load(dr);
                           
                            DataTable ProductsTable = new DataTable();
                            DataColumn Dtcolumn = new DataColumn("Product", typeof(string));
                            DataColumn Dtcolumn2 = new DataColumn("RelatedProduct", typeof(string));
                            ProductsTable.Columns.Add(Dtcolumn);
                            ProductsTable.Columns.Add(Dtcolumn2);
                 // code for one-to-many relation 
                            DataTable dt = dttable;
                            int colCount = dt.Columns.Count;
                            for (int r = 0; r < dt.Rows.Count; r++)
                            {
                                for (int i = 0; i < colCount; i++)
                                {
                                    for (int j = 0; j < colCount; j++)
                                    {
                                        if (string.IsNullOrEmpty(dt.Rows[r][i].ToString())) { i++; if (i >= colCount) break; }
                                        if (string.IsNullOrEmpty(dt.Rows[r][j].ToString())) { j++; if (j >= colCount) continue; }
                                        if (j != i)
                                        {
                                            if (string.IsNullOrEmpty(dt.Rows[r][i].ToString()) || string.IsNullOrEmpty(dt.Rows[r][j].ToString())) continue;
                                            ProductsTable.Rows.Add(dt.Rows[r][i].ToString(), dt.Rows[r][j].ToString());
                                        }
                                    }
                                }
                            }
                          

                            ////
                            connection.Close();
                            connection.Dispose();
                            GridView1.DataSource = ProductsTable;
                            GridView1.DataBind();
                         //
                        }//end of USING dataReader
                    }//end of USING sql connection
                }//end of for loop
              
            }//end of if(Files.Length > 0)
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportGridView();
    }
    private void ExportGridView()
    {
        string attachment = "attachment; filename=RelatedProducts.xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        // Create a form to contain the grid
        HtmlForm frm = new HtmlForm();
        GridView1.Parent.Controls.Add(frm);
        frm.Attributes["runat"] = "server";
        frm.Controls.Add(GridView1);

        frm.RenderControl(htw);
        //GridView1.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }
}