What's New?

[CLOSED] remote paging and sorting with SQL

Page 1 of 2 12 LastLast
  1. #1
    Apr 26, 2012, 3:05 PM

    [CLOSED] remote paging and sorting with SQL

    What is the best way to get remote paging and sorting with SQL?
    Can you provide an exampe?
    Thanks in advance
    Marco
    Last edited by Daniil; May 02, 2012 at 9:54 PM. Reason: [CLOSED]
  2. #2
    Apr 26, 2012, 4:54 PM
    Hi,

    Please investigate the following examples:
    https://examples2.ext.net/#/GridPane..._Sorting/Page/
    https://examples2.ext.net/#/GridPane...rting/Handler/
    https://examples2.ext.net/#/GridPane...ON_WebService/
    https://examples2.ext.net/#/GridPane...ML_WebService/
    https://examples2.ext.net/#/GridPane...SqlDataSource/
  3. #3
    Apr 27, 2012, 1:11 PM
    .aspx

    <%@ Page Language="C#"  %>

<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
<!DOCTYPE html>

<html>
<head runat="server">
    <title>GridPanel with JSON WebService - Ext.NET Examples</title>
</head>
<body>
    <form runat="server">
        <ext:ResourceManager runat="server" />

        <h1>GridPanel with JSON WebService</h1>
        
    <ext:GridPanel 
        runat="server"
        Title="Ordini di Vendita" 
        Frame="true"
        Height="300">
        <Store>
            <ext:Store runat="server" RemoteSort="true" PageSize="5">
                <Proxy>
                    <ext:AjaxProxy Json="true" Url="../WebServices/AjaxWebService.asmx/getSalesHeaderList">
                        <ActionMethods Read="POST" />                            
                        <Reader>
                            <ext:JsonReader Root="d.data" TotalProperty="d.total" />
                        </Reader>
                    </ext:AjaxProxy>
                </Proxy>
                <AutoLoadParams>
                    <ext:Parameter Name="start" Value="0" Mode="Raw" />
                    <ext:Parameter Name="limit" Value="5" Mode="Raw" />
                </AutoLoadParams>
                <Parameters>
                    <ext:StoreParameter Name="gridfilters" Value="" Mode="Value" />
                    <ext:StoreParameter Name="customerNo" Value="" Mode="Value" />
                </Parameters>
                <Model>
                    <ext:Model runat="server">
                        <Fields>
                            <ext:ModelField Name="Riga" Type="Int" />
                            <ext:ModelField Name="CodiceCliente" />
                            <ext:ModelField Name="Name" />
                            <ext:ModelField Name="Importo" Type="Float" />
                            <ext:ModelField Name="Stato" />
                            <ext:ModelField Name="DataOrdine" Type="Date" />
                        </Fields>
                    </ext:Model>
                </Model>
                <Sorters>
                    <ext:DataSorter Property="CodiceCliente" Direction="ASC" />
                </Sorters>
            </ext:Store>
        </Store>
        <ColumnModel runat="server">
            <Columns>  
                <ext:Column runat="server" Text="Riga" DataIndex="Riga" Sortable="true" />
                <ext:Column runat="server" Text="Codice Cliente" DataIndex="CodiceCliente" Sortable="true" />
                <ext:Column runat="server" Text="Name" DataIndex="Name" Flex="1" />
                <ext:NumberColumn runat="server" Text="Importo" DataIndex="Importo" />
                <ext:DateColumn runat="server" Text="Data" DataIndex="DataOrdine" />
                <ext:Column runat="server" Text="Stato" DataIndex="Stato"  />
            </Columns>
        </ColumnModel>                   
        <BottomBar>
            <ext:PagingToolbar 
                runat="server"                     
                />
        </BottomBar>
    </ext:GridPanel>
    </form>
</body>
</html>
    Web Service

            [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string getSalesHeaderList(string customerNo, int start, int limit, string sort, string gridfilters)
        {         


            string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["SQL_SCAMBIO_ConnectionString"].ConnectionString;
            DataTable dt = new DataTable();

            using (SqlConnection myConnection = new SqlConnection(strConn))
            {
                myConnection.Open();

                using (SqlCommand myCommand = new SqlCommand("SP_B2B_GetOrderList", myConnection))
                {
                    myCommand.CommandType = System.Data.CommandType.StoredProcedure;

                    myCommand.Parameters.Add(new SqlParameter("Start", start + 1));
                    myCommand.Parameters.Add(new SqlParameter("Limit", limit));
                    myCommand.Parameters.Add(new SqlParameter("GridFilters", DBNull.Value));
                    myCommand.Parameters.Add(new SqlParameter("CustomerNo", HttpContext.Current.Profile["CustomerCode"]));
                    using (SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand))
                    {
                        myAdapter.Fill(dt);
                    }
                }
            }

            StoreResponseData storeResponse = new StoreResponseData();
            storeResponse.Data = JSON.Serialize(dt);
            storeResponse.Total = 100;  // TO DO
            return storeResponse.ToString();

        }
  4. #4
    Apr 27, 2012, 1:13 PM
    Hi,
    please can you help me to find out what's wrong with my code?
    Thanks in advance
    Marco


    .aspx

    <%@ Page Language="C#"  %>

