Databases
StackOverflow2013
Postsdbo
POSQL: Sequentially doing UPDATE .WRITE on VarBinary column
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: Sequentially doing UPDATE .WRITE on VarBinary column
    primarykey
    data
    text
    <p>I'm trying to create a little test application which reads chunks of a FileStream and appends it to a VarBinary(max) column on an SQL Server 2005 Express.</p> <p>Everything works - the column gets filled as it's supposed to, but my machine still seems to buffer everything into memory and I just can't see why.</p> <p>I'm using the following code (C#):</p> <pre><code>using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString)) { connection.Open(); string id = Guid.NewGuid().ToString(); using (IDbCommand command = connection.CreateCommand()) { command.CommandText = "INSERT INTO [BLOB] ([Id],[Data]) VALUES (@p1,0x0)"; SqlParameter param = new SqlParameter("@p1", SqlDbType.VarChar); param.Value = id; command.Parameters.Add(param); command.ExecuteNonQuery(); } if (File.Exists(textBox1.Text)) { using (IDbCommand command = connection.CreateCommand()) { command.CommandText = "UPDATE [BLOB] SET [Data].WRITE(@data, @offset, @len) WHERE [Id]=@id"; SqlParameter dataParam = new SqlParameter("@data", SqlDbType.VarBinary); command.Parameters.Add(dataParam); SqlParameter offsetParam = new SqlParameter("@offset", SqlDbType.BigInt); command.Parameters.Add(offsetParam); SqlParameter lengthParam = new SqlParameter("@len", SqlDbType.BigInt); command.Parameters.Add(lengthParam); SqlParameter idParam = new SqlParameter("@id", SqlDbType.VarChar); command.Parameters.Add(idParam); idParam.Value = id; using (FileStream fs = new FileStream(textBox1.Text, FileMode.Open, FileAccess.Read, FileShare.Read)) { byte[] buffer = new byte[2090400]; //chunk sizes that are multiples of 8040 bytes. int read = 0; int offset = 0; while ((read = fs.Read(buffer, 0, buffer.Length)) &gt; 0) { dataParam.Value = buffer; offsetParam.Value = offset; lengthParam.Value = read; command.ExecuteNonQuery(); offset += read; } } } } } </code></pre> <p>Can anybody tell me why it buffers the file into memory? The <code>byte[]</code> buffer I'm using is only almost 2 MB in size.</p> <p>I could create a new buffer for each chunk, but that seems like a waste of CPU/memory also...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJuan Mellado
    1. USd0rk
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL: Sequentially doing UPDATE .WRITE on VarBinary column
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL: Sequentially doing UPDATE .WRITE on VarBinary column
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL: Sequentially doing UPDATE .WRITE on VarBinary column
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload