locked
Insert JSON request/response into SQL 2016 RRS feed

  • Question

  • User-1104215994 posted

    Hello,

    There <g class="gr_ gr_87 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="87" data-gr-id="87">is</g> a REST API host and a client. My web API is in the middle, <g class="gr_ gr_158 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="158" data-gr-id="158">client</g> makes calls to my web API and I send <g class="gr_ gr_300 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="300" data-gr-id="300">it's</g> request to REST API host. Return response from host to the client. I would like to insert request/response JSON data into SQL 2016. I wonder if I can use OPENJSON in the code base and insert data inside of JSON request/response.

    Best Regards.

    Thursday, January 24, 2019 7:34 AM

Answers

  • User36583972 posted

    Hello,

    There is a REST API host and a client. My web API is in the middle, client makes calls to my web API and I send it's request to REST API host. Return response from host to the client. I would like to insert request/response JSON data into SQL 2016. I wonder if I can use OPENJSON in the code base and insert data inside of JSON request/response.

    Best Regards.


    Hi cenk1536,

    SQL Server (starting with 2016) supports the OPENJSON function.

    The following links for your reference.

    Inserting JSON Text into SQL Server Table
    https://www.codeproject.com/Articles/1087995/Inserting-JSON-Text-into-SQL-Server-Table

    Insert JSON into SQL Server 2016 using C# and OPENJSON
    https://stackoverflow.com/questions/43517540/insert-json-into-sql-server-2016-using-c-sharp-and-openjson

    If you want to know more about OPENJSON (Transact-SQL), you can go to the Transact-SQL Forum.

    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 25, 2019 8:05 AM
  • User36583972 posted

    I tried this way in C# but still same error.

    string json = "{ quantity': '2', 'currency': 'EURO' }";
                    string saveJSON =
                        "INSERT INTO Request SELECT * FROM OPENJSON (' "+ json+ " ') WITH ( quantity int , currency nchar(10) );"; 

    Hi cenk1536,

    I have made a test on my side. It is work.

    Please refer the following format and modify your code.

                string jsondata = " N'{ \"StudentId\":\"24\",\"Name\":\"Test 265\", \"Phone\":\"123456789\"}'";
                using (SqlConnection openCon = new SqlConnection(@"Data Source="))
                {
                    string saveJSON =
                        "INSERT INTO[StudentMaster] (StudentId, Name, Phone)  SELECT StudentId, Name, Phone FROM OPENJSON ("+ jsondata + ") WITH(StudentId int, Name nvarchar(50), Phone nvarchar(50));";
    
                    using (SqlCommand querySaveStaff = new SqlCommand(saveJSON))
                    {
                        querySaveStaff.Connection = openCon;
    
                        openCon.Open();
    
                        querySaveStaff.ExecuteNonQuery();
    
                    }
                }

    Besides, It would be appreciated if you could close the thread by marking helpful posts as an answer. This will help other members to find the solution quickly if they have faced the similar issue. If you have a new question you can start a new thread. Please don't ask several questions in the same thread.


    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 28, 2019 10:03 AM
  • User753101303 posted

    Just keep your previous code and use parameters. With the code you shown just add :

    querySaveStaff.Parameters.AddWithValue("@json",json) and that's it. For now the string you are building is not correct because of the ' delimiters (also 'quantity':2 could be enough). It's safer to use SQL parameters which are intended to easily and safely pass parameters to a SQL statement.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 28, 2019 10:08 AM
  • User36583972 posted

    cenk1536

    Thank you Yohann. I have one question; in my imlementation I am getting JSON from request so each time how can I convert my JSON request with format below?

    string jsondata = " N'{ \"StudentId\":\"24\",\"Name\":\"Test 265\", \"Phone\":\"123456789\"}'";

    Hi cenk1536,

    You only need to check your json data and splicing it into the following format.


    N'{ "StudentId":"23","Name":"Test 123", "Phone":"123456789"}'

    Beside, Please try to do it yourself, instead of asking someone to provide you working code.


    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 28, 2019 10:17 AM

