8

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?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
powlette
  • 1,800
  • 20
  • 41
  • 1
    Look into the [SqlBulkCopy class](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx). – John Saunders Jul 25 '13 at 18:16
  • I've used SqlBulkCopy, but it requires the data be in memory in a DataTable. Unless there's some other way to use it? – powlette Jul 25 '13 at 18:16
  • 1
    I guess your question is answered at http://stackoverflow.com/questions/2101149/how-to-i-serialize-a-large-graph-of-net-object-into-a-sql-server-blob-without-c – Abbas Amiri Jul 25 '13 at 18:22
  • There are overloads which use `IDataReader`: http://msdn.microsoft.com/en-us/library/434atets.aspx – John Saunders Jul 25 '13 at 18:34

1 Answers1

4

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();
}
Lorentz Vedeler
  • 5,101
  • 2
  • 29
  • 40