Skip to content

Commit 208c9f6

Browse files
richardhoptonrichardhopton
committed
Add support for multi paging - required for ASA8
1 parent 50f0f54 commit 208c9f6

11 files changed

Lines changed: 155 additions & 77 deletions

File tree

‎Simple.Data.Ado/AdoAdapter.cs‎

Lines changed: 77 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -97,10 +97,16 @@ public override IEnumerable<IDictionary<string, object>> RunQuery(SimpleQuery qu
9797
{
9898
if (query.Clauses.OfType<WithCountClause>().Any()) return RunQueryWithCount(query, out unhandledClauses);
9999

100+
var commandBuilders = this.GetQueryCommandBuilders(query, out unhandledClauses);
100101
var connection = _connectionProvider.CreateConnection();
101-
return new QueryBuilder(this).Build(query, out unhandledClauses)
102-
.GetCommand(connection)
103-
.ToEnumerable(connection);
102+
if (ProviderSupportsCompoundStatements || commandBuilders.Length == 1)
103+
{
104+
return CommandBuilder.CreateCommand(_providerHelper.GetCustomProvider<IDbParameterFactory>(_schema.SchemaProvider), commandBuilders, connection).ToEnumerable(connection);
105+
}
106+
else
107+
{
108+
return commandBuilders.SelectMany(cb => cb.GetCommand(connection).ToEnumerable(connection));
109+
}
104110
}
105111

106112
private IEnumerable<IDictionary<string, object>> RunQueryWithCount(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
@@ -141,19 +147,83 @@ private IEnumerable<IDictionary<string, object>> RunQueryWithCount(SimpleQuery q
141147
}
142148
}
143149

144-
public override IEnumerable<IEnumerable<IDictionary<string,object>>> RunQueries(SimpleQuery[] queries, List<IEnumerable<SimpleQueryClauseBase>> unhandledClauses)
150+
private ICommandBuilder[] GetPagedQueryCommandBuilders(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
151+
{
152+
return this.GetPagedQueryCommandBuilders(query, -1, out unhandledClauses);
153+
}
154+
155+
private ICommandBuilder[] GetPagedQueryCommandBuilders(SimpleQuery query, Int32 bulkIndex, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
156+
{
157+
var commandBuilders = new List<ICommandBuilder>();
158+
var unhandledClausesList = new List<SimpleQueryClauseBase>();
159+
unhandledClauses = unhandledClausesList;
160+
161+
IEnumerable<SimpleQueryClauseBase> unhandledClausesForPagedQuery;
162+
var mainCommandBuilder = new QueryBuilder(this, bulkIndex).Build(query, out unhandledClausesForPagedQuery);
163+
unhandledClausesList.AddRange(unhandledClausesForPagedQuery);
164+
165+
const int maxInt = 2147483646;
166+
167+
var skipClause = query.Clauses.OfType<SkipClause>().FirstOrDefault() ?? new SkipClause(0);
168+
var takeClause = query.Clauses.OfType<TakeClause>().FirstOrDefault() ?? new TakeClause(maxInt);
169+
170+
if (skipClause.Count != 0 || takeClause.Count != maxInt)
171+
{
172+
var queryPager = this.ProviderHelper.GetCustomProvider<IQueryPager>(this.ConnectionProvider);
173+
if (queryPager == null)
174+
{
175+
unhandledClausesList.AddRange(query.OfType<SkipClause>());
176+
unhandledClausesList.AddRange(query.OfType<TakeClause>());
177+
}
178+
179+
var commandTexts = queryPager.ApplyPaging(mainCommandBuilder.Text, skipClause.Count, takeClause.Count);
180+
181+
foreach (var commandText in commandTexts)
182+
{
183+
var commandBuilder = new CommandBuilder(commandText, this._schema, mainCommandBuilder.Parameters);
184+
commandBuilders.Add(commandBuilder);
185+
}
186+
}
187+
return commandBuilders.ToArray();
188+
}
189+
190+
private ICommandBuilder[] GetQueryCommandBuilders(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
191+
{
192+
if (query.Clauses.OfType<TakeClause>().Any() || query.Clauses.OfType<SkipClause>().Any())
193+
{
194+
return this.GetPagedQueryCommandBuilders(query, out unhandledClauses);
195+
}
196+
else
197+
{
198+
return new[] { new QueryBuilder(this).Build(query, out unhandledClauses) };
199+
}
200+
}
201+
202+
private ICommandBuilder[] GetQueryCommandBuilders(SimpleQuery query, Int32 bulkIndex, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
203+
{
204+
if (query.Clauses.OfType<TakeClause>().Any() || query.Clauses.OfType<SkipClause>().Any())
205+
{
206+
return this.GetPagedQueryCommandBuilders(query, bulkIndex, out unhandledClauses);
207+
}
208+
else
209+
{
210+
return new[] { new QueryBuilder(this, bulkIndex).Build(query, out unhandledClauses) };
211+
}
212+
}
213+
214+
public override IEnumerable<IEnumerable<IDictionary<string, object>>> RunQueries(SimpleQuery[] queries, List<IEnumerable<SimpleQueryClauseBase>> unhandledClauses)
145215
{
146216
if (ProviderSupportsCompoundStatements && queries.Length > 1)
147217
{
148-
var commandBuilders = new ICommandBuilder[queries.Length];
218+
var commandBuilders = new List<ICommandBuilder>();
149219
for (int i = 0; i < queries.Length; i++)
150220
{
151221
IEnumerable<SimpleQueryClauseBase> unhandledClausesForThisQuery;
152-
commandBuilders[i] = new QueryBuilder(this, i).Build(queries[i], out unhandledClausesForThisQuery);
222+
commandBuilders.AddRange(GetQueryCommandBuilders(queries[i], i, out unhandledClausesForThisQuery));
153223
unhandledClauses.Add(unhandledClausesForThisQuery);
154224
}
155225
var connection = _connectionProvider.CreateConnection();
156-
var command = CommandBuilder.CreateCommand(_providerHelper.GetCustomProvider<IDbParameterFactory>(_schema.SchemaProvider), commandBuilders, connection);
226+
var command = CommandBuilder.CreateCommand(_providerHelper.GetCustomProvider<IDbParameterFactory>(_schema.SchemaProvider), commandBuilders.ToArray(), connection);
157227
foreach (var item in command.ToEnumerables(connection))
158228
{
159229
yield return item.ToList();

‎Simple.Data.Ado/CommandBuilder.cs‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,15 @@ public CommandBuilder(string text, DatabaseSchema schema, int bulkIndex)
4141
_parameterSuffix = (bulkIndex >= 0) ? "_c" + bulkIndex : string.Empty;
4242
}
4343

44+
public CommandBuilder(string text, DatabaseSchema schema, IEnumerable<KeyValuePair<ParameterTemplate, Object>> parameters)
45+
: this(text, schema, -1)
46+
{
47+
foreach (var kvp in parameters)
48+
{
49+
_parameters.Add(kvp.Key, kvp.Value);
50+
}
51+
}
52+
4453
public ParameterTemplate AddParameter(object value)
4554
{
4655
string name = _schemaProvider.NameParameter("p" + Interlocked.Increment(ref _number) + _parameterSuffix);

‎Simple.Data.Ado/DataReaderEnumerator.cs‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@ public bool MoveNext()
4545
{
4646
ExecuteReader();
4747
}
48-
return _lastRead = _reader.Read();
48+
return _lastRead = (_reader != null && _reader.Read());
4949
}
5050

5151
private void ExecuteReader()
@@ -55,7 +55,10 @@ private void ExecuteReader()
5555
if (_connection.State == ConnectionState.Closed)
5656
_connection.Open();
5757
_reader = _command.ExecuteReader();
58-
_index = _index ?? _reader.CreateDictionaryIndex();
58+
if (_reader != null)
59+
{
60+
_index = _index ?? _reader.CreateDictionaryIndex();
61+
}
5962
}
6063
catch (DbException ex)
6164
{
@@ -73,6 +76,10 @@ public IDictionary<string, object> Current
7376
{
7477
get
7578
{
79+
if (_reader == null)
80+
{
81+
return null;
82+
}
7683
if (!_lastRead) throw new InvalidOperationException();
7784
return _reader.ToDictionary(_index);
7885
}

‎Simple.Data.Ado/IQueryPager.cs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,9 @@
1+
using System.Collections.Generic;
2+
13
namespace Simple.Data.Ado
24
{
35
public interface IQueryPager
46
{
5-
string ApplyPaging(string sql, string skipParameterName, string takeParameterName);
7+
IEnumerable<string> ApplyPaging(string sql, int skip, int take);
68
}
79
}

‎Simple.Data.Ado/ProviderHelper.cs‎

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -112,12 +112,13 @@ private static IConnectionProvider LoadProviderByConnectionToken(ConnectionToken
112112

113113
public T GetCustomProvider<T>(IConnectionProvider connectionProvider)
114114
{
115-
return (T)_customProviderCache.GetOrAdd(typeof (T), t => GetCustomProviderExport<T>(connectionProvider));
115+
return (T)_customProviderCache.GetOrAdd(typeof (T), t => GetCustomProviderExport<T>(connectionProvider.GetType().Assembly) ??
116+
GetCustomProviderServiceProvider(connectionProvider as IServiceProvider, t));
116117
}
117118

118-
private static T GetCustomProviderExport<T>(IConnectionProvider connectionProvider)
119+
private static Object GetCustomProviderExport<T>(Assembly assembly)
119120
{
120-
using (var assemblyCatalog = new AssemblyCatalog(connectionProvider.GetType().Assembly))
121+
using (var assemblyCatalog = new AssemblyCatalog(assembly))
121122
{
122123
using (var container = new CompositionContainer(assemblyCatalog))
123124
{
@@ -126,6 +127,15 @@ private static T GetCustomProviderExport<T>(IConnectionProvider connectionProvid
126127
}
127128
}
128129

130+
private static Object GetCustomProviderServiceProvider(IServiceProvider serviceProvider, Type type)
131+
{
132+
if (serviceProvider != null)
133+
{
134+
return serviceProvider.GetService(type);
135+
}
136+
return null;
137+
}
138+
129139
public T GetCustomProvider<T>(ISchemaProvider schemaProvider)
130140
{
131141
return (T)_customProviderCache.GetOrAdd(typeof(T), t => GetCustomProviderExport<T>(schemaProvider));

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 0 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -46,7 +46,6 @@ public ICommandBuilder Build(SimpleQuery query, out IEnumerable<SimpleQueryClaus
4646
HandleGrouping();
4747
HandleHavingCriteria();
4848
HandleOrderBy();
49-
HandlePaging();
5049

5150
unhandledClauses = _unhandledClauses;
5251
return _commandBuilder;
@@ -164,27 +163,6 @@ private void HandleOrderBy()
164163
_commandBuilder.Append(" ORDER BY " + string.Join(", ", orderNames));
165164
}
166165

167-
private void HandlePaging()
168-
{
169-
const int maxInt = 2147483646;
170-
171-
var skipClause = _query.Clauses.OfType<SkipClause>().FirstOrDefault() ?? new SkipClause(0);
172-
var takeClause = _query.Clauses.OfType<TakeClause>().FirstOrDefault() ?? new TakeClause(maxInt);
173-
if (skipClause.Count != 0 || takeClause.Count != maxInt)
174-
{
175-
var queryPager = _adoAdapter.ProviderHelper.GetCustomProvider<IQueryPager>(_adoAdapter.ConnectionProvider);
176-
if (queryPager == null)
177-
{
178-
_unhandledClauses.AddRange(_query.OfType<SkipClause>());
179-
_unhandledClauses.AddRange(_query.OfType<TakeClause>());
180-
}
181-
182-
var skipTemplate = _commandBuilder.AddParameter("skip", DbType.Int32, skipClause.Count);
183-
var takeTemplate = _commandBuilder.AddParameter("take", DbType.Int32, takeClause.Count);
184-
_commandBuilder.SetText(queryPager.ApplyPaging(_commandBuilder.Text, skipTemplate.Name, takeTemplate.Name));
185-
}
186-
}
187-
188166
private string ToOrderByDirective(OrderByClause item)
189167
{
190168
var col = _table.FindColumn(item.Reference.GetName());

‎Simple.Data.SqlCe40/SqlCe40QueryPager.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@ public class SqlCe40QueryPager : IQueryPager
1313
{
1414
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
1515

16-
public string ApplyPaging(string sql, string skipParameterName, string takeParameterName)
16+
public IEnumerable<string> ApplyPaging(string sql, int skip, int take)
1717
{
1818
if (sql.IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) < 0)
1919
{
@@ -22,7 +22,7 @@ public string ApplyPaging(string sql, string skipParameterName, string takeParam
2222
sql += " ORDER BY " + columns.Split(',').First().Trim();
2323
}
2424

25-
return string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, skipParameterName, takeParameterName);
25+
yield return string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, skip, take);
2626
}
2727
}
2828
}

‎Simple.Data.SqlCe40Test/SqlCe40QueryPagerTest.cs‎

Lines changed: 13 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
using System.Text.RegularExpressions;
22
using NUnit.Framework;
33
using Simple.Data.SqlCe40;
4+
using System.Linq;
45

56
namespace Simple.Data.SqlCe40Test
67
{
@@ -12,27 +13,27 @@ public class SqlCe40QueryPagerTest
1213
[Test]
1314
public void ShouldApplyPagingUsingOrderBy()
1415
{
15-
const string sql = "select a,b,c from d where a = 1 order by c";
16-
const string expected =
17-
"select a,b,c from d where a = 1 order by c offset @skip rows fetch next @take rows only";
16+
var sql = "select a,b,c from d where a = 1 order by c";
17+
var expected = new[]{
18+
"select a,b,c from d where a = 1 order by c offset 5 rows fetch next 10 rows only"};
1819

19-
var modified = new SqlCe40QueryPager().ApplyPaging(sql, "@skip", "@take");
20-
modified = Normalize.Replace(modified, " ").ToLowerInvariant();
20+
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, 5, 10);
21+
var modified = pagedSql.Select(x=> Normalize.Replace(x, " ").ToLowerInvariant());
2122

22-
Assert.AreEqual(expected, modified);
23+
Assert.IsTrue(expected.SequenceEqual(modified));
2324
}
2425

2526
[Test]
2627
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered()
2728
{
28-
const string sql = "select a,b,c from d where a = 1";
29-
const string expected =
30-
"select a,b,c from d where a = 1 order by a offset @skip rows fetch next @take rows only";
29+
var sql = "select a,b,c from d where a = 1";
30+
var expected = new[]{
31+
"select a,b,c from d where a = 1 order by a offset 10 rows fetch next 20 rows only"};
3132

32-
var modified = new SqlCe40QueryPager().ApplyPaging(sql, "@skip", "@take");
33-
modified = Normalize.Replace(modified, " ").ToLowerInvariant();
33+
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, 10, 20);
34+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
3435

35-
Assert.AreEqual(expected, modified);
36+
Assert.IsTrue(expected.SequenceEqual(modified));
3637
}
3738
}
3839
}

‎Simple.Data.SqlServer/SqlQueryPager.cs‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@ public class SqlQueryPager : IQueryPager
1313
{
1414
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
1515

16-
public string ApplyPaging(string sql, string skipParameterName, string takeParameterName)
16+
public IEnumerable<string> ApplyPaging(string sql, int skip, int take)
1717
{
1818
var builder = new StringBuilder("WITH __Data AS (SELECT ");
1919

@@ -29,10 +29,10 @@ public string ApplyPaging(string sql, string skipParameterName, string takeParam
2929
builder.AppendLine();
3030
builder.Append(fromEtc);
3131
builder.AppendLine(")");
32-
builder.AppendFormat("SELECT {0} FROM __Data WHERE [_#_] BETWEEN {1} + 1 AND {1} + {2}", DequalifyColumns(columns),
33-
skipParameterName, takeParameterName);
32+
builder.AppendFormat("SELECT {0} FROM __Data WHERE [_#_] BETWEEN {1} AND {2}", DequalifyColumns(columns),
33+
skip + 1, skip + take);
3434

35-
return builder.ToString();
35+
yield return builder.ToString();
3636
}
3737

3838
private static string DequalifyColumns(string original)

0 commit comments

Comments
 (0)