Thursday, March 8, 2012

3 Different Ways to Display Progress in an ASP.NET AJAX Application

In this article, we will study three different techniques that allow you to visually display progress to users while performing partial-page updates using the UpdatePanel. For all the three approaches, I have used a .gif image to show a spinning gear kind of a progress bar while the UpdatePanel is performing some action. The image can be found with the source code for this article over here.
Method 1: Using PageRequestManager to display progress in an ASP.NET AJAX application
The PageRequestManager class handles the partial-page updates of the UpdatePanel. This class defines client events that you can use during an asynchronous request cycle. Let us see how to use some events of this class to display progress to the user while the UpdatePanel updates its contents.
Drag and drop a Button(btnInvoke) and Label(lblText) control inside the UpdatePanel. Also add a <div id="divImage" inside the UpdatePanel. This div will contain a .gif image that depicts progress and is initially set to invisible style="display:none". On the button click, perform a time consuming task. In our case, we have set a delay of 3 seconds by using Thread.Sleep(3000).  
C#
    protected void btnInvoke_Click(object sender, EventArgs e)
    {
        System.Threading.Thread.Sleep(3000);
        lblText.Text = "Processing completed";
    }
VB.NET
      Protected Sub btnInvoke_Click(ByVal sender As ObjectByVal e As EventArgs)
            System.Threading.Thread.Sleep(3000)
            lblText.Text = "Processing completed"
      End Sub
The markup and code for this sample is as shown below:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UsingPageRequestManager.aspx.cs" Inherits="UsingCSS" %>
 
<!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>Display Progress Using PageRequestManager</title>  
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
         <script type="text/javascript">
             // Get the instance of PageRequestManager.
             var prm = Sys.WebForms.PageRequestManager.getInstance();
             // Add initializeRequest and endRequest
             prm.add_initializeRequest(prm_InitializeRequest);
             prm.add_endRequest(prm_EndRequest);
            
             // Called when async postback begins
             function prm_InitializeRequest(sender, args) {
                 // get the divImage and set it to visible
                 var panelProg = $get('divImage');                
                 panelProg.style.display = '';
                 // reset label text
                 var lbl = $get('<%= this.lblText.ClientID %>');
                 lbl.innerHTML = '';
 
                 // Disable button that caused a postback
                 $get(args._postBackElement.id).disabled = true;
             }
 
             // Called when async postback ends
             function prm_EndRequest(sender, args) {
                 // get the divImage and hide it again
                 var panelProg = $get('divImage');                
                 panelProg.style.display = 'none';
 
                 // Enable button that caused a postback
                 $get(sender._postBackSettings.sourceElement.id).disabled = false;
             }
         </script>
 
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:Label ID="lblText" runat="server" Text=""></asp:Label>
                <div id="divImage" style="display:none">
                     <asp:Image ID="img1" runat="server" ImageUrl="~/images/progress.gif" />
                     Processing...
                </div>                
                <br />
                <asp:Button ID="btnInvoke" runat="server" Text="Click"
                    onclick="btnInvoke_Click" />
            </ContentTemplate>
        </asp:UpdatePanel>
    </div>
    </form>
</body>
</html>
 
As shown in the code above, we first get a reference to the PageRequestManager and then wire up the initializeRequest and endRequest events to execute, when an async postback begins and ends respectively.
When the user initiates a postback by clicking on the button kept inside the UpdatePanel, we set a delay of 3 seconds. To display progress to the user, we handle the InitializeRequest at the client side and set the divImage to visible. This shows up the .gif image with the progress as shown below. The button that caused the postback is disabled during this event, so in order to prevent users from hitting the button again.
Progress Bar
Note: Remember that you cannot have simultaneous async postbacks using ASP.NET AJAX.
When the async postback completes (in our case when 3 seconds are over), the endRequest event gets fired. The divImage is set to invisible, there by hiding the gif image and the button is enabled again.
Method 2: Using the UpdateProgress control to display progress in an ASP.NET AJAX application
Another very simple option to display progress during an async postback is to use the UpdateProgress control. You can use this control to display status information to the user, while the UpdatePanel updates its content. In the example below, we use the same .gif to display progress while the UpdatePanel is updating its content. For understanding purposes, we have emulated a time consuming operation by setting a delay of 3 seconds by using Thread.Sleep(3000) on the button click.
 C#
    protected void btnInvoke_Click(object sender, EventArgs e)
    {
        System.Threading.Thread.Sleep(3000);
        lblText.Text = "Processing completed";
    }