All replies

  • User753101303 posted

    Hi,

    Try maybe https://docs.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-2017

    If you have a number of properties you could consider to keep that separate. You should be able to transform those values to an object and maybe assign  all those values in a single call using json_modify.

    Thursday, January 24, 2019 8:46 AM
  • User1120430333 posted

    The client can be anything using HTTPClinet. The client is an ASP.NET MVC project that using the DTO to do CRUD with the database that is  using a Data Access Layer that is using the DAO and DTO patterns sitting behind the WebAPI controller. The DTO is kept in a classlib project called Entities and all projects have project reference to Entitoes and know abou the DTO.

    https://en.wikipedia.org/wiki/Data_transfer_object

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    https://en.wikipedia.org/wiki/Data_access_object

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

     

    using System;
    using System.Collections.Generic;
    using System.Net.Http;
    using System.Net.Http.Headers;
    using System.Text;
    using Entities;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Linq;
    
    namespace ProgMgmntCore2UserIdentity.WebApi
    {
        public class WebApi : IWebApi
        {
            #region ProjectApi
            
            public List<DtoProject> GetProjsByUserIdApi(string userid)
            {
                var dtoprojects = new List<DtoProject>();
    
                using (var client = new HttpClient())
                {
                    var uri = new Uri("http://progmgmntcore2api.com/api/project/GetProjsByUserId?userid=" + userid);
    
                    var response = client.GetAsync(uri).Result;
    
                    if (!response.IsSuccessStatusCode)
                        throw new Exception(response.ToString());
    
                    var responseContent = response.Content;
                    var responseString = responseContent.ReadAsStringAsync().Result;
    
                    dynamic projects = JArray.Parse(responseString) as JArray;
    
                    foreach (var obj in projects)
                    {
                        DtoProject dto = obj.ToObject<DtoProject>();
    
                        dtoprojects.Add(dto);
                    }
                }
    
                return dtoprojects;
            }
    
            public DtoProject GetProjByIdApi(int id)
            {
                DtoProject dto;
    
                using (var client = new HttpClient())
                {
                    var uri = new Uri("http://progmgmntcore2api.com/api/project/GetProjById?id=" + id);
                    HttpResponseMessage getResponseMessage = client.GetAsync(uri).Result;
    
                    if (!getResponseMessage.IsSuccessStatusCode)
                        throw new Exception(getResponseMessage.ToString());
    
                    var responsemessage = getResponseMessage.Content.ReadAsStringAsync().Result;
    
                    dynamic project = JsonConvert.DeserializeObject(responsemessage);
    
                    dto = project.ToObject<DtoProject>();
                }
    
                return dto;
            }
    
            public void CreateProjectApi(DtoProject dto)
            {
                using (var client = new HttpClient { BaseAddress = new Uri("http://progmgmntcore2api.com") })
                {
                    string serailizeddto = JsonConvert.SerializeObject(dto);
    
                    var inputMessage = new HttpRequestMessage
                    {
                        Content = new StringContent(serailizeddto, Encoding.UTF8, "application/json")
                    };
    
                    inputMessage.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    
                    HttpResponseMessage message =
                        client.PostAsync("api/project/CreateProject", inputMessage.Content).Result;
    
                    if (!message.IsSuccessStatusCode)
                        throw new Exception(message.ToString());
                }
            }
    
            public void UpdateProjectApi(DtoProject dto)
            {
                using (var client = new HttpClient { BaseAddress = new Uri("http://progmgmntcore2api.com") })
                {
                    string serailizeddto = JsonConvert.SerializeObject(dto);
    
                    var inputMessage = new HttpRequestMessage
                    {
                        Content = new StringContent(serailizeddto, Encoding.UTF8, "application/json")
                    };
    
                    inputMessage.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    
                    HttpResponseMessage message =
                        client.PostAsync("api/project/UpdateProject", inputMessage.Content).Result;
    
                    if (!message.IsSuccessStatusCode)
                        throw new Exception(message.ToString());
                }
            }
    
            public void DeleteProjectApi(DtoId dto)
            {
                using (var client = new HttpClient { BaseAddress = new Uri("http://progmgmntcore2api.com") })
                {
                    string serailizeddto = JsonConvert.SerializeObject(dto);
    
                    var inputMessage = new HttpRequestMessage
                    {
                        Content = new StringContent(serailizeddto, Encoding.UTF8, "application/json")
                    };
    
                    inputMessage.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    
                    HttpResponseMessage message =
                        client.PostAsync("api/project/DeleteProject", inputMessage.Content).Result;
    
                    if (!message.IsSuccessStatusCode)
                        throw new Exception(message.ToString());
                }
            }
    
            #endregion
    }
    
    using System.Collections.Generic;
    using DAL;
    using Entities;
    using Microsoft.AspNetCore.Mvc;
    
    namespace ProgMgmntCore2Api.Controllers
    {
        [Produces("application/json")]
        [Route("api/[controller]")]
        [ApiController]
    
        public class ProjectController : ControllerBase, IProjectController
        {
            private readonly IDaoProject _daoProject;
    
            public ProjectController(IDaoProject daoProject)
            {
                _daoProject = daoProject;
            }
    
            [HttpGet]
            [Route("GetProjById")]
            public DtoProject GetProjectById(int id)
            {
                return  _daoProject.GetProjectById(id);
            }
            
            [HttpGet]
            [Route("GetProjsByUserId")]
            public List<DtoProject> GetProjectsByUserId(string userid)
            {
                return _daoProject.GetProjectsByUserId(userid);
            }
    
            [HttpPost]
            [Route("CreateProject")]
            public void Post_CreateProject(DtoProject dto)
            {
                _daoProject.CreateProject(dto);
            }
    
            [HttpPost]
            [Route("DeleteProject")]
            public void Post_DeleteProject(DtoId dto)
            {
                _daoProject.DeleteProject(dto.Id);
            }
    
            [HttpPost]
            [Route("UpdateProject")]
            public void Post_UpdateProject(DtoProject dto)
            {
                _daoProject.UpdateProject(dto);
            }
        }
    }
    
    using System;
    using System.Collections.Generic;
    using System.Text;
    using Entities;
    
    namespace DAL
    {
        public interface IDaoProject
        {
            DtoProject GetProjectById(int id);
            List<DtoProject> GetProjectsByUserId(string userid);
            void CreateProject(DtoProject dto);
            void UpdateProject(DtoProject dto);
            void DeleteProject(int id);
        }
    }
    ==================================================================
    
    using System.Collections.Generic;
    using System.Linq;
    using System.Transactions;
    using DAL.Models.DB;
    using Entities;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Options;
    
    namespace DAL
    {
        public class DaoProject :IDaoProject
        {
            private readonly IOptions<ConnectionStrings> _options;
            
            public DaoProject(IOptions<ConnectionStrings> options)
            {
                _options = options;
            }
    
            public DtoProject GetProjectById(int id)
            {
                var dto = new DtoProject();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    var project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
    
                    if (project == null) return dto;
                    dto.ProjectId = project.ProjectId;
                    dto.ClientName = project.ClientName;
                    dto.ProjectName = project.ProjectName;
                    dto.Technology = project.Technology;
                    dto.ProjectType = project.ProjectType;
                    dto.UserId = project.UserId;
                    dto.StartDate = project.StartDate;
                    dto.EndDate = project.EndDate;
                    dto.Cost = project.Cost;
                }
    
                return dto;
            }
    
            public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }
    
            public void CreateProject(DtoProject dto)
            {
                using (var context = new ProjectManagementContext(_options))
                {
                    var project = new Projects
                    {
                        ClientName = dto.ClientName,
                        ProjectName = dto.ProjectName,
                        Technology = dto.Technology,
                        ProjectType = dto.ProjectType,
                        UserId = dto.UserId,
                        StartDate = dto.StartDate,
                        EndDate = dto.EndDate,
                        Cost = dto.Cost
                    };
    
                    context.Projects.Add(project);
                    context.SaveChanges();
               }
            }
    
            public void UpdateProject(DtoProject dto)
            {
                var project = new Projects();
                
                using (var context = new ProjectManagementContext(_options))
                {
                   project = (context.Projects.Where(a => a.ProjectId == dto.ProjectId)).SingleOrDefault();
                }
    
                if (project != null)
                {
                    project.ClientName = dto.ClientName;
                    project.ProjectName = dto.ProjectName;
                    project.Technology = dto.Technology;
                    project.ProjectType = dto.ProjectType;
                    project.UserId = dto.UserId;
                    project.StartDate = dto.StartDate;
                    project.EndDate = dto.EndDate;
                    project.Cost = dto.Cost;
                }
    
                using (var dbcontext = new ProjectManagementContext(_options))
                {
                    if (project == null) return;
                    dbcontext.Entry(project).State = EntityState.Modified;
                    dbcontext.SaveChanges();
                }
            }
    
            public void DeleteProject(int id)
            {
                Projects project;
    
                using (var context = new ProjectManagementContext(_options))
                {
                   project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
                }
    
                if (project == null) return;
    
                using (var newContext = new ProjectManagementContext(_options))
                {
                   
                    var tasks = new DaoTask(_options).GetTasksByProjectId(project.ProjectId);
                    using (TransactionScope scope = new TransactionScope())
                    {
                        foreach (var task in tasks)
                        {
                            new DaoTask(_options).DeleteTask(task.TaskId);
                        }
    
                        newContext.Entry(project).State = EntityState.Deleted;
                        newContext.SaveChanges();
    
                        scope.Complete();
                    }
                }
            }
        }
    }
    
    
    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }
    

    Thursday, January 24, 2019 10:13 AM
  • User-1104215994 posted

    Actually what I want is something like below in the code base (C#). Can I use OPENJSON like this?

    using(SqlConnection openCon=new SqlConnection("your_connection_String"))
        {
          string saveJSON = "INSERT INTO Employee 
        SELECT * 
        FROM OPENJSON(@json)
        WITH (name nvarchar(50), 
              age int, 
              homeAddress nvarchar(max) AS JSON, 
              officeAddress nvarchar(max) AS JSON
             );";
    
          using(SqlCommand querySaveStaff = new SqlCommand(saveJSON))
           {
             querySaveStaff.Connection=openCon;
             
             .....
             openCon.Open();
    
    
           }
         }

    Friday, January 25, 2019 6:44 AM
  • User1120430333 posted

    https://www.codeproject.com/Articles/1087995/Inserting-JSON-Text-into-SQL-Server-Table

    Apparently you can.

    But for me, it would be buried down in a Data Access Layer using the DAO pattern behind the WebAPI.

    Also I would use SSMS and test the T-SQL before proceeding. I would also use a test framework like Nunit, a test classlib project, a test class to test the DAL testing the method in the DAO class as a functional test, which the test  classlib project can be  hosted by MSTest.

    You can also use a console program to do the same thing to test the DAO in the DAL.

    Then you can proceed to use a console program as the WebAPI client to test the WebAPI and the functionality provided by the DAO. Myself,  I would do it that way instead of just leaping trying to get it to work in some middle-man real scenario struggling to get it to work. 

    The way I talk about, you did about 99% of the testing to ensure  that you know  it is going to work. The 1% left is hook it up and verify that  it works.

    You could say, I am just going to put the persistence code directly in the controller, but  I don't see that being any better than putting DB code directly in a MVC controller in the MVC project. 

    The choice is  your choice as to how you do it.

    Friday, January 25, 2019 7:53 AM
  • User36583972 posted

    Hello,

    There is a REST API host and a client. My web API is in the middle, client makes calls to my web API and I send it's request to REST API host. Return response from host to the client. I would like to insert request/response JSON data into SQL 2016. I wonder if I can use OPENJSON in the code base and insert data inside of JSON request/response.

    Best Regards.


    Hi cenk1536,

    SQL Server (starting with 2016) supports the OPENJSON function.

    The following links for your reference.

    Inserting JSON Text into SQL Server Table
    https://www.codeproject.com/Articles/1087995/Inserting-JSON-Text-into-SQL-Server-Table

    Insert JSON into SQL Server 2016 using C# and OPENJSON
    https://stackoverflow.com/questions/43517540/insert-json-into-sql-server-2016-using-c-sharp-and-openjson

    If you want to know more about OPENJSON (Transact-SQL), you can go to the Transact-SQL Forum.

    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 25, 2019 8:05 AM
  • User753101303 posted

    Ah for some reason I though you wanted to insert data into your json payload when reading back.

    It seems it should work (I prefer to explicitely list inserted and selected fields). When posting code always tell what happens. You have an error ?

    Friday, January 25, 2019 8:16 AM
  • User-1104215994 posted

    Haven't tried yet :) I will let you know.

    Friday, January 25, 2019 8:20 AM
  • User753101303 posted

    Done a quick test with the following that works as you expect (I believe) :

    SELECT name,age,homeAddress,officeAddress
        FROM OPENJSON('{"name":"Test","age":99,"homeAddress":{"number":2,"street":"A"},"officeAddress":{"number":4,"street":"B"}}')
        WITH (name nvarchar(50), 
              age int, 
              homeAddress nvarchar(max) AS JSON, 
              officeAddress nvarchar(max) AS JSON
             )

    when in doubt especially with something quite simple, please just try first and if it doesn't work tell us what happens exactly. It shows :

    Test	99	{"number":2,"street":"A"}	{"number":4,"street":"B"}

    Friday, January 25, 2019 9:30 AM
  • User1120430333 posted

    It looks to me that a SQL Injection Attack could be done against it.   

    Friday, January 25, 2019 10:39 AM
  • User753101303 posted

    You mean because using a string for the OPENJSON parameter or more generally keeping data as JSON in the db ?

    For #1, this is actually a quick test in SSMS to understand if OPENJSON does fit his needs or which problem he have with that. He is using already parameters and I assume the OP will keep using that.

    For #2, I still have to a give this a closer look...

    Friday, January 25, 2019 3:16 PM
  • User1120430333 posted

    You mean because using a string for the OPENJSON parameter or more generally keeping data as JSON in the db ?

    For #1, this is actually a quick test in SSMS to understand if OPENJSON does fit his needs or which problem he have with that. He is using already parameters and I assume the OP will keep using that.

    For #2, I still have to a give this a closer look...

    using(SqlConnection openCon=new SqlConnection("your_connection_String"))
        {
          string saveJSON = "INSERT INTO Employee 
        SELECT * 
        FROM OPENJSON(@json)
        WITH (name nvarchar(50), 
              age int, 
              homeAddress nvarchar(max) AS JSON, 
              officeAddress nvarchar(max) AS JSON
             );";
    
          using(SqlCommand querySaveStaff = new SqlCommand(saveJSON))
           {
             querySaveStaff.Connection=openCon;
             
             .....
             openCon.Open();
    
    
           }
         }

    I am talking about the above. How is the above any different in doing a SQL Injection Attack on the code in its present format? It looks to me that it can easily be manipulated.

    Friday, January 25, 2019 6:50 PM
  • User-1104215994 posted

    Hi,

    In this article, it says If your JSON is valid there is no risk that you will have some SQL injection attack. OPENJSON will not execute any command – it just returns a table row if JSON text is properly formatted.

    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/09/22/openjson-the-easiest-way-to-import-json-text-into-table/

    Saturday, January 26, 2019 6:52 AM
  • User-1104215994 posted

    Hello,

    I altered compatibility level of SQL. I tried on SQL server <g class="gr_ gr_74 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="74" data-gr-id="74">managmenet</g> studio, It works. But I am getting this error when I try to run it on Visual Studio 2017.

    Here is my error:

    "Incorrect syntax near 'quantity'.\r\nIncorrect syntax near the keyword 'with'. If this statement is a common table expression, an <g class="gr_ gr_287 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="287" data-gr-id="287">xmlnamespaces</g> clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

    Here is my sample code:

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace TestJSON
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (SqlConnection openCon = new SqlConnection("Server=Test\\SQLEXPRESS;Database=Test;Trusted_Connection=True;"))
                {
                    string saveJSON =
                        "INSERT INTO Request SELECT * FROM OPENJSON ({ 'quantity': '2', 'currency': 'EURO' }) WITH (quantity int , currency nchar(10));"; 
    
                    using (SqlCommand querySaveStaff = new SqlCommand(saveJSON))
                    {
                        querySaveStaff.Connection = openCon;
             
                        openCon.Open();
    
                        querySaveStaff.ExecuteNonQuery();
    
                    }
                }
            }
        }
    }
    

    Monday, January 28, 2019 9:14 AM
  • User753101303 posted

    Which SQL Server version are you using ? You need SQL Server 2016 or later....

    ah also is is a literal string (and so you can use a SQL parameter to transmit the value). See my earlier hardcoded demo.

    Monday, January 28, 2019 9:21 AM
  • User-1104215994 posted

    SQL Express 2016, I can insert from SSMS.

    Monday, January 28, 2019 9:26 AM
  • User-1104215994 posted

    I tried this way in C# but still same error.

    string json = "{ quantity': '2', 'currency': 'EURO' }";
                    string saveJSON =
                        "INSERT INTO Request SELECT * FROM OPENJSON (' "+ json+ " ') WITH ( quantity int , currency nchar(10) );"; 

    Monday, January 28, 2019 9:49 AM
  • User36583972 posted

    I tried this way in C# but still same error.

    string json = "{ quantity': '2', 'currency': 'EURO' }";
                    string saveJSON =
                        "INSERT INTO Request SELECT * FROM OPENJSON (' "+ json+ " ') WITH ( quantity int , currency nchar(10) );"; 

    Hi cenk1536,

    I have made a test on my side. It is work.

    Please refer the following format and modify your code.

                string jsondata = " N'{ \"StudentId\":\"24\",\"Name\":\"Test 265\", \"Phone\":\"123456789\"}'";
                using (SqlConnection openCon = new SqlConnection(@"Data Source="))
                {
                    string saveJSON =
                        "INSERT INTO[StudentMaster] (StudentId, Name, Phone)  SELECT StudentId, Name, Phone FROM OPENJSON ("+ jsondata + ") WITH(StudentId int, Name nvarchar(50), Phone nvarchar(50));";
    
                    using (SqlCommand querySaveStaff = new SqlCommand(saveJSON))
                    {
                        querySaveStaff.Connection = openCon;
    
                        openCon.Open();
    
                        querySaveStaff.ExecuteNonQuery();
    
                    }
                }

    Besides, It would be appreciated if you could close the thread by marking helpful posts as an answer. This will help other members to find the solution quickly if they have faced the similar issue. If you have a new question you can start a new thread. Please don't ask several questions in the same thread.


    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 28, 2019 10:03 AM
  • User753101303 posted

    Just keep your previous code and use parameters. With the code you shown just add :

    querySaveStaff.Parameters.AddWithValue("@json",json) and that's it. For now the string you are building is not correct because of the ' delimiters (also 'quantity':2 could be enough). It's safer to use SQL parameters which are intended to easily and safely pass parameters to a SQL statement.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 28, 2019 10:08 AM
  • User-1104215994 posted

    Thank you Yohann. I have one question; in my <g class="gr_ gr_90 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="90" data-gr-id="90"><g class="gr_ gr_106 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="106" data-gr-id="106">imlementation</g></g> I am getting JSON from request so each time how can I convert my JSON request with <g class="gr_ gr_296 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="296" data-gr-id="296">format</g> below?

    string jsondata = " N'{ \"StudentId\":\"24\",\"Name\":\"Test 265\", \"Phone\":\"123456789\"}'";

    Monday, January 28, 2019 10:09 AM
  • User-1104215994 posted

    Just keep your previous code and use parameters. With the code you <g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="12" data-gr-id="12">shown</g> just add :

    querySaveStaff.Parameters.AddWithValue("@json",<g class="gr_ gr_11 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="11" data-gr-id="11">json</g>) and that's it. For <g class="gr_ gr_13 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="13" data-gr-id="13">now</g> the string you are building is not correct because of the ' delimiters (also 'quantity':2 could be enough). It's safer to use SQL parameters which are intended to easily and safely pass parameters to a SQL statement.

    PatriceSc, would you please show me parameterized working code?

    Monday, January 28, 2019 10:10 AM
  • User36583972 posted

    cenk1536

    Thank you Yohann. I have one question; in my imlementation I am getting JSON from request so each time how can I convert my JSON request with format below?

    string jsondata = " N'{ \"StudentId\":\"24\",\"Name\":\"Test 265\", \"Phone\":\"123456789\"}'";

    Hi cenk1536,

    You only need to check your json data and splicing it into the following format.


    N'{ "StudentId":"23","Name":"Test 123", "Phone":"123456789"}'

    Beside, Please try to do it yourself, instead of asking someone to provide you working code.


    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 28, 2019 10:17 AM
  • User-1104215994 posted

    Thank you.

    using (SqlConnection openCon = new SqlConnection("Server=N1015048\\SQLEXPRESS;Database=Test;Trusted_Connection=True;"))
                {
                    
                    string json = "{ \"quantity\": \"3\", \"currency\": \"YEN\" }";
    
                    string saveJSON =
                        "INSERT INTO Request SELECT * FROM OPENJSON( @json) WITH ( quantity int , currency nchar(10) );"; 
    
                    using (SqlCommand querySaveStaff = new SqlCommand(saveJSON))
                    {
                        querySaveStaff.Connection = openCon;
                        querySaveStaff.Parameters.AddWithValue("@json", json);
                        openCon.Open();
    
                        querySaveStaff.ExecuteNonQuery();
    
                    }
                }

    Monday, January 28, 2019 10:28 AM