Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
A native JSON data type is introduced in SQL Server 2025 and Azure SQL databases. This native JSON data type is available for structured JSON documents with automatic validation, efficient transport, and efficient storage.
The Microsoft.Data.SqlClient provider offers built-in support for JSON columns through:
- The common language runtime (CLR)
stringtype for simple scenarios. SqlJsontype for advanced use cases, such as client-side validation and explicit handling of JSON semantics.- .NET 9 introduces a new SqlDbType.Json enumeration value for specifying JSON parameters. For earlier .NET versions, use
Microsoft.Data.SqlDbTypeExtensions, which provides forward-compatible support for new SQL types. - .NET 9 introduces a new
SqlDbType.Jsonenumeration value for specifying JSON parameters. - For earlier .NET versions,
Microsoft.Data.SqlDbTypeExtensionsis introduced as an alternative forSqlDbType.
Example
The following example demonstrates the following scenarios to interact with JSON data.
- Insert a JSON value using CLR type
string. - Insert the JSON value using
SqlJson. UsingSqlJsonis useful if you want to validate the JSON data before sending it to server. - Insert a JSON file by streaming.
- Read JSON values using
SqlDataReader. - Use stored procedure parameters for JSON data.
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();
}
}
}
}