Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Rocks!

SQL Database Administrator/Developer. Background in developing OLTP/document based databases, SQLXML and performance tuning with an unhealthy fascination for the SQL query optimiser!

FIGlet SQLCLR ASCII Font Art

If you haven’t heard of the name before, you must have seen what it does at some point. FIGlet which was first developed in the ‘90s is a freeware large ASCII art generator which generates text from standard characters. It had quite a number of fonts contributed by others during the first few years but not many in recent years from what I can tell. Sure, it’s practical uses nowadays are somewhat limited but it is a great deal of retro fun and can still actually be useful…well maybe not, but who cares!

If FIGlet passed you by here are some examples generated using some of the FIGlet fonts. I know that this old stuff, but I think some of the fonts are pretty cool and in geeky sort of way and some are pretty darn clever to be just made out of regular ASCII characters.

image

I do actually sometimes use these fonts myself to generate prominent text/comment sections that I add to my scripts in certain places (especially lengthy scripts) where I want to quickly find that place again in the future. Additionally, now that I have a CLR version I can also generate the text during any script execution that generates a lot of output by just running the CLR procedure during different points in the script so that the generated text becomes part of the output. I can then quickly find those key reference points within the output.

I currently use one of the many FIGlet generator websites to generate the text as and when, but seeing as it is all freeware and pretty easy to implement, I thought I’d have a little bit of fun and try and create a SQL CLR version so that I can generate the text straight off my SQL boxes as part of my dev/dba work. I found this codeproject VBScript version of FIGlet by Prasad Khandekar and it seemed to be a good starting point to create my own C# version and as a source for some of the fonts.

After a bit of playing around for a couple of evenings porting the VBScript version to C#, I came up with a pretty simple .net solution but I had to implement the fonts as properties within the code. I wanted to use resource files but you can’t do that within a SQL CLR. I also wanted to keep the assembly permission marked as “SAFE” so I didn’t want to store them on the file system and access the fonts that way. I could have also used a SQL table to store the fonts, but I felt that would have the deployment a bit more fiddly.. Keeping everything in the dll makes for a simple deployment script. Using reflection (thanks TC) kept the main code down to just a few dozen lines in the end. (Please note, I am not a full-time .net developer, I just know enough to create useful tools and the like so my code may not be best practice!).

If you want to download the SQL script to create the assembly and procs and try it out yourself, then you can download either a SQL Server 2012 version or SQL Server 2005/2008/R2 version.

The script creates two CLR procedures and a permission SAFE assembly:

  • dbo.FigletHelp
  • dbo.FigletPrint

FigletHelp doesn’t take any parameters and it simply dumps the font name and an example of every font that I added to the assembly (all 153 of them!) like in the screen shot above.

FigletPrint requires three parameters:

@Font VARCHAR = Case sensitive font name which you can get from FigletHelp
@PrintCommentTags BIT = encase the output inside a comment /* */ block
@Text VARCHAR = The actual text to render as a FIGlet font

I kept it limited to just displaying the generated text in the output window as I really struggled in my mind to come up with a use case to have the data return as a column.. It’ll be pretty straight forward to modify the code to return it as a column though if anyone does have a scenario for it! Also, there are many more fonts available from the FIGlet.org ftp site if the included ones just don’t cut it!

You can download the entire Visual Studio 2012 solution here if you prefer to get your hands on the source. I may try to get it added to codeproject one day and might even add a few more fonts as well if people like this retro blast from the past.

Enjoy!

Comments

Leave a comment on the original post [www.olcot.co.uk, opens in a new window]

Loading comments...