SQL SERVER 中用function 生成二維碼圖片數據流

場景：因業務需求，需要在SSRS報表中動態生成QRCODE

問題：SSRS中沒有綁定數據產生QRCODE的控件，所以處理思路是生成QRCODE圖片二進制數據流，存儲在對應表欄位中

環境：VS2012 、SQLserver2008、win7 64X

框架：.net2.0

調用插件：ThoughtWorks.QRCode

語言：SQL +C#

插件代碼封裝成類庫：

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
//using System.Threading.Tasks;
using ThoughtWorks.QRCode.Codec;
using System.Drawing;
using System.IO;
//using System.Runtime.Serialization.Formatters.Binary;
using System.Data;
using Microsoft.SqlServer.Server;
namespace QRcodeCreate
{
    public class QRcodeCreate
    {
        [SqlFunctionAttribute(FillRowMethodName = "FillFileRow")]
        public static byte[] QRcodeImage(string _date)
        {
            ThoughtWorks.QRCode.Codec.QRCodeEncoder encoder = new QRCodeEncoder();
            encoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.BYTE;//编码方式(注意：BYTE能支持中文，ALPHA_NUMERIC扫描出来的都是数字)
            encoder.QRCodeScale = 4;//大小(值越大生成的二维码图片像素越高)
            encoder.QRCodeVersion = 0;//版本(注意：设置为0主要是防止编码的字符串太长时发生错误)
            encoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M;//错误效验、错误更正(有4个等级)
            //String qrdata = "123487QBCT";
            System.Drawing.Bitmap bp = encoder.Encode(_date.ToString(), Encoding.Unicode);
            Image image = bp;
            
            //string str = System.Text.Encoding.Default.GetString(ms.ToArray());
            return ImageData(image);
            //return image;

        }

        //public static Byte[] ImageToBytes(Image image)
        //{
        //    var ms = new MemoryStream();
        //    var bf = new BinaryFormatter();
        //    bf.Serialize(ms, image);
        //    ms.Close();
        //    return ms.ToArray();
        //} 

        public static byte[] ImageData(System.Drawing.Image imgPhoto)
        {
            MemoryStream mstream = new MemoryStream();
            imgPhoto.Save(mstream, System.Drawing.Imaging.ImageFormat.Bmp);
            byte[] byData = new Byte[mstream.Length];
            mstream.Position = 0;
            mstream.Read(byData, 0, byData.Length); mstream.Close();
            return byData;
        }
    }
}

SQL註冊dll 並封裝成function（參照 https://www.cnblogs.com/woxpp/p/3990277.html ）

--修改系统配置的存储过程当设置 show advanced options 参数为 1 时，才允许修改系统配置中的某些高级选相！！系统中这些高级选项默认是不允许修改
exec sp_configure 'show advanced options','1'
go
--重新配置 就是用来更新使用sp_configure 系统存储过程更改的配置选项的当前配置值
reconfigure
go
--建立可信赖
alter database Cyerp set trustworthy on
go

CREATE ASSEMBLY [System.Security.Permissions.SecurityPermission] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Security.Permissions.SecurityPermission.dll' WITH PERMISSION_SET = UNSAFE

GO

--添加关联DLL
CREATE ASSEMBLY [System.Drawing] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' WITH PERMISSION_SET = UNSAFE
CREATE ASSEMBLY [System] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll' WITH PERMISSION_SET = UNSAFE
CREATE ASSEMBLY [System.Data] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll' WITH PERMISSION_SET = UNSAFE
CREATE ASSEMBLY [System.Xml] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll' WITH PERMISSION_SET = UNSAFE
CREATE ASSEMBLY [ThoughtWorks.QRCode] FROM 'c:\users\administrator\Desktop\ThoughtWorks.QRCode\ThoughtWorks.QRCode.dll' WITH PERMISSION_SET = UNSAFE
go

alter database Cyerp set trustworthy on

go
--开启CLR集成
EXEC sp_configure 'clr enabled','1'
go
--重新配置 就是用来更新使用sp_configure 系统存储过程更改的配置选项的当前配置值
reconfigure
--添加刚刚编译的DLL
create assembly QRcodeCreate from  'C:\Users\Administrator\Documents\visual studio 2013\Projects\測試二維碼圖片生成\QRcodeCreate\bin\Release\QRcodeCreate.dll'  
go

--drop assembly QRcodeCreate

use CYERP

go
--创建函数
CREATE FUNCTION dbo.QRcodeCreateText
( 
@url as nvarchar(50)
)
RETURNS varbinary(MAX)
AS EXTERNAL NAME QRcodeCreate.[QRcodeCreate.QRcodeCreate].QRcodeImage
               --Sql命名空间   dll命名空间 dll类 dll方法

測試結果：（輸入1123456 返回 二維碼圖片二進制流）

 

SSRS報表中綁定二維碼圖片數據流 效果如下：

 

 

在SQL 2012中.net4.0所以可能不適用，設計模式是一樣的