About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Wednesday, February 15, 2012

SQL-CLR: User Defined TVF - Barcode Parser

So, I created another SQL-CLR. This time it is a User Defined Function that is used to parse barcodes.

When setting this up, I added two attributes
  • FillRowMethodName - which is used to populate a table's row. The setup for this function, line 132 - 156, must accept an object as it's first value and then output fields that are used to populate the row.
  • TableDefinition - what the table that I want to return should include. One of the problems I ran into was that I was originally using varchar as my table values, but I would get this error: "Cannot use 'varchar' column in the result table of a streaming user-defined function".
For the intializing function, BarCodeParsercs, I need to return an IEnumerable value. Each value that is return in this function will end up as a row. In my case I just want one row, so I just created a string array with my single value.

This will end up calling the FillRow function to populate the row, for each value in the IEnumerable. In this case I call the GetData function to parse out my barcode and return a string array of values I needed to populate each of the table fields.

Now to deploy, you will need to first set your database table as TRUSTWORTHY

Note: To help debug the deployment script, copy the auto-generated deployment sql script, which can be found in the \bin\Debug and run it in SSMS, turned on "SQLCMD Mode" and executed the statement.




Code Snippet
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  6. using System.Collections;
  7.  
  8. public partial class UserDefinedFunctions
  9. {
  10.     [Microsoft.SqlServer.Server.SqlFunction(
  11.         FillRowMethodName="FillRow",
  12.         TableDefinition = "BarcodeType nvarchar(50) NULL, " +
  13.                         "PO nvarchar(50) NULL, " +
  14.                         "PartNumber nvarchar(50) NULL, " +
  15.                         "Qty nvarchar(50) NULL, " +
  16.                         "DateCode nvarchar(50) NULL, " +
  17.                         "SupplierName nvarchar(50) NULL, " +
  18.                         "SupplierSite nvarchar(50) NULL, " +
  19.                         "Date nvarchar(50) NULL, " +
  20.                         "Weight nvarchar(50) NULL")]
  21.     public static IEnumerable BarCodeParsercs(SqlString barcode)
  22.     {
  23.         return new String[]{barcode.Value};
  24.     }
  25.  
  26.     private static IEnumerable GetData(SqlString barcode)
  27.     {
  28.         string BarCodeType = "";
  29.         string PO = "";
  30.         string PartNumber = "";
  31.         string Qty = "";
  32.         string DateCode = "";
  33.         string SupplierName = "";
  34.         string SupplierSite = "";
  35.         string Date = "";
  36.         string Weight = "";
  37.  
  38.         string Barcode = barcode.Value;
  39.  
  40.         BarCodeType = Barcode.Substring(0, 3);
  41.         switch (BarCodeType)
  42.         {
  43.             case "]d2":
  44.                 {
  45.                     BarCodeType = "]d2";
  46.                     Barcode = Barcode.Substring(3);
  47.                     break;
  48.                 }
  49.             case "]C0":
  50.                 {
  51.                     BarCodeType = "]C0";
  52.                     Barcode = Barcode.Substring(3);
  53.                     break;
  54.                 }
  55.         }
  56.  
  57.         string[] sections = Barcode.Split('~');
  58.  
  59.         foreach (string section in sections)
  60.         {
  61.             string ThreeDigitCode = "";
  62.             string TwoDigitCode = "";
  63.             bool FoundMatch = false;
  64.  
  65.             if (section.Length > 3)
  66.             {
  67.                 ThreeDigitCode = section.Substring(0, 3);
  68.             }
  69.             if (section.Length > 2)
  70.             {
  71.                 TwoDigitCode = section.Substring(0, 2);
  72.             }
  73.  
  74.             switch (ThreeDigitCode)
  75.             {
  76.                 case "241":
  77.                     {
  78.                         PartNumber = section.Substring(3);
  79.                         FoundMatch = true;
  80.                         break;
  81.                     }
  82.                 case "400":
  83.                     {
  84.                         PO = section.Substring(3);
  85.                         FoundMatch = true;
  86.                         break;
  87.                     }
  88.             }
  89.             if (FoundMatch.Equals(false))
  90.             {
  91.                 switch (TwoDigitCode)
  92.                 {
  93.                     case "96":
  94.                         {
  95.                             Qty = section.Substring(2);
  96.                             break;
  97.                         }
  98.                     case "93":
  99.                         {
  100.                             DateCode = section.Substring(2);
  101.                             break;
  102.                         }
  103.                     case "91":
  104.                         {
  105.                             SupplierName = section.Substring(2);
  106.                             break;
  107.                         }
  108.                     case "92":
  109.                         {
  110.                             SupplierSite = section.Substring(2);
  111.                             break;
  112.                         }
  113.                     case "94":
  114.                         {
  115.                             Date = section.Substring(2);
  116.                             break;
  117.                         }
  118.                     case "95":
  119.                         {
  120.                             Weight = section.Substring(2);
  121.                             break;
  122.                         }
  123.                 }
  124.             }
  125.         }
  126.  
  127.         return new string[] { barcode.Value, BarCodeType, PO, PartNumber, Qty, DateCode, SupplierName, SupplierSite, Date, Weight };
  128.     }
  129.  
  130.  
  131.     public static void FillRow(Object obj,
  132.                             out SqlString BarcodeType,
  133.                             out SqlString PO,
  134.                             out SqlString PartNumber,
  135.                             out SqlString Qty,
  136.                             out SqlString DateCode,
  137.                             out SqlString SupplierName,
  138.                             out SqlString SupplierSite,
  139.                             out SqlString Date,
  140.                             out SqlString Weight)
  141.     {
  142.         String barcode = (String)obj;
  143.         String[] BarCode = (String[])GetData(barcode);
  144.  
  145.  
  146.         BarcodeType = (SqlString)BarCode[1];
  147.         PO = (SqlString)BarCode[2];
  148.         PartNumber = (SqlString)BarCode[3];
  149.         Qty = (SqlString)BarCode[4];
  150.         DateCode = (SqlString)BarCode[5];
  151.         SupplierName = (SqlString)BarCode[6];
  152.         SupplierSite = (SqlString)BarCode[7];
  153.         Date = (SqlString)BarCode[8];
  154.         Weight = (SqlString)BarCode[9];
  155.     }
  156. };


Once the code has been succesfully deployed. I just need to call the function, in SSMS, like so:
SELECT* FROM [dbo].[BarCodeParsercs] (N']d2400PO_09928-1~2413000510_E~96400~9320100614-320008210~910278~9232~94')




Helpful Resources:

http://www.mssqltips.com/sqlservertip/2582/introduction-to-sql-server-clr-table-valued-functions/
http://www.mssqltips.com/sqlservertip/1986/sqlclr-function-to-return-free-space-for-all-drives-on-a-server/


3 comments:

Anonymous said...

Good sharing,I learn a lot.In a word,i am totally a greenhand on SQL.
I am trying to print the QR Code on the reporting service, I am using sql 2005.I want to keep the QR Code on the reporting services when I exported it to the pdf files. It shows on the ssrs, but disappear on the pdf document, it is OK when exported to excel document.And I drag the barcode image from toolbox to the column. Hope you could help me.Thanks a lot.


Labels: .Net 3.5 sql-clr sql-server table value function user defined function .net barcode reader for pdf417

William Andrus said...

I would guess that the PDF output doesn't have the required font. You might need to download the font or choose one that the pdf already uses.

Anonymous said...

Hi,William Andrus,i followed your advice,i downloaded the font that the pdf already uses and finally solved my problem,thank you very much.