From 24d42e5eb284267966aee8bb687710bfcbc1a255 Mon Sep 17 00:00:00 2001 From: crupest Date: Sun, 11 Dec 2022 16:51:53 +0800 Subject: Develop secret api. v29 --- .../CrupestApi.Commons/Crud/CrudService.cs | 2 +- .../CrupestApi/CrupestApi.Commons/Crud/README.md | 27 ++++++++ .../CrupestApi.Commons/Crud/TableInfo.cs | 73 +++++++++++++++------- 3 files changed, 77 insertions(+), 25 deletions(-) create mode 100644 docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md (limited to 'docker/crupest-api/CrupestApi/CrupestApi.Commons') diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs index af6a8d5..9400a7b 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/CrudService.cs @@ -63,7 +63,7 @@ public class CrudService : IDisposable where TEntity : class // Return the key. public object Insert(TEntity entity) { - return _table.Insert(_dbConnection, _table.GenerateInsertClauseFromEntity(entity)); + return _table.Insert(_dbConnection, ); } public int Update(IUpdateClause updateClause, IWhereClause? filter) diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md new file mode 100644 index 0000000..c30ea90 --- /dev/null +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/README.md @@ -0,0 +1,27 @@ +# CRUD Technic Notes + +## Database Pipeline + +### Select + +1. Create select `what`, where clause, order clause, `Offset` and `Limit`. +2. Check clauses' related columns are valid. Then generate sql string and param list. +3. Convert param list to `Dapper` dynamic params. Execute sql and get `dynamic`s. +4. Run hook `AfterSelect` for every column. +5. Convert `dynamic`s to `TEntity`s. + +TODO: Continue here. + +### Insert + +1. Create insert clause consisting of insert items. +2. Check clauses' related columns are valid. Then generate sql string and param list. +3. Run hook `BeforeInsert` for every column. +4. Convert param list to `Dapper` dynamic params. Execute sql and return `KeyColumn` value. + +### Update + +1. Create update clause consisting of update items, where clause. +2. Check clauses' related columns are valid. Then generate sql string and param list. +3. Run hook `BeforeUpdate` for every column. +4. Convert param list to `Dapper` dynamic params. Execute sql and get count of affected rows. diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs index 61cd04f..089ff8a 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs @@ -239,7 +239,7 @@ CREATE TABLE {tableName}( /// /// If you call this manually, it's your duty to call hooks. /// - /// + /// public (string sql, ParamList parameters) GenerateSelectSql(string? selectWhat, IWhereClause? whereClause, IOrderByClause? orderByClause = null, int? skip = null, int? limit = null, string? dbProviderId = null) { CheckRelatedColumns(whereClause); @@ -389,30 +389,16 @@ CREATE TABLE {tableName}( return result; } - public virtual int SelectCount(IDbConnection dbConnection, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) - { - var (sql, parameters) = GenerateSelectSql("COUNT(*)", where, orderBy, skip, limit); - return dbConnection.QuerySingle(sql, parameters); - - } - - public virtual IEnumerable Select(IDbConnection dbConnection, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) - { - return Select>(dbConnection, null, where, orderBy, skip, limit); - } - /// - /// Select and call hooks. + /// ConvertParameters. Select. Call hooks. /// - public virtual IEnumerable Select(IDbConnection dbConnection, string? what, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) + public virtual List SelectDynamic(IDbConnection dbConnection, string? what, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) { var (sql, parameters) = GenerateSelectSql(what, where, orderBy, skip, limit); - return dbConnection.Query(sql, parameters).Select(d => + var queryResult = dbConnection.Query(sql, ConvertParameters(parameters)); + return queryResult.Select(d => { Type dynamicType = d.GetType(); - - var result = Activator.CreateInstance(); - foreach (var column in ColumnInfos) { object? value = null; @@ -428,17 +414,56 @@ CREATE TABLE {tableName}( value = column.ColumnType.ConvertFromDatabase(value); column.Hooks.AfterSelect(column, ref value, true); } - var propertyInfo = column.PropertyInfo; - if (propertyInfo is not null) + + if (dynamicProperty is not null) { - propertyInfo.SetValue(result, value); + dynamicProperty.SetValue(d, value); } } - return result; - }); + return d; + }).ToList(); + } + + public virtual int SelectCount(IDbConnection dbConnection, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) + { + var (sql, parameters) = GenerateSelectSql("COUNT(*)", where, orderBy, skip, limit); + return dbConnection.QuerySingle(sql, parameters); + + } + + public virtual TResult MapDynamicTo(dynamic d) + { + var result = Activator.CreateInstance(); + + Type dynamicType = d.GetType(); + Type resultType = typeof(TResult); + + foreach (var column in ColumnInfos) + { + var dynamicProperty = dynamicType.GetProperty(column.ColumnName); + // TODO: Maybe we can do better to get result property in case ColumnName is set to another value. + var resultProperty = resultType.GetProperty(column.ColumnName); + if (dynamicProperty is not null && resultProperty is not null) + { + resultProperty.SetValue(result, dynamicProperty.GetValue(d)); + } + } + + return result; + } + + /// + /// Select and call hooks. + /// + public virtual List Select(IDbConnection dbConnection, string? what, IWhereClause? where = null, IOrderByClause? orderBy = null, int? skip = null, int? limit = null) + { + List queryResult = SelectDynamic(dbConnection, what, where, orderBy, skip, limit).ToList(); + + return queryResult.Select(MapDynamicTo).ToList(); } + // TODO: Continue here. /// /// Insert a entity and call hooks. /// -- cgit v1.2.3