VB.NET
      Protected Sub btnInvoke_Click(ByVal sender As ObjectByVal e As EventArgs)
            System.Threading.Thread.Sleep(3000)
            lblText.Text = "Processing completed"
      End Sub
 
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UsingUpdateProgress.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>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <asp:UpdateProgress ID="updProgress"
        AssociatedUpdatePanelID="UpdatePanel1"
        runat="server">
            <ProgressTemplate>           
            <img alt="progress" src="images/progress.gif"/>
               Processing...           
            </ProgressTemplate>
        </asp:UpdateProgress>
       
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:Label ID="lblText" runat="server" Text=""></asp:Label>
                <br />
                <asp:Button ID="btnInvoke" runat="server" Text="Click"
                    onclick="btnInvoke_Click" />
            </ContentTemplate>
        </asp:UpdatePanel>        
    </div>
    </form>
</body>
</html>
 
In the code shown above, we use the AssociatedUpdatePanelID property of the UpdateProgress control to associate it with an UpdatePanel control. The ProgressTemplate property that can contain HTML, is used to specify the message displayed by an UpdateProgress control. In our case, we are displaying a .gif image progress as shown below
Progress
Method 3: Using UpdatePanelAnimationExtender to display progress in an ASP.NET AJAX application
The UpdatePanelAnimation control can also be used to visually display progress to the users while the UpdatePanel is performing some operation. As defined in the ASP.NET AJAX documentation “The UpdatePanelAnimationExtender is a simple extender that allows you to utilize the powerful animation framework with existing pages in an easy, declarative fashion. It is used to play animations both while an UpdatePanel is updating and after it has finished updating”. You can read more about the UpdatePanelAnimationExtender overhere. For this sample to work, drag and drop a UpdatePanelAnimationExtender from the AJAX Toolkit on to your page.
<Animations> are declared in the UpdatePanelAnimationExtender to create animation effect. Within the <Animations>, you can define the sequence of effects for the UpdatePanelAnimation. You can even use <ScriptAction> definitions to fine-tune and customize the animation.
In this example, we will show how to display an image progress bar using the <ScriptAction> definition of the UpdatePanelAnimationExtender. In the example given below, we have defined two <ScriptAction>; one for while the UpdatePanel is updating(<OnUpdating>) and one for after the UpdatePanel has finished updating(<OnUpdated>).
The two <ScriptAction> definition tags call two JavaScript methods respectively that are responsible for displaying and hiding the image progress bar while the UpdatePanel performs an update on the control. As shown in the previous methods, we have emulated a time consuming operation by setting a delay of 3 seconds by using Thread.Sleep(3000) on the button click. You can replace this code with any time consuming operation, like fetching records from a remote database or performing any similar resource intensive operation. The technique of hiding and showing the image also remains the same as we had discussed in Method 1. The entire source code and mark up is as shown below:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UsingUpdatePanelAnimation.aspx.cs" Inherits="UpdPnlAnimation" %>
 
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
 
<!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>
    <script type="text/javascript">
 
        function onUpdating()
        {
            // get the divImage
            var panelProg = $get('divImage');
            // set it to visible
            panelProg.style.display = '';
 
            // hide label if visible     
            var lbl = $get('<%= this.lblText.ClientID %>');
            lbl.innerHTML = '';
        }
 
        function onUpdated()
        {
            // get the divImage
            var panelProg = $get('divImage');
            // set it to invisible
            panelProg.style.display = 'none';
        }
 
 
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:Label ID="lblText" runat="server" Text=""></asp:Label>
                <div id="divImage" style="display:none">
                     <asp:Image ID="img1" runat="server" ImageUrl="~/images/progress.gif" />
                     Processing...
                </div>               
                <br />
                <asp:Button ID="btnInvoke" runat="server" Text="Click"
                    onclick="btnInvoke_Click" />
            </ContentTemplate>
        </asp:UpdatePanel>
        <cc1:UpdatePanelAnimationExtender ID="UpdatePanelAnimationExtender1"
        TargetControlID="UpdatePanel1" runat="server">
        <Animations>
            <OnUpdating>
               <Parallel duration="0">
                    <ScriptAction Script="onUpdating();" />
                    <EnableAction AnimationTarget="btnInvoke" Enabled="false" />                   
                </Parallel>
            </OnUpdating>
            <OnUpdated>
                <Parallel duration="0">
                    <ScriptAction Script="onUpdated();" />
                    <EnableAction AnimationTarget="btnInvoke" Enabled="true" />
                </Parallel>
            </OnUpdated>
        </Animations>
        </cc1:UpdatePanelAnimationExtender>       
    </div>
    </form>
