How can I save a byte[] array into a SQL Server database? This byte[] contains a HashAlgorithm value.
The data is needed again for later use. So converting it and NOT getting it back in its original state, is not what I want.
Add a comment
|
4 Answers
You should be able to write something like this:
string queryStmt = "INSERT INTO dbo.YourTable(Content) VALUES(@Content)";
using(SqlConnection _con = new SqlConnection(--your-connection-string-here--))
using(SqlCommand _cmd = new SqlCommand(queryStmt, _con))
{
SqlParameter param = _cmd.Parameters.Add("@Content", SqlDbType.VarBinary);
param.Value = YourByteArrayVariableHere;
_con.Open();
_cmd.ExecuteNonQuery();
_con.Close();
}
Using Linq-to-SQL, you'd write something like this:
using(YourDataContextHere ctx = new YourDataContextHere())
{
SomeClassOfYours item = new SomeClassOfYours();
item.ByteContent = (your byte content here);
ctx.SomeClassOfYourses.InsertOnSubmit(item);
ctx.SubmitChanges();
}
That will insert your byte[] into a column Content of type VARBINARY in your SQL Server table as a byte stream, which you can read back 1:1 again later on.
6 Comments
Sean Reilly
Good answer. However, based on the statement that the value is a hash, it's possible that it will be of constant length. If so, consider using binary with that length instead of varbinary.
marc_s
@Sean Reilly: true - but different hash algorithms also produce different length hashes, so you might want to use a VARBINARY with a suitable max length to accomodate all variations
Yustme
@marc_s i'll be using linq. do i have to do SqlDbType.VarBinary?
marc_s
@Yustme: updated my answer to include Linq-to-SQL, too. Yes, you'll need a VARBINARY column in your SQL Server table whichever way you access it. In Linq-to-SQL, it'll show up as a "binary" column in your entity class, which you can just set (and read out) like any other column.
Sean Reilly
@marc_s also true - however it's quite possible that all rows are the same hash algorithm. If (and only if) that is the case, I'd stand by my recommendation for a constant length binary instead of varbinary.
|
Use VARBINARY
Comments
Using Dapper you may save HTML Code easily like this:
using (var con = DapperConnection.Con)
{
string firstValue = "any text...";
string secondValue = "HTML code maybe ?";
// Convert your string into byte array:
byte[] htmlCode = Encoding.ASCII.GetBytes(secondValue);
// Define your insert query and execute it:
con.Execute($@" INSERT INTO [dbo].[YourTableName]
( [firstColumnName], [secondColumnName] )
VALUES
( {firstValue}, COMPRESS(@HTML) )", new { HTML = htmlCode });
}
You may then DECOMPRESS it from database like this:
SELECT [firstColumnName], CONVERT(varchar(MAX), DECOMPRESS([secondColumnName])) FROM [YourTableName];
1 Comment
Piotr Kula
Is COMPRESS and DECOMPRESS a SQL feature or a Dapper feature? TIL: This is pretty cool actually either way.
Varbinary or CHAR - with the value converted to a hex. I do that pretty often with hash values because it allows me to SEE and COMPARE them easily (on rintouts, during development), and the overhead is minimal.
