Thursday, October 15, 2009

Connecting Adobe Flex to SQL Server - Part 2 (Adobe Flex)

Create After creating .Net web services, they need to be integrated with Flex to show data.

  • Open IIS (Internet Information services) Manager and add crossdomain.xml to the root of the website. This file is required for flash to access the webserver content.

    1. <?xml version="1.0" ?>
    2. <cross-domain-policy>
    3.   <allow-access-from domain="*"/>
    4.   <site-control permitted-cross-domain-policies="all"/>
    5.   <allow-http-request-headers-from domain="*" headers="*"/>
    6. </cross-domain-policy>

  • Create a new Flex project in Adobe Flex Builder.
  • The main MXML file contains <mx:WebService> component. The layout contains <mx:ViewStack> with two <mx:VBox> containers, one with DataGrid and other with Form for new record insertion. <mx:WebService>contains reference to all its operations using <mx:Operation> tag with result event, handling the result for every operation. All these result handler events are essentially calling the  webservice’s GetDataSet method to refresh the databound ArrayCollection instance. There is an “itemEditEnd” event handler for dataGrid which fires webservice’s UpdateRecord method, when user ends editing for dataGrid item. When user clicks the “Delete” button, Actionscript code behind the MXML file gets the CONTACTID from the row selected by the user and calls the webservice’s DeleteRecord method. When user wants to insert a new record, view changes the selected child to Form for new record insertion, and on user submit, webservice’s InsertRecord method is called. Part of the code is adapted from Flex Builder “Create Application from Database” designer generated code.

    1. <?xml version="1.0" encoding="utf-8"?>
    2. <mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
    3.     layout="vertical" pageTitle="Flex SQL Server Application"
    4.     creationComplete="initApp()">
    5.   <!-- Link to external Actionscript file -->
    6.   <mx:Script source="FlexDBAppScript.as" />
    7.  
    8.   <mx:WebService id="websvc"
    9.         wsdl="http://localhost/WebService/Contacts.asmx?wsdl"
    10.         showBusyCursor="true"
    11.         fault="faultHandler(event)">
    12.     <mx:operation name="GetDataSet"
    13.             result="GetDataSetHandler(event)"/>
    14.     <mx:operation name="InsertRecord"
    15.             result="InsertRecordhandler(event)"/>
    16.     <mx:operation name="DeleteRecord"
    17.             result="DeleteRecordHandler(event)"/>
    18.     <mx:operation name="UpdateRecord"
    19.             result="UpdateRecordHandler(event)"/>
    20.   </mx:WebService>
    21.  
    22.   <mx:ViewStack id="appView" width="100%" height="100%">
    23.     <mx:VBox id="displayGrid">
    24.       <mx:DataGrid id="dataGrid"
    25.                 dataProvider="{dataArray}" rowCount="10"
    26.                 editable="true" resizableColumns="true"
    27.                 itemEditEnd="ItemEditEnd(event)">
    28.         <mx:columns>
    29.           <mx:DataGridColumn headerText="Contact ID" dataField="CONTACTID" editable="false"/>
    30.           <mx:DataGridColumn headerText="First Name" dataField="FIRST_NAME" />
    31.           <mx:DataGridColumn headerText="Last Name" dataField="LAST_NAME" />
    32.           <mx:DataGridColumn headerText="City" dataField="CITY" />
    33.           <mx:DataGridColumn headerText="State" dataField="STATE" />
    34.         </mx:columns>
    35.       </mx:DataGrid>
    36.       <mx:HBox>
    37.         <mx:Button label="Add New" id="btnAddNew" click="GoToInsertForm()"/>
    38.         <mx:Button label="Delete" id="btnDelete" click="DeleteItem()"/>
    39.       </mx:HBox>
    40.     </mx:VBox>
    41.  
    42.     <mx:VBox id="insertForm">
    43.       <mx:Form id="ContactForm" label="Insert New Record">
    44.         <mx:FormHeading label="Insert New Record"/>
    45.         <mx:FormItem label="First Name:" id="First_Name_form">
    46.           <mx:TextInput id="tInp_First_Name" text=""/>
    47.         </mx:FormItem>
    48.         <mx:FormItem label="Last Name:" id="Last_Name_form">
    49.           <mx:TextInput id="tInp_Last_Name" text=""/>
    50.         </mx:FormItem>
    51.         <mx:FormItem label="City:" id="City_form">
    52.           <mx:TextInput id="tInp_City" text=""/>
    53.         </mx:FormItem>
    54.         <mx:FormItem label="State:" id="State_form">
    55.           <mx:TextInput id="tInp_State" text=""/>
    56.         </mx:FormItem>
    57.       </mx:Form>
    58.       <mx:HBox>
    59.         <mx:Button label="Save" id="btnSubmit" click="InsertItem()"/>
    60.         <mx:Button label="Cancel" id="btnCancel" click="GoToDisplayGrid()"/>
    61.       </mx:HBox>
    62.     </mx:VBox>
    63.   </mx:ViewStack>
    64. </mx:Application>
    65.  

  • The actionscript code for MXML file is contained in a separate .as file.

    1. // Actionscript file for FlexDBApp.mxml
    2. // Part of the code is adapted from Flex- Create Application from Database generated code.
    3. import mx.collections.ArrayCollection;
    4. import mx.controls.Alert;
    5. import mx.events.CloseEvent;
    6. import mx.events.DataGridEvent;
    7. import mx.rpc.AsyncToken;
    8. import mx.rpc.events.FaultEvent;
    9. import mx.rpc.events.ResultEvent;
    10.  
    11. [Bindable]
    12. public var dataArray:ArrayCollection = new ArrayCollection();
    13.  
    14. private function initApp():void
    15. {
    16.     // Initialize and load the web service
    17.     websvc.loadWSDL();
    18.     websvc.GetDataSet();
    19. }
    20.  
    21. /**
    22. * Result event handler for websvc GetDataSetHandler call
    23. */
    24. private function GetDataSetHandler(e:ResultEvent):void
    25. {
    26.     // Populate dataArray with dataset table
    27.     dataArray = e.result.Tables.Contacts.Rows;
    28. }
    29.  
    30. /**
    31. * Event handler for Add New Button Click event
    32. */
    33. public function GoToInsertForm():void
    34. {
    35.     // Change te view's selected child to insert form
    36.     appView.selectedChild = insertForm;
    37. }
    38.  
    39. /**
    40. * Event handlet for Save Button Click event
    41. */
    42. public function InsertItem():void
    43. {
    44.     websvc.InsertRecord(tInp_First_Name.text, tInp_Last_Name.text, tInp_City.text, tInp_State.text);
    45.     GoToDisplayGrid();
    46. }
    47.  
    48. /**
    49. * Event handler for Delete Button Click event
    50. */
    51. public function GoToDisplayGrid():void
    52. {
    53.     // Change te view's selected child to display Grid
    54.     appView.selectedChild = displayGrid;
    55. }
    56.  
    57. /**
    58. * Result event handler for websvc InsertRecordhandler call
    59. */
    60. private function InsertRecordhandler(e:ResultEvent):void
    61. {
    62.     // Replopulate the dataArray after transaction
    63.     websvc.GetDataSet();
    64. }
    65.  
    66. /**
    67. * Event handler triggered when the user finishes editing an entry
    68. * triggers an "update" server command
    69. */
    70. private function ItemEditEnd(e:DataGridEvent):void
    71. {
    72.     // Get row number of the edited row
    73.     var dgRowIndex:int = e.rowIndex;
    74.     // Get name of DataField being edited
    75.     var dgFieldName:String = e.dataField;
    76.     // Get Column Index being edited
    77.     var dgColIndex:int = e.columnIndex;
    78.     
    79.     var vo:* = dataArray[dgRowIndex];
    80.     var col:DataGridColumn = dataGrid.columns[dgColIndex];
    81.     var newvalue:String = dataGrid.itemEditorInstance[col.editorDataField];
    82.     vo[dgFieldName] = newvalue;
    83.     websvc.UpdateRecord(vo.CONTACTID, vo.FIRST_NAME, vo.LAST_NAME, vo.CITY, vo.STATE);
    84. }
    85.  
    86. /**
    87. * Result event handler for websvc UpdateRecordHandler call
    88. */
    89. private function UpdateRecordHandler(e:ResultEvent):void
    90. {
    91.     // Replopulate the dataArray after transaction
    92.     websvc.GetDataSet();
    93. }
    94.  
    95. /**
    96. * Event handler for Delete Button Click event
    97. */
    98. public function DeleteItem():void
    99. {
    100.     if (dataGrid.selectedItem)
    101.     {
    102.         Alert.show("Are you sure you want to delete the selected record?",
    103.         "Confirm Delete", 3, this, DeleteClickHandler);
    104.     }
    105. }
    106.  
    107. /**
    108. * Event handler function for the Confirm dialog raises when the Delete Button is pressed.
    109. * If the pressed button was Yes, then the product is deleted.
    110. * @param object event
    111. * @return nothing
    112. */
    113. private function DeleteClickHandler(event:CloseEvent):void
    114. {
    115.     var indexForDelete:int = dataGrid.selectedIndex;
    116.     if (indexForDelete == -1)
    117.     {
    118.         return;
    119.        }
    120.         
    121.     if (event.detail == Alert.YES)
    122.     {
    123.         var vo:* = dataArray[indexForDelete];
    124.         websvc.DeleteRecord(vo.CONTACTID);
    125.     }
    126. }
    127.  
    128. /**
    129. * Result event handler for websvc DeleteRecordHandler call
    130. */
    131. private function DeleteRecordHandler(e:ResultEvent):void
    132. {
    133.     // Replopulate the dataArray after transaction
    134.     websvc.GetDataSet();
    135. }
    136.  
    137. /**
    138. *Fault event handler for websvc WebService call
    139. */
    140. private function faultHandler(event:FaultEvent):void
    141. {
    142.     Alert.show(event.fault.message, "Application Error");
    143. }