</body>
</html>
 
C#
    protected void btnInvoke_Click(object sender, EventArgs e)
    {
        System.Threading.Thread.Sleep(3000);
        lblText.Text = "Processing completed";
    }
VB.NET
      Protected Sub btnInvoke_Click(ByVal sender As ObjectByVal e As EventArgs)
            System.Threading.Thread.Sleep(3000)
            lblText.Text = "Processing completed"
      End Sub
 
The progress will look similar to the ones shown in the other methods
progress
Well those were the three different methods of displaying progress to the user while the UpdatePanel updates its contents. The entire source code of this article can be downloaded from here. I hope this article was useful and I thank you for viewing it. 

Triggering an Update Panel from a control in a different ContentPlaceHolder

I can across an anamoly today when I tried to trigger an Update Panel from a control in a different ContentPlaceHolder.  The IDE recognized the control in the designer as I was able to select the control ID from the dropdown.  But, when I ran the page I received an error stating: "A control with ID 'Button1' could not be found for the trigger in UpdatePanel 'UpdatePanel1'." 
This is something I think should be fixed in the AJAX framework.  The Unique ID of the button is "ctl00$ContentPlaceHolder2$Button1", but the UpdatePanel is in ContentPlaceHolder1 so it assigned the controlID of the trigger to "ctl00$ContentPlaceHolder1$Button1" causing the YSOD ("Yellow Screen of Death").
The work around that I currently use is to dynamically add the trigger to the update panel.

    protected void Page_Init()
    {
     
         //LinkButton lbtnStart = new LinkButton();
         //lbtnStart = (LinkButton)this.Form.FindControl("ContentPlaceHolder1").FindControl("lbtnStart");
        //AsyncPostBackTrigger tr = new AsyncPostBackTrigger();
        //tr.ControlID = lbtnStart.UniqueID;
        //UpdatePanel1.Triggers.Add(tr);

        AsyncPostBackTrigger trigger = new AsyncPostBackTrigger();
        trigger.EventName = "Click";
        trigger.ControlID = lbtnStart.UniqueID;
        UpdatePanel1.Triggers.Add(trigger);
    }

āđāļĨāļ°comment āđƒāļ™UpdatePanel āļ•āļĢāļ‡ Triggers
  <asp:UpdatePanel ID="UpdatePanel1" runat="server"    >
        <ContentTemplate>
            <div runat="server" id="messageBox">
            </div>
        </ContentTemplate>
        <%--<Triggers>
           <asp:AsyncPostBackTrigger ControlID="lbtnStart" EventName="Click" />
        </Triggers>--%>
    </asp:UpdatePanel>

and 

āļāļēāļĢāđƒāļŠ้āļ‡āļēāļ™ SubQuery SqlServer

āļāļēāļĢāđƒāļŠ้āļ‡āļēāļ™ SUPQUERY 

āļĄāļēāļ–ึāļ‡āđ€āļĢื่āļ­āļ‡āļ‚āļ­āļ‡ SUPQUERY āļัāļ™āļš้āļēāļ‡āļ™āļ°āļ„āļ°
āļ•ัāļ§āļ­āļĒ่āļēāļ‡

select Customer_name ,zipcode
from Customers
where Zip_code = ANY (Select Zipcode
from Customers
where customername ='KPP'
or customername ='KPO' )


