Flash CS5.5 and AS3 – Flash (AS3) / ASHX (ASP.Net) / SQL Server – Post

This article explains in the most simpliest terms how to use action script to connect to SQL Server via an ashx file in order to post data from your flash application.

First, you build a flash game that keeps track of the gamer’s score. At the end of the game, you want to save the gamer’s score to your database.

To save the score, you need to add code to your action script to call an ashx file on your server.

First, define a URLVariables variable. Appended to this variable are the game statistics that you want to pass to your ashx file. (PlayerName, PlayerLevel, PlayerScore)

Second, you define a URLLoader variable.

Next, define a URLRequest variable that contains the url location of your ashx file. You then assign the POST method to your URLRequest variable and assign your defined variables to it’s data method.

Lastly, you assign the TEXT data format to your URLLoader, then execute the load method of the URLLoader passing as a parameter the URLRequest variable.

Below is the code:

var variables:URLVariables = new URLVariables;
variables.PlayerName = txtPlayerName.text;
variables.PlayerLevel = txtPlayerLevel.text;
variables.PlayerScore = txtPlayerScore.text;

var varLoaderPost:URLLoader = new URLLoader;

var varSend:URLRequest = new URLRequest("http://www.somewebsite.com/flash/somefile_POST.ashx");
varSend.method = URLRequestMethod.POST;
varSend.data = variables;

varLoaderPost.dataFormat = URLLoaderDataFormat.TEXT;
varLoaderPost.load(varSend);

Now that the action script code is explained, let’s talk about the ashx file that gets called by your flash application.

First, you receive the player name, level, and score posted to the ashx and assign them to variables.

Second, you establish your timezone of choice and get the current date.

Third, you open up your database connection.

Next, you build the insert sql statement.

Lastly, you execute the sql statement. The player’s data is now stored in the database.

The ashx finishes off by closing your database connection.

Below is the code:

somefile_POST.ashx

<%@ WebHandler Language="VB" Class="somefile_POST" %>

Imports System.Data.SqlClient
Imports System.Data
Imports System.Text

Public Class somefile_POST : Implements IHttpHandler
    Protected mLogger As NLog.Logger = NLog.LogManager.GetCurrentClassLogger

    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 SqlConnections
    Protected mSQLCommand As New SqlCommand

    Protected PlayerName As String = String.Empty
    Protected PlayerScore As String = String.Empty
    Protected PlayerLevel As String = String.Empty
   
    Protected _CurrentDateTime As DateTime = Nothing
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Try
            PlayerName = Trim(context.Request.Form("PlayerName"))
            PlayerScore = Trim(context.Request.Form("PlayerScore"))
            PlayerLevel = Trim(context.Request.Form("PlayerLevel"))

            Dim timeZoneInfo__1 As TimeZoneInfo
            'Set the time zone information to US Mountain Standard Time 
            timeZoneInfo__1 = TimeZoneInfo.FindSystemTimeZoneById("US Eastern Standard Time")
            'Get date and time in US Eastern Standard Time 
            _CurrentDateTime = TimeZoneInfo.ConvertTime(DateTime.Now, timeZoneInfo__1)
            'Print out the date and time

            OpenDatabaseConnection()

            ToDataBase()

            CloseDatabaseConnection()

        Catch ex As Exception
            mLogger.Error(ex.Message)
        End Try
    End Sub
    
    Protected Sub OpenDatabaseConnection()
        Try
            mDatabaseServer = "your.database.server"
            mDataBase = "yourdatabasename"
            mDatabaseUserID = "userid"
            mDatabasePassword = "password"

            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
            mLogger.Error(ex)
        End Try
    End Sub

    Protected Sub CloseDatabaseConnection()
        Try
            mSQLConnection.Close()
        Catch ex As Exception
            mLogger.Error(ex)
        End Try
    End Sub

    Protected Sub ToDataBase()
        Dim mString As New StringBuilder
        Try
            Dim mReplaceString As String = String.Empty

            mString.Append("INSERT INTO flash_game_table")
            mString.Append("(DatePlayed, PlayerName, PlayerLevel, PlayerScore) ")
            mString.Append("VALUES (")

            mString.Append("'")
            mString.Append(_CurrentDateTime)
            mString.Append("'")
            mString.Append(", ")

            mString.Append("'")
            mString.Append(PlayerName)
            mString.Append("'")
            mString.Append(", ")

            mString.Append("'")
            mString.Append(PlayerLevel)
            mString.Append("'")
            mString.Append(", ")

            mString.Append("'")
            mString.Append(PlayerScore)
            mString.Append("'")

            mString.Append(")")

            mSQLCommand.CommandText = mString.ToString
            mSQLCommand.ExecuteNonQuery()

        Catch ex As Exception
            mLogger.Error(ex)
            mLogger.Error("SQL Statement: " & mString.ToString)
        End Try
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class