ADO.NET Connectivity with Macromedia Flash Remoting MX

Manipulating a Database with Flash MX Professional 2004

Now that you've built the ASP.NET page, you need to develop the Macromedia Flash front end. In my example application, I took the liberty of developing an interface that takes advantage of the way Flash quickly responds to a user’s actions. Since article focuses on Flash Remoting and not on the user interface, I won’t provide details on the interface design. You can, however, use the starter file SQLQueryTool-starter.fla included in the code download to follow along with the prebuilt design.

Setting up the Flash application to connect with the Flash Remoting ASP.NET page is amazingly easy. You can establish a connection to the service with three simple lines of code. Then all you need to do is to pass a query string to the ASP.NET service and bind the results to the DataGrid, a Flash UI component.

Note: The DataGrid component is included in Macromedia Flash MX Professional 2004. Macromedia Flash MX users can purchase it as part of the Macromedia DevNet Resource Kit, Volume 1.

To get started:

Open Flash MX Professional 2004 and open the starter file, SQLQueryTool-starter.fla. This document provides some prebuilt user interface elements as well as a special error message dialog.

In the document, there are two keyframes: one named Query and another named Result. The Query keyframe contains the form. Users will submit data through this form to the ASP.NET Flash Remoting service. The Result keyframe is a DataGrid listing of results.
Save your Flash file as SQLQueryTool-mine.fla.
To start development, select the Query keyframe in the Actions layer and open the Actions panel. Copy and paste the following code into the panel:
```
#include "NetServices.as"

if( inited == null )
{
inited = true;
NetServices.setDefaultGatewayUrl( "http://localhost/services/gateway.aspx" );
gatewayConnection = NetServices.createGatewayConnection();

SQLQueryToolService = gatewayConnection.getService( "services", this );
}
```
This code block uses an if statement to prevent it from running more than once. It also initializes a connection to the Flash Remoting ASP.NET service. One of the key elements of this code block are the include statements that import the core Flash Remoting ActionScript file, NetServices.as.

The Flash Remoting initialization code first establishes a connection to a Flash Remoting server using the setDefaultGatewayUrl() method. Macromedia suggests that you pass this code as a parameter in the HTML code you use to embed your Flash application in a web page. This way you can easily change the host server without accessing the Flash file. The value for this method is a URL string pointing to a gateway.aspx file on the server. While all other implementations of Flash Remoting don’t require a physical gateway file, Flash Remoting with .NET does. You should place this file in the same directory where you store your .NET pages; it can be a blank ASP.NET page with that file name, gateway.aspx.

Flash Remoting can use the gateway connection you created to call to and manipulate your ASP.NET page. The ActionScript code uses dot notation to call out to the page. The dot notation starts at the web server root. This means you should type the address http://localhost/services/sqltool/ as services.sqltool. In this case, I’ve stored the gateway.aspx and SQLQueryToolAction.aspx files in the services directory of my web server, so the dot notation is simply services.
Next, create several functions to call the ASP.NET page and process its results by adding the following ActionScript after the Flash Remoting gateway code in the first frame of the file:
```
function SQLQueryToolAction( queryString )
{
	if( ( queryString == null ) || ( queryString == "" ) )
	{
		DisplayErrorMessage( "You  must provide a SQL query." );
		return;
	}	

	SQLQueryToolService.SQLQueryToolAction( queryString );
}

function SQLQueryToolAction_Result( result )
{
}

function SQLQueryToolAction_Status( error )
{
	DisplayErrorMessage( error.description );	
}
```
Calling the ASP.NET page is simple: Use the page name and call it using the Flash Remoting instance name SQLQueryToolService. As a best practice, you should wrap this call in a function to perform any last minute data preparation or error handling before sending data to your service. It is always wise to validate any parameters before passing them to the Flash Remoting service. As I mentioned earlier, Flash is a client-based application, so validating in Flash (as opposed to validating on the server) reduces the server’s load.

Since an ASP.NET page may not send the result object immediately, especially in the case of distributed applications like an XML web service, Flash Remoting uses callback functions to listen for a result and handle it. Each callback function has the same name as the ASP.NET page with additional suffixes such as _Result or _Status. The result callback function catches the resulting data from the operation. The status callback function catches any error messages received from the call to the operation. It is always a good idea to use the status callback message to alert the user of an issue and provide an option for how to proceed. In this case, the status callback function will call a prebuilt error message dialog, which displays the error to the user.
On the Query keyframe of the form layer, insert a dynamic text box named txtQuery at x-coordinate 22.5 and y-coordinate 43.5. Make the width 486.4 and the height 206.6. As holder text, type in the SQL query SELECT * FROM customers.
Place the query and reset buttons beneath the text box. (You can use the sample buttons available in the Library panel.) These buttons will call the Flash Remoting service and pass the text box as a parameter. The reset button will clear any code currently stored in the text box.
On the query button, open the ActionScript panel and add an onRelease callback function.
```
on( release )
{	
	SQLQueryToolAction( txtQuery.Text );
}
```
This function calls the Flash Remoting service when a user presses the query button and passes the text box string as a parameter. The additional error checking you placed in the Flash Remoting function also helps prevent invalid data from being sent to the ASP.NET page.
Place a similar block of ActionScript on the reset button.
```
on( release )
{
	txtQuery.text = "";
}
```
This callback function removes any text currently residing in the text box on reset.
Reopen the ActionScript in frame one of the file to examine it more closely.

Note that when users interface with the query form, they can enter SQL statements directly into the text box. Pressing the submit button passes the query, if valid, to the ASP.NET service. In response, the ASP.NET service binds the result database data and passes it to the _Result callback function. This callback function will grab the database results as an ActionScript RecordSet object and bind that data to the DataGrid UI component.
```
function SQLQueryToolAction_Result( result )
{
	RecordSetResults = result;
	gotoAndStop( "Result" );
}
```
Before you bind data to the DataGrid component, you store the results in a variable, RecordSetResults, and move the playhead to the Result key frame.
In the Result keyframe, drag the DataGrid component from the Components panel (Control-F7) and set the x-coordinate to 13.6 and the y-coordinate to 35. Also set the width to 522.4, the height to 224, and name the dataGrid dgResults.
Also in the Result key frame, place the following code to bind the RecordSetResults variable to the DataGrid component.
```
dgResults.setDataProvider( RecordSetResults );
stop();
```

That's it—run the Flash application and try a few SQL queries. You should also tweak your Flash application buttons so the reset button sends the user back to the query form.

To prevent users from deleting tables or records from the Northwind database, I added a quick ActionScript statement that prevents code with delete in the string from running. (You may have noticed this in the finished file.) To add this code, make the following changes to the SQLQueryToolAction function.

function SQLQueryToolAction( queryString )
{
	if( ( queryString == null ) || ( queryString == "" ) )
	{
		DisplayErrorMessage( "You must provide a SQL query." );
		return;
	}
	
	if( queryString.toLowerCase().indexOf( "delete" ) != -1 )
	{
		DisplayErrorMessage( "You can not delete records." );
		return;		
	}
	
	SQLQueryToolService.SQLQueryToolAction( queryString );
}

If you'd like, you can view a working model of the SQL Query Tool on my website.

A–C

D–H

I–Z

Developer services

Project centers

Community resources

Integration

Product documentation

Downloads

Samples

Training and books

Newsletters

Blogs

Archives

ADC program

Additional resources

Table of Contents

ADO.NET Connectivity with Macromedia Flash Remoting MX

Manipulating a Database with Flash MX Professional 2004