[CLOSED] Asynchronous execution of Stored Procedure

  1. #1

    [CLOSED] Asynchronous execution of Stored Procedure

    Hello,
    I have a Store Procedure to export an Excel file which runs from a [Direct Method] by clicking a button.
    But if data is very large can take up to 20 minutes to execute, how I can run it in parallel and keep using
    the program while Store Procedure is running?
    Last edited by fabricio.murta; Mar 24, 2017 at 3:50 PM. Reason: No user feedback in 7+ days
  2. #2
    Hello @atroul!

    You'll have to set up asynchronous behavior to your page. It is not really a feature or "not feature" of Ext.NET. That's more like an ASP.NET inherent question, but here's a couple examples in our examples explorer that explore this concept in different ways:

    - Progress bar: server side update
    - SignalR Stock Ticker
    - Assynchronous DirectEvents

    Some threads that may be interesting to check out:
    - Updating progress to the user when executing Long Tasks.
    - AjaxMethod - is it Asynchronous
    - Asynchronous DirectMethod

    I hope this helps!
    Fabrício Murta
    Developer & Support Expert
  3. #3
    Hello fabricio,

    I will examine the options you gave me but I would like to ask you if
    by opening a new page and execute the Stored Procedure there will work?
    And if that works is there any example which I can follow?

    Thank you
  4. #4
    Hello! Yes it will and the SignalR example pointed above is an example that will keep updating the progress once you reload the page (as long as you instruct the page to "open stock".

    All you have to do is have the page to trigger the event that monitors the action.
    Fabrício Murta
    Developer & Support Expert
  5. #5
    Hello Fabricio,

    I mean an example where I can see how by pressing a button I can open a new page where the Stored Procedure will run
    and while its running, I can minimize it for example and do other actions in the program.

    Thank you
  6. #6
    Hello @atroul!

    I can't think on an example doing exactly what you described. But I see that as a very feasible thing to do with Ext.NET.

    I suggest you to try and make a test case scenario reproducing it the way you need and, if you get stuck while trying that, then ask us for help with that in hand. That way, we will be able to provide you with help.
    Fabrício Murta
    Developer & Support Expert
  7. #7
    Hello Fabricio,

    Here is what I have done until now and the error I receive:

    Error
    Uncaught TypeError: Cannot read property 'load' of null
    ToExcel.ascx:ln29
    ToExcel.ascx
    <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="ToExcel.ascx.cs" Inherits="ICDMSAdmin.Modules.ToExcel" %>
    
    <%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
    
    <script src="Scripts/scriptlibrary.js"></script>
    
    <ext:DesktopModuleProxy runat="server">
        <Module ModuleID="toexcel">
            <Shortcut Name="Export DB To Excel" IconCls="x-ToExcel-shortcut" X="50" Y="560" TextCls="x-long-label" >
            </Shortcut>
            <Window>
                <ext:Window ID="WndwToExcel"  runat="server"
                    Maximizable="false" 
                    CloseAction="Destroy" 
                    Layout="FitLayout"
                    Width="400" 
                    Height="200" 
                    Resizable="false"     
                    BodyStyle="background-color: white; padding: 50px;"
                    Title="Export DB To Excel" 
                    AutoScroll="true"
                    ButtonAlign="Center">
                    <Defaults>
                       <ext:Parameter Name="margin" Value="0 5 0 0" Mode="Value" />
                    </Defaults>
                    <Items>
                        <ext:Button ID="btnExport" runat="server" Text="Export to Excel" >
                            <Listeners>
                                <Click Handler="#{WndwToExcel}.load('\\Pages\\WebForm1.aspx');" />
                            </Listeners>
                        </ext:Button>
                    </Items>
                </ext:Window>
            </Window>
        </Module>
    </ext:DesktopModuleProxy>
    ToExcel.ascx.cs
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Threading.Tasks;
    using Ext.Net;
    using ClosedXML;
    using System.IO;
    using System.Data.SqlClient;
    using System.Data;
    
    namespace ICDMSAdmin.Modules
    {
        public partial class ToExcel : System.Web.UI.UserControl
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!X.IsAjaxRequest)
                {
                    
                }
            }
        }
    }
    WebForm1.aspx
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="ICDMSAdmin.Pages.WebForm1" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <ext:ResourceManager runat="server" />
            <ext:FormPanel runat="server" Height="500">
                <LayoutConfig>
                    <ext:HBoxLayoutConfig Align="Stretch" Padding="0" />
                </LayoutConfig>
            </ext:FormPanel>
        </form>
    </body>
    </html>
    WebForm1.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.Threading.Tasks;
    using Ext.Net;
    using ClosedXML;
    using System.IO;
    using System.Data.SqlClient;
    using System.Data;
    
    namespace ICDMSAdmin.Pages
    {
        public partial class WebForm1 : System.Web.UI.Page
        {
    
            public int ctid = 1058;
    
            protected void Page_Load(object sender, EventArgs e)
            {
                if (ctid > 0)
                {
    
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
                        SqlCommand command = new SqlCommand("GetCRFDataForAnalysis", sqlConnection);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandTimeout = 0; //infinite waiting time
                        command.Parameters.AddWithValue("@Ctid", ctid);
                        command.Parameters.AddWithValue("@SId", DBNull.Value);
                        command.Parameters.AddWithValue("@PId", DBNull.Value);
                        SqlDataAdapter sda = new SqlDataAdapter();
                        command.Connection = sqlConnection;
                        sda.SelectCommand = command;
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        ds.Tables[0].TableName = "Database";
                        ds.Tables[1].TableName = "DBIndex";
    
                        DAL.DataContext dc = new DAL.DataContext();
    
                        var SelTrial = (from i in dc.CTs
                                        where i.id == ctid
                                        select i.Name).FirstOrDefault();
    
                        ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
                        foreach (DataTable dt in ds.Tables)
                        {
                            wb.Worksheets.Add(dt);
                        }
    
                        string fname = "attachment;filename=\"Export:";
                        string ctname = SelTrial.ToString();
                        string ending = ".xlsx\"";
    
                        HttpResponse httpResponse = Response;
                        httpResponse.Clear();
                        httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        //Provide you file name here
                        httpResponse.AddHeader("content-disposition", fname + ctname + ending);
    
                        // Flush the workbook to the Response.OutputStream
                        using (MemoryStream memoryStream = new MemoryStream())
                        {
                            wb.SaveAs(memoryStream);
                            memoryStream.WriteTo(httpResponse.OutputStream);
                            memoryStream.Close();
                        }
    
                        httpResponse.End();
    
                }
                else
                {
                    X.Msg.Alert("Warning", "Please select a Study first").Show();
                }
            }
        }
    }
    Last edited by atroul; Mar 08, 2017 at 8:51 AM.
  8. #8
    Hello @atroul! Thanks for the test case!

    In the example above, have you tried changing line 29 on your ToExcel.ascx to use App.WndwToExcel instead of #{WndwToExcel}?

    It really looks like the problem lies there.
    Fabrício Murta
    Developer & Support Expert

Similar Threads

  1. [CLOSED] Export To Excel with multiple sheets from Stored Procedure
    By atroul in forum 4.x Legacy Premium Help
    Replies: 6
    Last Post: Mar 10, 2017, 6:06 PM
  2. Parallel message bus
    By mhd in forum 2.x Help
    Replies: 0
    Last Post: Aug 06, 2014, 8:34 PM
  3. Replies: 12
    Last Post: Oct 03, 2011, 9:59 PM
  4. Replies: 11
    Last Post: Feb 06, 2009, 7:45 AM
  5. Sqldatasource - Update With Stored Procedure
    By Tbaseflug in forum 1.x Help
    Replies: 0
    Last Post: Jan 20, 2009, 8:51 PM

Tags for this Thread

Posting Permissions