using Devart.Data.SqlServer;
using Devart.Data.SqlServer.SqlTypes;
using System.Data;

namespace SqlJsonConsoleApp
{
        internal class Program
        {
                static void Main(string[] args)
                {
                        string connString = @"Data Source=localhost;Integrated Security=False;User ID=sa;TrustServerCertificate=True;Database=dbname";

                        CreateTableAndProcedure(connString);
                        InsertData(connString);
                        ReadJsonData(connString);
                        ReadJsonParam(connString);
                }

                private static void CreateTableAndProcedure(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = connection.CreateCommand())
                                {
                                        string tableCreate = "CREATE TABLE [JsonTable] (Id INT IDENTITY(1,1) PRIMARY KEY, Data json NULL);";
                                        string storedProcCreate = $@"CREATE PROCEDURE GetJsonProc
                                                @id int,
                                                @data json OUTPUT
                                                AS
                                                BEGIN
                                                SELECT @data = (SELECT Data FROM JsonTable WHERE Id = @id)
                                                END;";

                                        command.CommandText = "DROP TABLE IF EXISTS [JsonTable]";
                                        command.ExecuteNonQuery();
                                        command.CommandText = tableCreate;
                                        command.ExecuteNonQuery();

                                        command.CommandText = "DROP PROCEDURE IF EXISTS GetJsonProc";
                                        command.ExecuteNonQuery();
                                        command.CommandText = storedProcCreate;
                                        command.ExecuteNonQuery();
                                }
                        }
                }

                private static void InsertData(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = new SqlCommand("INSERT INTO JsonTable (Data) VALUES (@data)", connection))
                                {
                                        // Insert JSON value
                                        string jsonString = @"{ ""company"": ""Devart"", ""industry"": ""Software Development"", ""products"": [ ""dotConnect"", ""Entity Developer"", ""dbForge Studio"" ], ""website"": ""www.devart.com"" }";
                                        SqlParameter jsonParam = new SqlParameter("@data", SqlType.Json);
                                        jsonParam.SqlValue = new SqlJson(jsonString);
                                        command.Parameters.Add(jsonParam);
                                        command.ExecuteNonQuery();

                                        // Insert null value
                                        command.Parameters.Clear();
                                        jsonParam.Value = new SqlJson();
                                        command.Parameters.Add(jsonParam);
                                        command.ExecuteNonQuery();
                                }
                        }
                }

                private static void ReadJsonData(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = new SqlCommand("SELECT Data FROM JsonTable", connection))
                                {
                                        using (SqlDataReader reader = command.ExecuteReader())
                                        {
                                                while (reader.Read())
                                                {
                                                        if (!reader.IsDBNull(0))
                                                        {
                                                                // Read JSON with GetSqlJson
                                                                SqlJson sqlJson = reader.GetSqlJson(0);
                                                                Console.WriteLine($"JSON data by GetSqlJson: {sqlJson}");

                                                                // Read JSON with GetString
                                                                string jsonString = reader.GetString(0);
                                                                Console.WriteLine($"JSON data by GetString: {jsonString}");
                                                        }
                                                        else
                                                                Console.WriteLine("JSON data: NULL");
                                                }
                                        }
                                }
                        }
                }

                private static void ReadJsonParam(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = new SqlCommand("GetJsonProc", connection))
                                {
                                        command.CommandType = CommandType.StoredProcedure;
                                        command.Parameters.Add(new SqlParameter("@id", SqlType.Int) { Direction = ParameterDirection.Input, Value = 1 });

                                        SqlParameter jsonParam = new SqlParameter("@data", SqlType.Json) { Direction = ParameterDirection.Output };
                                        command.Parameters.Add(jsonParam);

                                        command.ExecuteNonQuery();
                                        Console.WriteLine($"JSON data from StoredProcedure: {jsonParam.Value}");
                                }
                        }
                }
        }
}