Dharmendra (.Net)

Wednesday, 20 August 2014

Paging and Sorting in GridView

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

<!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 bgcolor="Silver">
    <form id="form1" runat="server">
     <br />
    <h4 style="color: #808000">
        Article by Vithal Wadje</h4>
    <br />
<%--   <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>--%>
        <asp:GridView ID="GridView1" runat="server" AllowSorting="true" CellPadding="6" ForeColor="#333333"
            GridLines="None" PageSize="2" AllowPaging="true" OnPageIndexChanging="Gridpaging" OnSorting="Gridsorting">
            <AlternatingRowStyle BackColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerSettings FirstPageText="First" LastPageText="End"
                NextPageText="&gt;z" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#E3EAEB" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <%--<SortedAscendingCellStyle BackColor="#F8FAFA" />
            <SortedAscendingHeaderStyle BackColor="#246B61" />
            <SortedDescendingCellStyle BackColor="#D4DFE1" />
            <SortedDescendingHeaderStyle BackColor="#15524A" />--%>
        </asp:GridView>
<%-- </ContentTemplate>
    </asp:UpdatePanel> --%>
    </form>
</body>
</html>
--------------------------------------------------------------Code--------------------------
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Web;
using System.Data;
public partial class _Default : System.Web.UI.Page
{

    private const string ASCENDING = " ASC";
    private const string DESCENDING = " DESC";
    DataTable dt;
    //private void connection()
    //{
    //    constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
    //    con = new SqlConnection(constr);
    //    con.Open();

    //}

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bindgrid();

        }
    }

    private void Bindgrid()
    {
        //connection();
        //query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security
        //com = new SqlCommand(query, con);
        //SqlDataAdapter da = new SqlDataAdapter(com);

        dt = new DataTable();
        dt.Columns.Add("Name");
        dt.Columns.Add("Age");
        dt.Columns.Add("Mobile");
        //dt.Rows.Add(new DataRow()
        DataRow dr = dt.NewRow();
        dr["Name"] = "Dharmendra";
        dr["Age"] = "32";
        dr["Mobile"] = "9015095809";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["Name"] = "Rakes";
        dr["Age"] = "32";
        dr["Mobile"] = "9015095809";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["Name"] = "Mukes";
        dr["Age"] = "32";
        dr["Mobile"] = "9015095809";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["Name"] = "Gopal";
        dr["Age"] = "32";
        dr["Mobile"] = "9015095809";
        dt.Rows.Add(dr);
        dt.AcceptChanges();
        //da.Fill(dt);

        ViewState["Paging"] = dt;
        GridView1.DataSource = dt;
        GridView1.DataBind();
        //con.Close();

    }
    protected void Gridpaging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataSource = ViewState["Paging"];
        GridView1.DataBind();

    }

    public SortDirection CurrentSortDirection
    {
        get
        {
            if (ViewState["sortDirection"] == null)
            {
                ViewState["sortDirection"] = SortDirection.Ascending;
            }

            return (SortDirection)ViewState["sortDirection"];
        }
        set
        {
            ViewState["sortDirection"] = value;

        }
    }
    protected void Gridsorting(object sender, GridViewSortEventArgs e)
    {
        string ColumnTosort = e.SortExpression;

        if (CurrentSortDirection == SortDirection.Ascending)
        {
            CurrentSortDirection = SortDirection.Descending;
            SortGridView(ColumnTosort, DESCENDING);
        }
        else
        {
            CurrentSortDirection = SortDirection.Ascending;
            SortGridView(ColumnTosort, ASCENDING);
        }

    }

    private void SortGridView(string sortExpression, string direction)
    {
        // You can cache the DataTable for improving performance
        //dynamic dt = ViewState["Paging"];
        DataTable dtsort = (DataTable)ViewState["Paging"];// dt;
        DataView dv = new DataView(dtsort);
        dv.Sort = sortExpression + direction;

        GridView1.DataSource = dv;
        GridView1.DataBind();
    }
}

note: when column bound self then use:-
<asp:TemplateField HeaderText="Expiry date" SortExpression="ExpDate">

autocomplete

http://jqueryui.com/autocomplete/

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AutoCompleet.aspx.cs" Inherits="Admin_AutoCompleet" %>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>jQuery UI Autocomplete - Default functionality</title>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.11.1/themes/smoothness/jquery-ui.css">
    <script src="//code.jquery.com/jquery-1.10.2.js"></script>
    <script src="//code.jquery.com/ui/1.11.1/jquery-ui.js"></script>
    <link rel="stylesheet" href="/resources/demos/style.css">
    <script>
        $(function () {
            var availableTags = [
              "ActionScript",
              "AppleScript",
              "Asp",
              "BASIC",
              "C",
              "C++",
              "Clojure",
              "COBOL",
              "ColdFusion",
              "Erlang",
              "Fortran",
              "Groovy",
              "Haskell",
              "Java",
              "JavaScript",
              "Lisp",
              "Perl",
              "PHP",
              "Python",
              "Ruby",
              "Scala",
              "Scheme"
            ];
            $("#tags").autocomplete({
                source: availableTags
            });
        });
    </script>
</head>
<body>

    <div class="ui-widget">
        <label for="tags">Tags: </label>
        <input id="tags">
    </div>

</body>
</html>

Tuesday, 12 August 2014

pivot Table demo

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
insert into DailyIncome values ('JOHNS', 'FRI', 300)
insert into DailyIncome values ('FREDS', 'TUE', 500)
insert into DailyIncome values ('FREDS', 'TUE', 200)
insert into DailyIncome values ('SPIKE', 'MON', 900)
insert into DailyIncome values ('FREDS', 'FRI', 900)
insert into DailyIncome values ('FREDS', 'MON', 500)
insert into DailyIncome values ('JOHNS', 'SUN', 600)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('JOHNS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'SAT', 800)
insert into DailyIncome values ('SPIKE', 'TUE', 100)
insert into DailyIncome values ('SPIKE', 'THU', 300)
insert into DailyIncome values ('FREDS', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'TUE', 600)

select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

Read more:
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

Thursday, 7 August 2014

export gridview to excel

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="CS" EnableEventValidation="false"%>
protected void ExportToExcel(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);

//To Export all pages
GridView1.AllowPaging = false;
this.BindGrid();

GridView1.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.BackColor = GridView1.HeaderStyle.BackColor;
}
foreach (GridViewRow row in GridView1.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridView1.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}

GridView1.RenderControl(hw);

//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}

public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}

Friday, 1 August 2014

upgraded from a Visual Studio 2005 project to a Visual Studio 2010 project.

Friday, 25 July 2014

Password validation using asp.net control

http://aspsnippets.com/Articles/Implement-Password-Policy-using-Regular-Expressions-and-ASPNet-RegularExpression-Validator.aspx

Monday, 14 July 2014

delete duplicate records in sql

select * from employee
go
alter table employee add Id int Identity(1,1)
go
delete from employee where ID not in
(
select min(id) as ID from employee group by EmployeeID,Salary

)
go
alter table employee drop column Id
go

select * from employee