IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE TYPE='P' AND object_id = OBJECT_ID(N'[Test_Binary]') )
drop procedure Test_Binary;
go
Create PROCEDURE Test_Binary
(
@in_hexstring nvarchar(32)
)
AS
BEGIN
create table #T1(a nvarchar(32),b binary(32));
insert into #T1(a,b) values (@in_hexstring,Convert(binary(32),@in_hexstring));
select * from #T1;
end
--use
exec Test_Binary '123';我在c#中,是一个byte[]数组,想保存到sql server的binary中。
目前我是先将byte[]数据转成16进制字符串,通过变量@in_hexstring传到存储过程中,但在插入表的b字段时,使用Convert(binary(32),@in_hexstring)转后后保存,再select出来,就不是原来的值了。请问,在c#中,如何将byte[]保存到sql server的 binary中,保存后如何select出来还原回byte[]数组。
drop procedure Test_Binary;
go
Create PROCEDURE Test_Binary
(
@in_hexstring nvarchar(32)
)
AS
BEGIN
create table #T1(a nvarchar(32),b binary(32));
insert into #T1(a,b) values (@in_hexstring,Convert(binary(32),@in_hexstring));
select * from #T1;
end
--use
exec Test_Binary '123';我在c#中,是一个byte[]数组,想保存到sql server的binary中。
目前我是先将byte[]数据转成16进制字符串,通过变量@in_hexstring传到存储过程中,但在插入表的b字段时,使用Convert(binary(32),@in_hexstring)转后后保存,再select出来,就不是原来的值了。请问,在c#中,如何将byte[]保存到sql server的 binary中,保存后如何select出来还原回byte[]数组。
用函数------------------------
Create FUNCTION DecToBin(@Dec Int)
RETURNS VARCHAR(16)
AS
begin
declare @BinStr as varchar(20)
declare @Mod2 as int
declare @Div2 as int set @Div2 = @Dec / 2
set @Mod2 = @Dec % 2
set @BinStr='' while @Div2<>0
begin
if @Mod2=0
set @BinStr = @BinStr + '0'
else
set @BinStr = @BinStr + '1'
set @Dec = @Dec / 2
set @Div2 = @Dec / 2
set @Mod2 = @Dec % 2
end
set @BinStr = @BinStr + '1'
return REVERSE(@BinStr + right('0000000000000000',16-len(@BinStr)))
end
create function f_int2bin(@i int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
while @i>0
select @s=cast(@i%2 as varchar)+@s
,@i=@i/2
return(@s)
end
go--调用
select dbo.f_int2bin(200)
--十进制转为十六进制DECLARE
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),29327795562177529)
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT
SELECT
结果 = reverse(@str_return)/*
结果
__________________________
008613974878369F10000011x0
*/--十六进制转为十进制
CREATE FUNCTION dbo.f_hex_dec(@s varchar(16))
RETURNS bigint
AS
BEGIN
--作者:pbsql
--参数不得含'0'~'9'、'a'~'f'、'A'~'F'之外的任意字符(首尾空格除外),否则返回0
DECLARE @i int,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@s)
BEGIN
IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
BEGIN
SELECT @result=0
break
END
SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1
END
RETURN @result
END
GO
--sql
if(object_id('TestBinary') > 0)
drop table TestBinary
go
create table TestBinary(
a varchar(1000),
b varbinary(1000)
)
--下面是程序代码
class Program
{
static string connectStr = "Data Source=192.168.0.25;Initial Catalog=yl;User ID=sa;Password=123456"; static void Main(string[] args)
{
string value = "地方时地方fdssfsdf";
InsertValue(value);
GetValue();
Console.ReadLine();
}
static void GetValue() {
SqlConnection connection = new SqlConnection(connectStr);
try
{
DataTable dt = new DataTable();
string sql = "select * from TestBinary";
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(dt);
foreach (DataRow row in dt.Rows) {
Console.Write(row[0].ToString()+" ");
byte[] b = row[1] as byte[];
string result = Encoding.Default.GetString(b);
Console.Write(result);
Console.WriteLine();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
static void InsertValue(string value) {
SqlConnection connection = new SqlConnection(connectStr);
try
{
string sql = "insert into TestBinary values(@a,@b)";
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@a", System.Data.SqlDbType.VarChar, 100);
command.Parameters.Add("@b",System.Data.SqlDbType.VarBinary,1000);
byte[] result = Encoding.Default.GetBytes(value);
command.Parameters["@a"].Value = value;
command.Parameters["@b"].Value = result;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
}
在转换时加上style=2就行了。谢谢各位。