【C#】JSON转DataTable存入数据库

由于JSON直接转DataTable可能会存在类型丢失
如下:
在这里插入图片描述

采用读取数据库表字段类型构建DataTable

/// <summary>
 /// JSON转DataTale存入数据库
 /// </summary>
 /// <param name="json"></param>
 public static void JsonDataTableTest(string json)
 {
     string sql = string.Format("SELECT name AS column_name , TYPE_NAME(system_type_id) AS column_type  FROM sys.columns WHERE object_id = OBJECT_ID(N'{0}')", "JSON_TO_DATATABLE_TEST");
     var dt = DbHelperSQL.Query(sql).Tables[0];
     DataTable mrow = new DataTable();
     foreach (DataRow row in dt.Rows)
     {
         string column_name = Convert.ToString(row["column_name"]);
         string column_type = Convert.ToString(row["column_type"]);
         Console.WriteLine($"column_name:{column_name},column_type:{column_type}");
         // 从数据库获取类型,构造DataTable,JSON直接转DataTable可能会类型丢失
         mrow.Columns.Add(column_name, DBTypeToCSharpType(column_type));
     }
     
     JArray data = JObject.Parse(json)["data"] as JArray;
     foreach(JObject o in data)
     {
         var row = mrow.NewRow();
         foreach (JProperty property in o.Properties())
         {
             if (mrow.Columns.Contains(property.Name))
             {
                 // 如果JSON的值为null
                 if(o[property.Name].Type == JTokenType.Null)
                 {
                     row[property.Name] = DBNull.Value;
                 }
                 else
                 {
                     row[property.Name] = o[property.Name];
                 }
                 
             }
         }
         mrow.Rows.Add(row);
     }

     Console.WriteLine(mrow);

     AddTable(mrow, "JSON_TO_DATATABLE_TEST");
 }

 /// <summary>
 /// 将数据库数据类型字符串,转为C#数据类型字符串。
 /// </summary>
 /// <param name="dbType">数据库数据类型字符串。</param>
 /// <returns>C#数据类型</returns>
 private static Type DBTypeToCSharpType(string dbType)
 {
     Type cSharpType = null;
     switch (dbType.ToLower())
     {
         case "bit":
             cSharpType = typeof(bool);
             break;
         case "tinyint":
             cSharpType = typeof(byte);
             break;
         case "smallint":
             cSharpType = typeof(short);
             break;
         case "int":
             cSharpType = typeof(int);
             break;
         case "bigint":
             cSharpType = typeof(long);
             break;
         case "real":
             cSharpType = typeof(float);
             break;
         case "float":
             cSharpType = typeof(double);
             break;
         case "smallmoney":
         case "money":
         case "decimal":
         case "numeric":
             cSharpType = typeof(decimal);
             break;
         case "char":
         case "varchar":
         case "nchar":
         case "nvarchar":
         case "text":
         case "ntext":
             cSharpType = typeof(string);
             break;
         case "samlltime":
         case "date":
         case "smalldatetime":
         case "datetime":
         case "datetime2":
         case "datetimeoffset":
             cSharpType = typeof(DateTime);
             break;
         case "timestamp":
         case "image":
         case "binary":
         case "varbinary":
             cSharpType = typeof(byte[]);
             break;
         case "uniqueidentifier":
             cSharpType = typeof(Guid);
             break;
         case "variant":
         case "sql_variant":
             cSharpType = typeof(object);
             break;
         default:
             cSharpType = typeof(string);
             break;
     }
     return cSharpType;
 }


 /// <summary>
 /// 批量更新
 /// </summary>
 /// <param name="dt">更新的内容</param>
 /// <param name="tableName">目标表</param>
 /// <returns></returns>
 public static Boolean AddTable(DataTable dt, string tableName)
 {
     using (SqlConnection sqlCon = new SqlConnection(DbHelperSQL.connectionString))
     {
         try
         {
             sqlCon.Open();
             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlCon))
             {
                 bulkCopy.DestinationTableName = tableName;
                 for (int i = 0; i < dt.Columns.Count; i++)
                 {
                     bulkCopy.ColumnMappings.Add(dt.Columns[i].Caption.ToString(), dt.Columns[i].Caption.ToString());
                 }
                 bulkCopy.WriteToServer(dt);
                 return true;
             }
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             sqlCon.Close();
         }
     }
 }

案例

private static void Main(string[] args)
{
   
    JsonDataTableTest(@"{""data"":[{""num"":100,""name"":""xiuyuan"",""time"":""2023-10-01 00:00:33""},{""num"":100.33,""name"":""xiaoming""}]}");
}

在这里插入图片描述

在这里插入图片描述

  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值