ANY āļ„ืāļ­ āļ‚้āļ­āļĄูāļĨāļ•ัāļ§āđƒāļ”āļ•ัāļ§āļŦāļ™ึ่āļ‡āļ็āđ„āļ”้āđƒāļ™āļœāļĨāļĨัāļžāļ˜์āļ‚āļ­āļ‡āļ‹ัāļšāļ„ิāļ§āļĢี āļ”ัāļ‡āļ™ั้āļ™āļ–้āļēāđ€āļ›็āļ™ = any āļˆāļ°āđƒāļŠ้āđ€āļ›āļĢีāļĒāļšāđ€āļ—ีāļĒāļšāļ‚้āļ­āļĄูāļĨāļ§่āļēāļĄีāļ„่āļēāđ€āļ—่āļēāļัāļ™āļัāļšāļœāļĨāļĨัāļžāļ˜์āđƒāļ™āļ‹ัāļšāļ„ิāļ§āļĢีāļ•ัāļ§āđƒāļ”āļ•ัāļ§āļŦāļ™ึ่āļ‡āļŦāļĢืāļ­āđ„āļĄ่ āļ™ั่āļ™āđ€āļ­āļ‡ āļ„āļ° āļ™āļ­āļāļˆāļēāļāļ™ี้āļĒัāļ‡āļĄี some , all āđ‚āļ”āļĒ
SOME āļˆāļ°āļĄีāļ„āļ§āļēāļĄāļŦāļĄāļēāļĒāđ€āļŦāļĄืāļ­āļ™ ANY
ALL āļˆāļ°āļĄีāļ„āļ§āļēāļĄāļŦāļĄāļēāļĒāđ€āļ›็āļ™ āļ‚้āļ­āļĄูāļĨāļ—ั้āļ‡āļŦāļĄāļ” āđƒāļ™āļœāļĨāļĨัāļžāļ˜์āļ‚āļ­āļ‡āļ‹ัāļšāļ„ิāļ§āļĢี

āļ‚้āļ­āļˆāļģāļัāļ”āļ‚āļ­āļ‡āļāļēāļĢāđƒāļŠ้āļ‡āļēāļ™ SUBQUERY
1. āđ„āļĄ่āļŠāļēāļĄāļēāļĢāļ–āđƒāļŠ้āļ‡āļēāļ™āļ§āļĨี order by āđƒāļ™āļ‹ัāļšāļ„ิāļ§āļĢี āđ€āļžāļĢāļēāļ°āļœāļĨāļĨัāļžāļ˜์āđƒāļ™āļ‹ัāļšāļ„ิāļ§āļĢีāļ–ูāļāļ™āļģāđ„āļ›āđƒāļŠ้āđ€āļ›āļĢีāļĒāļšāđ€āļ—ีāļĒāļšāļ—ั้āļ‡āļŦāļĄāļ”āļ­āļĒู่āđāļĨ้āļ§ āļˆึāļ‡āđ„āļĄ่āļ•้āļ­āļ‡āļĄีāļāļēāļĢāđ€āļĢีāļĒāļ‡āļĨāļģāļ”ัāļš āđ€āļ§้āļ™āđāļ•่āļˆāļ°āļĄีāļāļēāļĢāđƒāļŠ้āļ‡āļēāļ™āļ„ีāļĒ์āđ€āļ§ิāļĢ์āļ” TOP āļĢ่āļ§āļĄāļ”้āļ§āļĒ
2. āđ„āļĄ่āļŠāļēāļĄāļēāļĢāļ–āđƒāļŠ้āļ„ีāļĒ์āđ€āļ§ิāļĢ์āļ” INTO āđ„āļ”้
3.āļ„āļ­āļĨัāļĄāļ™์āļ—ี่āļĄีāļŠāļ™ิāļ”āļ‚้āļ­āļĄูāļĨāđ€āļ›็āļ™ ntext , text āđāļĨāļ° image āđ„āļĄ่āļŠāļēāļĄāļēāļĢāļ–āđ€āļĨืāļ­āļāļ‚ึ้āļ™āļĄāļēāđ„āļ”้
4. āļāļēāļĢāđƒāļŠ้āļ‹ัāļšāļ„ิāļ§āļĢีāļĢ่āļ§āļĄāļัāļšāđ‚āļ­āđ€āļ›āļ­āđ€āļĢāđ€āļ•āļ­āļĢ์ āļ—ี่āđƒāļŠ้āđ€āļ›āļĢีāļĒāļšāđ€āļ—ีāļĒāļšāļัāļ™ āđ€āļŠ่āļ™ = < > āļˆāļ°āđ„āļĄ่āļŠāļēāļĄāļēāļĢāļ–āđƒāļŠ้āļ§āļĨี GROUP BY āđāļĨāļ° HAVING āđ„āļ”้ āđ€āļ§้āļ™āđāļ•่āļˆāļ°āļĄีāļ„ีāļĒ์āđ€āļ§ิāļĢ์āļ” ANY, ALL, SOME

āļāļēāļāļัāļ™āđ„āļ§้āļ„āļ°āļŠāļģāļŦāļĢัāļš SUPQUERY

āļ„āļģāļŠั่āļ‡ CASE WHEN SqlServer

