Skip to content

Commit e19271c

Browse files
ericdc1ericdc1
committed
Added GetListPaged, DeleteList, and RecordCount methods
Cases ericdc1#42 and ericdc1#46
1 parent 10f28cc commit e19271c

File tree

16 files changed

+434
-75
lines changed

16 files changed

+434
-75
lines changed

‎Dapper.SimpleCRUD NET45/SimpleCRUDAsync.cs

Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,6 +142,53 @@ public static Task<IEnumerable<T>> GetListAsync<T>(this IDbConnection connection
142142
return connection.GetListAsync<T>(new { });
143143
}
144144

145+
/// <summary>
146+
/// <para>By default queries the table matching the class name</para>
147+
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
148+
/// <para>conditions is an SQL where clause ex: "where name='bob'" - not required </para>
149+
/// <para>orderby is a column or list of columns to order by ex: "lastname, age desc" - not required - default is by primary key</para>
150+
/// <para>Returns a list of entities that match where conditions</para>
151+
/// </summary>
152+
/// <typeparam name="T"></typeparam>
153+
/// <param name="connection"></param>
154+
/// <param name="pageNumber"></param>
155+
/// <param name="rowsPerPage"></param>
156+
/// <param name="conditions"></param>
157+
/// <param name="orderby"></param>
158+
/// <returns>Gets a list of entities with optional exact match where conditions</returns>
159+
public static Task<IEnumerable<T>> GetListPagedAsync<T>(this IDbConnection connection, int pageNumber, int rowsPerPage, string conditions, string orderby)
160+
{
161+
if (string.IsNullOrEmpty(_getPagedListSql))
162+
throw new Exception("GetListPage is not supported with the current SQL Dialect");
163+
164+
var currenttype = typeof(T);
165+
var idProps = GetIdProperties(currenttype).ToList();
166+
if (!idProps.Any())
167+
throw new ArgumentException("Entity must have at least one [Key] property");
168+
169+
var name = GetTableName(currenttype);
170+
var sb = new StringBuilder();
171+
var query = _getPagedListSql;
172+
if (string.IsNullOrEmpty(orderby))
173+
{
174+
orderby = idProps.First().Name;
175+
}
176+
177+
//create a new empty instance of the type to get the base properties
178+
BuildSelect(sb, GetScaffoldableProperties((T)Activator.CreateInstance(typeof(T))).ToArray());
179+
query = query.Replace("{SelectColumns}", sb.ToString());
180+
query = query.Replace("{TableName}", name);
181+
query = query.Replace("{PageNumber}", pageNumber.ToString());
182+
query = query.Replace("{RowsPerPage}", rowsPerPage.ToString());
183+
query = query.Replace("{OrderBy}", orderby);
184+
query = query.Replace("{WhereClause}", conditions);
185+
186+
if (Debugger.IsAttached)
187+
Trace.WriteLine(String.Format("GetListPaged<{0}>: {1}", currenttype, query));
188+
189+
return connection.QueryAsync<T>(query);
190+
}
191+
145192
/// <summary>
146193
/// <para>Inserts a row into the database asynchronously</para>
147194
/// <para>By default inserts into the table matching the class name</para>
@@ -343,6 +390,69 @@ public static Task<int> DeleteAsync<T>(this IDbConnection connection, object id,
343390
return connection.ExecuteAsync(sb.ToString(), dynParms, transaction, commandTimeout);
344391
}
345392

