Wednesday, July 9, 2014

Furniture Query for Stock Sale And Remaining Products Monthly Wise


select * from total
select * from done

select x.item,x.one-y.one as one,x.two-y.two as two
from
total x
left outer join
done y
on x.id=y.id

insert into total values('Chair',6,9),('Table',7,8)

insert into done values('Chair',2,4),('Table',5,2)

delete from done where one=5




select  x.item,x.one-y.one as one,x.two-y.two as two
from
total x
left outer join
done y
on x.id=y.id

select * from total
select * from done
select distinct(x.item),SUM(x.one-y.one) as one,SUM(x.two-y.two) as two
from
total x
left outer join                                                                                            
done y
on x.id=y.id


select distinct(item) from total

Nested Grid


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="innergrid_p.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>
    <form id="form1" runat="server">
    <div align="center">
        <asp:GridView ID="grdouter" runat="server" AutoGenerateColumns="False" OnRowDataBound="grdouter_onrowdatabound"
            DataKeyNames="ig_cid" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"
            CellPadding="2" ForeColor="Black" GridLines="None">
            <AlternatingRowStyle BackColor="PaleGoldenrod" />
            <Columns>
                <asp:TemplateField HeaderText="countryid">
                    <ItemTemplate>
                        <asp:Label ID="lblcid" runat="server" Text='<%# bind("ig_cid") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="countryname">
                    <ItemTemplate>
                        <asp:Label ID="lblcname" runat="server" Text='<%# bind("ig_cname") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="state">
                    <ItemTemplate>
                        <asp:GridView ID="grdinner" runat="server" AutoGenerateColumns="false" OnRowDataBound="grdinner_onrowdatabound"
                            DataKeyNames="ig_sid">
                            <Columns>
                                <asp:TemplateField HeaderText="stateid">
                                    <ItemTemplate>
                                        <asp:Label ID="lblstateid" runat="server" Text='<%# bind("ig_sid") %>'></asp:Label>
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="statename">
                                    <ItemTemplate>
                                        <asp:Label ID="lblstatename" runat="server" Text='<%# bind("ig_sname") %>'></asp:Label>
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Region">
                                    <ItemTemplate>
                                        <asp:GridView ID="grdnested" runat="server" AutoGenerateColumns="false">
                                            <Columns>
                                                <asp:TemplateField HeaderText="regionid">
                                                    <ItemTemplate>
                                                        <asp:Label ID="lblrid" runat="server" Text='<%# bind("ig_rid") %>'></asp:Label>
                                                    </ItemTemplate>
                                                </asp:TemplateField>
                                                <asp:TemplateField HeaderText="regionname">
                                                    <ItemTemplate>
                                                        <asp:Label ID="lblrname" runat="server" Text='<%# bind("ig_rname") %>'></asp:Label>
                                                    </ItemTemplate>
                                                </asp:TemplateField>
                                            </Columns>
                                        </asp:GridView>
                                    </ItemTemplate>
                                </asp:TemplateField>
                            </Columns>
                        </asp:GridView>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="Tan" />
            <HeaderStyle BackColor="Tan" Font-Bold="True" />
            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
            <SortedAscendingCellStyle BackColor="#FAFAE7" />
            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
            <SortedDescendingCellStyle BackColor="#E1DB9C" />
            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>




ASPX.CS
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    ado _objado;
    protected void Page_Load(object sender, EventArgs e)
    {

        _objado = new ado();
        grdouter.DataSource = _objado.getoutergriddetails();
        grdouter.DataBind();
    }

    protected void grdouter_onrowdatabound(object sender, GridViewRowEventArgs e)
    {
        _objado = new ado();
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string id = grdouter.DataKeys[e.Row.RowIndex].Value.ToString();
            GridView inner = e.Row.FindControl("grdinner") as GridView;
            inner.DataSource = _objado.getinnergriddetails(Convert.ToInt32(id));
            inner.DataBind();

        }
    }
    protected void grdinner_onrowdatabound(object sender, GridViewRowEventArgs e)
    {
        _objado = new ado();
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
           

            Label lbl_id = (Label)e.Row.FindControl("lblstateid");
           
            string id2 = lbl_id.Text;
            GridView nested = e.Row.FindControl("grdnested") as GridView;
            nested.DataSource = _objado.getnestedgriddetails(Convert.ToInt32(id2));
            nested.DataBind();


       
        }
   
    }
}
 ---------------------------------------------------------------
.cs------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// Summary description for ado
/// </summary>
public class ado
{
    string conn = ConfigurationManager.ConnectionStrings["config"].ToString();

    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter dap;
    DataSet ds;

    public DataSet getoutergriddetails()
    {
        con = new SqlConnection(conn);
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_country";
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        ds = new DataSet();
        dap = new SqlDataAdapter(cmd);
        dap.Fill(ds);
        return ds;


    }
    public DataSet getinnergriddetails(int ig_cid)
    {
        con = new SqlConnection(conn);
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_state";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ig_cid", ig_cid);
        con.Open();
        ds = new DataSet();
        dap = new SqlDataAdapter(cmd);
        dap.Fill(ds);
        return ds;

    }

    public DataSet getnestedgriddetails(int ig_sid)
    {
        con = new SqlConnection(conn);
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_region";
        cmd.CommandType = CommandType.StoredProcedure;
        //cmd.Parameters.AddWithValue("@ig_cid", ig_cid);
        cmd.Parameters.AddWithValue("@ig_sid", ig_sid);
        con.Open();
        ds = new DataSet();
        dap = new SqlDataAdapter(cmd);
        dap.Fill(ds);
        return ds;
    }
}

//    public object getnestedgriddetails(int p)
//    {
//        throw new NotImplementedException();
//    }
//}


Tuesday, July 8, 2014

GridView Paging



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

<!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>
    <asp:GridView ID="grd" runat="server" AllowPaging="true" PageSize="3"
            onpageindexchanging="grd_PageIndexChanging"
            onpageindexchanged="grd_PageIndexChanged">
   
    </asp:GridView>
    </div>
    </form>
</body>
</html>

----------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Pagging : System.Web.UI.Page
{
    DataClassesDataContext _objdbml = new DataClassesDataContext();
    Country _objcountry = new Country();
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {

            var da = from p in _objdbml.Countries
                     select p;
            SqlCommand _objcmd = (SqlCommand)_objdbml.GetCommand(da);
            DataSet ds = new DataSet();
            SqlDataAdapter _dap = new SqlDataAdapter(_objcmd);
            _dap.Fill(ds);


            grd.DataSource = ds;
            grd.DataBind();
        }
        catch (Exception ex)
        {
            Response.Redirect("Default2.aspx");
        }
        finally
        {
        }
    }
    protected void grd_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            grd.PageIndex = Convert.ToInt32(e.NewPageIndex.ToString());
            grd.DataBind();
        }
        catch (Exception ex)
        {
            Response.Redirect("Default2.aspx");
        }

    }
    protected void grd_PageIndexChanged(object sender, EventArgs e)
    {

    }
}




Sql Query Stock And Sale

create table total(id int identity(1,1) primary key,item varchar(50),one int,two int)

insert into total values('Chair',5,8),('Table',9,9)
select * from total

create table done(id int identity(1,1) primary key,item varchar(50),one int,two int)
insert into done values('Chair',1,5),('Table',3,3)


select * from total
select * from done
select x.id,x.item,x.one-y.one as one,x.two-y.two as two
from total x
left outer join

done y on x.id=y.id
Subscribe to: Posts (Atom)