āļāļēāļĢāđ€āļĨืāļ­āļāļ—āļģāļ‡āļēāļ™āļ•āļēāļĄāđ€āļ‡ื่āļ­āļ™āđ„āļ‚āđ‚āļ”āļĒāđƒāļŠ้āļ„āļģāļŠั่āļ‡ CASE

āļĢูāļ›āđāļšāļšāļ„āļģāļŠั่āļ‡

CASE input_expression
WHEN when_expression THEN result_expression
[ …n ]
[
ELSE else_result_expression
]
END

āļ•ัāļ§āļ­āļĒ่āļēāļ‡ āļˆ้āļē

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price

āļ”ูāļ•ัāļ§āļ­āļĒ่āļēāļ‡āļāļēāļĢāļ™āļģ Case āļĄāļēāđƒāļŠ้āļĢ่āļ§āļĄāļัāļšāļ„āļģāļŠั่āļ‡ update āļัāļ™āļš้āļēāļ‡āļ„āļ° 

UPDATE titles
SET Category = CASE type WHEN 'popular_comp' 
THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHERE Location = 'Thailand'
AND Status = 'A'

āļ™āļ­āļāļˆāļēāļāļ™ี้ Case āļĒัāļ‡āļŠāļēāļĄāļēāļĢāļ–āļ™āļģāļĄāļēāđƒāļŠ้āđƒāļ™āļŠ่āļ§āļ‡āļ‚āļ­āļ‡āļāļēāļĢ where āđ„āļ”้āļ”้āļ§āļĒāļ™āļ°āļ„āļ°
āļĨāļ­āļ‡āļ™āļģāđ„āļ›āļ›āļĢāļ°āļĒุāļāļ•์āđƒāļŠ้āļัāļ™āļ•่āļ­āļ™āļ°āļ„āļ° āļĄีāļ›āļĢāļ°āđ‚āļĒāļŠāļ™์āļĄāļēāļāđ†āļ„āļ°

Wednesday, March 7, 2012

āļāļēāļĢāđƒāļŠ้ Temp table āđƒāļ™ SQL server

āļ§ัāļ™āļ™ี้āļ‚āļ­āļ™āļģāđ€āļŠāļ™āļ­āļ„āļ§āļēāļĄāļĢู้āđ€āļี่āļĒāļ§āļัāļš SQL server 2000 -2005 āđƒāļ™āđ€āļĢื่āļ­āļ‡āļ‚āļ­āļ‡āļāļēāļĢ
āļŠāļĢ้āļēāļ‡ Temp Table āđƒāļ™āļāļēāļĢāđ€āļ‚ีāļĒāļ™ Store Procedure āļัāļ™āļ„āļ°

āļŦāļĨāļēāļĒāļ„āļ™āļ„āļ‡āđ„āļĄ่āļĢู้āļˆัāļāļ§่āļēāļĄัāļ™āļ„ืāļ­āļ­āļ°āđ„āļĢ Temp Table āļ„āļĨ้āļēāļĒāļัāļšāļāļēāļĢāļŠāļĢ้āļēāļ‡ Table āļ›āļāļ•ิāļ„āļ° āđ€āļžีāļĒāļ‡āđāļ•่āļĄัāļ™āđ€āļ›็āļ™ Temp āļ—ี่āļ–ูāļāļŠāļĢ้āļēāļ‡āļ‚ึ้āļ™āļĄāļēāđƒāļ™āļ‚āļ“āļ°āļ™ั้āļ™ āđ€āļ—่āļēāļ™ั้āļ™ āđ„āļĄ่āđ„āļ”้āđ€āļ›็āļ™ Table āļ—ี่āļ–ูāļāļŠāļĢ้āļēāļ‡āļ‚ึ้āļ™āļˆāļĢิāļ‡ āļāļēāļĢāđƒāļŠ้āļ‡āļēāļ™ Temp Table āļĄีāļ›āļĢāļ°āđ‚āļĒāļŠāļ™์āļĄāļēāļāđƒāļ™āļāļēāļĢāļ™āļģāđ„āļ›āđ€āļ‚ีāļĒāļ™ Store Procedure āļ—่āļēāļ™āđƒāļ”āļ—ี่āļĒัāļ‡āđ„āļĄ่āđ€āļ„āļĒāļĨāļ­āļ‡āļ™āļģāđ„āļ›āļ›āļĢāļ°āļĒุāļāļ•์āđƒāļŠ้ āļ—่āļēāļ™āļˆāļ°āļžāļšāļ§่āļēāļĄัāļ™āļŠ่āļ§āļĒāđāļ้āļ›ัāļāļŦāļēāļ—ี่āļ‹ัāļšāļ‹้āļ­āļ™ āđāļĨāļ°āđāļ้āđ„āļ‚āļ›ัāļāļŦāļēāđ€āļĢื่āļ­āļ‡āļāļēāļĢāđ€āļĢีāļĒāļāļ‚้āļ­āļĄูāļĨāļˆāļģāļ™āļ§āļ™āļĄāļēāļāđ† āļˆāļēāļāļāļēāļĢāļ—āļģāļ‡āļēāļ™ āļĢāļ§āļĄāļ–ึāļ‡āļĨāļ”āļ āļēāļĢāļ°āļāļēāļĢāļŠāļĢ้āļēāļ‡ table āļ•่āļēāļ‡āđ†āļ—ี่āđ„āļĄ่āļˆāļģāđ€āļ›็āļ™ āļ™āļ­āļāļˆāļēāļāļ™ั้āļ™āļĒัāļ‡āļĒืāļ”āļŦāļĒุ่āļ™āļ•่āļ­āļāļēāļĢāđ€āļžิ่āļĄāļˆāļģāļ™āļ§āļ™ field āđ‚āļ”āļĒāļ—ี่āđ„āļĄ่āļ•้āļ­āļ‡āđ„āļ›āļĒุ่āļ‡āļัāļšāđ‚āļ„āļĢāļ‡āļŠāļĢ้āļēāļ‡āļ‚āļ­āļ‡ āđ€āļ—āđ€āļšิ้āļĨāļˆāļĢิāļ‡āđāļ•่āļ­āļĒ่āļēāļ‡āđƒāļ”

