postgres14,使用SqlSugar插入json字段,一直有问题,大神帮忙看看 返回

SqlSugar 处理完成
14 394
该叫什么 moo 发布于2023/9/13
悬赏:0 飞吻

表test有个image字段,json类型

定义

[SugarColumn(IsJson = true)]

 public string image { get; set; }

装进参数中:

postParams["image"] = new {name = "test"};


 _db.Insertable(postParams).AS("test").ExecuteCommand();
报错:

Can't write CLR type <>f__AnonymousType13`1[System.String] with handler type TextHandler

如果是:

postParams[attr.key] = JsonConvert.DeserializeObject("json字符串");
也会报:

column "image" is of type json but expression is of type text



请问哪里错了呢,。

热忱回答14

  • fate sta fate sta VIP0
    2023/9/13

    你搞错用法了吧

    0 回复
  • fate sta fate sta VIP0
    2023/9/13

    [SugarColumn(IsJson = true)]

     public JObject image { get; set; }



    xx.image = JObject.parse(ew {name = "test"});


    差不多这样的

    0 回复
  • fate sta fate sta VIP0
    2023/9/13

    记得 清空错数据

    0 回复
  • moo moo VIP0
    2023/9/13

    不行啊,还是提示

    column "image" is of type json but expression is of type text

    打印的sql参数是:

    [Name]:@image [Value]:{

      "uid": 1,

      "status": "done",

      "name": "底座.png",

      "url": "http://localhost:8080/static/底座.png"

    } [Type]:AnsiString    


    0 回复
  • fate sta fate sta VIP0
    2023/9/13
    0 回复
  • fate sta fate sta VIP0
    2023/9/13

    按模版提供可以重现DEMO

    0 回复
  • moo moo VIP0
    2023/9/13

    数据库: postgres15

    表:

    create table expert_experts
    (
    id serial not null
    constraint expert_experts_pk
    primary key,
    avatar json
    );
    alter table expert_experts owner to postgres;
    create unique index expert_experts_id_uindex
    on expert_experts (id);

    实体:

    [SugarTable("expert_experts")]
    public class ExpertEntity
    {
        [SugarColumn(IsJson = true)]
        public JObject avatar { get; set; }
    }


    服务,关键代码:

    Dictionary<string, object> postParams = new Dictionary<string, object>();
    postParams[attr.key] = JObject.Parse("{\"avatar\":\"http://xxx.jpg\"}");
    _db.Insertable(postParams).AS("expert_experts").ExecuteCommand();


    保存错误异常:

    异常:42804: column "avatar" is of type json but expression is of type text


    0 回复
  • fate sta fate sta VIP0
    2023/9/13

    你用字典肯定不行

    0 回复
  • fate sta fate sta VIP0
    2023/9/13

    你都没用到类

    0 回复
  • moo moo VIP0
    2023/9/14

    参数传进来是字典,意思要再转成对象?

    0 回复
  • moo moo VIP0
    2023/9/14

    Dictionary<stringobject> 这个值已经是object了,还是不行?


    日期之前也有问题,需要包装成日期方面的对象,现在也把json包装成json对象了。

    0 回复
  • moo moo VIP0
    2023/9/14

    已解决

     Type objectType = typeof(T);
                object obj = Activator.CreateInstance<T>();
                foreach (var kvp in postParams)
                {
                    PropertyInfo property = objectType.GetProperty(kvp.Key);
                    if (property != null && property.CanWrite)
                    {
                        FormItemAttribute fia = property.GetCustomAttribute<FormItemAttribute>();
                        if (fia != null)
                        {
                            object value = Convert.ChangeType(kvp.Value, property.PropertyType);
                            property.SetValue(obj, value);
                        }
                     
                    }
                }
                return _db.InsertableByObject(obj).ExecuteCommand();



    0 回复
  • fate sta fate sta VIP0
    2023/9/14

    你写复杂了

    0 回复
  • fate sta fate sta VIP0
    2023/9/14
       //根据字典转成类对象         
        var value= db.DynamicBuilder().CreateObjectByType(type,new Dictionary<stringobject>() { { "Id", 1 }, { "Name""jack" } });

    不需要反射,有自带的字典转TYPE

    0 回复