Hilmar Buchta

SQL Server 2005

I just played with a simple way to return an image from a stored procedure by returning a base 64 encoded string of the image in a certain image format (e.g. PNG or JPEG).

Our sample stored procedure looks like this:

public static String SampleImage()
        {
            Bitmap bmp = new Bitmap( 80, 20, PixelFormat.Format32bppArgb);
            Graphics gBmp = Graphics.FromImage(bmp);
            System.Random RndObject = new System.Random();

            gBmp.CompositingMode = CompositingMode.SourceCopy;

            int y=10;
            int y1 = 0;
            Pen p = new Pen(Color.Black);
            gBmp.DrawLine(p, 0, 10, 80, 10);

            p.Color = Color.Blue;

            p.Width = 1;
            for (int i=10;i<=80;i+=10) {
                y1 = RndObject.Next(1, 19);
                gBmp.DrawLine(p, i – 10, y, i, y1);
                y = y1;
            }

            MemoryStream IS = new MemoryStream();
            bmp.Save(IS, ImageFormat.Png);                       
            IS.Flush();

            return Convert.ToBase64String(IS.ToArray());
        }

The function draws a very simple random line chart (we provided no real data but in some later posts I will provide some samples) and returns the resulting in-memory bitmap as base 64 encoded string.

Therefore the MDX code 

with member bmp as ASStatistics.SampleImage()

select {bmp} on columns,
[Product].[Product].[Product] on 1

from [Adventure Works]

 

returns a list of products together with the base 64 encoded image string. We use this MDX query for the report data source. In order to show the image on a report, we have to use the .Net function Convert.FromBase64(…). So, first we place an image object on our report, then we set the value property of the image to

=Convert.FromBase64String(Fields!bmp.Value)

 

That’s all folks. The result looks like this:

 

image

 

Ok, this isn’t exactly nice, but it still gives us some freedom in designing custom data aware graphics, like sparklines or other micro charts, and put them on a report.