How to send large amount of Data To SqlServer Stored Procedure
.net c# sqlbulkcopy sql-server stored-procedures
Question
I want to send large amounts of data from a .Net based application to the SQL Server. This Data Records are coming from a CSV file and should be funneled through a Stored Procedure on the SQL Server side. I know about SqlBulkCopy but SqlBulklCopy writes directly to tables. Means, it is not usable for me. I am speaking from over 100.000.000 Records. I won't be able to hold this amount of data in memory. So ideally I ask if there exists some kind of streaming Data from .Net to a Stored Procedure. I am grateful for any help.
Accepted Answer
I think you are seeking for Table-Valued Parameters. There is enough documentation how to create TVP type in the database and use it inside a stored procedure. When setting up a SqlParameter, you can supply IEnumerable<SqlDataRecord> which can easily be provided by a C# iterator function directly reading sequentially CSV file data records one by one and for each record, parse, convert and yield a corresponding SqlDataRecord. At the Sql Server side the data will still be bulk inserted into a tempdb before passed to the stored procedure, but at least at client side you don't need a memory to hold all that data.