· 9 years ago · Oct 01, 2016, 01:08 PM
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using System.Data;
7using System.Data.SqlClient;
8using System.Configuration;
9using Dapper;
10using System.Reflection;
11
12using Tahleelati.Data;
13
14namespace Moodie.Libraries.DapperRepository
15{
16
17 //TODO: UPDATED TO ASYNC ONCE CORE FEATURE SET READY
18
19 /// <summary>
20 /// Base repository class
21 /// </summary>
22 public class Repository<T> : IRepository<T> where T : DatabaseEntity
23 {
24 //TODO: UPDATE REPO TO EDIT CLASSES RATHER THAN RETURN AND SWITCH TO TRY PATTERN
25 #region Static column, table and schema name dictionaries and information
26 //store all primary ID keys
27 protected static Dictionary<string, string> AllPrimaryKeys;
28
29 // store all sort keys
30 protected static Dictionary<string, string> AllSortKeys; //TODO: POSSIBLY ADD FUNCTIONALITY TO PREDEFINE DEFAULT ORDERBY KEYS
31
32 //store all columns with a direct database representation
33 protected static Dictionary<string, List<string>> AllColumns; //TODO: Add functionality to be able to override database column name
34
35 // store all columns generated
36 protected static Dictionary<string, List<string>> AllGeneratedColumns;
37
38 // store all core columns required to generate list
39 protected static Dictionary<string, List<string>> AllTitleColumns;
40
41 /// <summary>
42 /// List of table names paired with class name as the key
43 /// </summary>
44 protected static Dictionary<string, string> TableNames;
45 /// <summary>
46 /// List of schema names paired with class name as the key
47 /// </summary>
48 protected static Dictionary<string, string> SchemaNames;
49 #endregion
50
51 #region Static sql expression dictionaries
52 protected static Dictionary<string, string> CreateStatements;
53 //protected static Dictionary<string, string> SelectStatements;
54 protected static Dictionary<string, string> UpdateStatements;
55 protected static Dictionary<string, string> DeleteStatements;
56 #endregion
57
58 #region Static database & connection Infromation
59 private static string _connectionString;
60 /// <summary>
61 /// database connection string
62 /// </summary>
63 protected static string ConnectionString
64 {
65 get
66 {
67 if (_connectionString == null)
68 {
69 // set connection string or get default
70 _connectionString = ConfigurationManager.ConnectionStrings["TahleelatiCS"].ConnectionString;
71 //_connectionString = ConfigurationManager.AppSettings["ConnectionStrings:TahleelatiCS"];
72 }
73 return _connectionString;
74
75 }
76 }
77
78 #endregion
79
80 #region instance sql expressions
81
82 private string _createStatement;
83 protected string createStatement
84 {
85 get
86 {
87 if (_createStatement == null)
88 {
89 _createStatement = GetCreateSql();
90 }
91 return _createStatement;
92 }
93 }
94
95 //private string _selectAllStatement;
96 ///// <summary>
97 ///// SQL expression used to select current class type
98 ///// </summary>
99 //protected string selectAllStatement
100 //{
101 // get
102 // {
103 // if(_selectAllStatement == null)
104 // {
105 // _selectAllStatement = GetSelectAllSql();
106 // }
107 // return _selectAllStatement;
108 // }
109 //}
110
111 private string _updateStatement;
112 /// <summary>
113 /// SQL expression used for updating current class type
114 /// </summary>
115 protected string updateStatement
116 {
117 get
118 {
119 if(_updateStatement == null)
120 {
121 _updateStatement = GetUpdateSql();
122 }
123 return _updateStatement;
124 }
125 }
126
127 private string deleteStatement;
128 #endregion
129
130 #region instance properties, fields and variables
131 private string _schemaName;
132 private string _tableName;
133
134 private string _primaryKey;
135 private string _sortKey;
136
137 private List<string> _columns;
138 private List<string> _genColumns;
139 private List<string> _titleColumns; // all columns required for generating an entitieslist
140
141 /// <summary>
142 /// Schema name of current entity
143 /// </summary>
144 public string SchemaName
145 {get {return _schemaName;}}
146
147 /// <summary>
148 /// Table name of current entity
149 /// </summary>
150 public string TableName{get{ return _tableName; } }
151
152 /// <summary>
153 /// Name of primary key column for current entity type (default is Id)
154 /// </summary>
155 public string PrimaryKey { get { return _primaryKey; } }
156
157 public string SortKey { get { return _sortKey; } }
158
159 /// <summary>
160 /// List of all non-primary key column names for current entity type
161 /// </summary>
162 public List<string> Columns { get { return _columns; } }
163
164 public List<string> GeneratedColumns { get { return _genColumns; } }
165
166 public List<string> TitleColumns { get { return _titleColumns; } }
167
168 /// <summary>
169 /// Database connection object
170 /// </summary>
171 protected IDbConnection db;
172
173
174 protected string className;
175 #endregion
176
177
178 #region constructors
179 /// <summary>
180 /// Constructor
181 /// </summary>
182 public Repository()
183 {
184 // get the class name
185 className = (typeof(T).Name);
186
187 // Get/Generate the columns and primary key names
188 GetColumnsList();
189
190 // get schema name
191 _schemaName = GetSchemaName();
192
193 // get table name
194 _tableName = GetTableName();
195
196 }
197 #endregion
198
199 /// <summary>
200 /// Create a new entity.
201 /// </summary>
202 /// <param name="entity">Entity to update, results updated within</param>
203 /// <returns></returns>
204 public virtual bool Create(T entity)
205 {
206 if(entity.Id != null)
207 {
208 return false;
209 }
210 //try
211 //{
212 using (var db = new SqlConnection(ConnectionString))
213 {
214 //set creation and last update times for entity
215 entity.CreationDate = DateTime.UtcNow;
216 entity.LastUpdateDate = DateTime.UtcNow;
217 //get new globalid TODO: Change code to intergrate global id retrieval within create statement.
218 var globalIdStatement = String.Format("SELECT NEXT VALUE FOR {0}.GlobalId", SchemaName);
219 var globalId = db.Query<long>(globalIdStatement);
220 entity.GlobalId = globalId.First();
221 // call query to create, returning id as int.
222 var result = db.Query<long>(createStatement, entity);
223 entity.Id = result.First();
224
225 //return result as successful.
226 return true;
227 }
228 //} catch(Exception ex)
229 //{
230 // return false;
231 //}
232 }
233
234 /// <summary>
235 /// Get entity by ID
236 /// </summary>
237 /// <param name="id">Entity ID</param>
238 /// <returns>Entity, or null if not found</returns>
239 public virtual T Find(long id)
240 {
241 using (var db = new SqlConnection(ConnectionString))
242 {
243 var sql = string.Format("SELECT * FROM {0}.{1} WHERE {2} = @{2}", SchemaName, TableName, PrimaryKey); //db query will replace @{PrimaryKey} for the id
244 var result = db.Query<T>(sql, new { id });
245 return result.FirstOrDefault();
246 }
247
248 }
249
250 //returns minimal info to convert all items found to lsit
251 public virtual List<T> List(string orderBy = null, bool orderAscending = true, int take = 0, int page = 0)
252 {
253 //add check to see if can T be cast to list type
254 using (var db = new SqlConnection(ConnectionString))
255 {
256 string sql = GenerateListSql(orderBy: orderBy, orderAscending: orderAscending, take: take, page: page);
257 var r = db.Query<T>(sql);
258 //return results
259 return r.ToList();
260 }
261 }
262
263 public virtual List<T> GetAll(string orderBy = null, bool orderAscending = true, int take = 0, int page = 0)
264 {
265 using (var db = new SqlConnection(ConnectionString))
266 {
267 string sql = GenerateSelectAllSql(orderBy: orderBy, orderAscending: orderAscending, take: take, page: page);
268 var t = db.Query<T>(sql.ToString());
269 return t.ToList();
270 }
271
272 }
273
274 ///// <summary>
275 ///// Save a entity. If entity already exists will be updated, else a new entity will be created
276 ///// </summary>
277 ///// <param name="entity">Entity to save</param>
278 ///// <returns>New/Updated entity</returns>
279 //public virtual T Save(T entity)
280 //{
281 // if (entity.Id == default(int))
282 // {
283 // return entity = Create(entity);
284 // }
285 // else
286 // {
287 // return entity = Update(entity);
288 // }
289 //}
290
291 /// <summary>
292 /// update existing entity
293 /// </summary>
294 /// <param name="entity"></param>
295 /// <returns></returns>
296 public virtual bool Update(T entity)
297 {
298 if(entity.Id < 1)
299 {
300 return false;
301 }
302 try
303 {
304 using (var db = new SqlConnection(ConnectionString))
305 {
306 entity.LastUpdateDate = DateTime.UtcNow;
307 db.Execute(updateStatement, entity);
308 return true;
309 }
310 } catch
311 {
312 return false;
313 }
314
315 }
316
317 /// <summary>
318 /// Delete entity
319 /// </summary>
320 /// <param name="entity"></param>
321 /// <returns></returns>
322 public virtual bool Delete(long entityId)
323 {
324 using (var db = new SqlConnection(ConnectionString))
325 {
326 string sql = String.Format("DELETE FROM {0}.{1} WHERE {2} = {3};",SchemaName,TableName, PrimaryKey,entityId);
327 var t = db.Execute(sql);
328 if (t > 0)
329 {
330 return true;
331 } else
332 {
333 return false;
334 }
335 }
336 }
337
338 #region Table Information
339 /// <summary>
340 /// Returns the database table name
341 /// </summary>
342 protected string GetTableName()
343 {
344
345 //initalise table dictionary if it does not exist
346 if (TableNames == null)
347 {
348 TableNames = new Dictionary<string, string>();
349
350 }
351 else
352 {
353 // check if value already exists
354 string returnVal;
355 if (TableNames.TryGetValue(className, out returnVal))
356 {
357 return returnVal;
358 }
359 }
360
361 // if value doesnt already exist, set the correct table name and return it
362 TableNameAttribute tn = (TableNameAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableNameAttribute));
363 if (tn != null)
364 {
365 // add table name if it exists as an overload
366 TableNames.Add(className, tn.Name);
367 }
368 else
369 {
370 // set as default table name
371 TableNames.Add(className, className + "s");
372 }
373
374 //return the table name
375 return TableNames[className];
376 }
377
378 /// <summary>
379 /// Returns the schema name for the current class, or the default from the base class
380 /// </summary>
381 /// <returns></returns>
382 protected string GetSchemaName()
383 {
384 if (SchemaNames == null)
385 {
386 SchemaNames = new Dictionary<string, string>();
387 }
388 else
389 {
390 string returnVal;
391 if (SchemaNames.TryGetValue(className, out returnVal))
392 {
393 return returnVal;
394 }
395 }
396
397 // if gotten this far schema name doesnt yet exist for class, get name from attribute
398 SchemaNameAttribute sn = (SchemaNameAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(SchemaNameAttribute));
399 SchemaNames.Add(className, sn.Name);
400
401 return sn.Name;
402 }
403
404 /// <summary>
405 /// Gets or generates the list of all columns names, pimary key and display name/sort key information
406 /// </summary>
407 private void GetColumnsList()
408 {
409 // Initialize dictionaries if not already initialised
410 if (AllPrimaryKeys == null)
411 {
412 AllPrimaryKeys = new Dictionary<string, string>();
413 }
414
415 if (AllSortKeys == null)
416 {
417 AllSortKeys = new Dictionary<string, string>();
418 }
419
420
421 if (AllColumns == null)
422 {
423 AllColumns = new Dictionary<string, List<string>>();
424
425 }
426
427 if(AllGeneratedColumns == null)
428 {
429 AllGeneratedColumns = new Dictionary<string, List<string>>();
430 }
431
432 if(AllTitleColumns == null)
433 {
434 AllTitleColumns = new Dictionary<string, List<string>>();
435 }
436
437 // check if column values already exist, if not generate list and details, else get from stored lists. Can assume if columns exists, generated columns and other values also do
438 if (!AllColumns.TryGetValue(className, out _columns))
439 {
440 _columns = new List<string>();
441 _genColumns = new List<string>();
442 _titleColumns = new List<string>();
443
444
445 // get all properties for class
446 var props = typeof(T).GetProperties();
447 // for any with the column attribute, add to lsit
448 foreach (var prop in props)
449 {
450 // get custom attributes
451 ColumnAttribute ca = (ColumnAttribute)Attribute.GetCustomAttribute(prop, typeof(ColumnAttribute));
452
453 //check if database column
454 if (ca != null)
455 {
456 if (ca.IsPrimaryKey)
457 {
458 // set as the primary key
459 _primaryKey = prop.Name;
460 }
461 else
462 {
463 // add to list of columns
464 _columns.Add(prop.Name);
465 }
466
467 //check if set as sortkey
468 if (ca.IsSortKey)
469 {
470 _sortKey = prop.Name;
471 }
472
473 if (ca.IsListItem)
474 {
475 _titleColumns.Add(prop.Name);
476 }
477
478 }
479 }
480
481 // if primarykey hasn't yet been set, revert to default key name
482 if (_primaryKey == null)
483 {
484 _primaryKey = "Id";
485 }
486 if (_sortKey == null)
487 {
488 _sortKey = _primaryKey;
489 }
490
491 // add the new list to the allcolumns dictionary
492 AllColumns[className] = _columns;
493
494 // add the primary key
495 AllPrimaryKeys[className] = _primaryKey;
496
497 //add the sort keys to the dictionary
498 AllSortKeys[className] = _sortKey;
499
500 //add the new list of generated columns to the dictionary
501 AllGeneratedColumns[className] = _genColumns;
502 //add the new list of all List required columns to the dictionary
503 AllTitleColumns[className] = _titleColumns;
504 }
505 else
506 {
507 // if columns exist, (semi)safe to assume PrimaryKey & SortKey also exists and load. Columns already loaded above in if statement tryget method
508 _primaryKey = AllPrimaryKeys[className];
509 _sortKey = AllSortKeys[className];
510
511 _columns = AllColumns[className];
512 _genColumns = AllGeneratedColumns[className];
513 _titleColumns = AllTitleColumns[className];
514
515 }
516 }
517 #endregion
518
519 #region SqlGenerationMethods
520 private string GetCreateSql()
521 {
522 string sql;
523 // check if dictionary list exists
524 if (CreateStatements == null)
525 {
526 CreateStatements = new Dictionary<string, string>();
527 }
528
529 // check statement already exists in the dictionay
530 if (!CreateStatements.TryGetValue(className, out sql))
531 {
532 // generate Create Statement
533 StringBuilder cols = new StringBuilder();
534 StringBuilder paras = new StringBuilder();
535
536 //create a comma seperate list of values and params
537 cols.Append("(");
538 paras.Append("VALUES (");
539
540 for (int i = 0, len = Columns.Count; i < len; i++)
541 {
542 cols.Append(Columns[i]);
543 paras.AppendFormat("@{0}", Columns[i]);
544 if (i < (len - 1))
545 {
546 cols.Append(", ");
547 paras.Append(", ");
548 }
549 else
550 {
551 //close
552 cols.Append(") ");
553 paras.Append(") ");
554 }
555 }
556
557
558 // put it all together, add select statement to return new ID
559 sql = String.Format("INSERT INTO {0}.{1} {2} {3}; SELECT SCOPE_IDENTITY();", SchemaName, TableName, cols.ToString(), paras.ToString());
560
561 // add to create statements list
562 CreateStatements[className] = sql;
563
564 }
565 // return statement
566 return sql;
567 }
568
569 /// <summary>
570 /// Get or generate if not found the update sql statement for current class
571 /// </summary>
572 /// <returns></returns>
573 private string GetUpdateSql()
574 {
575 string sql;
576 // check if dictionary isn't yet initialized
577 if(UpdateStatements == null)
578 {
579 UpdateStatements = new Dictionary<string, string>();
580 }
581
582 // check if statement already generated, if not generate.
583 if (!UpdateStatements.TryGetValue(className, out sql))
584 {
585 // if statement not found, generate
586 StringBuilder sb = new StringBuilder();
587 sb.AppendFormat("UPDATE {0}.{1} SET ", SchemaName, TableName);
588 for (int i = 0, len = Columns.Count; i < len; i++)
589 {
590 sb.AppendFormat("{0} = @{0}", Columns[i]);
591
592 if (i < (len - 1))
593 {
594 sb.Append(", ");
595 }
596 }
597
598 sb.AppendFormat(" WHERE {0} = @{0};", PrimaryKey);
599 // add to dictionary
600 sql = sb.ToString();
601 UpdateStatements[className] = sql;
602 }
603
604 // return sql statement
605 return sql;
606 }
607
608 /// <summary>
609 /// Generate a sql function to get minimal list from database
610 /// </summary>
611 /// <param name="take"></param>
612 /// <param name="page"></param>
613 /// <returns></returns>
614 private string GenerateListSql(string orderBy = null, bool orderAscending = true, int take = 0, int page = 0)
615 {
616 string sql = GenerateSqlSelect(TitleColumns);
617 sql = AddSqlOrder(sql, SortKey,!orderAscending);
618 if (take > 0)
619 {
620 sql = AddSqlPage(sql, take, page);
621 }
622 return sql;
623 }
624 /// <summary>
625 /// return the select statement for class type
626 /// </summary>
627 /// <returns></returns>
628 private string GenerateSelectAllSql(string orderBy = null, bool orderAscending = true, int take = 0, int page = 0)
629 {
630 //generate base select statement
631 string sql = GenerateSqlSelect();
632 //add order statement
633 sql = AddSqlOrder(sql, (orderBy ?? SortKey), !orderAscending);
634
635 //if take is selected, add page statement
636 if (take > 0)
637 {
638 sql = AddSqlPage(sql, take, page);
639 }
640
641 return sql;
642 }
643
644 /// <summary>
645 /// Generate sql select statement.
646 /// </summary>
647 /// <param name="columnNames">List of columns to generate based upon. If not selected, main column names by defaulr</param>
648 /// <returns></returns>
649 private string GenerateSqlSelect(List<string> columnNames = null)
650 {
651 //if columns not selected, default to main (all) columns
652 if (columnNames == null)
653 {
654 columnNames = Columns;
655 }
656
657 StringBuilder sb = new StringBuilder();
658 //append select statement and Id select
659 sb.AppendFormat("SELECT {0}, ",PrimaryKey);
660
661 //append each column to select from
662 for(int i=0,len=columnNames.Count; i<len;)
663 {
664 //TODO: change to be able to select column names as different names based on column attribute override.
665 sb.AppendFormat("{0}", columnNames[i]);
666 if (++i < len)
667 {
668 sb.Append(", ");
669 }
670 }
671 //append table details
672 sb.AppendFormat(" FROM {0}.{1}", SchemaName, TableName);
673
674 // convert to string and return
675 return sb.ToString();
676 }
677
678 /// <summary>
679 /// Add ordering statement to exisitng sql statement
680 /// </summary>
681 /// <param name="sql">exisitng sql statement</param>
682 /// <param name="orderBy">Column to order by</param>
683 /// <param name="descending">Override ordering to descending order.</param>
684 /// <returns></returns>
685 private string AddSqlOrder(string sql, string orderBy, bool descending = false)
686 {
687 var sb = new StringBuilder();
688 //append the existing statement
689 sb.Append(sql);
690 //append the order by statement
691 sb.AppendFormat(" ORDER BY {0} {1}", orderBy, (!descending ? "ASC" : "DESC"));
692 //return appended string
693 return sb.ToString();
694
695 }
696 /// <summary>
697 /// Add paging sql statement to existing sql statement
698 /// </summary>
699 /// <param name="sql">existing sql statement</param>
700 /// <param name="take">number of entities to take</param>
701 /// <param name="page">Pages to skip</param>
702 /// <returns></returns>
703 private string AddSqlPage(string sql, int take = 10, int page = 0)
704 {
705 var sb = new StringBuilder();
706 sb.Append(sql);
707 sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (page * take), take);
708
709 return sb.ToString();
710
711 }
712 #endregion
713 }
714
715 /*
716 * Helper functions
717 */
718
719}