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