C# and SQL Server with User Defined table

I have a C# Web Service that accepts inbound JSon Data
Uses newton soft to parse and a function to place into a .Net DataTable

I then pass that DataTable into SQL Server Stored procedure as a type Structured
My Stored proc accepts the TYPE
Inserts data into a table
Works clean and smooth

I have now learned that I will begin receiving the data instead of 5-10 records at a time (Dtatable is 10 columns)
But around 50K records.

So my question...
Will my service handle this ok?

Web Service and SQL Server instance are on the same server
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
can you think of upgrading to SQL 2016? Because json is supported in that newer version. Have a look at http://emoreau.com/Entries/Articles/2016/06/New-in-SQL-2016--Native-support-from-JSON.aspx
0
 
Russ SuterCommented:
For that many records you might consider just reading the file into a DataTable then using SqlBulkCopy to transfer the data into a table. You could then modify your stored procedure so it simply uses the table data. SqlBulkCopy is extremely fast. It does however have the downside of being difficult to troubleshoot if your data doesn't match the table definition.

This is my preferred method for importing large chunks of external data into a database. It has the added bonus of allowing me to write whatever business logic I need in C# to ensure the best data result.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey guys,
 I just learned that the data will come to me in batches with a max size of 1MB.

That seems very manageable?

The bulk copy would be an option if I were the one controlling the inbound data... but I am not.

SQL 2016 is at least a year away.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.