SQL to XLS

using System.Data;
using System.Data.SqlClient;
using System.IO;

public partial class Sql2Xl : System.Web.UI.Page
{
    SqlConnection cn = new SqlConnection("data source=SRIKANTA-PC\\SQLEXPRESS;initial catalog=adi;user id=sa;password=banka");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (ConnectionState.Closed == cn.State)
        {
            cn.Open();
        }

    }

   
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("select * from student", cn);
        da.Fill(ds);
        ds.WriteXml(Server.MapPath(".") + "/abc.xml");

        File.Move(Server.MapPath(".") + "/abc.xml", Server.MapPath(".") + "/demo.xls");

       
    }
}

Get Selected Row Values of a GridView on Client Side using JavaScript

-------------------------------------GRIDPAGE.ASPX--------------------------------------

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
             
            e.Row.Attributes["onclick"] = "GetRowValue(this);"; 
        }


    }

Add a server variable:

<asp:HiddenField ID="hdnSelected" runat="server" />

Add a Style

<style>
        .AquaMarine {
            background-color: red;
        }
    </style>
--------------------------------------GRIDPAGE.ASPX----------------------------------

function GetRowValue(obj) {
            obj.className = 'AquaMarine';
       
            var curRow = parseInt(obj.rowIndex);

            var grid = document.getElementById("<%= GridView1.ClientID %>");

            for (i = 1; i < grid.rows.length; i++) {
                if(i != curRow)
                grid.rows[i].style.backgroundColor = '#ffffff';
            }

            //Initialize hidden filed value
            document.getElementById('<%=hdnRowValues.ClientID%>').value = '';
            //Set values from the row to hidden field.
            for(j=1;j<grid.rows[curRow].cells.length;j++)
            {
             
                cell = grid.rows[curRow].cells[j];
                document.getElementById('<%=hdnRowValues.ClientID%>').value = document.getElementById('<%=hdnRowValues.ClientID%>').value + cell.innerText + ',';
                }
         
              _selectedRowValue = document.getElementById('<%=hdnRowValues.ClientID%>').value;
        }

---------------------------------------------------------------

sql to excel

http://www.aspsnippets.com/Articles/Export-data-from-SQL-Server-to-Excel-in-ASPNet-using-C-and-VBNet.aspx

Select Some Checkboxes

Javascript

-----------------------------------------------------------------------------------
    function AddRemoveSelected(obj, studId) {

            //debugger;

            if (obj.checked == true) {
                document.getElementById('<%=hdnSelected.ClientID%>').value = document.getElementById('<%=hdnSelected.ClientID%>').value + ',' + studId;
            }
            else {
                document.getElementById('<%=hdnSelected.ClientID%>').value = document.getElementById('<%=hdnSelected.ClientID%>').value.replace(studId, '');
            }

        }

-------------------------------------------ASPX---------------------------------------------
 <ItemTemplate>


                            <asp:CheckBox ID="selectchk" onclick='<%# "AddRemoveSelected(this," + Eval("StudID") + ");" %>'
                                runat="server" />

                        </ItemTemplate>




-------------------------------------------------------ASPX.CS---------------------------

protected void Page_Load(object sender, EventArgs e)
    {
        ////Get hidden filed values
        string strRawIDs = hdnSelected.Value;

        string[] strIDs = strRawIDs.Split(',');

        ///////////////////////////

        

Alias

protected void Page_Load(object sender, EventArgs e)
    {
        if (ConnectionState.Closed == cn.State)
        {
            cn.Open();
        }

        //string strFName = "FirstName";
        //SqlDataAdapter da = new SqlDataAdapter("select * from student order by " + strFName, cn);

        SqlCommand cmd = new SqlCommand("Select * from student", cn);
        SqlDataReader dr = cmd.ExecuteReader();

        List<student> lstStudent = new List<student>();
        while (dr.Read())
        {
            student oStud = new student();
            oStud.StudentID = int.Parse(dr[0].ToString());
            oStud.FirstName = dr[1].ToString();
            oStud.LastName = dr[2].ToString();

            lstStudent.Add(oStud);
        }

        //string fname= "n.FirstName";
        var dt = (from n in lstStudent select new { StudID = n.StudentID, StudFirstName = n.FirstName, n.LastName, Roll = "" });
        //lstStudent.OrderBy(x =>  x.FirstName).ToList();

        //SqlDataAdapter da = new SqlDataAdapter("select * from student", cn);
        //DataTable dt = new DataTable();
        //da.Fill(dt);

        GridView1.DataSource = dt;
        GridView1.DataBind();

     
    }
}

Using Variable in Lambda expression

public partial class GridPage : System.Web.UI.Page
{

    SqlConnection cn = new SqlConnection("data source=SRIKANTA-PC\\SQLEXPRESS;initial catalog=adi;user id=sa;password=banka");

