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

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