30 comments:

  1. I am having trouble with the 1st part is it possible to upload the sorce files for the .Net app

    tks

    email arturo_alcalde@yahoo.com

    ReplyDelete
  2. Hi, Sorry for delayed response. I am having trouble putting attachments in blogspot entries. I can try to email you the source code.
    Thanks,
    Amrit

    ReplyDelete
  3. Excellent tutorial.. it worked fine for me.. Thanks..

    ReplyDelete
  4. Open IIS (Internet Information services) Manager and add crossdomain.xml to the root of the website.

    what does this mean?

    i havnt created a website yet..which website folder are you talking here..
    in iis tree we have

    "websites folder/default web site/all iis configured websites"

    ReplyDelete
  5. I am talking about website root. When you open IIS Manager, there is node called Websites and then go further down to Default Web Site. You can add this crossdomain.xml direclty to the physical directory where your websites reside, for ex C:\Inetpub\wwwroot and it will show up in the root of Default Web Site. \
    Hopefully this helps. Let me know if need more help on this,
    Thanks

    ReplyDelete
  6. What i am getting after reading your post is that i have to paste the crossdomain.xml to the root of my webservice .SAy i created my web serice project by name test..so i have to paste this crossdomain xml in the root of test folder under the default website node in iis..right....?

    ReplyDelete
  7. Really i cant understand the comments on crossdomain.xml
    plz explain

    ReplyDelete
  8. Hi Amrit,
    it seems ur post is excellent,can u please mail me your source code to my mail id
    suri201183@gmail.com
    Thanks in advance.....

    ReplyDelete
  9. Thanks Amrit,
    Great post - saved me lots of time. The only issue I had was that in .Net 2 onwards by default the web.config file will not have the following:








    And if this isnt present then you will get IOErrorEvents from the web service.

    Thanks,
    Andrew

    ReplyDelete
  10. The text should have been:
    <webServices>
    <protocols>
    <add name="HttpGet"/>
    <add name="HttpPost"/>
    </protocols>
    </webServices>

    ReplyDelete
  11. Thanks for the tutorial--works great! Note that I removed the crossdomain.xml file and it still works, though I'm using localhost (I assume it won't work without that file if on different computers).

    I'm now in the process of really diving into what is happening under the hood on the Flex side (since I'm new to Flex--I'm a .Net developer). Plus, I'm migrating the Flex app to my wife's netbook to ensure I can hit the web service without localhost.

    If anyone is stuck just let me know and I'll offer what I've learned in getting this code to work.

    Tools I used for this tutorial: SQL Server 2008 Express, SQL Server 2008 Management Studio Express, Visual Web Developer 2008 Express, IIS 5.1, and 32-bit Windows XP Pro.

    Troy S.

    p.s. See http://www.adobe.com/devnet/articles/crossdomain_policy_file_spec.html for more info about the crossdomain.xml file.

    ReplyDelete
  12. Excellent blog i have ever seen regarding FLEX/ASP.Net/MSSQL Server combination.

    It was really worked for me without single error/problem.

    Thanks a lot for the help...

    ReplyDelete
  13. I just want to add some information on crossdomain.xml file as it was little bit confusing on the blog.

    We can't add any file from IIS Manager, so please follow the steps below to add the xml file.

    To add crossdomain.xml file go to inetpub->wwwroot->webservice(your web application folder)-> create crossdomain.xml file here...

    Run the iisreset command to restart the IIS server...

    ReplyDelete
  14. I am writing this to clear confusions regarding crossdomain.xml. It is required for Flash/FLEX apps to access your sever applications. To make all applications accessible to FLEX (assuming that you might be developing other webservices), open the the folder where website root is pointing to and place crossdomain.xml over there. If you only want to make one webservice available to FLEX application, place it webservice folder. Again thanks everyone for appreciating this blog post on flex.
    Thanks,
    Amrit

    ReplyDelete
  15. Is this tutorial available anywhere in vb?

    ReplyDelete
  16. Hello Melissa,
    I haven't got the code in vb.net. Follow the same steps (except select your coding language as vb in visual studio). Then use this website to convert the code listed in blog to vb.net.
    http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Thanks,
    Amrit

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. Hi Amrit,
    Thanks a lot for sharing. I can do getdataset and inserRecord without any problem, but cannot get the updateRecord right becauase ran into the error "faultCode:EncodingError faultString:'Array of input arguments did not contain a required parameter at position 0' faultDetail:'null'" Any idea would be very appreciated.

    Eve

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. You can definately use this post as baseline for connecting SQL server to Flex. ASP.NET has changed a lot since this post blog was written in 2009. I might do a newer and updated post sometime using .NET REST services.

      Also I am not getting very good idea on the error you are getting on this.

      Thanks,
      Amrit

      Delete
    3. Please checkout the updated blog at http://chbsinc.blogspot.com/2013/04/connecting-client-side-scripts-to-sql.html

      Thanks,
      Amrit

      Delete
  20. Hi Amrit, Thanks for helping us out. But could you please send the project source files on my email id at akshayingoa@gmail.com

    I will be very grateful.

    Akshay

    ReplyDelete
  21. Hello Akshay,
    It has been a long time since I wrote this blog post. I don't even remember if I have all the files by now. Please try to follow directions. Things should work out as directed.
    Also I am planning to write another post following this which will be using latest technologies such as rest web services in .Net. If you are in urgency you can use this code otherwise the new post might be better option to use.

    Thanks,
    Amrit

    ReplyDelete
  22. Hello Amrit,

    I was able to connect the database and the application is working fine. But, before I put it on a live server, I need to secure it using some basic authentication(username and password). I don't know how to achieve this.

    Could you please put some light on this.

    Thanks,

    Akshay

    ReplyDelete
  23. Hello Akshay,
    Use forms authentication with Membership API in IIS. Make the page hosting flash object an ASP.Net page instead of html created by flash builder. Use another login page ( asp.net page) to make user login to the site and redirect him/her to flex page. Also the pages hosting the .net web services will also reside in the same site protected by forms authentication.
    Hope this helps.
    Thanks,
    Amrit

    ReplyDelete
    Replies
    1. Please checkout the updated blog at http://chbsinc.blogspot.com/2013/04/connecting-client-side-scripts-to-sql.html

      Thanks,
      Amrit

      Delete
  24. Hello,
    Very good blog. Can you give me your position about the viability of flex technology (lot of people tell me that is a dead technology...)
    My question:
    I wrote an asp.net 4.0 website, with function onto webservice.asmx in order to take data form the DB without refresh (with javascript).
    Can I use this very file (webservice.asmx) with this functions for my flex project?
    Can you email me the code of your tutorials

    ReplyDelete
    Replies
    1. Hello Raphael,
      Thanks for appreciation. Flex is still not a dead technology. Considering a bigger picture there are still thousands of websites running in Flash. They are not going to switch to Javascript overnight. Definately one should start looking into Javascript with HTML5 comming so strongly. Another thiing is that these are all client side coding technologies. So the same webservice can be used for Flex, or Javascript.
      Anyway I am updating this blog to with newer ASP.NET technology and .NET 4.5 content. Bear with me for few days, I will post the updated the files.
      Thanks,
      Amrit

      Delete
    2. Please checkout the updated blog at http://chbsinc.blogspot.com/2013/04/connecting-client-side-scripts-to-sql.html

      Thanks,
      Amrit

      Delete