SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Generate QR Code® barcodes in an SSRS report with the QRCoder library

By Stan Kulp,

While the QRCode4CS open source library used in the article, Generate two-dimensional QR Code® bar codes in an SSRS report, enables the display of QR Codes in SSRS reports, it turns out that it only supports the Micro QR Code standard, which limits the input string to 34 characters.

In contrast, the open source QRCoder library can support an input string of up to 2952 characters.

Attribution

This article uses code extracted from the following QRCoder project archive: https://github.com/codebude/QRCoder/archive/master.zip >> QRCoder-master.zip

Overview

These are the steps required to create an SSRS report that displays QR code symbols using the QRCoder library

  1. Obtain existing or compile the two QRCoder assemblies with strong names and the PartiallyTrustedCallers attribute*
  2. Install the two assemblies to the global assembly cache (GAC) using the gacutil.exe utility
  3. Create a new SSRS report that queries a table of test data
  4. Add a reference to the QRCoderImageToByteArray GAC assembly 
  5. Add a custom code function that sends a string to the QRCoderImageToByteArray assembly and accepts a byte array in return
  6. Add a field to the report and increase the physical dimensions of the field to accomodate a larger QR code symbol
  7. Drag-and-drop an image componenet to the new field to bring up the image properties dialogue
  8. Change the image source to Database, add a function that references the custom code function and change the MIME type to image/png
  9. Set the size property of the image component to fill the available field space while maintaining the original aspect ratio
  10. Execute the report to display the data and the QR code generated from the data

*A strong name is required to insert an assemby into the GAC. SSRS requires assemblies it references to include the PartiallyTrustedCallers attribute.

Resource file content

Unzip the attached QRCoderResources.zip file and browse to the extracted QRCoderResources folder in File Explorer.

The QRCoderDemoExe folder contains an executable that can be used to test the output of the QRCoder library.

The QRCoderDemoSln, QRCoderImageToByteArraySln and QRCodeSln folders contain the C# projects used to compile the QR Coder demo application and assemblies. (You can load these projects into Visual Studio or SharpDevelop and compile them yourself if you wish.)

The StronglyNamedPartiallyTrustedCallersDLLs folder contains the assemblies that will be used to generate QR Codes in an SSRS report.

The QRCoder-master.zip file is the original QRCoder archive downloaded from github.

Test the QRCoder library

Browse to the QRCoderDemoExe folder to display the QRCoder.dll assembly and the QRCoderDemo application that uses it to generate QRCodes.

Double-click the QRCodeDemo application to generate a QRCode symbol.

By default the application generates a QR Code for the string 1234567890, but you can enter any string you wish into the textbox to generate a symbol for that string.

Test the application with typical character strings you wish to convert to QR Code symbols and read the symbols with your QR Code reader of choice to confirm the library is satisfactory for your purposes.

Assemblies used to generate QR Code symbols in SSRS reports

Browse to the StronglyNamedPartiallyTrustedCallersDLLs folder.

The QRCoder.dll assembly can generate QR Code symbols from an input string in a variety of image formats including bitmap.

SQL Server Reporting Services cannot display images directly, however, but requires images to be streamed as byte arrays. To address this, the QRCoderImageToByteArray.dll assembly passes an input string to the BitmapByteQRCode() function of the QRCoder.dll assembly and streams the returned bitmap image as a byte array to SSRS.

Install the assemblies to the global assembly cache (GAC)

Find gacutil.exe with Agent Ransack or File Explorer.

Copy the path of the appropriate 32- or 64-bit version of gacutil.exe:

C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\x64\gacutil.exe

Open a command console as administrator and change to the StronglyNamedPartiallyTrustedCallersDLLs folder.

Install the assemblies to the GAC with the following commands:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\x64\gacutil.exe" -i QRCoder.dll

"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\x64\gacutil.exe" -i QRCoderImageToByteArray.dll

Confirm the assemblies have been added to the C:\Windows\Microsoft.NET\assembly\GAC_MSIL folder.

Create test data for the report

Open a new query window in SQL Server Management Studio and paste the following SQL code into it.

BEGIN TRY
    DROP DATABASE TestDB
END TRY
BEGIN CATCH
END CATCH
GO

BEGIN TRY
    CREATE DATABASE TestDB
END TRY
BEGIN CATCH
END CATCH
GO

USE TestDB

BEGIN TRY
    DROP TABLE TestData
END TRY
BEGIN CATCH
END CATCH
GO

CREATE TABLE [dbo].[TestData](
    [City] [nvarchar](50) NULL,
    [Zip] [nvarchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Seattle','98101')
INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Bangor','04402')
INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Miami','33133')
INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Houston','77001')
INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Boston','02201')

SELECT * FROM [dbo].[TestData]

Execute the query to create a database named TestDB and a table named TestData that contains some zip code data.

Create and execute the QRCoder Demo SSRS report

Start a new Report Server Project Wizard in Visual Studio.

Insert the appropriate connection string information.

Insert the following code into the Query string text box.

SELECT city,zip FROM dbo.TestData

Select tabular report type.

Move the city and zip fields into the Details text box.

Click the finish button.

Select Report Properties from the Report menu.

Click on the References tab followed by the add button then the browse tab and browse to the QRCoderImgeToByteArray.dll assembly in the GAC.

Click on the Code tab and add the following function to the custom code text box:

Public Function QRCoderByteArray(ByVal inputString As String) as Byte()
     Return QRCoderImageToByteArray.QRCoderImageToByteArrayClass.renderBmpToByteArray(inputString)
End Function

RIght-click on the top of the zip column and insert a new column to the right.

Increase the height of the data row and the width of the new column to make room for a larger QRCode symbol.

Drag-and-drop an image component into the new field to bring up the image properties window.

Select Database as the image source and click on the use this field function button.

Enter the following code into the expression text box:

=Code.QRCoderByteArray(Fields!city.Value + " " + Fields!zip.Value)

Select image/png as MIME type:

Click on the size node and select the fit proportional radio button and click OK.

Click on the Preview tab of the report console.

The city, zip and QR code for the combined city and zip are displayed in the report.

The barcode scanner app on my phone correctly deciphers the first QR code symbol.

Conclusion

The previous article on displaying QR code symbols in SSRS reports used an open source library which, unknown to me at the time, only supported the Micro QR Code.standard, which limited it to a 34-character input string.

The QRCoder library is a more rigorous implementation of the ISO/IEC 18004 QR Code standard. It encodes up to 2952 alphanumeric characters, which is the specified binary/byte maximum for the standard.

 

Resources:

QRCoderResources.zip
Total article views: 891 | Views in the last 30 days: 891
 
Related Articles
BLOG

Symbols

I just want to write the setup I went through for using debug symbols within SQL Server to resolve c...

FORUM

Pipe Symbol as Column delimiter

Pipe Symbol

FORUM

Passing Symbols as Input to SP

Unable to pass Symbols as Input to SP

FORUM

Decimal symbol

Is possible to set decimal symbol

ARTICLE

Creating a heat map in Reporting Services

Use custom assemblies to generate a heat map matrix in SQL Server Reporting Services

Tags
c#    
open source    
qr code    
reporting services (ssrs)    
 
Contribute