SQL Server 2025 和 Azure SQL 数据库中引入了原生 JSON 数据类型。 此本机 JSON 数据类型适用于具有自动验证、高效传输和高效存储的结构化 JSON 文档。
Microsoft.Data.SqlClient 提供程序通过以下方法为 JSON 列提供内置支持：
- 适用于简单场景的通用语言运行时 (CLR)
string类型。 -
SqlJson类型用于高级使用场景，例如客户端验证和对 JSON 语义的显式处理。 - .NET 9 引入了用于指定 JSON 参数的新 SqlDbType.Json 枚举值。 对于早期 .NET 版本，请使用
Microsoft.Data.SqlDbTypeExtensions，它为新的 SQL 类型提供向前兼容的支持。 - .NET 9 引入了用于指定 JSON 参数的新
SqlDbType.Json枚举值。 - 对于早期 .NET 版本，
Microsoft.Data.SqlDbTypeExtensions作为替代方法SqlDbType引入。
Example
以下示例演示了以下与 JSON 数据交互的方案。
- 使用 CLR 类型
string插入 JSON 值。 - 使用
SqlJson插入 JSON 值。 如果要在将 JSON 数据发送到服务器之前验证 JSON 数据，则使用SqlJson非常有用。 - 通过流式传输插入 JSON 文件。
- 使用
SqlDataReader. 读取 JSON 值。 - 对 JSON 数据使用存储过程参数。
using System.Data;
using Microsoft.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlTypes;
using System.Text.Json;
using System;
using System.IO;
class Program
{
// NOTE: For production, prefer Encrypt=True and TrustServerCertificate=False with a valid cert.
private const string connString = @"Server=tcp:localhost;Database=Demo2;Integrated Security=True;Encrypt=true;TrustServerCertificate=true;";
private const string tableName = "jsonTab";
private const string createBody = "(Id INT IDENTITY(1,1) PRIMARY KEY, Data json NULL)";
static void Main(string[] args)
{
CreateTable(connString, tableName, createBody);
InsertJSONWithStr(connString);
InsertJSONWithSqlJson(connString);
InsertJSONFile(connString);
ReadJSONWithGetString(connString);
ReadJSONWithSqlJson(connString);
ReadJsonWithSPParam(connString);
}
#region Helper Methods to Create/Drop Table and stored procedures
public static void CreateSP(string connString, string spName, string spBody)
{
DropSP(connString, spName);
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
string spCreate = "CREATE PROCEDURE " + spName + spBody;
using (SqlCommand command = sqlConnection.CreateCommand())
{
command.CommandText = spCreate;
command.ExecuteNonQuery();
}
}
}
public static void DropSP(string connString, string spName)
{
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(string.Format("IF (OBJECT_ID('{0}') IS NOT NULL) \n DROP PROCEDURE {0}", spName), sqlConnection))
{
cmd.ExecuteNonQuery();
}
}
}
private static void CreateTable(string connString, string tableName, string createBody)
{
DropTable(connString, tableName);
string tableCreate = "CREATE TABLE [" + tableName + "]" + createBody;
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand command = sqlConnection.CreateCommand())
{
command.CommandText = tableCreate;
command.ExecuteNonQuery();
}
}
}
private static void DropTable(string connString, string tableName)
{
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(string.Format("IF (OBJECT_ID('{0}') IS NOT NULL) \n DROP TABLE {0}", tableName), sqlConnection))
{
cmd.ExecuteNonQuery();
}
}
}
#endregion
private static void ReadJsonWithSPParam(string connString)
{
Console.WriteLine("Reading JSON with Stored Procedure and Output Parameter");
// Create stored procedure for reading JSON value
string storedProcName = "GetJsonData";
string tableName = "jsonTab";
string createSP = $@"
@id int,
@jsonData json OUTPUT
AS
BEGIN
SELECT @jsonData = (SELECT Data FROM {tableName} WHERE Id = @id)
END;";
CreateSP(connString, storedProcName, createSP);
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
string procName = "GetJsonData";
// Execute Stored Procedure
using (SqlCommand spCommand = new SqlCommand(procName, connection))
{
spCommand.CommandType = CommandType.StoredProcedure;
spCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Direction = ParameterDirection.Input, Value = 1 });
//Use SqlDbType.Json depending on the dotnet version or or SqlDbTypeExtensions.Json
SqlParameter outputParam = new SqlParameter("@jsonData", SqlDbTypeExtensions.Json) { Direction = ParameterDirection.Output };
spCommand.Parameters.Add(outputParam);
spCommand.ExecuteNonQuery();
Console.WriteLine($"JSON Data from SP: {outputParam.Value}");
}
}
}
private static void ReadJSONWithSqlJson(string connString)
{
Console.WriteLine("Reading JSON into SqlJson");
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand("SELECT Data FROM jsonTab", sqlConnection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
//Read JSON with GetString
var jsonData = reader.GetSqlJson(0);
Console.WriteLine($"JSON Data: {jsonData}");
}
else
{
Console.WriteLine("JSON Data: NULL");
}
}
}
}
}
}
private static void ReadJSONWithGetString(string connString)
{
Console.WriteLine("Reading JSON with GetString");
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand("SELECT Data FROM jsonTab", sqlConnection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
//Read JSON with GetString
string jsonData = reader.GetString(0);
Console.WriteLine($"JSON Data: {jsonData}");
}
else
{
Console.WriteLine("JSON Data: NULL");
}
}
}
}
}
}
private static void InsertJSONFile(string connString)
{
Console.WriteLine("Inserting JSON File");
var dummyData = new
{
name = "Keith Johnson",
age = 36,
city = "Boston"
};
string jsonString = JsonSerializer.Serialize(dummyData, new JsonSerializerOptions { WriteIndented = true });
string jsonFile = "dummyData.json";
if (File.Exists(jsonFile))
{
File.Delete(jsonFile);
}
File.WriteAllText(jsonFile, jsonString);
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand("INSERT INTO jsonTab (Data) VALUES (@jsonData)", sqlConnection))
{
SqlParameter jsonParam = new SqlParameter("@jsonData", SqlDbTypeExtensions.Json);
using (StreamReader jsonFileReader = File.OpenText(jsonFile))
{
jsonParam.Value = jsonFileReader;
command.Parameters.Add(jsonParam);
command.ExecuteNonQuery();
}
}
}
if (File.Exists(jsonFile))
{
File.Delete(jsonFile);
}
}
private static void InsertJSONWithSqlJson(string connString)
{
Console.WriteLine("Inserting JSON with SqlJson");
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand("INSERT INTO jsonTab (Data) VALUES (@jsonData)", sqlConnection))
{
//Insert non-null value with SqlJson
string jsonData = @"{ ""name"": ""Jane Smith"", ""age"": 25, ""city"": ""Los Angeles"" }";
SqlJson sqlJsonObj = new SqlJson(jsonData);
SqlParameter jsonParam = new SqlParameter("@jsonData", SqlDbTypeExtensions.Json) { Value = sqlJsonObj };
command.Parameters.Add(jsonParam);
command.ExecuteNonQuery();
command.Parameters.Clear();
//Insert null value with SqlJson
sqlJsonObj = new SqlJson();
jsonParam.Value = sqlJsonObj;
command.Parameters.Add(jsonParam);
command.ExecuteNonQuery();
}
}
}
private static void InsertJSONWithStr(string connString)
{
Console.WriteLine("Inserting JSON with string");
string insertQuery = "INSERT INTO jsonTab (Data) VALUES (@jsonData)";
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(insertQuery, sqlConnection))
{
//Insert non-null value
string jsonData = @"{ ""name"": ""John Doe"", ""age"": 30, ""city"": ""New York"" }";
command.Parameters.AddWithValue("@jsonData", jsonData);
command.ExecuteNonQuery();
command.Parameters.Clear();
//Insert null value
command.Parameters.AddWithValue("@jsonData", DBNull.Value);
command.ExecuteNonQuery();
}
}
}
}