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();
    }
}

DWR like .NET Comet Ajax in ASP.NET

link - http://www.codeproject.com/KB/aspnet/pokein.aspx

C# String Theory—String intern pool

The string intern pool is a table that contains a single reference to each unique literal string declared or created programmatically in your application. The Common Language Runtime (CLR) uses the intern pool to minimize string storage requirements. As a result, an instance of a literal string with a particular value only exists once in the system. For example, if you assign the same literal string to several different variables, at runtime, the CLR retrieves the unique reference to that literal string from the intern pool and assigns it to each variable.
The String.Intern (string) method searches the intern pool for a string equal to the specified value. If such a string exists, its reference in the intern pool is returned. Otherwise, a reference to the specified string is added to the intern pool and that reference is returned.
In the following example, the string, declared with a value of "Intern pool" is interned; because, it is a string literal. The string built is a new string object with the same value as declared but generated by the System.Text.StringBuilder class. The Intern method searches for a string with the same value as built. Since the string already exists in the intern pool, the method returns the same reference that is assigned to declared and assigns that reference to interned.
References declared and built compare unequal because they refer to different objects, while references declared and interned compare equal because they refer to the same string.

String declared = "Intern pool"; 
String built    = new StringBuilder().Append("Intern ")
   .Append("pool").ToString(); 
String interned = String.Intern(built); 
Console.WriteLine ((Object)built==(Object)declared);    // different references
Console.WriteLine ((Object)interned==(Object)declared); // same reference 

Performance considerations

When trying to reduce the total memory allocated by your application, remember that interning has two unfortunate side effects. Firstly, the memory allocated for interned String objects is unlikely to be released until the CLR terminates: the CLR's references to interned String objects may persist after your application or application domain terminates. Secondly, to intern a string, a string must first be created. Thus, despite the fact that the memory will eventually be garbage collected, the memory used by the String object will still be allocated.

For more Details:- http://en.csharp-online.net/CSharp_String_Theory%E2%80%94String_intern_pool 

Good article on state management

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

What are 'AS' and 'IS' key word in C#

The 'AS' operator is used to perform certain types of conversions between compatible reference types.

 class csrefKeywordsOperators
   {
       class Base
       {
           public override string  ToString()
           {
             return "Base";
           }
       }
       class Derived : Base
       { }

       class Program
       {
           static void Main()
           {

               Derived d = new Derived();

               Base b = d as Base;
               if (b != null)
               {
                   Console.WriteLine(b.ToString());
               }

           }
       }
   }


The 'IS' keyword causes a compile-time warning if the expression is known to always be true or to always be false, but typically evaluates type compatibility at run time.
The is operator cannot be overloaded.
Note that the is operator only considers reference conversions, boxing conversions, and unboxing conversions. Other conversions, such as user-defined conversions, are not considered.

class Class1 {}
class Class2 {}
class Class3 : Class2 { }

class IsTest
{
    static void Test(object o)
    {
        Class1 a;
        Class2 b;

        if (o is Class1)
        {
            Console.WriteLine("o is Class1");
            a = (Class1)o;
            // Do something with "a."
        }
        else if (o is Class2)
        {
            Console.WriteLine("o is Class2");
            b = (Class2)o;
            // Do something with "b."
        }

        else
        {
            Console.WriteLine("o is neither Class1 nor Class2.");
        }
    }
    static void Main()
    {
        Class1 c1 = new Class1();
        Class2 c2 = new Class2();
        Class3 c3 = new Class3();
        Test(c1);
        Test(c2);
        Test(c3);
        Test("a string");
    }
}
/*
Output:
o is Class1
o is Class2
o is Class2
o is neither Class1 nor Class2.
*/

How can delete duplicate rows from table except one row?

This problem can be solved by many ways. But I have two simple method :-
1) using  simple sub query Method
2) using Rowcount

1) delete
   top (select count(*)-1 from TableName where Condition )
   from
 TableName where Condition .
Note :- Both condition should be same for apropriate result.

2) RowCount is very usefull keyword in sqlserver. By this way we can fix , how many row will be affected .
suppose in a table we have n number of rows , we want to display only 1 row , just write down
     set rowcount 1
after that any DML or DDL command effects only one row of  table.
and ,
    set rowcount 0 , for all rows from table
For the above problem , at first get number of similar rows from table using " select count(*) from TABLENAME  where Condtion " (suppose get n rows , where n is integer)
Then
set rowcount  n-1
delete  from  TABLENAME  where  Condition
set rowcount 0

What is a GUID and How to create a GUID in C# ?

GUID --->   Globally unique identifier   -  128-bit integer
GUID  can be used to uniquely identify something
This method can be in system name space . GUID method System.Guid.NewGuid() initializes a new instance of the GUID class.
Syntax -   System.Guid.NewGuid();
Each time get a new number.