How to save byte[] using a procedure?
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 I开发者_如何学运维NTEGER
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.
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
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.
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
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++;
}
}
精彩评论