PDA

View Full Version : [CLOSED] remote paging and sorting with SQL



marco.morreale
Apr 26, 2012, 3:05 PM
What is the best way to get remote paging and sorting with SQL?
Can you provide an exampe?
Thanks in advance
Marco

Daniil
Apr 26, 2012, 4:54 PM
Hi,

Please investigate the following examples:
http://examples2.ext.net/#/GridPanel/Paging_and_Sorting/Page/
http://examples2.ext.net/#/GridPanel/Paging_and_Sorting/Handler/
http://examples2.ext.net/#/GridPanel/Paging_and_Sorting/JSON_WebService/
http://examples2.ext.net/#/GridPanel/Paging_and_Sorting/XML_WebService/
http://examples2.ext.net/#/GridPanel/DataSource_Controls/SqlDataSource/

marco.morreale
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.Connecti onStrings["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();

}

marco.morreale
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.Connecti onStrings["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();

}

Daniil
Apr 27, 2012, 1:30 PM
Could you clarify the problem? I mean what exactly should I check?

marco.morreale
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

Daniil
Apr 27, 2012, 1:42 PM
According to the response the JsonReader should be configured this way.

<ext:JsonReader Root="data" TotalProperty="total" />

marco.morreale
Apr 27, 2012, 1:56 PM
I did but still no record shown.
M

Daniil
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.

marco.morreale
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.Connecti onStrings["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

marco.morreale
Apr 27, 2012, 2:49 PM
Hi, I modified my project to work with Northwind db so you can check.

Just create this simple stored procedure

sql stored procedure




USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SP_B2B_GetOrderList] Script Date: 04/27/2012 14:31:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Marco Morreale
-- Create date: 27 aprile 2012
-- Description: Restituisce la lista ordini
-- =============================================
ALTER PROCEDURE [dbo].[GetOrderList]
@Start int
,@Limit int
,@GridFilters nvarchar(50)
,@CustomerNo nvarchar(20)

AS
BEGIN
DECLARE @FirstRow int
DECLARE @LastRow int

SET @FirstRow = (@Start -1) * @Limit + 1
SET @LastRow = @FirstRow + @Limit -1

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC, OrderID DESC)AS Riga
,OrderID
,OrderDate
,RequiredDate
,ShippedDate
,CustomerID
,ShipName
,ShipCity
FROM Orders
WHERE CustomerID = @CustomerNo OR @CustomerNo is NULL
) A
WHERE Riga BETWEEN @FirstRow AND @LastRow
Order By Riga
END



.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.TotalRecords" />
</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="OrderID" />
<ext:ModelField Name="ShipName" />
<ext:ModelField Name="ShipCity" />
<ext:ModelField Name="OrderDate" Type="Date" />
</Fields>
</ext:Model>
</Model>
<Sorters>
<%--<ext:DataSorter Property="CodiceCliente" Direction="ASC" />--%>
<ext:DataSorter Property="OrderID" Direction="ASC" />
</Sorters>
</ext:Store>
</Store>
<ColumnModel runat="server">
<Columns>
<ext:Column runat="server" Text="OrderID" DataIndex="OrderID" Sortable="true" />
<ext:Column runat="server" Text="Codice Cliente" DataIndex="ShipName" Sortable="true" />
<ext:Column runat="server" Text="Name" DataIndex="ShipCity" Flex="1" />
<ext:DateColumn runat="server" Text="Data" DataIndex="OrderDate" />
</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.Connecti onStrings["NorthwindConnectionString"].ConnectionString;
DataTable dt = new DataTable();

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

using (SqlCommand myCommand = new SqlCommand("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", "SAVEA"));
using (SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand))
{
myAdapter.Fill(dt);
}
}
}



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;
}

Daniil
Apr 27, 2012, 2:55 PM
I just realized that a response is a string.

public string getSalesHeaderList(...)

If you want to use StoreResponseData, it should look this way.

<ext:JsonReader Root="data" TotalProperty="total" />
and

[WebMethod]
public void GetTestData()
{
StoreResponseData storeResponse = new StoreResponseData();
storeResponse.Data = JSON.Serialize(dataTable);
storeResponse.Total = 100;
storeResponse.Return();
}

Or you can return an object like this.

<ext:JsonReader Root="d.data" TotalProperty="d.total" />
and

[WebMethod]
public object GetTestData()
{
return new
{
data = dataTable,
total = 100
};
}

EDIT: We would recommend to don't use StoreResponseData in that case.

marco.morreale
Apr 27, 2012, 3:39 PM
Hi,
I modified the web service as follows but now I get the error:


"A circular reference was detected while serializing an object of type \u0027System.Reflection.RuntimeModule\u0027.","StackTrace":" in System.Web.Script.Serialization.JavaScriptSerializ er.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)\r\n in System.Web.Script.Serialization.JavaScriptSerializ er.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)\r\n in System.Web.Script.Serialization.JavaScriptSerializ er.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)\r\n in System.Web.Script.Serialization.JavaScriptSerializ er.SerializeValueInternal(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)\r\n in System.Web.Script.Serialization.JavaScriptSerializ er.SerializeValue(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)\r\n in System.Web.Script.Serialization.JavaScriptSerializ er.SerializeCustomObject(Object o, StringBuilder sb, Int32 depth, Hashtable objectsInUse, SerializationFormat serializationFormat)\r\n in..... "




[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public object getSalesHeaderList(string customerNo, int start, int limit, string sort, string gridfilters)
{
string strConn = System.Configuration.ConfigurationManager.Connecti onStrings["NorthwindConnectionString"].ConnectionString;
DataTable dt = new DataTable();
int total = 100;

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

using (SqlCommand myCommand = new SqlCommand("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", "SAVEA"));

// Return value as parameter
SqlParameter returnValue = new SqlParameter("total", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(returnValue);

using (SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand))
{
myAdapter.Fill(dt);
}

total = (int)returnValue.Value;
}
}

return new
{
data = dt,
total = total
};

}

Daniil
Apr 27, 2012, 4:02 PM
Confirmed, a DataTable cannot be sent from a WebService that way.

You should return a list (or array) objects.

return new
{
data = /* a list (or an array) of objects */,
total = total
};

Something like this

Example

return new
{
data = new object[]
{
new
{
Ring = 1,
AnotherProperty = "value"
},
new
{
Ring = 1,
AnotherProperty = "value"
},
},
total = 100
};

So, you need to convert DataTable to a list (or an array) of objects.