āļ”้āļ§āļĒāļāļēāļĢāđ€āļ‚ีāļĒāļ™āļ„āļģāļŠั่āļ‡āđ€āļžีāļĒāļ‡āļ„āļģāļŠั่āļ‡āļŠุāļ”āđ€āļ”ีāļĒāļ§ āļĄัāļ™āļ­āļēāļˆāļ—āļģāđƒāļŦ้āļāļēāļĢāđāļŠāļ”āļ‡āļœāļĨāļŠ้āļē āļ§ิāļ˜ีāļāļēāļĢāđƒāļŠ้ temp table āļ็āļ„ืāļ­ 
āļ•ัāļ§āļ­āļĒ่āļēāļ‡āļ„āļģāļŠั่āļ‡āđƒāļ™āļāļēāļĢāļŠāļĢ้āļēāļ‡ āļˆึāļ‡āđ€āļŦāļĄืāļ­āļ™āļัāļšāļāļēāļĢ Create table āļ˜āļĢāļĢāļĄāļ”āļēāđ† āđ€āļžีāļĒāļ‡āđāļ•่....

āļ•ัāļ§āļ­āļĒ่āļēāļ‡ āļāļēāļĢāļŠāļĢ้āļēāļ‡ Temp Table
create table #temp
(
name varchar(30),
surname varchar(50)
)

āļˆāļ°āļŠัāļ‡āđ€āļāļ•āđ„āļ”้āļ§่āļēāļāļēāļĢāļŠāļĢ้āļēāļ‡ Temp Table āļ•่āļēāļ‡āļˆāļēāļāļāļēāļĢāđ€āļ‚ีāļĒāļ™āļ„āļģāļŠั่āļ‡āļŠāļĢ้āļēāļ‡ Table āļ˜āļĢāļĢāļĄāļ”āļēāļ•āļĢāļ‡āļ—ี่ āļ•้āļ­āļ‡āļĄีāđ€āļ„āļĢื่āļ­āļ‡āļŦāļĄāļēāļĒ # āļ­āļĒู่āļ‚้āļēāļ‡āļŦāļ™้āļēāđ€āļŠāļĄāļ­
āļŠุāļ”āļ—้āļēāļĒāđ€āļĄื่āļ­āđƒāļŠ้āļ‡āļēāļ™ Temp Table āđ€āļŠāļĢ็āļˆāđāļĨ้āļ§ āļ็āļ„āļ§āļĢāļˆāļ° āđ€āļ‚ีāļĒāļ™āļ„āļģāļŠั่āļ‡

drop table #temp 

