How to save byte[] using a procedure?

Question

This stored procedure does not save the data, it seems to be a problem with the VARBINARY. I am passing a byte[] to it, but then it doesn't work. If I send this parameter as NULL it works.

I'm calling the procedure with the following code:

public Community AddCommunity(string name, string description, byte[] picture, User owner, int? venue, int communityID) 
{
    using (var database = new Database())
    {
        return database.Scope.GetSqlQuery<Community>("QP_AddCommunity ?, ?, ?, ?, ?, ?", "VARCHAR Name, VARCHAR Description, VARBINARY Picture, INTEGER Owner, INTEGER Venue, INTEGER ID").GetResult(name, description, picture, owner.ID, venue, communityID);
    }
}

The procedure is the following:

CREATE PROCEDURE [dbo].[QP_AddCommunity]
    @Name VARCHAR(120),
    @Description VARCHAR(MAX),
    @Picture VARBINARY(MAX),
    @Owner INTEGER,
    @Venue INTEGER,
    @ID INTEGER

AS
BEGIN
    SET NOCOUNT ON;

    IF(SELECT COUNT(*) FROM QT_Community WHERE ID = @ID) = 0
        INSERT INTO QT_Community(Name, [Description], Picture, [Owner], Venue) VALUES(@Name, @Description, @Picture, @Owner, @Venue);

    ELSE
        UPDATE QT_Community SET Name = @Name, [Description] = @Description, Picture = @Picture, [Owner] = @Owner, Venue = @Venue WHERE ID = @ID;

    SELECT * FROM QT_Community WHERE ID = @@IDENTITY;


END

What's wrong with this code? Isn't VARBINARY a byte[] ?

---

This code works when executing on SQL Server Management Studio.

DECLARE @X varbinary(20)
Set @X = CAST('Testing' As varbinary(20))
EXECUTE [QP_AddCommunity] 'aaaaa', 'descricao', @X, 216, NULL, 0;

But when calling from the GetSqlQuery method with something on the byte[] the transaction says it's not active and not dirty. BUT if the byte[] is null it works as it should.

Answer 1

Try using the .WRITE method. On your INSERT, insert 0x for Picture, then update independently.

UPDATE QT_Community 
    SET Picture.Write (@Picture, 0, DATALENGTH(Picture))
    WHERE ID = @ID

Answer 2

Accordingly to this table it seems either BLOB, BINARY, VARBINARY would be valid types for [] of primitive type.

You could try to ask on their forums, maybe someone will be able to help you.

Answer 3

Example (Ado.Net):

byte[] ba = UlongsToBytes(ul);
try
{
string source = @"packet size=4096;integrated security=SSPI;data source=MyPC\MyNamedInstance;persist security info=False;initial catalog=Sandbox";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand a = new SqlCommand("INSERT BigintsTarget(bi) SELECT * FROM dbo.ParseImageIntoBIGINTs(@BIGINTs)", conn);
a.CommandType = System.Data.CommandType.Text;
a.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.Image,2147483647));
for(int q=0; q<10; q++)
{
a.Parameters[0].Value = ba;
int res = a.ExecuteNonQuery();
}
d2 = DateTime.Now;
SqlCommand b = new SqlCommand("INSERT BigintsTarget1(bi) SELECT * FROM dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs)", conn);
b.CommandType = System.Data.CommandType.Text;
b.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.VarChar,2147483647));
for(int q=0; q<10; q++)
{
b.Parameters[0].Value = sss;
int res = b.ExecuteNonQuery();
}
//b.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
string s = ex.Message;
int t=0;
t++;
}

} 

Answer 4

i found that it is impossible as this answer shows

Hello gaurav, currently our GetSqlQuery method cannot operate properly with parameters of type LongVarBinary or VarBinary, thus making it impossible for the stored procedure to work as expected. We are aware of this problem and we are working on fixing it. As a work around you should try and use Linq to achieve your goal. Greetings, Petar the Telerik team