393+
/// <summary>
394+
/// <para>Deletes a list of records in the database</para>
395+
/// <para>By default deletes records in the table matching the class name</para>
396+
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
397+
/// <para>Deletes records where that match the where clause</para>
398+
/// <para>conditions is an SQL where clause ex: "where name='bob'"</para>
399+
400+
/// <para>The number of records effected</para>
401+
/// <para>Supports transaction and command timeout</para>
402+
/// </summary>
403+
/// <typeparam name="T"></typeparam>
404+
/// <param name="connection"></param>
405+
/// <param name="conditions"></param>
406+
/// <param name="transaction"></param>
407+
/// <param name="commandTimeout"></param>
408+
/// <returns>The number of records effected</returns>
409+
public static Task<int> DeleteListAsync<T>(this IDbConnection connection, string conditions, IDbTransaction transaction = null, int? commandTimeout = null)
410+
{
411+
if (string.IsNullOrEmpty(conditions))
412+
throw new ArgumentException("DeleteList<T> requires a where clause");
413+
if (!conditions.ToLower().Contains("where"))
414+
throw new ArgumentException("DeleteList<T> requires a where clause and must contain the WHERE keyword");
415+
416+
var currenttype = typeof(T);
417+
var name = GetTableName(currenttype);
418+
419+
var sb = new StringBuilder();
420+
sb.AppendFormat("Delete from {0}", name);
421+
sb.Append(" " + conditions);
422+
423+
if (Debugger.IsAttached)
424+
Trace.WriteLine(String.Format("DeleteList<{0}> {1}", currenttype, sb));
425+
426+
return connection.ExecuteAsync(sb.ToString(), null, transaction, commandTimeout);
427+
}
428+
429+
/// <summary>
430+
/// <para>By default queries the table matching the class name</para>
431+
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
432+
/// <para>Returns a number of records entity by a single id from table T</para>
433+
/// <para>conditions is an SQL where clause ex: "where name='bob'" - not required </para>
434+
/// </summary>
435+
/// <typeparam name="T"></typeparam>
436+
/// <param name="connection"></param>
437+
/// <param name="conditions"></param>
438+
/// <returns>Returns a count of records.</returns>
439+
public static async Task<int> RecordCountAsync<T>(this IDbConnection connection, string conditions = "")
440+
{
441+
var currenttype = typeof(T);
442+
var name = GetTableName(currenttype);
443+
var sb = new StringBuilder();
444+
sb.Append("Select count(1)");
445+
sb.AppendFormat(" from {0}", name);
446+
sb.Append(" " + conditions);
447+
448+
if (Debugger.IsAttached)
449+
Trace.WriteLine(String.Format("RecordCount<{0}>: {1}", currenttype, sb));
450+
451+
var query = await connection.QueryAsync<int>(sb.ToString());
452+
return query.Single();
453+
}
454+
455+
346456

347457

348458
}

‎Dapper.SimpleCRUD.nuspec

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
<package >
33
<metadata>
44
<id>Dapper.SimpleCRUD</id>
5-
<version>1.8.7</version>
5+
<version>1.9.0-beta</version>
66
<title>Dapper.SimpleCRUD</title>
77
<authors>Eric Coffman</authors>
88
<owners>Eric Coffman</owners>
@@ -35,6 +35,7 @@
3535
* version 1.8.5 Added support for ReadOnly attribute on properties which allows selecting it from the database but ignores it on inserts and updates
3636
* version 1.8.6 Allow a column named Id column to not be considered a key when another [Key] is specified
3737
* version 1.8.7 Fix for GUID primary key named Id
38+
* version 1.9.0-beta Added GetListPaged, DeleteList, and RecordCount methods
3839
</releaseNotes>
3940
<copyright>Copyright Eric Coffman 2015</copyright>
4041
<tags>orm dapper micro-orm CRUD</tags>

‎Dapper.SimpleCRUD/Properties/AssemblyInfo.cs

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -32,5 +32,5 @@
3232
// You can specify all the values or you can default the Build and Revision Numbers
3333
// by using the '*' as shown below:
3434
// [assembly: AssemblyVersion("1.0.*")]
35-
[assembly: AssemblyVersion("1.8.7.0")]
36-
[assembly: AssemblyFileVersion("1.8.7.0")]
35+
[assembly: AssemblyVersion("1.9.0.0")]
36+
[assembly: AssemblyFileVersion("1.9.0.0")]

