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)
    {

    }
}

Ajax Call to controller and view

Create.cshtml
------------------------------------------------------------


@model MvcApplication1.Models.student

@{
    ViewBag.Title = "Create";
}
<!-- for popup -->
    <div id="dvPop" style="display:none;position:absolute;top:100px;left:100px;z-index:1000;height:400px;width:400px;background-color:red"></div>
<!-- End for popup -->



<h2>Create</h2>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>student</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.FirstName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.FirstName)
            @Html.ValidationMessageFor(model => model.FirstName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.LastName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.LastName)
            @Html.ValidationMessageFor(model => model.LastName)
        </div>

        <p>
            <input type="submit" value="Create" />
           
            <input type="button" value="ShowList" onclick="showStudList();" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>
<script src="http://code.jquery.com/jquery-2.1.4.min.js"></script>
 <script lang="javascript">
     function showStudList() {

       
        // $(this).load("/StudentName/index");

         $.ajax({
             type: "GET",
             url: "/StudentName/index",
             data: {},
             success: function (viewHTML) {
                 document.getElementById('dvPop').style.display = '';
                 $("#dvPop").html(viewHTML);
             },
             error: function (errorData) { onError(errorData); }
         });

     }

</script>




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

StudentNameController.cs
--------------------------------


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcApplication1.Models;

namespace MvcApplication1.Controllers
{
    public class StudentNameController : Controller
    {
        //
        // GET: /StudentName/

        private adiEntities db = new adiEntities();

        public ActionResult Index()
        {
            //Get student List
            List<student> studList = db.students.Where(x=>x.FirstName=="Ram").ToList();
            return PartialView("Index", studList);
        }

    }
}

Event Bubbling

GridOperation.ASPX


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

<%@ Register src="ShowGrid.ascx" tagname="ShowGrid" tagprefix="uc1" %>

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
   
    <div>
       
        <uc1:ShowGrid ID="ShowGrid1" runat="server" />
       
    </div>
    </form>
</body>
</html>



Pager.ASCX

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="Pager.ascx.cs" Inherits="UC_to_UC.LucyJobs.Pager" %>
<asp:DropDownList ID="ddlPager" runat="server" AutoPostBack="True"
    onselectedindexchanged="ddlPager_SelectedIndexChanged">
    <asp:ListItem>1</asp:ListItem>
    <asp:ListItem>2</asp:ListItem>
    <asp:ListItem>3</asp:ListItem>

</asp:DropDownList>

Pager.ASCX.cs

namespace UC_to_UC.LucyJobs

{

    public partial class Pager : System.Web.UI.UserControl

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        public delegate void GetPageIndexEventHandler(CustomEventArgs e);

        public event GetPageIndexEventHandler getPageCustomEvent;

        protected void ddlPager_SelectedIndexChanged(object sender, EventArgs e)

        {

            if (getPageCustomEvent != null)

            {

                CustomEventArgs cArgs = new CustomEventArgs();

                cArgs.PageNum = int.Parse(ddlPager.SelectedValue);

                getPageCustomEvent(cArgs);

            }

        }

    }

}

ShowGrid.ASCX

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="ShowGrid.ascx.cs" Inherits="UC_to_UC.LucyJobs.ShowGrid" %>

<%@ Register src="Pager.ascx" tagname="Pager" tagprefix="uc1" %>

<asp:GridView ID="gvStudents" runat="server" AllowPaging="true" PageSize="3" PagerSettings-Visible="false">

</asp:GridView>

<uc1:Pager ID="Pager1" runat="server" />

ShowGrid.ASCX.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace UC_to_UC.LucyJobs

{

    public partial class ShowGrid : System.Web.UI.UserControl

    {

        

        List<student> oStudList = null;

        protected void Page_Load(object sender, EventArgs e)

        {

            

                oStudList = new List<student>()

            { 

                new student{roll=1,Name="Srikanta",Dept="MCA"},

                new student{roll=2,Name="Sriram", Dept="MBA"},

                new student{roll=3,Name="Susant",Dept="BA"},

                new student{roll=4,Name="Srikanta1",Dept="MCA"},

                new student{roll=5,Name="Sriram1", Dept="MBA"},

                new student{roll=6,Name="Susant1",Dept="BA"},

                new student{roll=7,Name="Srikanta2",Dept="MCA"},

                new student{roll=8,Name="Sriram2", Dept="MBA"},

                new student{roll=9,Name="Susant2",Dept="BA"},

                new student{roll=10,Name="Srikanta3",Dept="MCA"},

                new student{roll=11,Name="Sriram3", Dept="MBA"},

                new student{roll=12,Name="Susant3",Dept="BA"}

             };

                if (!IsPostBack)

                {

                gvStudents.DataSource = oStudList;

                gvStudents.DataBind();

                gvStudents.PageIndex = 0;

            }

            else

            {

                Pager1.getPageCustomEvent += new Pager.GetPageIndexEventHandler(Pager1_getPageCustomEvent);

            }

        }

        void Pager1_getPageCustomEvent(CustomEventArgs e)

        {

            BindStudentData(e.PageNum);

        }

              

        public void BindStudentData(int PageNum)

        {

           // gvStudents.DataSource = null;

            oStudList = new List<student>()

            { 

                new student{roll=1,Name="Srikanta",Dept="MCA"},

                new student{roll=2,Name="Sriram", Dept="MBA"},

                new student{roll=3,Name="Susant",Dept="BA"},

                new student{roll=4,Name="Srikanta1",Dept="MCA"},

                new student{roll=5,Name="Sriram1", Dept="MBA"},

                new student{roll=6,Name="Susant1",Dept="BA"},

                new student{roll=7,Name="Srikanta2",Dept="MCA"},

                new student{roll=8,Name="Sriram2", Dept="MBA"},

                new student{roll=9,Name="Susant2",Dept="BA"},

                new student{roll=10,Name="Srikanta3",Dept="MCA"},

                new student{roll=11,Name="Sriram3", Dept="MBA"},

                new student{roll=12,Name="Susant3",Dept="BA"}

             };

            gvStudents.DataSource = oStudList;

            gvStudents.DataBind();

            gvStudents.PageIndex = PageNum;

            

        }

    }

}

Student.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace UC_to_UC.LucyJobs

{

    public class student

    {

        public int roll { get; set; }

        public string Name { get; set; }

        public string Dept { get; set; }

    }

}

CustomEventArgs.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace UC_to_UC

{

    public class CustomEventArgs:EventArgs

    {

        // Declare a Code property of type string:

        int _iPageNum;

        public int PageNum

        {

            get

            {

                return _iPageNum;

            }

            set

            {

                _iPageNum = value;

            }

        }

    }

}