This article shows how to take data out of a database(Our web poll in thiscase) and generator gif''s on the fly. Thanks a million for Dave for contributing this user control.

Page 1. -- This calls the Code behind page (ImgPollResults2.aspx)

<%@ Page Language="vb" Src="ImgPollResults2.aspx.vb" Inherits="ImgPollResults2"%>

Page 2. -Code behind page

This is the code-behind page that calls the stored procedure to retrieve thedata. Couple of things that will have to be altered before this will work foryou is the stored proc or SQL string you pass and the connection string.

Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System
Imports System.Configuration

Public Class ImgPollResults2
Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, ByVal e AsSystem.EventArgs)
''Put user code to initialize the page here
Dim PollId As String = Request.QueryString("PID")

If Not (PollId Is Nothing) Then
GetDataBars(PollId)
End If


End Sub


Private Sub GetDataBars(ByVal PollId As String)

Dim AnswerCount As New ArrayList()
Dim AnswerLabel As New ArrayList()
Dim PollTitle As String
Dim dr As SqlDataReader
''Change the stored proc data and the querystring
Dim sqlText As String = "exec s_PollResults @PollId=" & PollId
dr = GetDataReader(sqlText)

While dr.Read()
PollTitle = dr.Item("Question").ToString()
AnswerCount.Add(dr.Item("AnswerCount").ToString())
AnswerLabel.Add(dr.Item("Answer").ToString())
End While


If AnswerCount.Count > 0 Then
DrawBarGraph(PollTitle, AnswerLabel, AnswerCount, Response.OutputStream)
End If

End Sub

Private Function GetDataReader(ByVal sqlText As String) As SqlDataReader

Dim dr As SqlDataReader
Dim sqlConn As SqlConnection = New SqlConnection(GetDBConnString())
Dim sqlCmd As SqlCommand = New SqlCommand(sqlText, sqlConn)
sqlCmd.Connection.Open()
dr = sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dr

End Function


Private Function GetDBConnString() As String
Return "server=localhost;uid=sa;pwd=;database=pubs"
End Function

Private Sub DrawBarGraph(ByVal strTitle As String, ByVal aX As ArrayList, ByVal aY As ArrayList, ByVal Target As Stream)

Const iColWidth As Integer = 100
Const iColSpace As Integer = 25
Const iMaxHeight As Integer = 100
Const iHeightSpace As Integer = 25
Const iXLegendSpace As Integer = 12
Const iTitleSpace As Integer = 50

Dim iMaxWidth As Integer = (iColWidth + iColSpace) * aX.Count +iColSpace, iMaxColHeight As Integer = 0, iTotalHeight As Integer =iMaxHeight + iXLegendSpace + iTitleSpace

Dim objBitmap As Bitmap = New Bitmap(iMaxWidth, iTotalHeight)
Dim objGraphics As Graphics = Graphics.FromImage(objBitmap)

objGraphics.FillRectangle(New SolidBrush(Color.White), 0, 0, iMaxWidth, iTotalHeight)
objGraphics.FillRectangle(New SolidBrush(Color.Ivory), 0, 0, iMaxWidth, iMaxHeight)

'' find the maximum value
Dim iValue As Integer
For Each iValue In aY
If iValue > iMaxColHeight Then iMaxColHeight = iValue
Next

Dim iBarX As Integer = iColSpace, iCurrentHeight As Integer
Dim objBrush As SolidBrush = New SolidBrush(Color.Purple)
Dim fontLegend As Font = New Font("Arial", 8)
Dim fontValues As Font = New Font("Arial", 8)
Dim fontTitle As Font = New Font("Arial", 12)

'' loop through and draw each bar
Dim iLoop As Integer
For iLoop = 0 To aX.Count - 1
iCurrentHeight = (Convert.ToDouble(aY(iLoop)) /Convert.ToDouble(iMaxColHeight)) * Convert.ToDouble(iMaxHeight -iHeightSpace)

objGraphics.FillRectangle(objBrush, iBarX, iMaxHeight - iCurrentHeight, iColWidth, iCurrentHeight)
objGraphics.DrawString(aX(iLoop), fontLegend, objBrush, iBarX, iMaxHeight)
objGraphics.DrawString(String.Format("{0:#,###}", aY(iLoop)),fontValues, objBrush, iBarX, iMaxHeight - iCurrentHeight - 15)

iBarX += (iColSpace + iColWidth)
Next

objGraphics.DrawString(strTitle, fontTitle, objBrush, (iMaxWidth / 2) - strTitle.Length * 4, iMaxHeight + iXLegendSpace)

objBitmap.Save(Target, ImageFormat.Gif)
objGraphics.Dispose()
objBitmap.Dispose()

End Sub


End Class