‎Dapper.SimpleCRUD/SimpleCRUD.cs

Lines changed: 128 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
using System;
22
using System.Collections.Generic;
3+
using System.Configuration;
34
using System.Data;
45
using System.Diagnostics;
56
using System.Linq;
@@ -19,10 +20,11 @@ static SimpleCRUD()
1920
{
2021
SetDialect(_dialect);
2122
}
22-
23+
2324
private static Dialect _dialect = Dialect.SQLServer;
2425
private static string _encapsulation;
2526
private static string _getIdentitySql;
27+
private static string _getPagedListSql;
2628

2729
/// <summary>
2830
/// Returns the current dialect name
@@ -44,16 +46,19 @@ public static void SetDialect(Dialect dialect)
4446
_dialect = Dialect.PostgreSQL;
4547
_encapsulation = "{0}";
4648
_getIdentitySql = string.Format("SELECT LASTVAL() AS id");
49+
_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage})";
4750
break;
4851
case Dialect.SQLite:
4952
_dialect = Dialect.SQLite;
5053
_encapsulation = "{0}";
5154
_getIdentitySql = string.Format("SELECT LAST_INSERT_ROWID() AS id");
55+
_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage})";
5256
break;
5357
default:
5458
_dialect = Dialect.SQLServer;
5559
_encapsulation = "[{0}]";
5660
_getIdentitySql = string.Format("SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [id]");
61+
_getPagedListSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNUMBER BETWEEN (({PageNumber} - 1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage})";
5762
break;
5863
}
5964
}
@@ -155,7 +160,6 @@ public static IEnumerable<T> GetList<T>(this IDbConnection connection, string co
155160
var name = GetTableName(currenttype);
156161

157162
var sb = new StringBuilder();
158-
var whereprops = GetAllProperties(conditions).ToArray();
159163
sb.Append("Select ");
160164
//create a new empty instance of the type to get the base properties
161165
BuildSelect(sb, GetScaffoldableProperties((T)Activator.CreateInstance(typeof(T))).ToArray());
@@ -169,7 +173,6 @@ public static IEnumerable<T> GetList<T>(this IDbConnection connection, string co
169173
return connection.Query<T>(sb.ToString());
170174
}
171175

172-
173176
/// <summary>
174177
/// <para>By default queries the table matching the class name</para>
175178
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
@@ -183,6 +186,56 @@ public static IEnumerable<T> GetList<T>(this IDbConnection connection)
183186
return connection.GetList<T>(new { });
184187
}
185188

