This article explains in the most simpliest terms how to use action script to connect to SQL Server via an ashx (ASP.Net) file in order to extract data for display in your flash application.
First, you build a flash game that keeps track and stores the gamer’s score. Before we even discuss that (in another article: http://www.jamesandchey.net/?p=543),
we want to retrieve the top score from the database saved from a previous game and display it to the current gamer.
To retreive the top score, you need to add code to your action script to call an ashx file on your server.
First, define a URLLoader variable.
Next, define a URLRequest variable that contains the url location of your ashx file. Also, assign the GET method to your URLRequest variable.
You then need to define an event listener to your URLLoader so it can fire a function (that you define) once the data is returned to your flash application via the ashx file.
The function you define will take the data (built in XML format by the ashx file) and load it into textbox variables for display.
Lastly, execute the load method of the URLLoader passing as a parameter the URLRequest variable.
Below is the code:
var varLoaderGet:URLLoader = new URLLoader;
var varGet:URLRequest = new URLRequest("http://www.somewebsite.com/flash/somefile_GET.ashx");
varGet.method = URLRequestMethod.GET;
varLoaderGet.addEventListener(Event.COMPLETE, loaderGetCompleteHandler, false, 0, true);
varLoaderGet.load(varGet);
function loaderGetCompleteHandler(e:Event):void
{
var dataXML:XML = XML(e.target.data);
_SQLDatePlayed.text = (dataXML.item[0]);
_SQLName.text = (dataXML.item[1]);
_SQLLevel.text = (dataXML.item[2]);
_SQLScore.text = (dataXML.item[3]);
}
Now that the action script code is explained, let’s talk about the ashx file that gets called by your flash application.
The first part of your ashx file connects to SQL Server and establishes a connection to your database.
Next, you build your query statement to retrieve the data.
Lastly, you execute the query and store the results in your DataTable.
Now that you have the data, you build the xml file using XmlTextWriter.
The ashx finishes off by writing out the XML to be retrieved by your flash application.
Below is the code:
somefile_GET.ashx
<%@ WebHandler Language="VB" Class="somefile_GET" %>
Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Data
Imports System.Text
Imports System.Xml
Imports System.IO
Public Class somefile_GET : Implements IHttpHandler
Protected mSQLDataReader As SqlDataReader
Protected somefileSchemaTable As DataTable
Protected somefileDataTable As DataTable
Protected mDatabaseServer As String = String.Empty
Protected mDataBase As String = String.Empty
Protected mDatabaseUserID As String = String.Empty
Protected mDatabasePassword As String = String.Empty
Protected mSQLConnection As New SqlConnection
Protected mSQLCommand As New SqlCommand
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
OpenDatabaseConnection()
Dim mString As New StringBuilder
mString.Append("SELECT top 1 * FROM flash_game_table order by PlayerScore desc, DatePlayed desc")
mSQLCommand.CommandText = mString.ToString
mSQLDataReader = mSQLCommand.ExecuteReader()
If mSQLDataReader.HasRows = True Then
DataReaderToDataTable()
End If
mSQLDataReader.Close()
CloseDatabaseConnection()
Dim somefile As New System.IO.MemoryStream
Dim writer As New XmlTextWriter(somefile, System.Text.Encoding.UTF8)
writer.WriteStartDocument(True)
writer.Formatting = Formatting.Indented
writer.Indentation = 2
writer.WriteStartElement("flash_game")
For Each pRow As DataRow In somefileDataTable.Rows
Dim mDatePlayed As String = String.Empty
Dim mPlayerName As String = String.Empty
Dim mPlayerLevel As String = String.Empty
Dim mPlayerScore As String = String.Empty
For Each pColumn As DataColumn In somefileDataTable.Columns
Select Case pColumn.Caption
Case "DatePlayed"
mDatePlayed = pRow(pColumn)
Case "PlayerName"
mPlayerName = pRow(pColumn).ToString
Case "PlayerLevel"
mPlayerLevel = pRow(pColumn).ToString
Case "PlayerScore"
mPlayerScore = pRow(pColumn).ToString
End Select
Next
writer.WriteStartElement("item")
writer.WriteString(mDatePlayed)
writer.WriteEndElement()
writer.WriteStartElement("item")
writer.WriteString(mPlayerName)
writer.WriteEndElement()
writer.WriteStartElement("item")
writer.WriteString(mPlayerLevel)
writer.WriteEndElement()
writer.WriteStartElement("item")
writer.WriteString(mPlayerScore)
writer.WriteEndElement()
Next
writer.WriteEndElement()
writer.WriteEndDocument()
writer.Flush()
'This is so I can see the xml that I am actually building. 'writer' is what I will be sending to my flash program.
Dim _somefileXML As String = Encoding.UTF8.GetString(somefile.GetBuffer(), 0, CInt(somefile.Length))
context.Response.ClearContent()
context.Response.Clear()
context.Response.ContentType = "text/xml"
context.Response.OutputStream.Write(somefile.ToArray(), 0, somefile.Length)
context.Response.Flush()
writer.Close()
End Sub
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
Protected Sub OpenDatabaseConnection()
Try
mDatabaseServer = "your.database.server"
mDataBase = "yourdatabasename"
mDatabaseUserID = "userid"
mDatabasePassword = "passwrod"
mSQLConnection.ConnectionString = "Data Source=" & mDatabaseServer & ";Initial Catalog=" & mDataBase & ";Persist Security Info=True;User ID=" & mDatabaseUserID & ";Password=" & mDatabasePassword
mSQLConnection.Open()
mSQLCommand.Connection = mSQLConnection
Catch ex As Exception
End Try
End Sub
Protected Sub CloseDatabaseConnection()
Try
mSQLConnection.Close()
Catch ex As Exception
End Try
End Sub
Protected Sub DataReaderToDataTable()
'This function receives a Data Reader and returns a Data Table
Try
'Table Schema
somefileSchemaTable = mSQLDataReader.GetSchemaTable()
'Data Table
somefileDataTable = New DataTable
Dim intCounter As Integer
'Now to create the Schema on the DataTable
For intCounter = 0 To somefileSchemaTable.Rows.Count - 1
'Current Row
Dim dataRow As DataRow = somefileSchemaTable.Rows(intCounter)
'Current Column Name
Dim columnName As String = CType(dataRow("ColumnName"), String)
'Current Column
Dim column As New DataColumn(columnName, CType(dataRow("DataType"), Type))
'Add Column to the DataTable
somefileDataTable.Columns.Add(column)
Next intCounter
'Now to fill the table with the reader
While mSQLDataReader.Read()
'New Row
Dim dataRow As DataRow = somefileDataTable.NewRow()
'Loop the fields
For intCounter = 0 To mSQLDataReader.FieldCount - 1
'Insert the current value of the DataReader to the DataRow
dataRow(intCounter) = mSQLDataReader.GetValue(intCounter)
Next
'Insert the Row into the DataTable
somefileDataTable.Rows.Add(dataRow)
End While
Catch ex As Exception
End Try
End Sub
End Class