2

I am new to Azure Functions.I have a got a requirement to insert data into AzureSQLDB. The data I will get in the form of json by calling a restful api. I am planning to create a Timer Trigger function as this data transfer should be done once in a month automatically.I was wondering which approach should I take.

  • Should I parse the json value into models in azure function and insert it one by one by executing query?
  • Should I Send the whole json string to AzureSqlDB and querying the json and insert values in the table?
  • Or is there any other approach considered as a best practice to this requirement?

Thanks in Advance..

Arup Ghosh
  • 33
  • 3

1 Answers1

3

There might be several opinions and approaches here, but I have at least a couple considerations:

  • You could parse JSON inside SQL Server, but you probably shouldn't. Normally, it's better to leverage general-purpose programming language like C# with all the libraries, testability and pay for cheap CPU time instead of doing that in SQL

  • Avoid making N separate database calls for N rows. With large number of rows the latency of many calls will accumulate pretty fast. Remember, that on Consumption plan you have max 10 minutes for a single Function invocation

  • Debatable, but I would avoid heavy libraries like Entity Framework

I don't know the exact structure of you SQL table, but my default approach would be:

  • Get the JSON
  • Convert it to C# objects / validate / apply any logic or transformation
  • Fill a plain ADO.NET DataTable
  • Use SqlBulkCopy to insert data into SQL, see this question
Mikhail Shilkov
  • 34,128
  • 3
  • 68
  • 107