    protected void Page_Load(object sender, EventArgs e)
    {
     
        if(ConnectionState.Closed == cn.State)
        {
            cn.Open();
        }

 string fname= "n.FirstName";
        lstStudent = (from n in lstStudent orderby n.FirstName descending select n).ToList();


        //string strFName = "FirstName";
        //SqlDataAdapter da = new SqlDataAdapter("select * from student order by " + strFName, cn);

        SqlCommand cmd = new SqlCommand("Select * from student", cn);
        SqlDataReader dr = cmd.ExecuteReader();

        List<student> lstStudent = new List<student>();
        while (dr.Read())
        {
            student oStud = new student();
            oStud.StudentID = int.Parse(dr[0].ToString());
            oStud.FirstName = dr[1].ToString();
            oStud.LastName = dr[2].ToString();

            lstStudent.Add(oStud);
        }


        lstStudent = lstStudent.OrderBy(x =>  x.FirstName).ToList();

        //SqlDataAdapter da = new SqlDataAdapter("select * from student", cn);
        //DataTable dt = new DataTable();
        //da.Fill(dt);

        GridView1.DataSource = lstStudent;
        GridView1.DataBind();

     

    

select ALL rows of gris grid

  Table : Student

---------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

GridView1.aspx

---------------------------------------------------------

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <script type="text/javascript">
        function SelectAll(id) {
            //get reference of GridView control
            var grid = document.getElementById("<%= GridView1.ClientID %>");
            //variable to contain the cell of the grid
            var cell;

            if (grid.rows.length > 0) {
                //loop starts from 1. rows[0] points to the header.
                for (i = 1; i < grid.rows.length; i++) {
                    //get the reference of first column
                    cell = grid.rows[i].cells[0];

                    //loop according to the number of childNodes in the cell
                    for (j = 0; j < cell.childNodes.length; j++) {
                        //if childNode type is CheckBox                 
                        if (cell.childNodes[j].type == "checkbox") {
                            //assign the status of the Select All checkbox to the cell 
                            //checkbox within the grid
                            cell.childNodes[j].checked = document.getElementById(id).checked;
                        }
                    }
                }
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                                    OnRowDataBound="GridView1_RowDataBound"
                                    OnRowCommand="GridView1_RowCommand"
                                    OnRowDeleting ="GridView1_RowDeleting"
                >
                <Columns>
                    <asp:TemplateField>

                        <HeaderTemplate>
                            <asp:CheckBox ID="allchk"
                                runat="server" Text="All" />
                        </HeaderTemplate>
                        <ItemTemplate>

                            <asp:CheckBox ID="selectchk"
                                runat="server" />

                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Student ID">

                        <ItemTemplate>
                            <asp:Label ID="namelbl"
                                runat="server" Text='<%#Eval("StudentID") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Student ID">

                        <ItemTemplate>
                            <asp:Label ID="namelbl1"
                                runat="server" Text='<%#Eval("FirstName") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Student ID">

                        <ItemTemplate>
                            <asp:Label ID="namelbl2"
                                runat="server" Text='<%#Eval("LastName") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Select">
                        <ItemTemplate>
                            <asp:LinkButton ID="LinkButton1"
                                OnClientClick="return confirm('Are you sure you want to delete this student?');"
                                CommandArgument='<%# Eval("StudentID") %>'
                                CommandName="Delete" runat="server">
         Delete</asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>

                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

-------------------------------------------------------------------------
GridView1.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.Data;
using System.Data.SqlClient;

public partial class GridPage : System.Web.UI.Page
{

    SqlConnection cn = new SqlConnection("data source=SRIKANTA-PC\\SQLEXPRESS;initial catalog=adi;user id=sa;password=banka");

    protected void Page_Load(object sender, EventArgs e)
    {
     
        if(ConnectionState.Closed == cn.State)
        {
            cn.Open();
        }

        SqlDataAdapter da = new SqlDataAdapter("select * from student", cn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        GridView1.DataSource = dt;
        GridView1.DataBind();

    }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {

        //header select all function
        if (e.Row.RowType == DataControlRowType.Header)
        {
            ((CheckBox)e.Row.FindControl("allchk")).Attributes.Add("onclick",
                "javascript:SelectAll('" +
                ((CheckBox)e.Row.FindControl("allchk")).ClientID + "')");
        }

    }


    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        int iCurrentStudID = int.Parse(e.CommandArgument.ToString());
        if (e.CommandName == "Delete")
        {
            SqlCommand cmd=new SqlCommand("Delete from Student where StudentID = " + iCurrentStudID + "");
            cmd.Connection = cn;

            if (ConnectionState.Closed == cn.State)
            {
                cn.Open();
            }

            cmd.ExecuteNonQuery();

            SqlDataAdapter da = new SqlDataAdapter("select * from student", cn);
            DataTable dt = new DataTable();
            da.Fill(dt);

            GridView1.DataSource = dt;
            GridView1.DataBind();

        }
    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }
}