189+
/// <summary>
190+
/// <para>By default queries the table matching the class name</para>
191+
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
192+
/// <para>conditions is an SQL where clause ex: "where name='bob'" - not required </para>
193+
/// <para>orderby is a column or list of columns to order by ex: "lastname, age desc" - not required - default is by primary key</para>
194+
/// <para>Returns a list of entities that match where conditions</para>
195+
/// </summary>
196+
/// <typeparam name="T"></typeparam>
197+
/// <param name="connection"></param>
198+
/// <param name="pageNumber"></param>
199+
/// <param name="rowsPerPage"></param>
200+
/// <param name="conditions"></param>
201+
/// <param name="orderby"></param>
202+
/// <returns>Gets a paged list of entities with optional exact match where conditions</returns>
203+
public static IEnumerable<T> GetListPaged<T>(this IDbConnection connection, int pageNumber, int rowsPerPage, string conditions, string orderby)
204+
{
205+
if (string.IsNullOrEmpty(_getPagedListSql))
206+
throw new Exception("GetListPage is not supported with the current SQL Dialect");
207+
208+
if (pageNumber<1)
209+
throw new Exception("Page must be greater than 0");
210+
211+
var currenttype = typeof(T);
212+
var idProps = GetIdProperties(currenttype).ToList();
213+
if (!idProps.Any())
214+
throw new ArgumentException("Entity must have at least one [Key] property");
215+
216+
var name = GetTableName(currenttype);
217+
var sb = new StringBuilder();
218+
var query = _getPagedListSql;
219+
if (string.IsNullOrEmpty(orderby))
220+
{
221+
orderby = idProps.First().Name;
222+
}
223+
224+
//create a new empty instance of the type to get the base properties
225+
BuildSelect(sb, GetScaffoldableProperties((T)Activator.CreateInstance(typeof(T))).ToArray());
226+
query = query.Replace("{SelectColumns}", sb.ToString());
227+
query = query.Replace("{TableName}", name);
228+
query = query.Replace("{PageNumber}", pageNumber.ToString());
229+
query = query.Replace("{RowsPerPage}", rowsPerPage.ToString());
230+
query = query.Replace("{OrderBy}", orderby);
231+
query = query.Replace("{WhereClause}", conditions);
232+
233+
if (Debugger.IsAttached)
234+
Trace.WriteLine(String.Format("GetListPaged<{0}>: {1}", currenttype, query));
235+
236+
return connection.Query<T>(query);
237+
}
238+
186239
/// <summary>
187240
/// <para>Inserts a row into the database</para>
188241
/// <para>By default inserts into the table matching the class name</para>
@@ -219,6 +272,7 @@ public static IEnumerable<T> GetList<T>(this IDbConnection connection)
219272
public static TKey Insert<TKey>(this IDbConnection connection, object entityToInsert, IDbTransaction transaction = null, int? commandTimeout = null)
220273
{
221274
var idProps = GetIdProperties(entityToInsert).ToList();
275+
222276
if (!idProps.Any())
223277
throw new ArgumentException("Insert<T> only supports an entity with a [Key] or Id property");
224278
if (idProps.Count() > 1)
@@ -231,9 +285,9 @@ public static TKey Insert<TKey>(this IDbConnection connection, object entityToIn
231285
{
232286
throw new Exception("Invalid return type");
233287
}
234-
if (keytype == typeof (Guid))
288+
if (keytype == typeof(Guid))
235289
{
236-
var guidvalue = (Guid)idProps.First().GetValue(entityToInsert,null);
290+
var guidvalue = (Guid)idProps.First().GetValue(entityToInsert, null);
237291
if (guidvalue == Guid.Empty)
238292
{
239293
var newguid = SequentialGuid();
@@ -252,20 +306,20 @@ public static TKey Insert<TKey>(this IDbConnection connection, object entityToIn
252306
BuildInsertValues(entityToInsert, sb);
253307
sb.Append(")");
254308

255-
if (keytype == typeof (Guid))
309+
if (keytype == typeof(Guid))
256310
{
257311
sb.Append(";select '" + idProps.First().GetValue(entityToInsert, null) + "' as id");
258312
}
259313
else
260314
{
261-
sb.Append(";" + _getIdentitySql);
315+
sb.Append(";" + _getIdentitySql);
262316
}
263-
317+
264318
if (Debugger.IsAttached)
265319
Trace.WriteLine(String.Format("Insert: {0}", sb));
266320

267321
var r = connection.Query(sb.ToString(), entityToInsert, transaction, true, commandTimeout);
268-
322+
269323
if (keytype == typeof(Guid))
270324
{
271325
return (TKey)idProps.First().GetValue(entityToInsert, null);
@@ -387,6 +441,68 @@ public static int Delete<T>(this IDbConnection connection, object id, IDbTransac
387441
return connection.Execute(sb.ToString(), dynParms, transaction, commandTimeout);
388442
}
389443

444+
/// <summary>
445+
/// <para>Deletes a list of records in the database</para>
446+
/// <para>By default deletes records in the table matching the class name</para>
447+
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
448+
/// <para>Deletes records where that match the where clause</para>
449+
/// <para>conditions is an SQL where clause ex: "where name='bob'"</para>
450+
451+
/// <para>The number of records effected</para>
452+
/// <para>Supports transaction and command timeout</para>
453+
/// </summary>
454+
/// <typeparam name="T"></typeparam>
455+
/// <param name="connection"></param>
456+
/// <param name="conditions"></param>
457+
/// <param name="transaction"></param>
458+
/// <param name="commandTimeout"></param>
459+
/// <returns>The number of records effected</returns>
460+
public static int DeleteList<T>(this IDbConnection connection, string conditions, IDbTransaction transaction = null, int? commandTimeout = null)
461+
{
462+
if (string.IsNullOrEmpty(conditions))
463+
throw new ArgumentException("DeleteList<T> requires a where clause");
464+
if (!conditions.ToLower().Contains("where"))
465+
throw new ArgumentException("DeleteList<T> requires a where clause and must contain the WHERE keyword");
466+
467+
var currenttype = typeof(T);
468+
var name = GetTableName(currenttype);
469+
470+
var sb = new StringBuilder();
471+
sb.AppendFormat("Delete from {0}", name);
472+
sb.Append(" " + conditions);
473+
474+
if (Debugger.IsAttached)
475+
Trace.WriteLine(String.Format("DeleteList<{0}> {1}", currenttype, sb));
476+
477+
return connection.Execute(sb.ToString(), null, transaction, commandTimeout);
478+
}
479+
480+
/// <summary>
481+
/// <para>By default queries the table matching the class name</para>
482+
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
483+
/// <para>Returns a number of records entity by a single id from table T</para>
484+
/// <para>conditions is an SQL where clause ex: "where name='bob'" - not required </para>
485+
/// </summary>
486+
/// <typeparam name="T"></typeparam>
487+
/// <param name="connection"></param>
488+
/// <param name="conditions"></param>
489+
/// <returns>Returns a count of records.</returns>
490+
public static int RecordCount<T>(this IDbConnection connection, string conditions = "")
491+
{
492+
var currenttype = typeof(T);
493+
var name = GetTableName(currenttype);
494+
var sb = new StringBuilder();
495+
sb.Append("Select count(1)");
496+
sb.AppendFormat(" from {0}", name);
497+
sb.Append(" " + conditions);
498+
499+
if (Debugger.IsAttached)
500+
Trace.WriteLine(String.Format("RecordCount<{0}>: {1}", currenttype, sb));
501+
502+
return connection.Query<int>(sb.ToString()).Single();
503+
}
504+
505+
390506
//build update statement based on list on an entity
391507
private static void BuildUpdateSet(object entityToUpdate, StringBuilder sb)
392508
{
@@ -536,13 +652,13 @@ private static bool IsReadOnly(PropertyInfo pi)
536652
//Get all properties that are NOT named Id, DO NOT have the Key attribute, and are not marked ReadOnly
537653
private static IEnumerable<PropertyInfo> GetUpdateableProperties(object entity)
538654
{
539-
var updateableProperties = GetScaffoldableProperties(entity);
655+
var updateableProperties = GetScaffoldableProperties(entity);
540656
//remove ones with ID
541657
updateableProperties = updateableProperties.Where(p => p.Name != "Id");
542658
//remove ones with key attribute
543-
updateableProperties = updateableProperties.Where(p=> p.GetCustomAttributes(true).Any(attr => attr.GetType().Name == "KeyAttribute") == false);
659+
updateableProperties = updateableProperties.Where(p => p.GetCustomAttributes(true).Any(attr => attr.GetType().Name == "KeyAttribute") == false);
544660
//remove ones that are readonly
545-
updateableProperties = updateableProperties.Where(p=> p.GetCustomAttributes(true).Any(attr => (attr.GetType().Name == "ReadOnlyAttribute") && IsReadOnly(p)) == false);
661+
updateableProperties = updateableProperties.Where(p => p.GetCustomAttributes(true).Any(attr => (attr.GetType().Name == "ReadOnlyAttribute") && IsReadOnly(p)) == false);
546662

547663
return updateableProperties;
548664
}

0 commit comments

Comments
 (0)