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

Question

Hi All, I have a situation to insert records into local database from api resultant (which is large json arry about 5000 records).i can insert into local database. But only issue is if it is large data . How to handle this process , is there any way to insert like batch by batch insert into database in order reduce the memory usage. If anyone know suggest some idea with suitable example. Thanks in advance Karthik K

Sachin Singh · Answer

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; } }