How to insert multiple JSON rows into a T-SQL table using a stored procedure

Question

I am trying to insert multiple rows of JSON output into SQL using C#.

This is C# code that I have:

string connString = @"MultipleActiveResultSets=true;Data Source=db;USER id=yy;Password=xxx;Initial Catalog=dim";

string sprocname = "InsertPerfCounterData2";
string paramName = "@json";

string paramValue = SayHello(logger);

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(sprocname, conn))
    {
           cmd.CommandType = CommandType.StoredProcedure;

           cmd.Parameters.Add(new SqlParameter(paramName, paramValue));

           cmd.ExecuteReader();
     }
}

I am getting an Exception Unhandled error at cmd.ExecuteReader();

System.Data.SqlClient.SqlException: 'JSON text is not properly formatted. Unexpected character ']' is found at position 501.'

How do I modify C# code to make it work?

This is the stored procedure code:

CREATE PROCEDURE [dbo].[InsertPerfCounterData2]
    @json NVARCHAR(max)
AS
BEGIN
    INSERT INTO dbo.PerfCounter3 ([RECORDNO], [BATCH_DATE])
        SELECT 
            GLD.RECORDNO, GLD.BATCH_DATE
        FROM 
            OPENJSON(CONCAT('[',@JSON,']')) OJ
        CROSS APPLY 
            OPENJSON(OJ.[value],'$.GLDETAIL')
                  WITH (RECORDNO varchar(30),
                        BATCH_DATE date) GLD;
END

This is example of JSON input:

 {
    "GLDETAIL": {
      "RECORDNO": "264378-1756289-919567--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },
  {
    "GLDETAIL": {
      "RECORDNO": "264378-1756290-919568--accrual",
      "BATCH_DATE": "02/01/2022"
    }
  },

Update (5/5/2022):

I am trying to declare "result.Data" (based on @Charlieface's answer on the bottom).

If the result of resultJson is like this, and data type is String:

resultJson =
 "[{\"GLDETAIL\":{\"RECORDNO\":\"264378-1756289-919567-- 
  accrual\",\"BATCH_DATE\":\"02/01/2022\"}},{\"GLDETAIL\": 
  {\"RECORDNO\":\"264378-1756290-919568-- 
  accrual\",\"BATCH_DATE\":\"02/01/2022\"}}]"

How do I apply into "result.Data" properly?

foreach (var r in result.Data)
table.Rows.Add(r.RECORDNO, r.BATCH_DATE);

Have you verified that the JSON is properly formatted? What's around position 501? Does the last object in your array end with a comma? Could you simplify your posted code and post a minimal reproducible example? — gunr2171
– gunr2171, Commented May 1, 2022 at 1:43
@gunr2171 You were right. The problem was the last comma in the JSON test that created an error. There was an issue on other C# code where it determines whether there should be comma or not. I will post on a new thread soon and share. — Java
– Java, Commented May 1, 2022 at 3:43
@gunr2171 I just posted on a new thread: stackoverflow.com/questions/72073896/… — Java
– Java, Commented May 1, 2022 at 4:00
I would suggest you don't use this at all, especially given you are rolling your own JSON creator. Instead use a Table Valued Parameter eg stackoverflow.com/a/71915357/14868997 or SqlBulkCopy. Also cmd.ExecuteReader(); is wrong here as there is no output, you should use cmd.ExecuteNonQuery(); — Charlieface
– Charlieface, Commented May 1, 2022 at 10:55

Charlieface · Accepted Answer · 2022-05-01 11:05:00Z

1

Considering you are trying to roll your own JSON writer, you are using the wrong tool for the job. For a bulk insert into SQL Server, the easiest and fastest method is to use SqlBulkCopy.

For example (reading between the lines from your other question and guessing data types):

var table = new DataTable();
table.Columns.Add("RECORDNO", typeof(int));
table.Columns.Add("BATCH_DATE", typeof(DateTime));

foreach (var r in result.Data)
    table.Rows.Add(r.RECORDNO, r.BATCH_DATE);

const string connString = @"MultipleActiveResultSets=true;Data Source=db;USER id=yy;Password=xxx;Initial Catalog=dim";

using (var conn = new SqlConnection(connString))
using (var bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "PerfCounter3";
    conn.Open();
    bulk.WriteToServer(table);
}

Another option is to use a Table Valued Parameter, for which there are many examples online already.

answered May 1, 2022 at 11:05

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Java Over a year ago

I updated the post with question regards to how to get the data from String output to "result.Data".

Collectives™ on Stack Overflow

How to insert multiple JSON rows into a T-SQL table using a stored procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related