āļŦāļĨัāļ‡āļˆāļēāļāļ—ี่āđƒāļŠ้āļ‡āļēāļ™āļ—ุāļāļ­āļĒ่āļēāļ‡āđ€āļŠāļĢ็āļˆāđāļĨ้āļ§āļ™āļ°āļ„āļ°āđ€āļžื่āļ­āđ€āļ›็āļ™āļāļēāļĢāļ„ืāļ™āļ—āļĢัāļžāļĒāļēāļāļĢāđƒāļŦ้āļĢāļ°āļšāļš

āļ‚āļ­āļĒāļāļ•ัāļ§āļ­āļĒ่āļēāļ‡ āļ™āļ°āļ„āļ° āđ€āļŠ่āļ™
āļ–้āļēāļ„ุāļ“āļˆāļģāđ€āļ›็āļ™āļ•้āļ­āļ‡āđāļŠāļ”āļ‡āļ‚้āļ­āļĄูāļĨ āđ‚āļ”āļĒāļāļēāļĢ join āļ‚้āļ­āļĄูāļĨāļˆāļēāļ 5 table āļ”ัāļ‡āļ™ี้ 
(āļ‚āļ­āļĒāļāļ•ัāļ§āļ­āļĒ่āļēāļ‡āļ—ี่āđ„āļĄ่āļĨāļ°āđ€āļ­ีāļĒāļ”āļĄāļēāļāļ™ัāļāļ™āļ°āļ„āļ° )

book_Master -- āđ€āļ็āļš book_name , ID_book
Price_Master -- āđ€āļ็āļš ID_Book , Per_unit
Count_stock -- āđ€āļ็āļš ID_Book , date , book_in_stock
Sell_history -- āđ€āļ็āļš ID_Book , order_sell , date_sell
Period -- āđ€āļ็āļš month , month_thai

āļ‹ึ่āļ‡āļ‚้āļ­āļĄูāļĨāđƒāļ™āđāļ•่āļĨāļ° Table āļ™ั้āļ™āļĄีāļĄāļēāļāļĄāļēāļĒāļĄāļŦāļēāļĻāļēāļĨ

āļ•้āļ­āļ‡āļāļēāļĢāđ€āļ‚ีāļĒāļ™ Store Procedure āđ€āļžื่āļ­āđ€āļ็āļšāļ‚้āļ­āļĄูāļĨ āļŠื่āļ­ āļ‚āļ­āļ‡āļŦāļ™ัāļ‡āļŠืāļ­(book_name) ,āļŠื่āļ­āļ§ัāļ™āđ€āļ”ืāļ­āļ™āļ›ีāđ€āļ”ืāļ­āļ™āļ āļēāļĐāļēāđ„āļ—āļĒ (date) , āļĢāļēāļ„āļēāļŦāļ™ัāļ‡āļŠืāļ­āļ•่āļ­āđ€āļĨ่āļĄ(Per_unit) ,āļĒāļ­āļ”āļāļēāļĢāļŠั่āļ‡āļ‹ื้āļ­āđƒāļ™āđ€āļ”ืāļ­āļ™āļ™ั้āļ™āđ† (order_sell), āļˆāļģāļ™āļ§āļ™āđ€āļ‡ิāļ™āļĢāļ§āļĄāļ—ั้āļ‡āļŦāļĄāļ”(Total) ,āļˆāļģāļ™āļ§āļ™āļŦāļ™ัāļ‡āļŠืāļ­āļ—ี่āđ€āļŦāļĨืāļ­āļ­āļĒู่ (stock)

āļ„ุāļ“āļ­āļēāļˆāļˆāļ°āđ€āļ‚ีāļĒāļ™āļ”้āļ§āļĒāļ„āļģāļŠั่āļ‡āļ”ัāļ‡āļ•่āļ­āđ„āļ›āļ™ี้ 

