PageRenderTime 51ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 0ms

/Current/ODX.Core/Pager.cs

#
C# | 380 lines | 268 code | 49 blank | 63 comment | 45 complexity | 590901792580a22410b736fa3a3fa6b8 MD5 | raw file
  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. namespace ODX.Core
  6. {
  7. /// <summary>
  8. /// <see cref="Pager{T}"/> can query underlying data source with free SQL query or using a simple filter.
  9. /// <see cref="Pager{T}"/> with SQL can be executen on the SQL data source only.
  10. /// <see cref="Pager{T}"/> with filter could be executed on any data source and even on the disconnected <see cref="Session"/>
  11. /// (Session with no any <see cref="IDataProvider"/> assigned).
  12. /// </summary>
  13. public enum PagerFilterType
  14. {
  15. /// <summary>Filter-based <see cref="Pager{T}"/></summary>
  16. Filter,
  17. /// <summary>SQL-based <see cref="Pager{T}"/></summary>
  18. Select
  19. }
  20. /// <summary>
  21. /// Supports paging and paged sequential (cursor-like) data source quering without connected cursors.
  22. /// </summary>
  23. /// <typeparam name="T">Tyoe of objects, returned by <see cref="Pager{T}"/></typeparam>
  24. public class Pager<T> : IEnumerable<T> where T : Entity
  25. {
  26. private struct SF
  27. {
  28. public string field;
  29. public bool desc;
  30. }
  31. private readonly Session session;
  32. private readonly string filterOrSelect;
  33. private readonly int pageSize;
  34. private T lastEntity;
  35. private T firstEntity;
  36. private SF[] srt;
  37. private readonly object[] listFilterParameters;
  38. private readonly PagerFilterType filterType;
  39. private string alias = string.Empty;
  40. /// <summary>Creates <see cref="Pager{T}"/> object</summary>
  41. public Pager(Session session, string filterOrSelect, string sort, int pageSize, params object[] listFilterParameters)
  42. : this(session, filterOrSelect, sort, pageSize, PagerFilterType.Filter, listFilterParameters)
  43. {
  44. }
  45. /// <summary>Creates <see cref="Pager{T}"/> object</summary>
  46. public Pager(Session session, string filterOrSelect, string sort, int pageSize, PagerFilterType filterType, params object[] listFilterParameters)
  47. : this(session, filterOrSelect, sort, pageSize, filterType)
  48. {
  49. this.listFilterParameters = listFilterParameters;
  50. }
  51. /// <summary>Creates <see cref="Pager{T}"/> object</summary>
  52. public Pager(Session session, string filterOrSelect, string sort, int pageSize)
  53. : this(session, filterOrSelect, sort, pageSize, PagerFilterType.Filter)
  54. {
  55. }
  56. /// <summary>Creates <see cref="Pager{T}"/> object</summary>
  57. public Pager(Session session, string filterOrSelect, string sort, int pageSize, PagerFilterType filterType)
  58. {
  59. this.session = session;
  60. this.filterOrSelect = filterOrSelect;
  61. this.pageSize = pageSize;
  62. this.filterType = filterType;
  63. if (sort == null || sort.Trim().Length == 0)
  64. sort = "ID";
  65. else
  66. sort = sort + ", ID";
  67. string[] fields = sort.Split(',');
  68. srt = new SF[fields.Length];
  69. for (int i = 0; i < srt.Length; i++ )
  70. {
  71. if (fields[i].Trim().ToUpper().EndsWith(" DESC"))
  72. srt[i].desc = true;
  73. else
  74. srt[i].desc = false;
  75. srt[i].field = fields[i].Trim().Split(' ')[0];
  76. }
  77. }
  78. /// <summary>
  79. /// If free SQL is used to create a <see cref="Pager{T}"/>
  80. /// and a number of tables were joined to create appropriate query
  81. /// you should specify an alias name you gave to the main table
  82. /// which fields will be selected.
  83. /// </summary>
  84. /// <remarks>
  85. /// Assume we have an addresses database in which every Person assigned one ore more addresses.
  86. /// if we want to discover addresses of people elder a particular age we can use the following SQL
  87. /// <code>
  88. /// SELECT a.* FROM Adress a INNER JOIN Person p ON p.ID = a.PersonID
  89. /// WHERE p.BirthDate > ?
  90. /// </code>
  91. /// In this case <see cref="Alias"/> should be set to
  92. /// <code>
  93. /// pager.Alias = "a.";
  94. /// </code>
  95. /// </remarks>
  96. public string Alias
  97. {
  98. get { return alias; }
  99. set { alias = value ?? string.Empty; }
  100. }
  101. private NullsPosition NullsPosition
  102. {
  103. get
  104. {
  105. if ( session.DataProvider == null )
  106. return NullsPosition.Minimum;
  107. NullsPosition pos = session.DataProvider.NullsPosition;
  108. if ( pos == NullsPosition.Undefined )
  109. throw new OdxException(
  110. "Can't page query: Nulls relative position is undefined for the data provider!!!");
  111. return pos;
  112. }
  113. }
  114. private static string BuildClause(string field, string sign)
  115. {
  116. return string.Format("{0} {1} ?", field, sign);
  117. }
  118. private string GetSort(bool forward)
  119. {
  120. List<string> sort = new List<string>();
  121. foreach ( SF sf in srt )
  122. {
  123. if ( forward ^ sf.desc )
  124. sort.Add(alias + sf.field);
  125. else
  126. sort.Add(alias + sf.field + " DESC");
  127. }
  128. return string.Join(", ", sort.ToArray());
  129. }
  130. private string ForwardSort {get{ return GetSort(true); }}
  131. private string BackwardSort {get{ return GetSort(false); }}
  132. private string GetFilter(bool forward, out object[] values)
  133. {
  134. Entity e = forward ? lastEntity : firstEntity;
  135. List<string> F = new List<string>();
  136. List<object> pValues = new List<object>();
  137. for (int i = 0; i < srt.Length && e != null; i++)
  138. {
  139. List<string> f = new List<string>();
  140. for (int j = 0; j <= i; j++)
  141. {
  142. object value = e.GetProperty(srt[j].field);
  143. if ( j < i )
  144. {
  145. if (value == null || value is DBNull)
  146. f.Add(alias + srt[j].field + " IS NULL");
  147. else
  148. {
  149. f.Add(BuildClause(alias + srt[j].field, "="));
  150. pValues.Add(value);
  151. }
  152. }
  153. else
  154. {
  155. bool ascending = forward ^ srt[j].desc;
  156. bool nullsMin = NullsPosition == NullsPosition.Minimum;
  157. if (value == null || value is DBNull)
  158. {
  159. if ( !(ascending ^ nullsMin) )
  160. f.Add(alias + srt[j].field + " IS NOT NULL");
  161. else
  162. break;
  163. }
  164. else
  165. {
  166. string sign = ascending ? ">" : "<";
  167. string flt = BuildClause(alias + srt[j].field, sign);
  168. if ( ascending ^ nullsMin )
  169. f.Add(string.Format("( {0} OR {1})",
  170. flt,
  171. alias + srt[j].field + " IS NULL"));
  172. else
  173. f.Add(flt);
  174. pValues.Add(value);
  175. }
  176. }
  177. }
  178. if (f.Count > 0)
  179. F.Add("(" + string.Join(" AND ", f.ToArray()) + ")");
  180. }
  181. string filter = F.Count > 0 ? "(" + string.Join(" OR ", F.ToArray()) + ")" : string.Empty;
  182. switch(filterType)
  183. {
  184. case PagerFilterType.Filter:
  185. if (filterOrSelect != null)
  186. {
  187. if (filter.Length > 0)
  188. filter += " AND ";
  189. filter += "(" + filterOrSelect + ")";
  190. if (listFilterParameters != null)
  191. pValues.AddRange(listFilterParameters);
  192. }
  193. break;
  194. case PagerFilterType.Select:
  195. if ( filter.Length == 0 )
  196. filter = filterOrSelect.Replace("<PagerWhere>", "1=1");
  197. else
  198. filter = filterOrSelect.Replace("<PagerWhere>", filter);
  199. if (listFilterParameters != null)
  200. pValues.AddRange(listFilterParameters);
  201. break;
  202. }
  203. values = pValues.ToArray();
  204. return filter;
  205. }
  206. private void ClearFirstLast()
  207. {
  208. firstEntity = null;
  209. lastEntity = null;
  210. }
  211. private void SetFirstLast(ICollection<T> c)
  212. {
  213. if ( c.Count > 0 )
  214. {
  215. List<T> al = new List<T>(c);
  216. lastEntity = al[al.Count - 1];
  217. firstEntity = al[0];
  218. }
  219. else
  220. ClearFirstLast();
  221. }
  222. private ICollection<T> Select(string filter, string sort, int count, object[] parameters)
  223. {
  224. if (session.DataProvider != null)
  225. {
  226. IDataProvider provider = session.DataProvider;
  227. DataSet selection;
  228. if ( filterType == PagerFilterType.Filter )
  229. {
  230. string tableName = session.Pm.GetTypeTable(typeof(T));
  231. selection = provider.Select(tableName, filter, sort, count, parameters);
  232. }
  233. else
  234. {
  235. string tableName = session.Pm.GetTypeTable(typeof(T));
  236. selection = ((ISqlDataProvider)provider).SelectSql(filter + " ORDER BY " + sort, tableName, count, parameters);
  237. }
  238. return session.Merge<T>(selection);
  239. }
  240. return
  241. session.Select<T>(filter, sort, count, parameters);
  242. }
  243. /// <summary>
  244. /// Selects first N objects.
  245. /// </summary>
  246. /// <param name="count">Number of objects to retrieve</param>
  247. /// <returns>First N objects.</returns>
  248. public ICollection<T> SelectFirst(int count)
  249. {
  250. ClearFirstLast();
  251. object[] values;
  252. string filter = GetFilter(true, out values);
  253. ICollection<T> c = Select(filter, ForwardSort, count, values);
  254. SetFirstLast(c);
  255. return c;
  256. }
  257. /// <summary>
  258. /// Selects next N objects. Can be called only after <see cref="SelectFirst"/>.
  259. /// </summary>
  260. /// <param name="count">Number of objects to retrieve.</param>
  261. /// <returns>Next N objects.</returns>
  262. public ICollection<T> SelectNext(int count)
  263. {
  264. if (lastEntity == null)
  265. return new T[0];
  266. object[] values;
  267. string filter = GetFilter(true, out values);
  268. ICollection<T> c = Select(filter, ForwardSort, count, values);
  269. SetFirstLast(c);
  270. return c;
  271. }
  272. /// <summary>
  273. /// Select last N objects.
  274. /// </summary>
  275. /// <param name="count">Number of object to retrieve.</param>
  276. /// <returns>Last N objects</returns>
  277. public ICollection<T> SelectLast(int count)
  278. {
  279. ClearFirstLast();
  280. object[] values;
  281. string filter = GetFilter(false, out values);
  282. List<T> al = new List<T>(Select(filter, BackwardSort, count, values));
  283. al.Reverse();
  284. SetFirstLast(al);
  285. return al.ToArray();
  286. }
  287. /// <summary>
  288. /// Selects N previous objects
  289. /// </summary>
  290. /// <param name="count">Number of object to select.</param>
  291. /// <returns>Previous N objects.</returns>
  292. public ICollection<T> SelectPrev(int count)
  293. {
  294. if (firstEntity == null)
  295. return new T[0];
  296. object[] values;
  297. string filter = GetFilter(false, out values);
  298. List<T> al = new List<T>(Select(filter, BackwardSort, count, values));
  299. al.Reverse();
  300. SetFirstLast(al);
  301. return al.ToArray();
  302. }
  303. /// <summary>Returns first page</summary>
  304. public ICollection<T> First { get { return SelectFirst(pageSize); } }
  305. /// <summary>Returns next page</summary>
  306. public ICollection<T> Next { get { return SelectNext(pageSize); } }
  307. /// <summary>Returns last page</summary>
  308. public ICollection<T> Last { get { return SelectLast(pageSize); } }
  309. /// <summary>Returns previous page</summary>
  310. public ICollection<T> Prev { get { return SelectPrev(pageSize); } }
  311. IEnumerator<T> IEnumerable<T>.GetEnumerator()
  312. {
  313. ICollection<T> first = First;
  314. foreach (T t in first)
  315. yield return t;
  316. ICollection<T> next;
  317. while((next = Next).Count > 0 )
  318. foreach (T t in next)
  319. yield return t;
  320. }
  321. /// <summary>
  322. /// Gets an enumerator to interate through all the query result set
  323. /// reading data page by page.
  324. /// </summary>
  325. /// <returns>An iterator object, linked to the <see cref="Pager{T}"/> object.</returns>
  326. public IEnumerator GetEnumerator()
  327. {
  328. return ((IEnumerable<T>) this).GetEnumerator();
  329. }
  330. }
  331. }