<%@ Page Language="C#"  AutoEventWireup="true"%>
<%@ Import Namespace="System.Collections.Generic" %>

<%@ Register Src="wucCity.ascx" TagPrefix="uc" TagName="City" %>
<%@ Register Src="wucAddressType.ascx" TagPrefix="uc" TagName="AddressType" %>
<%@ Register Src="GridViewExplanation.ascx"  TagPrefix="uc" TagName="Explanation"  %>

<script runat="server">
    protected void sqlAddress_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
        lblStatus.Text = "SqlCacheDependency: Select query was executed against the database";
        }
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<h3>Using Polling SQL Cache Dependencies with DataSource Caching</h3>
    <uc:Explanation ID="Explanation1" runat="server" />
    <asp:SqlDataSource ID="sqlAddress" runat="server" ConnectionString="<%$ ConnectionStrings:WEBSWAPPConnectionString %>"
        SelectCommand="SELECT a.Id, a.SuiteNumber, a.AddressLine1, a.AddressLine2, a.CityId, a.PostalCode, a.AddressTypeId, a.RecordVersion, b.Name AS AddressType, (SELECT f.CityName + ', ' + f.ProvinceName + ', ' + g.Name AS CityName FROM (SELECT CityName, e.Name AS ProvinceName, e.ParentId AS CountryId FROM (SELECT Name AS CityName, ParentId AS ProvinceId FROM Location AS c WHERE (a.CityId = Id)) AS d LEFT OUTER JOIN Location AS e ON d.ProvinceId = e.Id) AS f LEFT OUTER JOIN Location AS g ON f.CountryId = g.Id) as CityName FROM Address AS a INNER JOIN AddressType AS b ON a.AddressTypeId = b.Id INNER JOIN Location AS c ON a.CityId = c.Id"
       EnableCaching="true" SqlCacheDependency="WEBSWAPP:Address"
        OnSelecting="sqlAddress_Selecting" 
        UpdateCommand="UPDATE Address SET SuiteNumber =@SuiteNumber, AddressLine1 =@AddressLine1, AddressLine2 =@AddressLine2, CityId =@CityId, PostalCode =@PostalCode, AddressTypeId =@AddressTypeId WHERE Id=@Id"
         DeleteCommand="Delete FROM Address where Id=@Id">
        <UpdateParameters>
            <asp:Parameter Name="SuiteNumber" />
            <asp:Parameter Name="AddressLine1" />
            <asp:Parameter Name="AddressLine2" />
            <asp:Parameter Name="CityId" />
            <asp:Parameter Name="PostalCode" />
            <asp:Parameter Name="AddressTypeId" />
            
        </UpdateParameters>
    </asp:SqlDataSource>
    
    <asp:Label BackColor="Gold" runat="server" ID="lblStatus" EnableViewState="false" ToolTip="A notification that the query is being executed against the database due to cahce setup based on SqlCacheDependency"></asp:Label>
    <br />
    <asp:GridView ID="gvAddress" DataSourceID="sqlAddress" runat="server" AutoGenerateColumns="False"
        DataKeyNames="Id,RecordVersion" Width="98%" AllowSorting="true" AllowPaging="true" PageSize="5" PagerSettings-Mode="NextPreviousFirstLast" 
         PagerSettings-FirstPageText="FirstPage" PagerSettings-NextPageText="Next"  PagerSettings-PreviousPageText="Previous" PagerSettings-LastPageText="Last">
        <HeaderStyle BackColor="#818dd5"  ForeColor="White"/>
        <EditRowStyle BackColor="Gold" />
        <Columns>
            <asp:CommandField ShowEditButton="true"  ShowDeleteButton="true" />
            <asp:BoundField DataField="SuiteNumber" HeaderText="Suite #" ControlStyle-Width="50px" SortExpression="SuiteNumber" />
            <asp:BoundField DataField="AddressLine1" HeaderText="Address Line 1"  ControlStyle-Width="200px" SortExpression="AddressLine1"/>
            <asp:BoundField DataField="AddressLine2" HeaderText="Address Line 2"  ControlStyle-Width="100px" SortExpression="AddressLine2"/>
            <asp:TemplateField HeaderText="City, Province, Country"  ControlStyle-Width="300px" SortExpression="CityName">
                <ItemTemplate>
                    <%#Eval("CityName") %>
                </ItemTemplate>
                <EditItemTemplate>
                    <uc:City ID="ucCity" runat="server" CityId='<%#Bind("CityId") %>' />
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="PostalCode" HeaderText="Postal Code" ControlStyle-Width="50px" SortExpression="PostalCode"/>
            <asp:TemplateField ControlStyle-Width="50px" HeaderText="Type" SortExpression="AddressType">
                <ItemTemplate>
                    <%#Eval("AddressType") %>
                </ItemTemplate>
                <EditItemTemplate>
                    <uc:AddressType ID="ucAddressType" runat="server" AddressTypeId='<%#Bind("AddressTypeId") %>' />
                </EditItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

</asp:Content>