Develop secret api. v26

11 files changed, 271 insertions, 192 deletions

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
 {
+    /// <summary>
+    /// If value is null, then it might because the column does not designated a value or it is designated null.
+    /// </summary>
     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.
+    /// <summary>Called after SELECT. Please use multicast if you want to customize it because there are many default behavior in it.</summary 
     public ColumnHookAction AfterSelect;
 
-    // Called before INSERT.
+    /// <summary>Called before INSERT. Please use multicast if you want to customize it because there are many default behavior in it.</summary 
     public ColumnHookAction BeforeInsert;
 
-    // Called before UPDATE
+    /// <summary>Called before UPDATE. Please use multicast if you want to customize it because there are many default behavior in it.</summary 
     public ColumnHookAction BeforeUpdate;
 }
 
@@ -29,6 +32,9 @@ public class ColumnInfo
 {
     private readonly AggregateColumnMetadata _metadata = new AggregateColumnMetadata();
 
+    /// <summary>
+    /// Initialize a column without corresponding property.
+    /// </summary>
     public ColumnInfo(TableInfo table, IColumnMetadata metadata, Type clrType, IColumnTypeProvider typeProvider)
     {
         Table = table;
@@ -42,6 +48,9 @@ public class ColumnInfo
         );
     }
 
+    /// <summary>
+    /// Initialize a column with corresponding property.
+    /// </summary>
     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;
+    /// <summary>
+    /// 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.
+    /// </summary>
+    /// <seealso cref="ColumnMetadataKeys.ActAsKey"/>
+    /// <seealso cref="TableInfo.KeyColumn"/>
+    public bool IsSpecifiedAsKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.ActAsKey) is true;
+    public ColumnIndexType Index => Metadata.GetValueOrDefault<ColumnIndexType?>(ColumnMetadataKeys.Index) ?? ColumnIndexType.None;
+    public UpdateBehavior UpdateBehavior => Metadata.GetValueOrDefault<UpdateBehavior?>(ColumnMetadataKeys.UpdateBehavior) ?? UpdateBehavior.NullIsNotUpdate;
+
+    /// <summary>
+    /// The real column name. Maybe set in metadata or just the property name.
+    /// </summary>
+    /// <value></value>
+    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<bool?>(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;
-    /// <summary>
-    /// 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.
-    /// </summary>
-    /// <seealso cref="ColumnMetadataKeys.ActAsKey"/>
-    /// <seealso cref="TableInfo.KeyColumn"/>
-    public bool IsSpecifiedAsKey => Metadata.GetValueOrDefault(ColumnMetadataKeys.ActAsKey) is true;
-
-    public ColumnIndexType Index => Metadata.GetValueOrDefault<ColumnIndexType?>(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. 
     /// </summary>
     public const string ActAsKey = nameof(ColumnAttribute.ActAsKey);
+
+    /// <summary>
+    /// Define what to do when update.
+    /// </summary>
+    public const string UpdateBehavior = nameof(ColumnAttribute.UpdateBehavior);
+}
+
+[Flags]
+public enum UpdateBehavior
+{
+    /// <summary>
+    /// Null value means do not update that column.
+    /// </summary>
+    NullIsNotUpdate = 0,
+    /// <summary>
+    /// Null value means set to null.
+    /// </summary>
+    NullIsSetNull = 1
 }
 
 public interface IColumnMetadata
@@ -98,22 +116,23 @@ public class ColumnAttribute : Attribute, IColumnMetadata
     // default None
     public ColumnIndexType Index { get; init; } = ColumnIndexType.None;
 
-    /// <see cref="ColumnMetadataKeys.DefaultEmptyForString"/>
+    /// <seealso cref="ColumnMetadataKeys.DefaultEmptyForString"/>
     public bool DefaultEmptyForString { get; init; }
 
-    /// <see cref="ColumnMetadataKeys.ClientGenerate"/>
+    /// <seealso cref="ColumnMetadataKeys.ClientGenerate"/>
     public bool ClientGenerate { get; init; }
 
-    /// <see cref="ColumnMetadataKeys.DefaultValueGenerator"/>
+    /// <seealso cref="ColumnMetadataKeys.DefaultValueGenerator"/>
     public string? DefaultValueGenerator { get; init; }
 
-    /// <see cref="ColumnMetadataKeys.NoUpdate"/>
+    /// <seealso cref="ColumnMetadataKeys.NoUpdate"/>
     public bool NoUpdate { get; init; }
 
-    /// <see cref="ColumnMetadataKeys.ActAsKey"/>
+    /// <seealso cref="ColumnMetadataKeys.ActAsKey"/>
     public bool ActAsKey { get; init; }
 
-
+    /// <seealso cref="ColumnMetadataKeys.UpdateBehavior">
+    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;
+
+/// <summary>
+/// This will always represent null value in database.
+/// </summary>
+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<InsertItem> 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;
+
+/// <summary>
+/// This exception means the exception is caused by user and can be safely shown to user.
+/// </summary>
+[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<OrderByItem> 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<ParamInfo>
+{
+    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)
+    /// <summary>
+    /// If you call this manually, it's your duty to call hooks.
+    /// </summary>
+    /// <seealso cref="Select"/>
+    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)
+    /// <summary>
+    /// If you call this manually, it's your duty to call hooks.
+    /// </summary>
+    /// <seealso cref="Insert"/>
+    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)
+    /// <summary>
+    /// If you call this manually, it's your duty to call hooks.
+    /// </summary>
+    /// <seealso cref="Update"/>
+    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)
+    /// <summary>
+    /// If you call this manually, it's your duty to call hooks.
+    /// </summary>
+    /// <seealso cref="Delete"/>
+    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<object?>(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<IEnumerable<object?>>(dbConnection, null, where, orderBy, skip, limit);
     }
 
+    /// <summary>
+    /// Select and call hooks.
+    /// </summary>
     public virtual IEnumerable<TResult> Select<TResult>(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.
+    /// <summary>
+    /// Insert a entity and call hooks.
+    /// </summary>
+    /// <returns>The key of insert entity.</returns>
     public object Insert(IDbConnection dbConnection, IInsertClause insert)
     {
         object? key = null;
@@ -468,6 +468,10 @@ CREATE TABLE {tableName}(
         return key ?? throw new Exception("No key???");
     }
 
+    /// <summary>
+    /// Upgrade a entity and call hooks.
+    /// </summary>
+    /// <returns>The key of insert entity.</returns>
     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<UpdateItem> 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);
     }
 }