create proc Show_History
(
@book_ID varchar(50),
@Date_order varchar(8) --āļŠāļĄāļĄāļ•ิāđƒāļŦ้āļĢัāļšāļ„่āļēāđ€āļ›็āļ™ '16052552'

As
Begin
Set NoCount ON

insert into Show_History
select A.ID_book ,
A.book_name , 
E.month_thai,
substring (@Date_order,5,4) As year,
D.order_sell,
B.Per_unit,
isnull(order_sell ,0.0) * isnull(per_unit ,0.0) As Total,
isnull(C.count_stock,0.0) - isnull(D.order_sell,0.0) As stock
from book_Master A,
Price_Master B, 
count_stock C, 
Sell_history D , 
Period E
where A.ID_Book = @book_ID 
and A.ID_Book = B.ID_book
and A.ID_Book = C.ID_Book 
and A.ID_Book = D.ID_Book 
and E.month = substring((@Date_order,2,2) 

Set NoCount Off
End

āļ—ีāļ™ี้āļĨāļ­āļ‡āļĄāļēāļ”ูāļāļēāļĢāļŠāļĢ้āļēāļ‡ procedure āļ”้āļ§āļĒ Temp Table āļัāļ™

create proc Show_History
(
@book_ID varchar(50),
@Date_order varchar(8) --āļŠāļĄāļĄāļ•ิāđƒāļŦ้āļĢัāļšāļ„่āļēāđ€āļ›็āļ™ '16052552'

As
Begin
Set NoCount ON

create table #Show_history
(
ID_book varchar(10) ,
book_name varchar(50) , 
month_thai varchar(10) ,
year varchar(4) ,
order_sell int ,
Per_unit numeric(10,8),
Total numeric(10,8),
stock int
)

insert into #Show_history (ID_book, book_name ,year, order_sell, 
Per_unit ,Total )
select A.ID_book , A. book_name , substring (@Date_order,5,4),
D.order_sell, B.Per_unit, isnull(order_sell ,0.0)*isnull 
(per_unit ,0.0) 
from book_Master A,
Price_Master B,
Sell_history D 
where A.ID_Book = @book_ID 
and A.ID_Book = B.ID_book 
and A.ID_Book = D.ID_Book 

update T
set T.month_thai = E.month 
from Period E, #Show_history T
where E.month = substring((@Date_order,2,2) 

update #Show_history 
set Total = isnull(C.count_stock,0.0) - isnull(T.order_sell,0.0) 
from #Show_history T, count_stock C
where T.ID_book = C.ID_book

select * from #Show_history

drop table #Show_history

Set NoCount Off
End

āļˆāļēāļāļ•ัāļ§āļ­āļĒ่āļēāļ‡āļāļēāļĢāđ€āļ‚ีāļĒāļ™ āđ‚āļ”āļĒāļāļēāļĢāđƒāļŠ้ Temp Table āđ€āļ‚้āļēāļĄāļēāļŠ่āļ§āļĒāļˆāļ°āļ—āļģāđƒāļŦ้āđ€āļĢāļēāļŠāļēāļĄāļēāļĢāļ– āļĒืāļ”āļŦāļĒุ่āļ™ āđƒāļ™āļāļēāļĢāđāļŠāļ”āļ‡āļ‚้āļ­āļĄูāļĨāđ„āļ”้āļĄāļēāļāļ‚ึ้āļ™āđāļĨāļ°āđ€āļĢีāļĒāļāļ”ูāļ‚้āļ­āļĄูāļĨāđ„āļ”้āđ„āļ§āļ‚ึ้āļ™ āļ‹ึ่āļ‡āļšāļēāļ‡āļ„āļĢั้āļ‡ āļ–้āļēāļ„ุāļ“āļ•้āļ­āļ‡āļāļēāļĢāđ€āļžิ่āļĄ field āđƒāļ™āļ āļēāļĒāļŦāļĨัāļ‡āļ็āļŠāļēāļĄāļēāļĢāļ–āđ€āļžิ่āļĄāđ„āļ”้āļ•āļĢāļ‡āļŠ่āļ§āļ‡āļ‚āļ­āļ‡āļāļēāļĢ create table #show_history āđ„āļ”้āđ€āļĨāļĒ āđ‚āļ”āļĒāļ–้āļēāļŦāļēāļāļ„ุāļ“āđ„āļĄ่āđƒāļŠ้ Temp table āļ™ี้ āļ„ุāļ“āļ­āļēāļˆāļˆāļ°āļ•้āļ­āļ‡āđ„āļ›āļ™ั่āļ‡āđāļ้āđ‚āļ„āļĢāļ‡āļŠāļĢ้āļēāļ‡āļ‚āļ­āļ‡ Table āļ‹ึ่āļ‡āļšāļēāļ‡āļ—ีāļ­āļēāļˆāļˆāļ°āļāļĢāļ°āļ—āļšāļัāļš table āļ­ื่āļ™āđ†āļ—ี่āđ€āļ›็āļ™ table āļˆāļĢิāļ‡āđ† āđ‚āļ”āļĒāđ„āļĄ่āļĢู้āļ•ัāļ§