存储过程读取文件为二进制 然后用cs代码打印出来

drop PROCEDURE BYS_READ_FILE
go
CREATE PROCEDURE BYS_READ_FILE
 @fileName varchar(8000), @message varchar(8000) OUTPUT AS
BEGIN
 DECLARE @object int
 DECLARE @hr int
 DECLARE @property varchar(255)
 DECLARE @return varbinary(8000)
 DECLARE @src varchar(255), @desc varchar(255)

 
-- SET TEXTSIZE 99999999
 -- Create an object.
 EXEC @hr = sp_OACreate 'ADODB.Stream', @object OUT
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 -- Set a property.
 EXEC @hr = sp_OASetProperty @object, 'type', 1
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 EXEC @hr = sp_OASetProperty @object, 'Mode', 3
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 
 EXEC @hr = sp_OAMethod @object, 'open'
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 EXEC @hr = sp_OAMethod @object, 'LoadFromFile', NULL, @fileName
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
-- EXEC @hr = sp_OAMethod @object, 'Size', @return OUT
-- IF @hr <> 0
-- BEGIN
--    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
--    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
--    SET @message = @src+':'+@desc
--     RETURN
-- END
 PRINT cast(@return as int)
 CREATE TABLE #READ_BUFFER(
     DATA image
 )
 
 INSERT INTO #READ_BUFFER VALUES ('')
 set @return = cast('' as varbinary)
        DECLARE @pBinary varbinary(16)
 DECLARE @pos int
 SELECT @pBinary = TEXTPTR(DATA) FROM #READ_BUFFER
 SET @pos = 0
 UPDATETEXT #READ_BUFFER.DATA @pBinary 0 NULL
 WHILE @return IS NOT NULL
 BEGIN
  EXEC @hr = sp_OAMethod @object, 'Read', @return OUT, 8000
  IF @hr <> 0
  BEGIN
     EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
     SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
     SET @message = @src+':'+@desc
      RETURN
  END
  IF @return IS NOT NULL
  BEGIN
     DECLARE @readData varbinary(8000)
     set @readData = cast(@return as varbinary(8000))
     UPDATETEXT #READ_BUFFER.DATA @pBinary NULL 0 @readData
     set @pos = @pos + DATALENGTH(@return)
  END
 END
 EXEC @hr = sp_OAMethod @object, 'close'
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
insert into a(s,ll)  select DATA,datalength(DATA) as aaa from #READ_BUFFER
-- SELECT DATA as s into a FROM #READ_BUFFER
END
GO

------红色的代表是插入表

 
CREATE PROCEDURE BYS_WRITE_FILE
 @fileName varchar(255), @datBinary image, @message varchar(1024) OUTPUT AS
BEGIN
 DECLARE @object int
 DECLARE @hr int
 DECLARE @property varchar(255)
 DECLARE @return sql_variant
 DECLARE @src varchar(255), @desc varchar(255)
 
 -- Create an object.
 EXEC @hr = sp_OACreate 'ADODB.Stream', @object OUT
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 -- Set a property.
 EXEC @hr = sp_OASetProperty @object, 'type', 1
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 EXEC @hr = sp_OASetProperty @object, 'Mode', 3
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 
 EXEC @hr = sp_OAMethod @object, 'open'
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 EXEC @hr = sp_OAMethod @object, 'Write', NULL, @datBinary
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 EXEC @hr = sp_OAMethod @object, 'SaveToFile', NULL, @fileName,2
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
 
 EXEC @hr = sp_OAMethod @object, 'close'
 IF @hr <> 0
 BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    SET @message = @src+':'+@desc
     RETURN
 END
END
GO

declare @fileName varchar(255)
declare @message varchar(1024)
set @filename='E:/14407081917170600.jpg'
exec BYS_READ_FILE @filename,@message

--insert into a(s) values(@message)
--print @message

create proc pro_GETIMAGE
@id int
as
select * from a where id=@id
go

drop table a
go
create table a
(
id int identity(1,1),
s image,
ll int
)
go

create proc pro_Ja
@id int
as
select s from a where id=@id

 

开启sql配置服务
--
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;
--GO
--sp_configure 'Ole Automation Procedures', 1;
--GO
--RECONFIGURE;
--GO

 

----------打印图片

  int imgId = Convert.ToInt32(Request.QueryString["id"].ToString());//imgId为图片的id  

string connString = "server=192.168.0.128;database=EastHH_User;uid=sa";
            SqlConnection conn = new SqlConnection(connString);
            //数据库操作语句

            SqlCommand comm = new SqlCommand("pro_GETIMAGE", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4));
            comm.Parameters["@id"].Value = imgId;
            conn.Open();//打开数据库连接
            SqlDataReader dr = comm.ExecuteReader();//读出数据
            dr.Read();//读一行
            //设定输出文件的类型
            //Response.ContentType = "bmp";
            //输出图片文件二进制数据
            Response.OutputStream.Write((byte[])dr["s"], 0, (int)dr["ll"]);
            Response.End();
            dr.Close();
            conn.Close();

 

接受方式
 <img  src="readimage.aspx?id=1">