From bd523a6a7cac09fe580223c3d75e41e1e100f603 Mon Sep 17 00:00:00 2001 From: crupest Date: Sun, 11 Dec 2022 10:43:56 +0800 Subject: Develop secret api. v26 --- .../CrupestApi.Commons/Crud/ColumnInfo.cs | 134 ++++++++++++--------- .../CrupestApi.Commons/Crud/ColumnMetadata.cs | 31 ++++- .../CrupestApi.Commons/Crud/DbNullValue.cs | 9 ++ .../Crud/DynamicParametersExtensions.cs | 41 ------- .../CrupestApi.Commons/Crud/InsertClause.cs | 8 +- .../CrupestApi.Commons/Crud/InternalException.cs | 15 +++ .../CrupestApi.Commons/Crud/OrderByClause.cs | 6 +- .../CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs | 62 ++++++++++ .../CrupestApi.Commons/Crud/TableInfo.cs | 130 ++++++++++---------- .../CrupestApi.Commons/Crud/UpdateClause.cs | 9 +- .../CrupestApi.Commons/Crud/WhereClause.cs | 18 ++- 11 files changed, 271 insertions(+), 192 deletions(-) create mode 100644 docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs delete mode 100644 docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs create mode 100644 docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InternalException.cs create mode 100644 docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs (limited to 'docker/crupest-api/CrupestApi/CrupestApi.Commons') diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs index 545397d..800594d 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnInfo.cs @@ -6,6 +6,9 @@ namespace CrupestApi.Commons.Crud; public class ColumnHooks { + /// + /// If value is null, then it might because the column does not designated a value or it is designated null. + /// public delegate void ColumnHookAction(ColumnInfo column, ref object? value); public ColumnHooks(ColumnHookAction afterSelect, ColumnHookAction beforeInsert, ColumnHookAction beforeUpdate) @@ -15,13 +18,13 @@ public class ColumnHooks BeforeUpdate = beforeUpdate; } - // Called after SELECT. + /// Called after SELECT. Please use multicast if you want to customize it because there are many default behavior in it.Called before INSERT. Please use multicast if you want to customize it because there are many default behavior in it.Called before UPDATE. Please use multicast if you want to customize it because there are many default behavior in it. + /// Initialize a column without corresponding property. + /// public ColumnInfo(TableInfo table, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider) { Table = table; @@ -42,6 +48,9 @@ public class ColumnInfo ); } + /// + /// Initialize a column with corresponding property. + /// public ColumnInfo(TableInfo table, PropertyInfo propertyInfo, IColumnTypeProvider typeProvider) { Table = table; @@ -62,6 +71,7 @@ public class ColumnInfo } public TableInfo Table { get; } + // If null, there is no corresponding property. public PropertyInfo? PropertyInfo { get; } = null; @@ -71,6 +81,56 @@ public class ColumnInfo public ColumnHooks Hooks { get; } + public bool IsPrimaryKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsPrimaryKey) is true; + public bool IsAutoIncrement => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsAutoIncrement) is true; + public bool IsNotNull => IsPrimaryKey || Metadata.GetValueOrDefault(ColumnMetadataKeys.NotNull) is true; + public bool IsClientGenerate => Metadata.GetValueOrDefault(ColumnMetadataKeys.ClientGenerate) is true; + public bool IsNoUpdate => Metadata.GetValueOrDefault(ColumnMetadataKeys.NoUpdate) is true; + /// + /// This only returns metadata value. It doesn't not fall back to primary column. If you want to get the real key column, go to table info. + /// + /// + /// + public bool IsSpecifiedAsKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.ActAsKey) is true; + public ColumnIndexType Index => Metadata.GetValueOrDefault(ColumnMetadataKeys.Index) ?? ColumnIndexType.None; + public UpdateBehavior UpdateBehavior => Metadata.GetValueOrDefault(ColumnMetadataKeys.UpdateBehavior) ?? UpdateBehavior.NullIsNotUpdate; + + /// + /// The real column name. Maybe set in metadata or just the property name. + /// + /// + public string ColumnName + { + get + { + object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.ColumnName); + Debug.Assert(value is null || value is string); + return ((string?)value ?? PropertyInfo?.Name) ?? throw new Exception("Failed to get column name."); + } + } + + public MethodInfo? DefaultValueGeneratorMethod + { + get + { + object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.DefaultValueGenerator); + Debug.Assert(value is null || value is string); + MethodInfo? result; + if (value is null) + { + string methodName = ColumnName + "DefaultValueGenerator"; + result = Table.EntityType.GetMethod(methodName, BindingFlags.Static); + } + else + { + string methodName = (string)value; + result = Table.EntityType.GetMethod(methodName, BindingFlags.Static) ?? throw new Exception("The default value generator does not exist."); + } + + return result; + } + } + private void TryCoerceStringFromNullToEmpty(ref object? value) { if (ColumnType.ClrType == typeof(string) && (Metadata.GetValueOrDefault(ColumnMetadataKeys.DefaultEmptyForString) ?? false) && value is null) @@ -88,85 +148,39 @@ public class ColumnInfo { if (column.IsClientGenerate && value is not null) { - throw new Exception($"Column {column.ColumnName} can't be set manually."); - } - - var defaultValueGeneratorMethod = DefaultValueGeneratorMethod; - if (defaultValueGeneratorMethod is not null) - { - value = defaultValueGeneratorMethod.Invoke(null, new object[] { }); + throw new UserException($"'{column.ColumnName}' can't be set manually. It is auto generated."); } + DefaultValueGeneratorMethod?.Invoke(null, new object[] { }); OnBeforeSet(column, ref value); } protected void OnBeforeUpdate(ColumnInfo column, ref object? value) { - OnBeforeSet(column, ref value); - if (column.IsNoUpdate) { - throw new Exception($"Column {column.ColumnName} not updatable."); + throw new UserException($"'{column.ColumnName}' is not updatable."); } - } - - protected void OnBeforeSet(ColumnInfo column, ref object? value) - { - TryCoerceStringFromNullToEmpty(ref value); - if (value is null && column.IsNotNull) + if ((column.UpdateBehavior & UpdateBehavior.NullIsSetNull) != 0 && value is null) { - throw new Exception($"Column {column.ColumnName} can't be null."); + value = DbNullValue.Instance; } - } - public string ColumnName - { - get - { - object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.ColumnName); - Debug.Assert(value is null || value is string); - return ((string?)value ?? PropertyInfo?.Name) ?? throw new Exception("Failed to get column name."); - } + OnBeforeSet(column, ref value); } - public MethodInfo? DefaultValueGeneratorMethod + protected void OnBeforeSet(ColumnInfo column, ref object? value) { - get - { - object? value = Metadata.GetValueOrDefault(ColumnMetadataKeys.DefaultValueGenerator); - Debug.Assert(value is null || value is string); - MethodInfo? result; - if (value is null) - { - string methodName = ColumnName + "DefaultValueGenerator"; - result = Table.EntityType.GetMethod(methodName, BindingFlags.Static); - } - else - { - string methodName = (string)value; - result = Table.EntityType.GetMethod(methodName, BindingFlags.Static) ?? throw new Exception("The default value generator does not exist."); - } + TryCoerceStringFromNullToEmpty(ref value); - return result; + if (value is null && column.IsNotNull) + { + throw new UserException($"{column.ColumnName} can't be null."); } } - public bool IsPrimaryKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsPrimaryKey) is true; - public bool IsAutoIncrement => Metadata.GetValueOrDefault(ColumnMetadataKeys.IsAutoIncrement) is true; - public bool IsNotNull => IsPrimaryKey || Metadata.GetValueOrDefault(ColumnMetadataKeys.NotNull) is true; - public bool IsClientGenerate => Metadata.GetValueOrDefault(ColumnMetadataKeys.ClientGenerate) is true; - public bool IsNoUpdate => Metadata.GetValueOrDefault(ColumnMetadataKeys.NoUpdate) is true; - /// - /// This only returns metadata value. It doesn't not fall back to primary column. If you want to get the real key column, go to table info. - /// - /// - /// - public bool IsSpecifiedAsKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.ActAsKey) is true; - - public ColumnIndexType Index => Metadata.GetValueOrDefault(ColumnMetadataKeys.Index) ?? ColumnIndexType.None; - public string GenerateCreateTableColumnString(string? dbProviderId = null) { StringBuilder result = new StringBuilder(); diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs index 9fb3999..e7c74f3 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ColumnMetadata.cs @@ -34,6 +34,24 @@ public static class ColumnMetadataKeys /// This column acts as key when get one entity for http get method in path. /// public const string ActAsKey = nameof(ColumnAttribute.ActAsKey); + + /// + /// Define what to do when update. + /// + public const string UpdateBehavior = nameof(ColumnAttribute.UpdateBehavior); +} + +[Flags] +public enum UpdateBehavior +{ + /// + /// Null value means do not update that column. + /// + NullIsNotUpdate = 0, + /// + /// Null value means set to null. + /// + NullIsSetNull = 1 } public interface IColumnMetadata @@ -98,22 +116,23 @@ public class ColumnAttribute : Attribute, IColumnMetadata // default None public ColumnIndexType Index { get; init; } = ColumnIndexType.None; - /// + /// public bool DefaultEmptyForString { get; init; } - /// + /// public bool ClientGenerate { get; init; } - /// + /// public string? DefaultValueGenerator { get; init; } - /// + /// public bool NoUpdate { get; init; } - /// + /// public bool ActAsKey { get; init; } - + /// + public UpdateBehavior UpdateBehavior { get; init; } = UpdateBehavior.NullIsNotUpdate; public bool TryGetValue(string key, out object? value) { diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs new file mode 100644 index 0000000..5dc5a61 --- /dev/null +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DbNullValue.cs @@ -0,0 +1,9 @@ +namespace CrupestApi.Commons.Crud; + +/// +/// This will always represent null value in database. +/// +public class DbNullValue +{ + public static DbNullValue Instance { get; } = new DbNullValue(); +} \ No newline at end of file diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs deleted file mode 100644 index 956206d..0000000 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/DynamicParametersExtensions.cs +++ /dev/null @@ -1,41 +0,0 @@ -using System.Data; -using System.Diagnostics; -using Dapper; - -namespace CrupestApi.Commons.Crud; - -public static class DynamicParametersExtensions -{ - private static Random random = new Random(); - private const string chars = "abcdefghijklmnopqrstuvwxyz"; - - public static string GenerateRandomKey(int length) - { - lock (random) - { - var result = new string(Enumerable.Repeat(chars, length) - .Select(s => s[random.Next(s.Length)]).ToArray()); - return result; - } - } - - public static string GenerateRandomParameterName(DynamicParameters parameters) - { - var parameterName = GenerateRandomKey(10); - int retryTimes = 1; - while (parameters.ParameterNames.Contains(parameterName)) - { - retryTimes++; - Debug.Assert(retryTimes <= 100); - parameterName = GenerateRandomKey(10); - } - return parameterName; - } - - public static string AddRandomNameParameter(this DynamicParameters parameters, object? value) - { - var parameterName = GenerateRandomParameterName(parameters); - parameters.Add(parameterName, value); - return parameterName; - } -} diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs index b5f9f38..7f248cf 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InsertClause.cs @@ -19,7 +19,7 @@ public interface IInsertClause : IClause { List Items { get; } string GenerateColumnListSql(string? dbProviderId = null); - (string sql, DynamicParameters parameters) GenerateValueListSql(string? dbProviderId = null); + (string sql, ParamList parameters) GenerateValueListSql(string? dbProviderId = null); } public class InsertClause : IInsertClause @@ -57,14 +57,14 @@ public class InsertClause : IInsertClause return string.Join(", ", Items.Select(i => i.ColumnName)); } - public (string sql, DynamicParameters parameters) GenerateValueListSql(string? dbProviderId = null) + public (string sql, ParamList parameters) GenerateValueListSql(string? dbProviderId = null) { - var parameters = new DynamicParameters(); + var parameters = new ParamList(); var sb = new StringBuilder(); for (var i = 0; i < Items.Count; i++) { var item = Items[i]; - var parameterName = parameters.AddRandomNameParameter(item.Value); + var parameterName = parameters.AddRandomNameParameter(item.Value, item.ColumnName); sb.Append($"@{parameterName}"); if (i != Items.Count - 1) sb.Append(", "); diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InternalException.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InternalException.cs new file mode 100644 index 0000000..1a10b97 --- /dev/null +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/InternalException.cs @@ -0,0 +1,15 @@ +namespace CrupestApi.Commons.Crud; + +/// +/// This exception means the exception is caused by user and can be safely shown to user. +/// +[System.Serializable] +public class UserException : Exception +{ + public UserException() { } + public UserException(string message) : base(message) { } + public UserException(string message, System.Exception inner) : base(message, inner) { } + protected UserException( + System.Runtime.Serialization.SerializationInfo info, + System.Runtime.Serialization.StreamingContext context) : base(info, context) { } +} \ No newline at end of file diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs index 68b5d60..a1aaa45 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/OrderByClause.cs @@ -22,7 +22,7 @@ public class OrderByItem public interface IOrderByClause : IClause { List Items { get; } - (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null); + (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null); } public class OrderByClause : IOrderByClause @@ -44,8 +44,8 @@ public class OrderByClause : IOrderByClause return Items.Select(x => x.ColumnName).ToList(); } - public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null) + public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null) { - return ("ORDER BY " + string.Join(", ", Items.Select(i => i.GenerateSql())), new DynamicParameters()); + return ("ORDER BY " + string.Join(", ", Items.Select(i => i.GenerateSql())), new ParamList()); } } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs new file mode 100644 index 0000000..4b253b7 --- /dev/null +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/ParamMap.cs @@ -0,0 +1,62 @@ +using System.Data; +using System.Diagnostics; + +namespace CrupestApi.Commons.Crud; + +public record ParamInfo(string Name, object? Value, string? ColumnName = null); + +public class ParamList : List +{ + private static Random random = new Random(); + private const string chars = "abcdefghijklmnopqrstuvwxyz"; + public static string GenerateRandomKey(int length) + { + lock (random) + { + var result = new string(Enumerable.Repeat(chars, length) + .Select(s => s[random.Next(s.Length)]).ToArray()); + return result; + } + } + + public string GenerateRandomParameterName() + { + var parameterName = GenerateRandomKey(10); + int retryTimes = 1; + while (ContainsKey(parameterName)) + { + retryTimes++; + Debug.Assert(retryTimes <= 100); + parameterName = GenerateRandomKey(10); + } + return parameterName; + } + + + public bool ContainsKey(string name) + { + return this.SingleOrDefault(p => p.Name.Equals(name, StringComparison.OrdinalIgnoreCase)) is not null; + } + + public object? this[string key] + { + get + { + return this.SingleOrDefault(p => p.Name.Equals(key, StringComparison.OrdinalIgnoreCase)) ?? throw new KeyNotFoundException("Key not found."); + } + } + + public void Add(string name, object? value, string? columnName = null) + { + Add(new ParamInfo(name, value, columnName)); + } + + // Return the random name. + public string AddRandomNameParameter(object? value, string? columnName = null) + { + var parameterName = GenerateRandomParameterName(); + var param = new ParamInfo(parameterName, value, columnName); + Add(param); + return parameterName; + } +} diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs index 498529c..93d02fd 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/TableInfo.cs @@ -136,13 +136,19 @@ public class TableInfo { // Check if there is only one primary key. bool hasPrimaryKey = false; + bool hasKey = false; foreach (var column in ColumnInfos) { if (column.IsPrimaryKey) { - if (hasPrimaryKey) throw new Exception("Two columns are primary key."); + if (hasPrimaryKey) throw new Exception("More than one columns are primary key."); hasPrimaryKey = true; } + + if (column.IsSpecifiedAsKey) + { + if (hasKey) throw new Exception("More than one columns are specified as key column."); + } } if (!hasPrimaryKey) throw new Exception("No column is primary key."); @@ -231,22 +237,26 @@ CREATE TABLE {tableName}( } } - public (string sql, DynamicParameters parameters) GenerateSelectSql(string? what, IWhereClause? whereClause, IOrderByClause? orderByClause = null, int? skip = null, int? limit = null, string? dbProviderId = null) + /// + /// 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); CheckRelatedColumns(orderByClause); - var parameters = new DynamicParameters(); + var parameters = new ParamList(); StringBuilder result = new StringBuilder() - .Append($"SELECT {what ?? "*"} FROM ") + .Append($"SELECT {selectWhat ?? "*"} FROM ") .Append(TableName); if (whereClause is not null) { result.Append(' '); var (whereSql, whereParameters) = whereClause.GenerateSql(dbProviderId); - parameters.AddDynamicParams(whereParameters); + parameters.AddRange(whereParameters); result.Append(whereSql); } @@ -254,7 +264,7 @@ CREATE TABLE {tableName}( { result.Append(' '); var (orderBySql, orderByParameters) = orderByClause.GenerateSql(dbProviderId); - parameters.AddDynamicParams(orderByClause); + parameters.AddRange(orderByParameters); result.Append(orderBySql); } @@ -275,49 +285,15 @@ CREATE TABLE {tableName}( return (result.ToString(), parameters); } - public InsertClause GenerateInsertClauseFromEntity(object entity) - { - Debug.Assert(EntityType.IsInstanceOfType(entity)); - - var insertClause = InsertClause.Create(); - - foreach (var column in ColumnInfos) - { - var propertyInfo = column.PropertyInfo; - if (propertyInfo is null) - { - if (column.IsAutoIncrement) - { - continue; - } - else - { - throw new Exception($"Property {column.ColumnName} not found."); - } - } - - var propertyValue = propertyInfo.GetValue(entity); - if (propertyValue is null) - { - if (column.IsAutoIncrement) - { - continue; - } - else - { - insertClause.Add(column.ColumnName, propertyValue); - } - } - } - - return insertClause; - } - - public (string sql, DynamicParameters parameters) GenerateInsertSql(IInsertClause insertClause, string? dbProviderId = null) + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + public (string sql, ParamList parameters) GenerateInsertSql(IInsertClause insertClause, string? dbProviderId = null) { CheckRelatedColumns(insertClause); - var parameters = new DynamicParameters(); + var parameters = new ParamList(); var result = new StringBuilder() .Append("INSERT INTO ") @@ -329,41 +305,49 @@ CREATE TABLE {tableName}( var (valueSql, valueParameters) = insertClause.GenerateValueListSql(dbProviderId); result.Append(valueSql).Append(");"); - parameters.AddDynamicParams(valueParameters); + parameters.AddRange(valueParameters); return (result.ToString(), parameters); } - public (string sql, DynamicParameters parameters) GenerateUpdateSql(IWhereClause? whereClause, IUpdateClause updateClause) + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + public (string sql, ParamList parameters) GenerateUpdateSql(IWhereClause? whereClause, IUpdateClause updateClause) { CheckRelatedColumns(whereClause); CheckRelatedColumns(updateClause); - var parameters = new DynamicParameters(); + var parameters = new ParamList(); StringBuilder sb = new StringBuilder("UPDATE "); sb.Append(TableName); sb.Append(" SET "); var (updateSql, updateParameters) = updateClause.GenerateSql(); sb.Append(updateSql); - parameters.AddDynamicParams(updateParameters); + parameters.AddRange(updateParameters); if (whereClause is not null) { sb.Append(" WHERE "); var (whereSql, whereParameters) = whereClause.GenerateSql(); sb.Append(whereSql); - parameters.AddDynamicParams(whereParameters); + parameters.AddRange(whereParameters); } sb.Append(';'); return (sb.ToString(), parameters); } - public (string sql, DynamicParameters parameters) GenerateDeleteSql(IWhereClause? whereClause) + /// + /// If you call this manually, it's your duty to call hooks. + /// + /// + public (string sql, ParamList parameters) GenerateDeleteSql(IWhereClause? whereClause) { CheckRelatedColumns(whereClause); - var parameters = new DynamicParameters(); + var parameters = new ParamList(); StringBuilder sb = new StringBuilder("DELETE FROM "); sb.Append(TableName); @@ -371,7 +355,7 @@ CREATE TABLE {tableName}( { sb.Append(" WHERE "); var (whereSql, whereParameters) = whereClause.GenerateSql(); - parameters.AddDynamicParams(whereParameters); + parameters.AddRange(whereParameters); sb.Append(whereSql); } sb.Append(';'); @@ -379,19 +363,29 @@ CREATE TABLE {tableName}( return (sb.ToString(), parameters); } - private DynamicParameters ConvertParameters(DynamicParameters parameters) + private DynamicParameters ConvertParameters(ParamList parameters) { var result = new DynamicParameters(); - foreach (var paramName in parameters.ParameterNames) + foreach (var param in parameters) { - var value = parameters.Get(paramName); - if (value is null) + if (param.Value is null || param.Value is DbNullValue) { - result.Add(paramName, null); + result.Add(param.Name, null); continue; } - var typeInfo = _columnTypeProvider.Get(value.GetType()); - result.Add(paramName, typeInfo.ConvertToDatabase(value)); + + var columnName = param.ColumnName; + IColumnTypeInfo typeInfo; + if (columnName is not null) + { + typeInfo = GetColumn(columnName).ColumnType; + } + else + { + typeInfo = _columnTypeProvider.Get(param.Value.GetType()); + } + + result.Add(param.Name, typeInfo.ConvertToDatabase(param.Value), typeInfo.DbType); } return result; } @@ -408,6 +402,9 @@ CREATE TABLE {tableName}( return Select>(dbConnection, null, where, orderBy, skip, limit); } + /// + /// Select and call hooks. + /// public virtual IEnumerable Select(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); @@ -422,9 +419,9 @@ CREATE TABLE {tableName}( object? value = null; var dynamicProperty = dynamicType.GetProperty(column.ColumnName); if (dynamicProperty is not null) value = dynamicProperty.GetValue(d); - column.Hooks.AfterSelect(column, ref value); if (value is not null) value = column.ColumnType.ConvertFromDatabase(value); + column.Hooks.AfterSelect(column, ref value); var propertyInfo = column.PropertyInfo; if (propertyInfo is not null) { @@ -436,7 +433,10 @@ CREATE TABLE {tableName}( }); } - // Returns the insert entity's key. + /// + /// Insert a entity and call hooks. + /// + /// The key of insert entity. public object Insert(IDbConnection dbConnection, IInsertClause insert) { object? key = null; @@ -468,6 +468,10 @@ CREATE TABLE {tableName}( return key ?? throw new Exception("No key???"); } + /// + /// Upgrade a entity and call hooks. + /// + /// The key of insert entity. public virtual int Update(IDbConnection dbConnection, IWhereClause? where, IUpdateClause update) { var (sql, parameters) = GenerateUpdateSql(where, update); diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs index b9cafee..de5c6c3 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/UpdateClause.cs @@ -1,5 +1,4 @@ using System.Text; -using Dapper; namespace CrupestApi.Commons.Crud; @@ -18,7 +17,7 @@ public class UpdateItem public interface IUpdateClause : IClause { List Items { get; } - (string sql, DynamicParameters parameters) GenerateSql(); + (string sql, ParamList parameters) GenerateSql(); } public class UpdateClause : IUpdateClause @@ -56,9 +55,9 @@ public class UpdateClause : IUpdateClause return Items.Select(i => i.ColumnName).ToList(); } - public (string sql, DynamicParameters parameters) GenerateSql() + public (string sql, ParamList parameters) GenerateSql() { - var parameters = new DynamicParameters(); + var parameters = new ParamList(); StringBuilder result = new StringBuilder(); @@ -69,7 +68,7 @@ public class UpdateClause : IUpdateClause result.Append(", "); } - var parameterName = parameters.AddRandomNameParameter(item.Value); + var parameterName = parameters.AddRandomNameParameter(item.Value, item.ColumnName); result.Append($"{item.ColumnName} = @{parameterName}"); } diff --git a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs index 98fe49d..bf3d8b1 100644 --- a/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs +++ b/docker/crupest-api/CrupestApi/CrupestApi.Commons/Crud/WhereClause.cs @@ -1,12 +1,10 @@ -using System.Data; using System.Text; -using Dapper; namespace CrupestApi.Commons.Crud; public interface IWhereClause : IClause { - (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null); + (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null); } public class CompositeWhereClause : IWhereClause @@ -28,12 +26,12 @@ public class CompositeWhereClause : IWhereClause return this; } - public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null) + public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null) { - var parameters = new DynamicParameters(); + var parameters = new ParamList(); var sql = new StringBuilder(); var subclauses = GetSubclauses(); - if (subclauses is null) return ("", parameters); + if (subclauses is null) return ("", new()); var first = true; foreach (var subclause in Subclauses) { @@ -56,7 +54,7 @@ public class CompositeWhereClause : IWhereClause { sql.Append(")"); } - parameters.AddDynamicParams(subParameters); + parameters.AddRange(subParameters); } return (sql.ToString(), parameters); } @@ -162,10 +160,10 @@ public class SimpleCompareWhereClause : IWhereClause return new SimpleCompareWhereClause(column, "<=", value); } - public (string sql, DynamicParameters parameters) GenerateSql(string? dbProviderId = null) + public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null) { - var parameters = new DynamicParameters(); - var parameterName = parameters.AddRandomNameParameter(Value); + var parameters = new ParamList(); + var parameterName = parameters.AddRandomNameParameter(Value, Column); return ($"{Column} {Operator} @{parameterName}", parameters); } } -- cgit v1.2.3