EntitySpaces version: EntitySpaces 2007 Developer - Release 2007.0.0913.0
MyGeneration version: 1.2.0.7
Database: SQL Server 2005 (SP2)
Basically I would like to populate a Gridview from a collection that has been populated by stored procedure and still allow paging and sorting.
I have spent a couple of hours looking through the forums, but can't find a way to get this working.
Here is the stored procedure, which does the paging and handles the sorting using a dynamic order by clause:
Code:
1 CREATE PROCEDURE [dbo].[spSelectCompaniesNotInAccountGroup]
2 @AccountGroupID [uniqueidentifier],
3 @PageSize INT,
4 @PageNumber INT,
5 @OrderBy VARCHAR(255)
6 AS
7 SET NOCOUNT ON;
8
9 -- Set up for paging
10 WITH [Results] AS
11 (
12 SELECT R.*, ROW_NUMBER()
13 OVER
14 (
15 ORDER BY
16 CASE @OrderBy
17 WHEN 'Company__PartyID' THEN Cast([Company__PartyID] AS VARCHAR)
18 WHEN 'Company__Party__PartyID' THEN Cast([Company__Party__PartyID] AS VARCHAR)
19 WHEN 'Company__Party__ParentPartyID' THEN Cast([Company__Party__ParentPartyID] AS VARCHAR)
20 WHEN 'Company__Party__PartyTypeID' THEN Cast([Company__Party__PartyTypeID] AS VARCHAR)
21 WHEN 'Company__Party__PartyType__LookupID' THEN Cast([Company__Party__PartyType__LookupID] AS VARCHAR)
22 WHEN 'Company__Party__PartyType__ParentLookupID' THEN Cast([Company__Party__PartyType__ParentLookupID] AS VARCHAR)
23 WHEN 'Company__Party__PartyType__HierarchyLevel' THEN Cast([Company__Party__PartyType__HierarchyLevel] AS VARCHAR)
24 WHEN 'Company__Party__PartyType__Name' THEN Cast([Company__Party__PartyType__Name] AS VARCHAR)
25 WHEN 'Company__Party__PartyType__Description' THEN Cast([Company__Party__PartyType__Description] AS VARCHAR)
26 WHEN 'Company__Party__PartyType__Code' THEN Cast([Company__Party__PartyType__Code] AS VARCHAR)
27 WHEN 'Company__Party__PartyType__Active' THEN Cast([Company__Party__PartyType__Active] AS VARCHAR)
28 WHEN 'Company__Party__PartyType__CreatedBy' THEN Cast([Company__Party__PartyType__CreatedBy] AS VARCHAR)
29 WHEN 'Company__Party__PartyType__CreatedDate' THEN Cast([Company__Party__PartyType__CreatedDate] AS VARCHAR)
30 WHEN 'Company__Party__PartyType__LastUpdatedBy' THEN Cast([Company__Party__PartyType__LastUpdatedBy] AS VARCHAR)
31 WHEN 'Company__Party__PartyType__LastUpdated' THEN Cast([Company__Party__PartyType__LastUpdated] AS VARCHAR)
32 WHEN 'Company__Party__HierarchyLevel' THEN Cast([Company__Party__HierarchyLevel] AS VARCHAR)
33 WHEN 'Company__Party__CreatedBy' THEN Cast([Company__Party__CreatedBy] AS VARCHAR)
34 WHEN 'Company__Party__CreatedDate' THEN Cast([Company__Party__CreatedDate] AS VARCHAR)
35 WHEN 'Company__Party__LastUpdatedBy' THEN Cast([Company__Party__LastUpdatedBy] AS VARCHAR)
36 WHEN 'Company__Party__LastUpdated' THEN Cast([Company__Party__LastUpdated] AS VARCHAR)
37 WHEN 'Company__CurrencyID' THEN Cast([Company__CurrencyID] AS VARCHAR)
38 WHEN 'Company__Currency__CurrencyID' THEN Cast([Company__Currency__CurrencyID] AS VARCHAR)
39 WHEN 'Company__Currency__Name' THEN Cast([Company__Currency__Name] AS VARCHAR)
40 WHEN 'Company__Currency__Description' THEN Cast([Company__Currency__Description] AS VARCHAR)
41 WHEN 'Company__Currency__ISOCode' THEN Cast([Company__Currency__ISOCode] AS VARCHAR)
42 WHEN 'Company__Currency__CreatedBy' THEN Cast([Company__Currency__CreatedBy] AS VARCHAR)
43 WHEN 'Company__Currency__CreatedDate' THEN Cast([Company__Currency__CreatedDate] AS VARCHAR)
44 WHEN 'Company__Currency__LastUpdatedBy' THEN Cast([Company__Currency__LastUpdatedBy] AS VARCHAR)
45 WHEN 'Company__Currency__LastUpdated' THEN Cast([Company__Currency__LastUpdated] AS VARCHAR)
46 WHEN 'Company__AccountManagerID' THEN Cast([Company__AccountManagerID] AS VARCHAR)
47 WHEN 'Company__AccountManager__PartyID' THEN Cast([Company__AccountManager__PartyID] AS VARCHAR)
48 WHEN 'Company__AccountManager__Party__PartyID' THEN Cast([Company__AccountManager__Party__PartyID] AS VARCHAR)
49 WHEN 'Company__AccountManager__Party__ParentPartyID' THEN Cast([Company__AccountManager__Party__ParentPartyID] AS VARCHAR)
50 WHEN 'Company__AccountManager__Party__PartyTypeID' THEN Cast([Company__AccountManager__Party__PartyTypeID] AS VARCHAR)
51 WHEN 'Company__AccountManager__Party__HierarchyLevel' THEN Cast([Company__AccountManager__Party__HierarchyLevel] AS VARCHAR)
52 WHEN 'Company__AccountManager__Party__CreatedBy' THEN Cast([Company__AccountManager__Party__CreatedBy] AS VARCHAR)
53 WHEN 'Company__AccountManager__Party__CreatedDate' THEN Cast([Company__AccountManager__Party__CreatedDate] AS VARCHAR)
54 WHEN 'Company__AccountManager__Party__LastUpdatedBy' THEN Cast([Company__AccountManager__Party__LastUpdatedBy] AS VARCHAR)
55 WHEN 'Company__AccountManager__Party__LastUpdated' THEN Cast([Company__AccountManager__Party__LastUpdated] AS VARCHAR)
56 WHEN 'Company__AccountManager__ContactTypeID' THEN Cast([Company__AccountManager__ContactTypeID] AS VARCHAR)
57 WHEN 'Company__AccountManager__ContactType__LookupID' THEN Cast([Company__AccountManager__ContactType__LookupID] AS VARCHAR)
58 WHEN 'Company__AccountManager__ContactType__ParentLookupID' THEN Cast([Company__AccountManager__ContactType__ParentLookupID] AS VARCHAR)
59 WHEN 'Company__AccountManager__ContactType__HierarchyLevel' THEN Cast([Company__AccountManager__ContactType__HierarchyLevel] AS VARCHAR)
60 WHEN 'Company__AccountManager__ContactType__Name' THEN Cast([Company__AccountManager__ContactType__Name] AS VARCHAR)
61 WHEN 'Company__AccountManager__ContactType__Description' THEN Cast([Company__AccountManager__ContactType__Description] AS VARCHAR)
62 WHEN 'Company__AccountManager__ContactType__Code' THEN Cast([Company__AccountManager__ContactType__Code] AS VARCHAR)
63 WHEN 'Company__AccountManager__ContactType__Active' THEN Cast([Company__AccountManager__ContactType__Active] AS VARCHAR)
64 WHEN 'Company__AccountManager__ContactType__CreatedBy' THEN Cast([Company__AccountManager__ContactType__CreatedBy] AS VARCHAR)
65 WHEN 'Company__AccountManager__ContactType__CreatedDate' THEN Cast([Company__AccountManager__ContactType__CreatedDate] AS VARCHAR)
66 WHEN 'Company__AccountManager__ContactType__LastUpdatedBy' THEN Cast([Company__AccountManager__ContactType__LastUpdatedBy] AS VARCHAR)
67 WHEN 'Company__AccountManager__ContactType__LastUpdated' THEN Cast([Company__AccountManager__ContactType__LastUpdated] AS VARCHAR)
68 WHEN 'Company__AccountManager__Title' THEN Cast([Company__AccountManager__Title] AS VARCHAR)
69 WHEN 'Company__AccountManager__Forenames' THEN Cast([Company__AccountManager__Forenames] AS VARCHAR)
70 WHEN 'Company__AccountManager__Surname' THEN Cast([Company__AccountManager__Surname] AS VARCHAR)
71 WHEN 'Company__AccountManager__JobTitle' THEN Cast([Company__AccountManager__JobTitle] AS VARCHAR)
72 WHEN 'Company__AccountManager__CreatedBy' THEN Cast([Company__AccountManager__CreatedBy] AS VARCHAR)
73 WHEN 'Company__AccountManager__CreatedDate' THEN Cast([Company__AccountManager__CreatedDate] AS VARCHAR)
74 WHEN 'Company__AccountManager__LastUpdatedBy' THEN Cast([Company__AccountManager__LastUpdatedBy] AS VARCHAR)
75 WHEN 'Company__AccountManager__LastUpdated' THEN Cast([Company__AccountManager__LastUpdated] AS VARCHAR)
76 WHEN 'Company__DisplayName' THEN Cast([Company__DisplayName] AS VARCHAR)
77 WHEN 'Company__BillingName' THEN Cast([Company__BillingName] AS VARCHAR)
78 WHEN 'Company__CreatedBy' THEN Cast([Company__CreatedBy] AS VARCHAR)
79 WHEN 'Company__CreatedBy__UserID' THEN Cast([Company__CreatedBy__UserID] AS VARCHAR)
80 WHEN 'Company__CreatedBy__PartyID' THEN Cast([Company__CreatedBy__PartyID] AS VARCHAR)
81 WHEN 'Company__CreatedBy__Party__PartyID' THEN Cast([Company__CreatedBy__Party__PartyID] AS VARCHAR)
82 WHEN 'Company__CreatedBy__Party__ContactTypeID' THEN Cast([Company__CreatedBy__Party__ContactTypeID] AS VARCHAR)
83 WHEN 'Company__CreatedBy__Party__Title' THEN Cast([Company__CreatedBy__Party__Title] AS VARCHAR)
84 WHEN 'Company__CreatedBy__Party__Forenames' THEN Cast([Company__CreatedBy__Party__Forenames] AS VARCHAR)
85 WHEN 'Company__CreatedBy__Party__Surname' THEN Cast([Company__CreatedBy__Party__Surname] AS VARCHAR)
86 WHEN 'Company__CreatedBy__Party__JobTitle' THEN Cast([Company__CreatedBy__Party__JobTitle] AS VARCHAR)
87 WHEN 'Company__CreatedBy__Party__CreatedBy' THEN Cast([Company__CreatedBy__Party__CreatedBy] AS VARCHAR)
88 WHEN 'Company__CreatedBy__Party__CreatedDate' THEN Cast([Company__CreatedBy__Party__CreatedDate] AS VARCHAR)
89 WHEN 'Company__CreatedBy__Party__LastUpdatedBy' THEN Cast([Company__CreatedBy__Party__LastUpdatedBy] AS VARCHAR)
90 WHEN 'Company__CreatedBy__Party__LastUpdated' THEN Cast([Company__CreatedBy__Party__LastUpdated] AS VARCHAR)
91 WHEN 'Company__CreatedBy__Username' THEN Cast([Company__CreatedBy__Username] AS VARCHAR)
92 WHEN 'Company__CreatedBy__Password' THEN Cast([Company__CreatedBy__Password] AS VARCHAR)
93 WHEN 'Company__CreatedBy__LastLoggedIn' THEN Cast([Company__CreatedBy__LastLoggedIn] AS VARCHAR)
94 WHEN 'Company__CreatedBy__Active' THEN Cast([Company__CreatedBy__Active] AS VARCHAR)
95 WHEN 'Company__CreatedBy__CreatedBy' THEN Cast([Company__CreatedBy__CreatedBy] AS VARCHAR)
96 WHEN 'Company__CreatedBy__CreatedDate' THEN Cast([Company__CreatedBy__CreatedDate] AS VARCHAR)
97 WHEN 'Company__CreatedBy__LastUpdatedBy' THEN Cast([Company__CreatedBy__LastUpdatedBy] AS VARCHAR)
98 WHEN 'Company__CreatedBy__LastUpdated' THEN Cast([Company__CreatedBy__LastUpdated] AS VARCHAR)
99 WHEN 'Company__CreatedDate' THEN Cast([Company__CreatedDate] AS VARCHAR)
100 WHEN 'Company__LastUpdatedBy' THEN Cast([Company__LastUpdatedBy] AS VARCHAR)
101 WHEN 'Company__LastUpdatedBy__UserID' THEN Cast([Company__LastUpdatedBy__UserID] AS VARCHAR)
102 WHEN 'Company__LastUpdatedBy__PartyID' THEN Cast([Company__LastUpdatedBy__PartyID] AS VARCHAR)
103 WHEN 'Company__LastUpdatedBy__Party__PartyID' THEN Cast([Company__LastUpdatedBy__Party__PartyID] AS VARCHAR)
104 WHEN 'Company__LastUpdatedBy__Party__ContactTypeID' THEN Cast([Company__LastUpdatedBy__Party__ContactTypeID] AS VARCHAR)
105 WHEN 'Company__LastUpdatedBy__Party__Title' THEN Cast([Company__LastUpdatedBy__Party__Title] AS VARCHAR)
106 WHEN 'Company__LastUpdatedBy__Party__Forenames' THEN Cast([Company__LastUpdatedBy__Party__Forenames] AS VARCHAR)
107 WHEN 'Company__LastUpdatedBy__Party__Surname' THEN Cast([Company__LastUpdatedBy__Party__Surname] AS VARCHAR)
108 WHEN 'Company__LastUpdatedBy__Party__JobTitle' THEN Cast([Company__LastUpdatedBy__Party__JobTitle] AS VARCHAR)
109 WHEN 'Company__LastUpdatedBy__Party__CreatedBy' THEN Cast([Company__LastUpdatedBy__Party__CreatedBy] AS VARCHAR)
110 WHEN 'Company__LastUpdatedBy__Party__CreatedDate' THEN Cast([Company__LastUpdatedBy__Party__CreatedDate] AS VARCHAR)
111 WHEN 'Company__LastUpdatedBy__Party__LastUpdatedBy' THEN Cast([Company__LastUpdatedBy__Party__LastUpdatedBy] AS VARCHAR)
112 WHEN 'Company__LastUpdatedBy__Party__LastUpdated' THEN Cast([Company__LastUpdatedBy__Party__LastUpdated] AS VARCHAR)
113 WHEN 'Company__LastUpdatedBy__Username' THEN Cast([Company__LastUpdatedBy__Username] AS VARCHAR)
114 WHEN 'Company__LastUpdatedBy__Password' THEN Cast([Company__LastUpdatedBy__Password] AS VARCHAR)
115 WHEN 'Company__LastUpdatedBy__LastLoggedIn' THEN Cast([Company__LastUpdatedBy__LastLoggedIn] AS VARCHAR)
116 WHEN 'Company__LastUpdatedBy__Active' THEN Cast([Company__LastUpdatedBy__Active] AS VARCHAR)
117 WHEN 'Company__LastUpdatedBy__CreatedBy' THEN Cast([Company__LastUpdatedBy__CreatedBy] AS VARCHAR)
118 WHEN 'Company__LastUpdatedBy__CreatedDate' THEN Cast([Company__LastUpdatedBy__CreatedDate] AS VARCHAR)
119 WHEN 'Company__LastUpdatedBy__LastUpdatedBy' THEN Cast([Company__LastUpdatedBy__LastUpdatedBy] AS VARCHAR)
120 WHEN 'Company__LastUpdatedBy__LastUpdated' THEN Cast([Company__LastUpdatedBy__LastUpdated] AS VARCHAR)
121 WHEN 'Company__LastUpdated' THEN Cast([Company__LastUpdated] AS VARCHAR)
122 ELSE Cast([Company__BillingName] AS VARCHAR)
123 END ASC
124 ) AS RowNumber
125 FROM
126 (
127 SELECT
128 [Company__PartyID],
129 [Company__Party__PartyID],
130 [Company__Party__ParentPartyID],
131 [Company__Party__PartyTypeID],
132 [Company__Party__PartyType__LookupID],
133 [Company__Party__PartyType__ParentLookupID],
134 [Company__Party__PartyType__HierarchyLevel],
135 [Company__Party__PartyType__Name],
136 [Company__Party__PartyType__Description],
137 [Company__Party__PartyType__Code],
138 [Company__Party__PartyType__Active],
139 [Company__Party__PartyType__CreatedBy],
140 [Company__Party__PartyType__CreatedDate],
141 [Company__Party__PartyType__LastUpdatedBy],
142 [Company__Party__PartyType__LastUpdated],
143 [Company__Party__HierarchyLevel],
144 [Company__Party__CreatedBy],
145 [Company__Party__CreatedDate],
146 [Company__Party__LastUpdatedBy],
147 [Company__Party__LastUpdated],
148 [Company__CurrencyID],
149 [Company__Currency__CurrencyID],
150 [Company__Currency__Name],
151 [Company__Currency__Description],
152 [Company__Currency__ISOCode],
153 [Company__Currency__CreatedBy],
154 [Company__Currency__CreatedDate],
155 [Company__Currency__LastUpdatedBy],
156 [Company__Currency__LastUpdated],
157 [Company__AccountManagerID],
158 [Company__AccountManager__PartyID],
159 [Company__AccountManager__Party__PartyID],
160 [Company__AccountManager__Party__ParentPartyID],
161 [Company__AccountManager__Party__PartyTypeID],
162 [Company__AccountManager__Party__HierarchyLevel],
163 [Company__AccountManager__Party__CreatedBy],
164 [Company__AccountManager__Party__CreatedDate],
165 [Company__AccountManager__Party__LastUpdatedBy],
166 [Company__AccountManager__Party__LastUpdated],
167 [Company__AccountManager__ContactTypeID],
168 [Company__AccountManager__ContactType__LookupID],
169 [Company__AccountManager__ContactType__ParentLookupID],
170 [Company__AccountManager__ContactType__HierarchyLevel],
171 [Company__AccountManager__ContactType__Name],
172 [Company__AccountManager__ContactType__Description],
173 [Company__AccountManager__ContactType__Code],
174 [Company__AccountManager__ContactType__Active],
175 [Company__AccountManager__ContactType__CreatedBy],
176 [Company__AccountManager__ContactType__CreatedDate],
177 [Company__AccountManager__ContactType__LastUpdatedBy],
178 [Company__AccountManager__ContactType__LastUpdated],
179 [Company__AccountManager__Title],
180 [Company__AccountManager__Forenames],
181 [Company__AccountManager__Surname],
182 [Company__AccountManager__JobTitle],
183 [Company__AccountManager__CreatedBy],
184 [Company__AccountManager__CreatedDate],
185 [Company__AccountManager__LastUpdatedBy],
186 [Company__AccountManager__LastUpdated],
187 [Company__DisplayName],
188 [Company__BillingName],
189 [Company__CreatedBy],
190 [Company__CreatedBy__UserID],
191 [Company__CreatedBy__PartyID],
192 [Company__CreatedBy__Party__PartyID],
193 [Company__CreatedBy__Party__ContactTypeID],
194 [Company__CreatedBy__Party__Title],
195 [Company__CreatedBy__Party__Forenames],
196 [Company__CreatedBy__Party__Surname],
197 [Company__CreatedBy__Party__JobTitle],
198 [Company__CreatedBy__Party__CreatedBy],
199 [Company__CreatedBy__Party__CreatedDate],
200 [Company__CreatedBy__Party__LastUpdatedBy],
201 [Company__CreatedBy__Party__LastUpdated],
202 [Company__CreatedBy__Username],
203 [Company__CreatedBy__Password],
204 [Company__CreatedBy__LastLoggedIn],
205 [Company__CreatedBy__Active],
206 [Company__CreatedBy__CreatedBy],
207 [Company__CreatedBy__CreatedDate],
208 [Company__CreatedBy__LastUpdatedBy],
209 [Company__CreatedBy__LastUpdated],
210 [Company__CreatedDate],
211 [Company__LastUpdatedBy],
212 [Company__LastUpdatedBy__UserID],
213 [Company__LastUpdatedBy__PartyID],
214 [Company__LastUpdatedBy__Party__PartyID],
215 [Company__LastUpdatedBy__Party__ContactTypeID],
216 [Company__LastUpdatedBy__Party__Title],
217 [Company__LastUpdatedBy__Party__Forenames],
218 [Company__LastUpdatedBy__Party__Surname],
219 [Company__LastUpdatedBy__Party__JobTitle],
220 [Company__LastUpdatedBy__Party__CreatedBy],
221 [Company__LastUpdatedBy__Party__CreatedDate],
222 [Company__LastUpdatedBy__Party__LastUpdatedBy],
223 [Company__LastUpdatedBy__Party__LastUpdated],
224 [Company__LastUpdatedBy__Username],
225 [Company__LastUpdatedBy__Password],
226 [Company__LastUpdatedBy__LastLoggedIn],
227 [Company__LastUpdatedBy__Active],
228 [Company__LastUpdatedBy__CreatedBy],
229 [Company__LastUpdatedBy__CreatedDate],
230 [Company__LastUpdatedBy__LastUpdatedBy],
231 [Company__LastUpdatedBy__LastUpdated],
232 [Company__LastUpdated]
233 FROM [dbo].[vwCompany]
234 WHERE [Company__PartyID] NOT IN
235 (
236 SELECT PartyID
237 FROM [dbo].[AccountGroupParty]
238 WHERE AccountGroupID = @AccountGroupID
239 )
240 ) R
241 )
242
243 -- Handle the paging
244 SELECT *
245 FROM [Results]
246 WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
The custom load method that populates the Collection (which is based on view that performs all the joins) is here:
Code:
1 public bool LoadCompaniesNotInAccountGroup(Guid gAccountGroupID, int iPageSize, int iPageNumber, string sOrderBy)
2 {
3 esParameters oParameters = new esParameters();
4 oParameters.Add("AccountGroupID", gAccountGroupID);
5 oParameters.Add("PageSize", iPageSize);
6 oParameters.Add("PageNumber", iPageNumber);
7 oParameters.Add("OrderBy", sOrderBy);
8
9 return this.Load(esQueryType.StoredProcedure, "spSelectCompaniesNotInAccountGroup", oParameters);
10 }
And finally I set the esDataSource here:
Code:
1 protected void edsDataSource_esSelect(object sender, EntitySpaces.Web.esDataSourceSelectEventArgs e)
2 {
3 this._oCompanyCollection = new vwCompanyCollection();
4 this._oCompanyCollection.LoadCompaniesNotInAccountGroup(this._gAccountGroupID, this.gvwRecordList.PageSize, this.gvwRecordList.PageIndex + 1, this.gvwRecordList.SortExpression);
5
6 if (e.SortItems == null)
7 this._oCompanyCollection.Query.OrderBy(this._oCompanyCollection.Query.Company__DisplayName.Ascending);
8
9 e.Collection = this._oCompanyCollection;
10 }
The problem is with lines 6 and 7 above, since if they are not there I get the classic error 'The ranking function "ROW_NUMBER" must have an ORDER BY clause.', which makes no sense seeing as my stored procedure handles this itself.
However, with lines 6 and 7 added, the query ignores my stored procedure and brings back everything into the collection.
I'm sure it must be something I am doing incorrectly, but who knows what...Can anyone suggest anything?
Cheers
Jim
Jim