BARCODE (Code39)
-
Hello, Have you ever needed to create a Barcode but all you could find are articles that use graphics or some font package? Well Here is a script that will allow you to use SQL Server and HTML to create the BarCode. You will not need anything special other then the ability to render the HTML. -------------------------------------------------- SQL CODE
-- -- Create the Function first -- CREATE FUNCTION F_TranslateToCode39(@StringToTranslate CHAR(1)) /**************************************************************** This function will be called by F_GenerateBarCode to generate the HTML string needed to represent the Character. ****************************************************************/ RETURNS VARCHAR(2000) AS BEGIN DECLARE @ReturnString VARCHAR(2000) ,@RawCode VARCHAR(12) ,@Counter INT -- -- First get the Raw representation of the character -- this will tell us what color the lines need to be -- for more references on this please refer to the following -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX -- http://www.barcodeisland.com/code39.phtml#Example -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SET @RawCode = (CASE UPPER(@StringToTranslate) WHEN '0' THEN '101001101101' WHEN '1' THEN '110100101011' WHEN '2' THEN '101100101011' WHEN '3' THEN '110110010101' WHEN '4' THEN '101001101011' WHEN '5' THEN '110100110101' WHEN '6' THEN '101100110101' WHEN '7' THEN '101001011011' WHEN '8' THEN '110100101101' WHEN '9' THEN '101100101101' WHEN 'A' THEN '110101001011' WHEN 'B' THEN '101101001011' WHEN 'C' THEN '110110100101' WHEN 'D' THEN '101011001011' WHEN 'E' THEN '110101100101' WHEN 'F' THEN '101101100101' WHEN 'G' THEN '101010011011' WHEN 'H' THEN '110101001101' WHEN 'I' THEN '101101001101' WHEN 'J' THEN '101011001101' WHEN 'K' THEN '110101010011' WHEN 'L' THEN '101101010011' WHEN 'M' THEN '110110101001' WHEN 'N' THEN '101011010011' WHEN 'O' THEN '110101101001' WHEN 'P' THEN '101101101001' WHEN 'Q' THEN '101010110011' WHEN 'R' THEN '110101011001' WHEN 'S' THEN '101101011001' WHEN 'T' THEN '101011011001' WHEN 'U' THEN '110010101011' WHEN 'V' THEN '100110101011' WHEN 'W' THEN '110011010101' WHEN 'X' THEN '100101101011' WHEN 'Y' THEN '110010110101' WHEN 'Z' THEN '100110110101' WHEN ' ' THEN '100110101101' WHEN '*' THEN '100101101101' WHEN '%' THEN '101001001001' WHEN '-' THEN '100101011011' ELSE '' END) -- -- Now that -
Hello, Have you ever needed to create a Barcode but all you could find are articles that use graphics or some font package? Well Here is a script that will allow you to use SQL Server and HTML to create the BarCode. You will not need anything special other then the ability to render the HTML. -------------------------------------------------- SQL CODE
-- -- Create the Function first -- CREATE FUNCTION F_TranslateToCode39(@StringToTranslate CHAR(1)) /**************************************************************** This function will be called by F_GenerateBarCode to generate the HTML string needed to represent the Character. ****************************************************************/ RETURNS VARCHAR(2000) AS BEGIN DECLARE @ReturnString VARCHAR(2000) ,@RawCode VARCHAR(12) ,@Counter INT -- -- First get the Raw representation of the character -- this will tell us what color the lines need to be -- for more references on this please refer to the following -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX -- http://www.barcodeisland.com/code39.phtml#Example -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SET @RawCode = (CASE UPPER(@StringToTranslate) WHEN '0' THEN '101001101101' WHEN '1' THEN '110100101011' WHEN '2' THEN '101100101011' WHEN '3' THEN '110110010101' WHEN '4' THEN '101001101011' WHEN '5' THEN '110100110101' WHEN '6' THEN '101100110101' WHEN '7' THEN '101001011011' WHEN '8' THEN '110100101101' WHEN '9' THEN '101100101101' WHEN 'A' THEN '110101001011' WHEN 'B' THEN '101101001011' WHEN 'C' THEN '110110100101' WHEN 'D' THEN '101011001011' WHEN 'E' THEN '110101100101' WHEN 'F' THEN '101101100101' WHEN 'G' THEN '101010011011' WHEN 'H' THEN '110101001101' WHEN 'I' THEN '101101001101' WHEN 'J' THEN '101011001101' WHEN 'K' THEN '110101010011' WHEN 'L' THEN '101101010011' WHEN 'M' THEN '110110101001' WHEN 'N' THEN '101011010011' WHEN 'O' THEN '110101101001' WHEN 'P' THEN '101101101001' WHEN 'Q' THEN '101010110011' WHEN 'R' THEN '110101011001' WHEN 'S' THEN '101101011001' WHEN 'T' THEN '101011011001' WHEN 'U' THEN '110010101011' WHEN 'V' THEN '100110101011' WHEN 'W' THEN '110011010101' WHEN 'X' THEN '100101101011' WHEN 'Y' THEN '110010110101' WHEN 'Z' THEN '100110110101' WHEN ' ' THEN '100110101101' WHEN '*' THEN '100101101101' WHEN '%' THEN '101001001001' WHEN '-' THEN '100101011011' ELSE '' END) -- -- Now that -
Hello, Have you ever needed to create a Barcode but all you could find are articles that use graphics or some font package? Well Here is a script that will allow you to use SQL Server and HTML to create the BarCode. You will not need anything special other then the ability to render the HTML. -------------------------------------------------- SQL CODE
-- -- Create the Function first -- CREATE FUNCTION F_TranslateToCode39(@StringToTranslate CHAR(1)) /**************************************************************** This function will be called by F_GenerateBarCode to generate the HTML string needed to represent the Character. ****************************************************************/ RETURNS VARCHAR(2000) AS BEGIN DECLARE @ReturnString VARCHAR(2000) ,@RawCode VARCHAR(12) ,@Counter INT -- -- First get the Raw representation of the character -- this will tell us what color the lines need to be -- for more references on this please refer to the following -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX -- http://www.barcodeisland.com/code39.phtml#Example -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SET @RawCode = (CASE UPPER(@StringToTranslate) WHEN '0' THEN '101001101101' WHEN '1' THEN '110100101011' WHEN '2' THEN '101100101011' WHEN '3' THEN '110110010101' WHEN '4' THEN '101001101011' WHEN '5' THEN '110100110101' WHEN '6' THEN '101100110101' WHEN '7' THEN '101001011011' WHEN '8' THEN '110100101101' WHEN '9' THEN '101100101101' WHEN 'A' THEN '110101001011' WHEN 'B' THEN '101101001011' WHEN 'C' THEN '110110100101' WHEN 'D' THEN '101011001011' WHEN 'E' THEN '110101100101' WHEN 'F' THEN '101101100101' WHEN 'G' THEN '101010011011' WHEN 'H' THEN '110101001101' WHEN 'I' THEN '101101001101' WHEN 'J' THEN '101011001101' WHEN 'K' THEN '110101010011' WHEN 'L' THEN '101101010011' WHEN 'M' THEN '110110101001' WHEN 'N' THEN '101011010011' WHEN 'O' THEN '110101101001' WHEN 'P' THEN '101101101001' WHEN 'Q' THEN '101010110011' WHEN 'R' THEN '110101011001' WHEN 'S' THEN '101101011001' WHEN 'T' THEN '101011011001' WHEN 'U' THEN '110010101011' WHEN 'V' THEN '100110101011' WHEN 'W' THEN '110011010101' WHEN 'X' THEN '100101101011' WHEN 'Y' THEN '110010110101' WHEN 'Z' THEN '100110110101' WHEN ' ' THEN '100110101101' WHEN '*' THEN '100101101101' WHEN '%' THEN '101001001001' WHEN '-' THEN '100101011011' ELSE '' END) -- -- Now thatWho the heck voted this 4? Um, it's very nice, and shows technical aptitude, but I would strongly recommend doing the conversion in C# on the client. Doing heavy string manipulation on the database server wastes processor time that would be better spent on data manipulations - work that cannot be done on the client. It's particularly pointless doing this work in the database server if the client is providing the data to convert. You're wasting a round-trip to the database server - not cheap even if client and server are on the same machine and you're using the Shared Memory network library. Stability. What an interesting concept. -- Chris Maunder
-
Who the heck voted this 4? Um, it's very nice, and shows technical aptitude, but I would strongly recommend doing the conversion in C# on the client. Doing heavy string manipulation on the database server wastes processor time that would be better spent on data manipulations - work that cannot be done on the client. It's particularly pointless doing this work in the database server if the client is providing the data to convert. You're wasting a round-trip to the database server - not cheap even if client and server are on the same machine and you're using the Shared Memory network library. Stability. What an interesting concept. -- Chris Maunder
Thank you for the comments. This script is part of a much bigger application where we barcode steps in the planning so they can be scanned on the floor. I just wanted to show a quick and dirty way to use it. If your only purpose was to use the textbox and not barcode information dynamically then yes it should be done on the client. The purpose of this script was to allow someone to use the function in a select statement to barcode information that will be presented on the screen. Again thank you for the comments. A 4 WOW... thank you! William O'Malley
-
Hello, Have you ever needed to create a Barcode but all you could find are articles that use graphics or some font package? Well Here is a script that will allow you to use SQL Server and HTML to create the BarCode. You will not need anything special other then the ability to render the HTML. -------------------------------------------------- SQL CODE
-- -- Create the Function first -- CREATE FUNCTION F_TranslateToCode39(@StringToTranslate CHAR(1)) /**************************************************************** This function will be called by F_GenerateBarCode to generate the HTML string needed to represent the Character. ****************************************************************/ RETURNS VARCHAR(2000) AS BEGIN DECLARE @ReturnString VARCHAR(2000) ,@RawCode VARCHAR(12) ,@Counter INT -- -- First get the Raw representation of the character -- this will tell us what color the lines need to be -- for more references on this please refer to the following -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX -- http://www.barcodeisland.com/code39.phtml#Example -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SET @RawCode = (CASE UPPER(@StringToTranslate) WHEN '0' THEN '101001101101' WHEN '1' THEN '110100101011' WHEN '2' THEN '101100101011' WHEN '3' THEN '110110010101' WHEN '4' THEN '101001101011' WHEN '5' THEN '110100110101' WHEN '6' THEN '101100110101' WHEN '7' THEN '101001011011' WHEN '8' THEN '110100101101' WHEN '9' THEN '101100101101' WHEN 'A' THEN '110101001011' WHEN 'B' THEN '101101001011' WHEN 'C' THEN '110110100101' WHEN 'D' THEN '101011001011' WHEN 'E' THEN '110101100101' WHEN 'F' THEN '101101100101' WHEN 'G' THEN '101010011011' WHEN 'H' THEN '110101001101' WHEN 'I' THEN '101101001101' WHEN 'J' THEN '101011001101' WHEN 'K' THEN '110101010011' WHEN 'L' THEN '101101010011' WHEN 'M' THEN '110110101001' WHEN 'N' THEN '101011010011' WHEN 'O' THEN '110101101001' WHEN 'P' THEN '101101101001' WHEN 'Q' THEN '101010110011' WHEN 'R' THEN '110101011001' WHEN 'S' THEN '101101011001' WHEN 'T' THEN '101011011001' WHEN 'U' THEN '110010101011' WHEN 'V' THEN '100110101011' WHEN 'W' THEN '110011010101' WHEN 'X' THEN '100101101011' WHEN 'Y' THEN '110010110101' WHEN 'Z' THEN '100110110101' WHEN ' ' THEN '100110101101' WHEN '*' THEN '100101101101' WHEN '%' THEN '101001001001' WHEN '-' THEN '100101011011' ELSE '' END) -- -- Now thatI've got a comment: this implementation is rubbish. I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this. Why is this written in T-SQL when a database isn't needed at all for this particular routine? The only thing you could justifiably use a database for here is the lookup from character to light/dark sequence, and you haven't done it. Where's the separation between representation and display? A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time! Even if your original string comes from a database, chucking a short string over makes much more sense than chucking over a wodge of HTML. The rendering of a barcode should be done in an ASP.NET web control (or if you aren't using ASP.NET, the functional equivalent). That way, if you later on need to render a barcode in a Windows application, you've got one routine written already to work out the light/dark sequence, and all you need to do is write a GDI renderer that accepts it. With care, this could be extended to support any barcode symbology.
-
I've got a comment: this implementation is rubbish. I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this. Why is this written in T-SQL when a database isn't needed at all for this particular routine? The only thing you could justifiably use a database for here is the lookup from character to light/dark sequence, and you haven't done it. Where's the separation between representation and display? A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time! Even if your original string comes from a database, chucking a short string over makes much more sense than chucking over a wodge of HTML. The rendering of a barcode should be done in an ASP.NET web control (or if you aren't using ASP.NET, the functional equivalent). That way, if you later on need to render a barcode in a Windows application, you've got one routine written already to work out the light/dark sequence, and all you need to do is write a GDI renderer that accepts it. With care, this could be extended to support any barcode symbology.
I have a comment. You try to post code for an example and expect people to read reply's QUOTE: I've got a comment: this implementation is rubbish. Then write something better. I explained why I posted this the way I did. QUOTE: I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this. I could really care less. QUOTE: A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time! PLEASE READ THE ABOVE COMMENT! Well thanks for the thoughts but when you have nothing of value to add then dont. Will
-
I have a comment. You try to post code for an example and expect people to read reply's QUOTE: I've got a comment: this implementation is rubbish. Then write something better. I explained why I posted this the way I did. QUOTE: I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this. I could really care less. QUOTE: A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time! PLEASE READ THE ABOVE COMMENT! Well thanks for the thoughts but when you have nothing of value to add then dont. Will
OMalleyW wrote: PLEASE READ THE ABOVE COMMENT! I did. And I disagree with it completely. Display oriented code just does not belong in any database layer. It doesn't matter two jots if your original barcode data comes from a database or not - generating HTML on the database side fails to distinguish between raw data and display, and makes the next person who has to maintain your code go "huh? wtf?". While I was admittedly harsh in my original reply (for which I apologise), the essence of the point remains - for this to be useful to most people, they would probably have to rewrite it in, probably similarly to how I suggested. Chucking blocks of HTML over an SQL Connection (particularly as the database will not be on the same machine as the web server) is also likely to be much less efficient than just chucking over the data that needs to be represented, and adds an unnecessary load onto your database that your web server should be taking on.
-
I have a comment. You try to post code for an example and expect people to read reply's QUOTE: I've got a comment: this implementation is rubbish. Then write something better. I explained why I posted this the way I did. QUOTE: I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this. I could really care less. QUOTE: A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time! PLEASE READ THE ABOVE COMMENT! Well thanks for the thoughts but when you have nothing of value to add then dont. Will
OMalleyW wrote: Then write something better. I explained why I posted this the way I did. Given that I do feel quite strongly about the difference, I've knocked up an ASP.NET user control as an approximate example of how I might go about it (the alternative is to write it with a GIF/PNG renderer). It's also a quickly hacked up job, but it outputs the essentially the same HTML as yours does so should be suitably illustrative. The way you call it from an ASP.NET page is like this:
<uc1:Code39 id="Code391" runat="server" Code="12345"></uc1:Code39>The Code attribute can be databound, which makes for easy use from data retrieved from SQL. The User Control implementation is as follows// Snip namespace and using stuff. public class Code39 : System.Web.UI.UserControl { // Lookup tables for initialisation private static Hashtable CodeLookup; static Code39() { CodeLookup = new Hashtable(); CodeLookup.Add('0', new bool[]{true, false,true, false,false,true, true, false,true, true, false,true}); CodeLookup.Add('1', new bool[]{true, true, false,true, false,false,true, false,true, false,true, true}); CodeLookup.Add('2', new bool[]{true, false,true, true, false,false,true, false,true, false,true, true}); CodeLookup.Add('3', new bool[]{true, true, false,true, true, false,false,true, false,true, false,true}); CodeLookup.Add('4', new bool[]{true, false,true, false,false,true, true, false,true, false,true, true}); CodeLookup.Add('5', new bool[]{true, true, false,true, false,false,true, true, false,true, false,true}); CodeLookup.Add('6', new bool[]{true, false,true, true, false,false,true, true, false,true, false,true}); CodeLookup.Add('7', new bool[]{true, false,true, false,false,true, false,true, true, false,true, true}); CodeLookup.Add('8', new bool[]{true, true, false,true, false,false,true, false,true, true, false,true}); CodeLookup.Add('9', new bool[]{true, false,true, true, false,false,true, false,true, true, false,true}); CodeLookup.Add('A', new bool[]{true, true, false,true, false,true, false,false,true, false,true, true}); CodeLookup.Add('B', new bool[]{true, false,true, true, false,true, false,false,true, false,t -
OMalleyW wrote: Then write something better. I explained why I posted this the way I did. Given that I do feel quite strongly about the difference, I've knocked up an ASP.NET user control as an approximate example of how I might go about it (the alternative is to write it with a GIF/PNG renderer). It's also a quickly hacked up job, but it outputs the essentially the same HTML as yours does so should be suitably illustrative. The way you call it from an ASP.NET page is like this:
<uc1:Code39 id="Code391" runat="server" Code="12345"></uc1:Code39>The Code attribute can be databound, which makes for easy use from data retrieved from SQL. The User Control implementation is as follows// Snip namespace and using stuff. public class Code39 : System.Web.UI.UserControl { // Lookup tables for initialisation private static Hashtable CodeLookup; static Code39() { CodeLookup = new Hashtable(); CodeLookup.Add('0', new bool[]{true, false,true, false,false,true, true, false,true, true, false,true}); CodeLookup.Add('1', new bool[]{true, true, false,true, false,false,true, false,true, false,true, true}); CodeLookup.Add('2', new bool[]{true, false,true, true, false,false,true, false,true, false,true, true}); CodeLookup.Add('3', new bool[]{true, true, false,true, true, false,false,true, false,true, false,true}); CodeLookup.Add('4', new bool[]{true, false,true, false,false,true, true, false,true, false,true, true}); CodeLookup.Add('5', new bool[]{true, true, false,true, false,false,true, true, false,true, false,true}); CodeLookup.Add('6', new bool[]{true, false,true, true, false,false,true, true, false,true, false,true}); CodeLookup.Add('7', new bool[]{true, false,true, false,false,true, false,true, true, false,true, true}); CodeLookup.Add('8', new bool[]{true, true, false,true, false,false,true, false,true, true, false,true}); CodeLookup.Add('9', new bool[]{true, false,true, true, false,false,true, false,true, true, false,true}); CodeLookup.Add('A', new bool[]{true, true, false,true, false,true, false,false,true, false,true, true}); CodeLookup.Add('B', new bool[]{true, false,true, true, false,true, false,false,true, false,tNow that is what I am talking about... Very nice post! When I first wrote the Barcode program it was an external project with classes that I created to render the bars. When I showed it to my boss and we talked about how it would be used, we decided that it would be to our advantage to have SQL Server do the work for us and just display it rather then take the time to format it later. As I look back on it a better solution would have been to generate an XML page that has a XSLT translation that formatted the data presented. That way I could use the built in OPENXML capability's of SQL Server and not wast the time generating the HTML. I am sorry about how I replied to you. I should have handled that one better, but from my stand point I was just posting code that someone could re-write and reuse as they saw fit like you did. If it is ok with you I would like to take some of the ideas above and use them in a new project I am going to be working on to render more codes. Thank you for the reply. William O'Malley