Can I insert a large text value into SQL Server from ASP.net without having the entire file in memory on the web server?

Question

As the question says, given the large text file, how can I get its contents in the nvarchar (max) column on the sql server without loading the entire contents of the file into memory (either to create a dynamic sql statement, or as an SP parameter)?

My best solution so far is to insert a row with an empty value, and then start the update in a loop, adding pieces of data every time in a transaction. Is there a better way besides copying the file to the database server and using BCP? Any way to transfer data?

Answer 1

Starting with .net4.5 SqlParameters TextReader Support https://msdn.microsoft.com/en-us/library/hh556234(v=vs.110).aspx

 using (SqlConnection conn = new SqlConnection(connection_string)) using (SqlCommand cmd = conn.CreateCommand()) using (FileStream file = File.OpenRead(@"C:\temp\bigtextfile.txt")) { cmd.CommandText = "INSERT INTO RandomTable (TextBlob) VALUES (@text)"; cmd.Parameters.Add("@text", System.Data.SqlDbType.NVarChar, -1).Value = file; conn.Open(); cmd.ExecuteNonQuery(); }