Friday, October 26, 2007 6:56 PM
royashbrook
chunking varbinary into sql2005
//reference this metholodogy justification at
// http://www.lacoude.com/Docs/public/public.aspx?doc=SQL90XML.PDF
int bufferSize = 0;
byte[] buffer = new byte[8040];
string update_sql = @"update tablewithblobs set blobcolumn .write(@a,null,0) where id = @b";
using (FileStream fs = File.Open(abinaryfilepath, FileMode.Open, FileAccess.Read))
using (BinaryReader br = new BinaryReader(fs))
using (SqlConnection cn = new SqlConnection(cs))
using (SqlCommand cmd = new SqlCommand(update_sql, cn))
{
cmd.CommandTimeout = 0;
cmd.Parameters.Add("@a", SqlDbType.VarBinary, bufferSize);
cmd.Parameters.AddWithValue("@b", v1);
cn.Open();
while ((bufferSize = br.Read(buffer, 0, buffer.Length)) != 0)
{
cmd.Parameters[0].Value = buffer;
cmd.ExecuteNonQuery();
}
cn.Close();
}
note that you will have to zero out the field if it's not empty by setting it to 0x0 first.
Filed under: C#, sql