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