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

Question

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

My best solution so far is to insert a row with an empty value and then in a loop run updates appending chunks of the data each time in a transaction. Is there a better way other than copying the file to the database server and using BCP? Some way to stream the data over?

Answer 1

As of .net4.5 SqlParameters support TextReader 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();
}