Flex and SQL

[NasifAlshaier]

Hello,

I am still having trouble understanding how to read a SQL data into my flex application... I would appreaciate anyone help and sample code.
Thanks

[DavidGalluzzo]

nasif,
you can work with SQL and other databases in flex by using a web service. You will have two sets of code. In my case I use C# .net to create a web service that returns a datatable. Once you get the results, you can assign them to an Array collection in Flex.

C# Code

/// <summary>
        ///  get Owners from Database
        /// </summary>
        /// <returns>DataTable</returns>
        [WebMethod]
        public DataTable getOwners(string search)
        {

            try
            {

                // create sql connection
                SqlConnection DbConnect = new SqlConnection(conString);
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.Connection = DbConnect;
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.CommandText = "sp_getOwnersSearch";

                //run query to fill dataset
                ds = new DataSet();
                da = new SqlDataAdapter(sqlCmd);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@Search", search); // add @User parameter

                da.Fill(ds, "Table");

                return ds.Tables["Table"];
            }
            catch (Exception ex)
            {
                throw CreateSafeException(ex);
            }
        }


Flex Code

import mx.rpc.soap.WebService;
import mx.controls.Alert;
import mx.rpc.events.FaultEvent;
import mx.rpc.events.ResultEvent;

private var gateway:WebService = new WebService();
public var ownersArr:ArrayCollection = new ArrayCollection(); // Holds All Owners

// Call .net web service
public function searchOwners(search:String):void
{
CursorManager.setBusyCursor(); // Set Cursor
ownersArr.removeAll();
gateway.wsdl =  "http://localhost/FlexAppBuilder_WS/Service1.asmx?wsdl"; // Path to Web service
gateway.loadWSDL();
gateway.getOwners.addEventListener(ResultEvent.RESULT, gotOwnersSearch); // Add Event listner
gateway.addEventListener(FaultEvent.FAULT, faultHandler);
gateway.getOwners(search); // Call getOwners Web Service
}

//gets Owners in system and stores inside array
public function gotOwnersSearch(e:ResultEvent):void
{
if(e.result.Tables.Table.Rows)
{
  var table:* = e.result.Tables.Table.Rows;
  ownersArr = table; // Assign Results to owners Array
}
CursorManager.removeBusyCursor(); // Remove Busy Cursor
}

/ fault handler
public function faultHandler(e:FaultEvent):void
{
CursorManager.removeBusyCursor();
Alert.show(e.fault.faultString);
}


Hope this helps

[JamesOBrien]

Furthermore,

Embedding SQL within your Flex application would open a significant security hole.

As described above, create a web service and make sure the parameters are cleaned before running the resulting SQL. I have previously created similar geoprocessing services using Python and Model builder.

James.