Hi Dilip,
You don't mention whether the varbinary(max) is an input or an output parameter. There are subtle differences:
Input Parameter:
drop procedure dbo.usp_VarBinaryIn
go
create procedure dbo.usp_VarBinaryIn
@binaryin_1 varbinary(max),
@binaryin_2 varbinary(max),
@binaryin_3 varbinary(max)
as
select datalength(@binaryin_1) + datalength(@binaryin_2) + datalength(@binaryin_3);
go
[Test]
public void VarBinaryIn()
{
using(SqlConnection conn = new SqlConnection("Server=tcp:chenbro,18980;Database=tempdb;Integrated Security=SSPI"))
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.usp_VarBinaryIn";
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
byte[] data = Convert.FromBase64String("TWFuIGlzIGRpc3Rpbmd1aXNo");
// Let SqlParameter class figure out the length...
SqlParameter varBinaryParam1 = new SqlParameter("@binaryin_1", data);
cmd.Parameters.Add(varBinaryParam1);
// Or, be explicit...
SqlParameter varBinaryParam2 = new SqlParameter("@binaryin_2", SqlDbType.VarBinary, data.Length);
varBinaryParam2.Value = data;
cmd.Parameters.Add(varBinaryParam2);
// Or, we don't have to send the whole byte array...
SqlParameter varBinaryParam3 = new SqlParameter("@binaryin_3", SqlDbType.VarBinary, 10);
varBinaryParam3.Value = data;
cmd.Parameters.Add(varBinaryParam3);
long length = (long)cmd.ExecuteScalar();
Assert.AreEqual(46, length);
}
}
Output Parameter:
drop procedure dbo.usp_VarBinaryOut
go
create procedure dbo.usp_VarBinaryOut
@binaryout varbinary(max) output
as
set context_info 0x1256698456;
select @binaryout = substring(context_info(), 0, 5)
go
[Test]
public void VarBinaryOut()
{
using(SqlConnection conn = new SqlConnection("Server=tcp:chenbro,18980;Database=tempdb;Integrated Security=SSPI"))
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "dbo.usp_VarBinaryOut";
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
// Don't need to be more explicit than this. Size -1 will return the entire byte array exactly...
SqlParameter varBinaryParam = new SqlParameter("@binaryout", SqlDbType.VarBinary, -1);
varBinaryParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(varBinaryParam);
cmd.ExecuteNonQuery();
byte[] contextInfo = (byte[])varBinaryParam.Value;
Assert.AreEqual("ElZphA==", Convert.ToBase64String(contextInfo));
}
}
--
Cheers,
Stefan Delmarco
http://www.fotia.co.uk