[Flex 4 and .Net] Retrieving tables from SQL database

Question

Hi everyone,

As the title says, I want to retrieve tables of data from a SQL database, using Flex 4 and .Net WebService.

I'm new to both Flex and DotNet. Please tell me a proper way to do it.

This is what I've done so far:

---

Retrieving an array of string: (this works)

.Net:

[WebMethod]
public String[] getTestArray()
{
    String[] arStr = { "AAA", "BBB", "CCC", "DDD" };
    return arStr;
}

Flex 4:

<?xml version="1.0" encoding="utf-8"?>
<s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" 
               xmlns:s="library://ns.adobe.com/flex/spark" 
               xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600">

    <fx:Script>
        <![CDATA[

            import mx.collections.ArrayCollection;
            import mx.controls.Alert;
            import mx.rpc.events.ResultEvent;

            [Bindable]
            private var ac:ArrayCollection = new ArrayCollection();

            protected function btn_clickHandler(event:MouseEvent):void
            {
                ws.getTestArray();
            }           

            protected function ws_resultHandler(event:ResultEvent):void
            {
                ac = event.result as ArrayCollection;
                Alert.show(ac.toString());              
            }

        ]]>
    </fx:Script>

    <fx:Declarations>
        <s:WebService id="ws" wsdl="http://localhost:50582/Service1.asmx?WSDL" result="ws_resultHandler(event)"/>
    </fx:Declarations>

    <s:Button x="10" y="30" label="Button" id="btn" click="btn_clickHandler(event)"/>

</s:Application>

---

Retrieving a DataTable: (this does not work)

DotNet:

[WebMethod]
public DataTable getUsers()
{           
    DataTable dt = new DataTable("Users");
    SqlConnection conn = new SqlConnection("server = 192.168.1.50; database = MyDatabase; user id = sa; password = 1234; integrated security = false");         
    SqlDataAdapter da = new SqlDataAdapter("select vFName, vLName, vEmail from Users", conn);
    da.Fill(dt);
    return dt;
}

Flex 4:

<?xml version="1.0" encoding="utf-8"?>
<s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" 
               xmlns:s="library://ns.adobe.com/flex/spark" 
               xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600">

    <fx:Script>
        <![CDATA[

            import mx.collections.ArrayCollection;
            import mx.controls.Alert;
            import mx.rpc.events.ResultEvent;

            [Bindable]
            private var ac:ArrayCollection = new ArrayCollection();

            protected function btn_clickHandler(event:MouseEvent):void
            {
                ws.getUsers();
            }           

            protected function ws_resultHandler(event:ResultEvent):void
            {
                ac = event.result as ArrayCollection;
                Alert.show(ac.toString());
            }

        ]]>
    </fx:Script>

    <fx:Declarations>
        <s:WebService id="ws" wsdl="http://localhost:50582/Service1.asmx?WSDL" result="ws_resultHandler(event)"/>
    </fx:Declarations>

    <s:Button x="10" y="30" label="Button" id="btn" click="btn_clickHandler(event)"/>

</s:Application>

Answer 1

I am also new to Flex and am having the same problem as yourself. The only thing that I have done differently to you is to fill a dataset and not a datatable with the dataadapter in my webservice. Using the FlashBuilder 4 Network monitor I can see that my data has actually arrived in the Flex application so the Webservice is working OK but my problem is that I cannot get my dropdownlist to fill. As a reference, I have also sent a string to my Flex application from the Webservice which, like your 'check', also works. However I do see a difference between the 2 results in the Network monitor, having to drill very much deeper before I see the data from the dataset. Trouble is I am too new to work out if this means that I have give a longer name to my dropdownlist fields to pick up the data but this will be my task tomorrow. Perhaps this is an idea for you to try.

Answer 2

I found the answer to this after some Googling so I'm posting it here to spread the love.

First create a variable to hold the result

[Bindable]
private var DotNetData:ArrayCollection;

Then in your handler

protected function ws_resultHandler(event:ResultEvent):void
{
    DotNetData = event.result.Tables.ListData.Rows;
    resultsDg.dataProvider = DotNetData;
}

In this example "resultsDg" is just a datagrid. "ListData" refers to the datatable name taken from the "msdata:MainDataTable" element in the response XML.

Inspiration from here: http://ranjitfx.wordpress.com/net-flex/