Batch insert from larger json Array Result in asp.net core ?

You can use SQLBulkCopy , convert json array to datatable and then follow below code

using (SqlConnection destinationCon = new SqlConnection(cs))
{
using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
{
bc.BatchSize = 50;
bc.NotifyAfter = 5000;
bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
bc.DestinationTableName = "tblProductDestination";
destinationCon.Open();
bc.WriteToServer(yourDataTable);
}
}
static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine(e.RowsCopied + " loaded....");
}
}

public class ConvertJsonStringToDataTable
{
public DataTable JsonStringToDataTable(string jsonString)
{
DataTable dt = new DataTable();
string[] jsonStringArray = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");
List<string> ColumnsName = new List<string>();
foreach (string jSA in jsonStringArray)
{
string[] jsonStringData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
foreach (string ColumnsNameData in jsonStringData)
{
try
{
int idx = ColumnsNameData.IndexOf(":");
string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"", "");
if (!ColumnsName.Contains(ColumnsNameString))
{
ColumnsName.Add(ColumnsNameString);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
}
}
break;
}
foreach (string AddColumnName in ColumnsName)
{
dt.Columns.Add(AddColumnName);
}
foreach (string jSA in jsonStringArray)
{
string[] RowData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
DataRow nr = dt.NewRow();
foreach (string rowData in RowData)
{
try
{
int idx = rowData.IndexOf(":");
string RowColumns = rowData.Substring(0, idx - 1).Replace("\"", "");
string RowDataString = rowData.Substring(idx + 1).Replace("\"", "");
nr[RowColumns] = RowDataString;
}
catch (Exception ex)
{
continue;
}
}
dt.Rows.Add(nr);
}
return dt;
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
You can use SQLBulkCopy , convert json array to datatable and then follow below code
using (SqlConnection destinationCon = new SqlConnection(cs))
                    {
                        using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
                        {
                            bc.BatchSize = 50;
                            bc.NotifyAfter = 5000;
                            bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
                            bc.DestinationTableName = "tblProductDestination";
                            destinationCon.Open();
                            bc.WriteToServer(yourDataTable);
                        }
                    }
        static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine(e.RowsCopied + " loaded....");
        }
    }

public class ConvertJsonStringToDataTable
{
   public DataTable JsonStringToDataTable(string jsonString)
   {
      DataTable dt = new DataTable();
      string[] jsonStringArray = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");
      List<string> ColumnsName = new List<string>();
      foreach (string jSA in jsonStringArray)
      {
         string[] jsonStringData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
         foreach (string ColumnsNameData in jsonStringData)
         {
            try
            {
               int idx = ColumnsNameData.IndexOf(":");
               string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"", "");
               if (!ColumnsName.Contains(ColumnsNameString))
               {
                  ColumnsName.Add(ColumnsNameString);
               }
            }
            catch (Exception ex)
            {
               throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
            }
         }
         break;
      }
      foreach (string AddColumnName in ColumnsName)
      {
         dt.Columns.Add(AddColumnName);
      }
      foreach (string jSA in jsonStringArray)
      {
         string[] RowData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
         DataRow nr = dt.NewRow();
         foreach (string rowData in RowData)
         {
            try
            {
               int idx = rowData.IndexOf(":");
               string RowColumns = rowData.Substring(0, idx - 1).Replace("\"", "");
               string RowDataString = rowData.Substring(idx + 1).Replace("\"", "");
               nr[RowColumns] = RowDataString;
            }
            catch (Exception ex)
            {
               continue;
            }
         }
         dt.Rows.Add(nr);
      }
      return dt;
   }
}
0