<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
<!DOCTYPE html>

<html>
<head runat="server">
    <title>GridPanel with JSON WebService - Ext.NET Examples</title>
</head>
<body>
    <form runat="server">
        <ext:ResourceManager runat="server" />

        <h1>GridPanel with JSON WebService</h1>
        
    <ext:GridPanel 
        runat="server"
        Title="Ordini di Vendita" 
        Frame="true"
        Height="300">
        <Store>
            <ext:Store runat="server" RemoteSort="true" PageSize="5">
                <Proxy>
                    <ext:AjaxProxy Json="true" Url="../WebServices/AjaxWebService.asmx/getSalesHeaderList">
                        <ActionMethods Read="POST" />                            
                        <Reader>
                            <ext:JsonReader Root="d.data" TotalProperty="d.total" />
                        </Reader>
                    </ext:AjaxProxy>
                </Proxy>
                <AutoLoadParams>
                    <ext:Parameter Name="start" Value="0" Mode="Raw" />
                    <ext:Parameter Name="limit" Value="5" Mode="Raw" />
                </AutoLoadParams>
                <Parameters>
                    <ext:StoreParameter Name="gridfilters" Value="" Mode="Value" />
                    <ext:StoreParameter Name="customerNo" Value="" Mode="Value" />
                </Parameters>
                <Model>
                    <ext:Model runat="server">
                        <Fields>
                            <ext:ModelField Name="Riga" Type="Int" />
                            <ext:ModelField Name="CodiceCliente" />
                            <ext:ModelField Name="Name" />
                            <ext:ModelField Name="Importo" Type="Float" />
                            <ext:ModelField Name="Stato" />
                            <ext:ModelField Name="DataOrdine" Type="Date" />
                        </Fields>
                    </ext:Model>
                </Model>
                <Sorters>
                    <ext:DataSorter Property="CodiceCliente" Direction="ASC" />
                </Sorters>
            </ext:Store>
        </Store>
        <ColumnModel runat="server">
            <Columns>  
                <ext:Column runat="server" Text="Riga" DataIndex="Riga" Sortable="true" />
                <ext:Column runat="server" Text="Codice Cliente" DataIndex="CodiceCliente" Sortable="true" />
                <ext:Column runat="server" Text="Name" DataIndex="Name" Flex="1" />
                <ext:NumberColumn runat="server" Text="Importo" DataIndex="Importo" />
                <ext:DateColumn runat="server" Text="Data" DataIndex="DataOrdine" />
                <ext:Column runat="server" Text="Stato" DataIndex="Stato"  />
            </Columns>
        </ColumnModel>                   
        <BottomBar>
            <ext:PagingToolbar 
                runat="server"                     
                />
        </BottomBar>
    </ext:GridPanel>
    </form>
