1.测试通过sql语句保存json数据，使用jsonb字段
public async Task<string> SavePostgreSQLTest(int count = 0,string files="") { string SqlStr = ""; string jsonParamStr = JsonConvert.SerializeObject(new { count = count, file = $"{files}", }); await Task.Run(() => { SqlStr = "INSERT INTO \"MyObj\".mytable (id, name, age,jsonb_result ,json_result) VALUES (@id, @Name, @Age,@JsonbResult::jsonb,@JsonResult)"; var parameters = new List<SugarParameter> { new SugarParameter("@id", 5), new SugarParameter("@Name", "周七"), new SugarParameter("@Age", 29), new SugarParameter("@JsonbResult", jsonParamStr), new SugarParameter("@JsonResult", jsonParamStr) }; try { SqlSugarHelperV1.ExcuteSqlString(SqlStr, parameters); } catch (Exception ex) { Log.Logger.Error($"LinkAsiaDB InitTireGlueingPLYDB 异常 {ex.Message} {ex.StackTrace}"); } }); return "jsonStr"; }2.sqlsugarhelper代码如下
using SqlSugar; namespace aaa.Helper.DB { public static class SqlSugarHelperV1 { private static SqlSugarScope _db; static SqlSugarHelperV1() { string mysqlConnectionStr = AppSettingsHelper.Configuration["DB:PostgreSQLConnStr"] ?? ""; // 初始化数据库连接 _db = new SqlSugarScope(new ConnectionConfig() { ConnectionString = mysqlConnectionStr,// "server=localhost;port=3306;database=testdb;user=root;password=123456;", DbType = SqlSugar.DbType.PostgreSQL, IsAutoCloseConnection = true, // 自动关闭连接 // InitKeyType = InitKeyType.Attribute, // 从特性读取主键和自增列信息 //ConfigureExternalServices = new ConfigureExternalServices() //{ // EntityService = (c, p) => // { // // 设置列的默认值 // if (p.IsPrimarykey == false && p.PropertyName == "Id") // { // p.IsIgnore = true; // 忽略非主键的Id列 // } // } //} }); //// 设置AOP事件 //_db.Aop.OnLogExecuting = (sql, pars) => //{ // Console.WriteLine($"SQL: {sql}"); // Console.WriteLine($"Parameters: {string.Join(", ", pars.Select(p => $"{p.ParameterName}:{p.Value}"))}"); //}; _db.Aop.OnError = (exp) => { Console.WriteLine($"PostgreSQL Error: {exp.Sql}"); Console.WriteLine($"Error Message: {exp.Message}"); }; } // 获取数据库实例 public static SqlSugarScope Db => _db; // 查询多个对象（使用 SQL） public static List<T> GetList<T>(string sql, object parameters = null) where T : class, new() { return _db.Ado.SqlQuery<T>(sql, parameters); } public static void ExcuteSqlString(string sql, object parameters = null) { _db.Ado.ExecuteCommand(sql, parameters); } } }postgresql的数据库表字段
下面是查询jsonb字段中的json中key的value值
select *,jsonb_result ->'name' as name1 from "MyObj".mytable;
)
