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

原创已于 2023-12-20 15:17:22 修改 · 1.2k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #c# #json

于 2023-05-22 21:47:07 首次发布

该代码示例展示了如何从JSON转换到DataTable时保持数据类型，通过查询数据库获取列类型，并使用C#的DBType转换函数确保类型正确性。然后，它使用SqlBulkCopy进行批量更新以保存到数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于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""}]}");
}

在这里插入图片描述