</body>
</html>
    Web Service

            [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string getSalesHeaderList(string customerNo, int start, int limit, string sort, string gridfilters)
        {         

           // TO DO : filter handling

            string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["SQL_SCAMBIO_ConnectionString"].ConnectionString;
            DataTable dt = new DataTable();

            using (SqlConnection myConnection = new SqlConnection(strConn))
            {
                myConnection.Open();

                using (SqlCommand myCommand = new SqlCommand("SP_B2B_GetOrderList", myConnection))
                {
                    myCommand.CommandType = System.Data.CommandType.StoredProcedure;

                    myCommand.Parameters.Add(new SqlParameter("Start", start + 1));
                    myCommand.Parameters.Add(new SqlParameter("Limit", limit));
                    myCommand.Parameters.Add(new SqlParameter("GridFilters", DBNull.Value));
                    myCommand.Parameters.Add(new SqlParameter("CustomerNo", HttpContext.Current.Profile["CustomerCode"]));
                    using (SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand))
                    {
                        myAdapter.Fill(dt);
                    }
                }
            }

            StoreResponseData storeResponse = new StoreResponseData();
            storeResponse.Data = JSON.Serialize(dt);
            storeResponse.Total = 100;  // TO DO
            return storeResponse.ToString();

        }
  5. #5
    Apr 27, 2012, 1:30 PM
    Could you clarify the problem? I mean what exactly should I check?
  6. #6
    Apr 27, 2012, 1:37 PM
    Sorry, I didn't explain.

    The problem is that the grid doesn't show any record.
    I don't get any error, anyway.

    In debug I can see data are sent to the page by the web service, as follows:

    {data:[{"Riga":1,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8876,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":321.71},{"Riga":2,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8874,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":54.38},{"Riga":3,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8873,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":29.63},{"Riga":4,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8871,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":18.19},{"Riga":5,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8869,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":52.30}], total: 100}
    The problem is on rendering, I guess.

    M
  7. #7
    Apr 27, 2012, 1:42 PM
    According to the response the JsonReader should be configured this way.
    <ext:JsonReader Root="data" TotalProperty="total" />
  8. #8
    Apr 27, 2012, 1:56 PM
    I did but still no record shown.
    M
  9. #9
    Apr 27, 2012, 2:05 PM
    Did you post a whole response?

    Unfortunately, I can't test your code because I have no "SQL_SCAMBIO_ConnectionString" conntection string.

    If you'd provide, I will check.
  10. #10
    Apr 27, 2012, 2:27 PM
    Yes, I did post the whole response.

    Also, I tried to edit my code to get the same response format of the example "Paging_and_Sorting/JSON_WebService" . I mean:

    Web Service

            [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string getSalesHeaderList(string customerNo, int start, int limit, string sort, string gridfilters)
        {         
            string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["SQL_SCAMBIO_ConnectionString"].ConnectionString;
            DataTable dt = new DataTable();

            using (SqlConnection myConnection = new SqlConnection(strConn))
            {
                myConnection.Open();

                using (SqlCommand myCommand = new SqlCommand("SP_B2B_GetOrderList", myConnection))
                {
                    myCommand.CommandType = System.Data.CommandType.StoredProcedure;

                    myCommand.Parameters.Add(new SqlParameter("Start", start + 1));
                    myCommand.Parameters.Add(new SqlParameter("Limit", limit));
                    myCommand.Parameters.Add(new SqlParameter("GridFilters", DBNull.Value));
                    myCommand.Parameters.Add(new SqlParameter("CustomerNo", HttpContext.Current.Profile["CustomerCode"]));
                    using (SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand))
                    {
                        myAdapter.Fill(dt);
                    }
                }
            }

            // return string.Format("{{'data':{0}}}", JSON.Serialize(dt));
            //string JSONString = string.Format("{{\"d\":{{\"Data\":{0},\"TotalRecords\":{1}}}}}", JSON.Serialize(dt), 100);
            //return JSONString;

            StoreResponseData storeResponse = new StoreResponseData();
            storeResponse.Data = JSON.Serialize(dt);
            storeResponse.Total = 100;
            //string JSONString = storeResponse.ToString();
            string JSONString = string.Format("{{\"d\":{{\"Data\":{0},\"TotalRecords\":{1}}}}}", storeResponse.Data, storeResponse.Total);

            return JSONString;
        }
    Then I modified the .aspx as follows

    .aspx
    <ext:JsonReader Root="d.Data" TotalProperty="d.TotalRecords" />
    this way I got the following response:

    {"d":{"Data":[{"Riga":1,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8876,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":321.71},{"Riga":2,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8874,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":54.38},{"Riga":3,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8873,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":29.63},{"Riga":4,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8871,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":18.19},{"Riga":5,"DataOrdine":"2012-03-02T00:00:00","Stato":"Confermato","NrOrdine":8869,"CodiceCliente":"00004","Name":"SAUFLON PHARMACEUTICALS ++++++","Importo":52.30}],"TotalRecords":100}}
    But still no record is displayed in the grid.

    Db SQL_SCAMBIO_ConnectionString ha tables linked to other dbs so it is a little unconmfortable to send.
    M
    Last edited by Daniil; Apr 27, 2012 at 2:37 PM. Reason: Please use [CODE] tags for all code
Page 1 of 2 12 LastLast

Similar Threads

  1. [CLOSED] Remote paging/sorting/filtering on SQLDataSource for GridPanel
    By jchau in forum 1.x Help
    Replies: 6
    Last Post: Nov 03, 2012, 10:48 PM
  2. [CLOSED] Difference in Paging Toolbar behaviour between Ext.NET 1.x and 2 for Grid with remote (ashx) paging and local filtering
    By anup in forum 2.x Legacy Premium Help
    Replies: 11
    Last Post: Jun 13, 2012, 4:53 PM
  3. Find record with remote paging and sorting
    By schrovena in forum 1.x Help
    Replies: 1
    Last Post: Apr 26, 2011, 6:00 PM
  4. [CLOSED] Remote Sorting issue
    By vali1993 in forum 1.x Legacy Premium Help
    Replies: 8
    Last Post: Feb 18, 2011, 5:56 PM
  5. [CLOSED] GridPanel with Remote Filtering, Sorting and Paging (datatable)
    By CarWise in forum 1.x Legacy Premium Help
    Replies: 5
    Last Post: Jul 23, 2010, 8:52 AM

Posting Permissions