Monday, October 29, 2007 10:28 AM royashbrook

chunking binary or xml into sql

this is a little unique of a setup. i'm having to update a varbin field, but the data that is coming in from the other sources is being cast from xml. so i'm sort of replicating the stupid thing we are doing on the other side, but it seems like a good code sample for this particular method. if you wanted to chunk up xml, you could use this method.

 

using (SqlConnection cn = new SqlConnection(cs))

{

string staging_sql = @"

create table ##t (t varchar(max));

insert ##t select '';

";

string load_sql = @"update ##t set t .write(@a,null,0)";

string update_and_cleanup_sql = @"

update

tablewithvarbincolumn

set

varbincolumn = cast(cast( (select top 1 t from ##t) as xml) as varbinary(max))

where

id1= @a

and id2= @b;

drop table ##t;

";

//open our connection

cn.Open();

//first let's create the staging tbl to chunk to

using (SqlCommand cmd = new SqlCommand(staging_sql, cn))

cmd.ExecuteNonQuery();

//now let's chunk to the staging table

//reference this metholodogy justification at

// http://www.lacoude.com/Docs/public/public.aspx?doc=SQL90XML.PDF

using (FileStream fs = File.Open(filepath, FileMode.Open, FileAccess.Read))

using (BinaryReader br = new BinaryReader(fs))

using (SqlCommand cmd = new SqlCommand(load_sql, cn))

{

cmd.Parameters.Add("@a", SqlDbType.VarBinary, bufferSize);

bufferSize = 8040;

buffer = br.ReadBytes(bufferSize); //this is set as a byte[] above, we are merely redefining the size.

while (buffer.Length > 0)

{

cmd.Parameters[0].Value = buffer;

cmd.ExecuteNonQuery();

buffer = br.ReadBytes(bufferSize);

}

}

//now let's update all of the data in sql and cleanup

using (SqlCommand cmd = new SqlCommand(update_and_cleanup_sql, cn))

{

cmd.Parameters.AddWithValue("@a", v1);cmd.Parameters.AddWithValue("@b", v1);

cmd.ExecuteNonQuery();

}

//close our connection

cn.Close();

}

 Filed under: ,

Comments

No Comments