Create barcodes using fonts in Microsoft SQL Server Report Builder


This tutorial illustrates how to create Rdlc barcodes using Microsoft SQL Server Report Builder and ConnectCode Barcode Software and Fonts package.

Prerequisites

  • Microsoft SQL Server 2016 is installed
  • Microsoft SQL Server Reporting Services (SSRS) is installed
  • AdventureWorks Sample Database (or any other sample database)
  • SQL Server Report Builder
  • ConnectCode Barcode Software and Fonts is installed
1. Launch Microsoft SQL Server Report Builder and select "New Report -> New Table or Matrix".



2. In the "New Table or Matrix" dialog, select the option to "Create a dataset", and click on the "Next" button. When asked to choose a data source, click on the "New" button to create a new Data Source. Next, click on the "Build" button to construct a new connection string.



2. In the "Connection Properties" dialog, select the "Server name" of your SQL Server. In "Select or enter a database name", select the AdventureWorks database. The screen below shows that we have selected the "DESKTOP-NCC8T6A" server and the "AdventureWorks2016CTP3" database. We have previously installed "SQL Server 2016" and the "AdventureWorks2016" sample database in a PC named "DESKTOP-NCC8T6A". You can also select any other database to proceed with this tutorial. Click on the "OK" button when you are done.



Note: If you do not see any database available for selection, simply download the AdventureWorks2016.bak from Microsoft website and perform a restore with "SQL Server Management Studio".

3. In the "Data Source Properties", click on the "OK" button. In the "New Table or Matrix" dialog, click on the "Next" button. In the "Design a query" dialog, select the "Product" table as shown below, and click on the "Next" button.



4. In the dialog shown below, select "ProductID", "Name" and "ProductNumber" fields and add them into "Row groups". Select the "Weight" field, add it into "Values" and click on the "Next" button. In the "Choose the layout" dialog, unchecked the "Show subtotals and grand totals" and "Expand/collapse groups" option, and click on the "Next" followed by the "Finish" button. A sample report will be generated.



5. In the Report Builder designer, right click on the gray area outside of the report and select "Report Properties" as shown below. We are going to use a DLL (Dynamic Link Library) provided by ConnectCode Barcode Software and Fonts package to generate the barcode.



6. Select the "References" tab, click on the "Add" button, followed by the "..." button. Select the "ConnectCodeBarcodeFontsLibrary.dll" from the following folder:

C:\Program Files (x86)\ConnectCode\Resource\Net DLL Integration Samples\DLL



Click on "Add or remove class", enter "Net.ConnnectCode.Barcode.BarcodeFonts" as the class name and "barcode" as the "Instance Name".

7. Click on the "Code" tab and enter the following custom code. This custom code uses the "barcode" instance defined in the previous step to define a "MakeBarcode function. The output characters (barcode.EncodedData), when applied with the Code 128 barcode font will give us an industry compliant Code 128 barcode.

Public Function MakeBarcode(datastring As String) as String 

barcode.BarcodeType = Net.ConnectCode.Barcode.BarcodeFonts.BarcodeEnum.Code128Auto

barcode.Data = datastring

barcode.encode()

return barcode.EncodedData 

End Function 

8. Next, we are going to apply the "MakeBarcode" function on the "[ProductNumber]" column in the report. Right click on the [ProductNumber] field and select "Expression. Enter the expression shown below. We are applying the MakeBarcode function on the [ProductNumber] field.

=Code.MakeBarcode(Fields!ProductNumber.Value)

9. You have performed steps 5 to 8 to setup the report to use "ConnectCodeBarcodeFontsLibrary.dll" to generate a Code 128 barcode. This DLL will need to be "Trusted" by Report Builder. To trust the DLL, copy the DLL from the following folder:

C:\Program Files (x86)\ConnectCode\Resource\Net DLL Integration Samples\DLL

to

C:\Program Files (x86)\Microsoft SQL Server\Report Builder

After copying the DLL, please modify "RSPreviewPolicy.config" in the following folder:

C:\Program Files (x86)\Microsoft SQL Server\Report Builder

Add the following into the file to grant FullTrust to the DLL:

<CodeGroup class="UnionCodeGroup" Name="BarcodeFonts" version="1" PermissionSetName="FullTrust"
Description="This code group grants ConnectCodeBarcodeFontsLibrary.dll FullTrust permission."> 
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files (x86)\Microsoft SQL Server\Report Builder\ConnectCodeBarcodeFontsLibrary.dll"/> 
</CodeGroup>  

Note: If you are going to deploy the report to Reporting Services, please ensure the ConnectCodeBarcodeFontsLibrary.dll is copied to the following SQL Server folder.

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin

Please add the following tag to the "rssrvpolicy.config" file.

<CodeGroup class="UnionCodeGroup" Name="BarcodeFonts" version="1" PermissionSetName="FullTrust"
Description="This code group grants ConnectCodeBarcodeFontsLibrary.dll FullTrust permission."> 
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\
Reporting Services\ReportServer\bin\ConnectCodeBarcodeFontsLibrary.dll"/> 
</CodeGroup> 

The "rssrvpolicy.config" file is available in the following folder.

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config

10. The final step is to apply the Code 128 barcode font on the output generated by the DLL. Select the "[ProductNumber]" field. This is the same field which we apply our expression in step 8. In the "Properties" section of Report Builder, select CCode128_S3 (or CCode128_S3_Trial) as the FontFamily and set the FontSize to 24.



11. Click on "Run" button in Report Builder. You will see the report with the generated barcodes.