So you gave up on trying to figure out how you can directly connect Flex to your database like with any server side language and decided to Google the answer. Well by now you probably figured out that your building a “client” application not a server side application.

So whats the best approach in achieving what you want? I have no clue, I do however have a solution that I put together using ASP.NET C#, Web Service, some duck tape and lot of Tylenol which makes getting at and querying your data just as easy as using SQL Manager which is actually a hell of a lot easier than querying your data with any server side code.

How it works - Exactly as that diagram above. Flex sends a request with the SQL Query string to the .NET Web Service that request runs the query against the database and returns the data in XML format to the web service and then the web service returns that XML data to Flex and then you do whatever you want with that XML data.

Lets get started already! – OK first things first lets build the .NET Web Service used to communicate with your database. I’m assuming you already have a database setup and Visual Studio (Express) installed.

You can download the latest version of Visual Studio Express for free here http://www.microsoft.com/Express/

ASP.NET C# Web Service - Open up Visual Studio and create a new Web Service project.

Lets build the database connection string in the web.config file so we dont have to change it in the code should the database connection string ever change.

Edit your web.config and insert the following code inside of the <configuration> tags

<appSettings>
<add key="yourConnectionName" value="Data Source=192.168.0.1,1433;Initial Catalog=DatabaseName;User ID=username;Password='password';" />
</appSettings>

Now in your source code file for your web service (”App_Code\yourfilename.cs”) create the following varible to get at the connection string we just built in the web.config

//Connection Strings pulled from web.config
string dbConnString= ConfigurationSettings.AppSettings["ConnectionName"];

Now lets create the function that talks to your database and gets the data in XML format

[WebMethod]
public XmlDocument getDataXML(string strQuery, string strRootNode, string strItemNode)
{
// Create vars out of connection string and query string
string dbConn = dbConnString;
string dbQuery = strQuery;

// Connect to the database and run the query
SqlConnection conn = new SqlConnection(dbConn);
SqlCommand cmd = new SqlCommand(dbQuery, conn);
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
ds.DataSetName = strRootNode;
da.SelectCommand = cmd;
da.Fill(ds, strItemNode);

// Return the data as XML
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(ds.GetXml());
return xmlDoc;
}

OK now that you have built your web service function the next step is to test it out. To do this run your project, click the “getDataXML” link, fill out the form and click the Invoke button. You should get a page with your data in XML format. If this is not the case then you most likely did something wrong (or my instructions are wrong). If you cant figure out what it is then post your problem/error as a comment on this form and I will try to help you out when I have a chance.

If it did work for you then lets move on to the next step –>

Ok now for the fun stuff. Flex makes it really easy to communicate with your web service so easy that it makes coding even more fun and rewarding (yes its possible that I may be a nerd).

Here is the code that I use to call my web service function and return the data in XML. You may notice that I set my resultFormat to e4x this will make filtering the data a lot easier.

When filtering your data you will have several options, I find pulling the data in once using your Web Service function and using e4x to filter the data the best option because it is less stress on the server.

Sorry for the formatting still getting used to this WordPress plugin.

<mx:WebService id="yourWebServiceID" wsdl="http://www.domain.com/Service.asmx?WSDL" showBusyCursor="true">
<mx:operation name="getDataXML" resultFormat="e4x">
<mx:request>
<strQuery>
SELECT * FROM yourTable
</strQuery>
<strRootNode>root</strRootNode>
<strItemNode>item</strItemNode>
</mx:request>
</mx:operation>
</mx:WebService>

Lets run the Web Service to get your data once the Flex Application has finished loading.

Add the following to your <mx:Application> tag:

creationComplete="init();"

Create the following function in your script section:

//Run once Flex has loaded
private function init():void
{
      //Send Get Request to .NET Web Service
      yourWebServiceID.getDataXML.send();
}

Now that we have are XML data lets display it using say oh a Flex DataGrid

<mx:DataGrid width="100%" height="100%" dataProvider="{yourWebServiceID.getDataXML.lastResult.root.item}"/>

That’s it! Yea OK it does seem like a lot of work but trust me its really not. If you already have a good understanding of Flex this should be cake for you.

If you run into any problems, have any ideas on improving the code and making it easier please drop a comment your contact information will not be shared with anyone unless you want it to (that is if I setup this blog correctly)

I’m looking for writers to share and give back to the online community so if you would like to be a content contributor please send me an email.

You can download the sample files here: Flex Web Service Sample (592) … When I have some more time I will include all of the project files along with a working example online for you to play with. You will be able to right-click on the source app and view the actual source.

I have noticed some problems being caused by the component I have been using to display the source code on this page so if you get errors in your Flex App when copying and pasting that is why. I am in the process of switching out the component to resolve this issue.