Tuesday, October 13, 2009

Connecting Adobe Flex to SQL Server - Part 1 (.Net WebService)

This blog is in transition to be moved to new URL. The new and updated version of this post is located at REST Web Services With ASP.Net Web API

Flex is really one of the cool coding languages with lot of flexibility and scope for development. It compiles into SWF file as an end product, which is a client side technology. Flex cannot connect to SQL server directly like ASP.Net or any other server side languages. It uses .net web services to connect to SQL server.
There can be different ways to connect Flex to SQL Server. The method described in this post requires following software:
  • Visual Studio 2008 any version or Visual Web Developer Express Edition
  • Flex Builder 3
  • SQL Server 2005 (Full Version or Express Edition)
  • IIS (Internet Information Services)
To get going
  • Open Visual Studio 2008. Click File –> New Website. Select ASP.NET Web Service as project template and rename webservice(.asmx) file as Contacts.asmx.newwebservice
  • For this example I have created a new database named “Sample” with a table called “Contacts”. The scheme of the table is shown below.image
  • After the new web service is created in VS, open web.config and add connection string for new SQL Sever database between appSettings and System.Web.
    1. <connectionStrings>
    2.     <add name="SampleDBConn" connectionString="Data Source=SERVER_NAME\SQLEXPRESS;Initial Catalog=SAMPLE;Integrated Security=True" providerName="System.Data.SqlClient" />
    3.   </connectionStrings>
  • Create a new database helper class in App_Code directory named “DBConn.cs". It contains various functions for creating  SqlDbConnection, opening & closing the connection, creating and filling a new DataSet from a given select query etc.  
    1. using System;
    2. using System.Configuration;
    3. using System.Data;
    4. using System.Data.SqlClient;
    5.  
    6. /// <summary>
    7. /// Helper class to provide access to database
    8. /// </summary>
    9. public class DBConn
    10. {
    11.     private string connString;
    12.     private SqlConnection sqlConn;
    13.  
    14.     public DBConn()
    15.     {
    16.         try
    17.         {
    18.             connString = ConfigurationManager.ConnectionStrings["SampleDBConn"].ConnectionString;
    19.         }
    20.         catch (Exception ex)
    21.         {
    22.             throw new ConfigurationErrorsException(
    23.                 "Required database isn't configured. Please add connection info to Web.config for 'AdventureWorks' database.", ex);
    24.         }
    25.         sqlConn = new SqlConnection(connString);
    26.     }
    27.  
    28.     /// <summary>
    29.     /// Open Connection for SQL transactions
    30.     /// </summary>
    31.     public void OpenSqlConnection()
    32.     {
    33.         if(sqlConn.State == ConnectionState.Closed)
    34.             sqlConn.Open();
    35.     }
    36.  
    37.     /// <summary>
    38.     /// Close connection after transactions
    39.     /// </summary>
    40.     public void CloseSqlConnection()
    41.     {
    42.         if (sqlConn.State == ConnectionState.Open)
    43.             sqlConn.Close();
    44.     }
    45.  
    46.     /// <summary>
    47.     /// Creates new SqlCommand for given query string
    48.     /// </summary>
    49.     /// <param name="queryString">Query String</param>
    50.     /// <returns>SqlCommand</returns>
    51.     public SqlCommand CreateSQLCommand(string queryString)
    52.     {
    53.         return new SqlCommand(queryString, sqlConn);
    54.     }
    55.  
    56.     /// <summary>
    57.     /// Creates and adds parameter to SqlCommand
    58.     /// </summary>
    59.     /// <param name="command">SqlCommand</param>
    60.     /// <param name="parameterName">Paramater Name</param>
    61.     /// <param name="value">Paramater Value</param>
    62.     public void CreateParameter(SqlCommand command, string parameterName, object value)
    63.     {
    64.         SqlParameter param = command.CreateParameter();
    65.         param.ParameterName = parameterName;
    66.         param.Value = value;
    67.         command.Parameters.Add(param);
    68.     }
    69.  
    70.     /// <summary>
    71.     /// Creates a new dataset and fills it with data for given query
    72.     /// </summary>
    73.     /// <param name="selectCmd">Select Query</param>
    74.     /// <param name="tabName">TableName</param>
    75.     /// <returns>DataSet</returns>
    76.     public DataSet ReturnDataSet(string selectCmd, string tabName)
    77.     {
    78.         sqlConn.Open();
    79.         DataSet dSet = new DataSet();
    80.         SqlDataAdapter sqlDBAdapter = new SqlDataAdapter(selectCmd, sqlConn);
    81.         sqlDBAdapter.Fill(dSet, tabName);
    82.         sqlConn.Close();
    83.         return dSet;
    84.     }
    85. }
  • In the code-behind webservice (asmx) file, create different WebMethods for retrieving, inserting, updating and deleting the data from the database. I have created a web method for data retrieval returning the C# DataSet. All these functions are using DBConn.cs, the database helper class created in previous step. The table used in this example is Conatcts table created in previous steps.
    1. using System;
    2. using System.Data;
    3. using System.Data.SqlClient;
    4. using System.Text;
    5. using System.Web.Services;
    6.  
    7. /// <summary>
    8. /// Summary description for Contact
    9. /// </summary>
    10. [WebService(Namespace = "http://tempuri.org/")]
    11. [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    12. public class Contacts : WebService
    13. {
    14.     private DBConn dbConnection;
    15.     private readonly string[] FieldNames = {"CONTACTID, FIRST_NAME, LAST_NAME, CITY, STATE"};
    16.     public Contacts()
    17.     {
    18.         dbConnection = new DBConn();
    19.     }
    20.  
    21.     /// <summary>
    22.     /// Get data as DataSet
    23.     /// </summary>
    24.     /// <returns>DataSet</returns>
    25.     [WebMethod]
    26.     public DataSet GetDataSet()
    27.     {
    28.         try
    29.         {
    30.             StringBuilder sql = new StringBuilder(128);
    31.  
    32.             sql.Append("SELECT ");
    33.             sql.Append(String.Join(",", FieldNames));
    34.             sql.Append(" FROM CONTACTS");
    35.             return dbConnection.ReturnDataSet(sql.ToString(), "Contacts");
    36.         }
    37.         catch (Exception ex)
    38.         {
    39.             throw new Exception("Data Rerieval Error", ex);
    40.         }
    41.     }
    42.  
    43.     /// <summary>
    44.     /// Inserts a new record
    45.     /// </summary>
    46.     [WebMethod]
    47.     public void InsertRecord(string First_Name, string Last_Name, string City, string State)
    48.     {
    49.         try
    50.         {
    51.             string qString = @"INSERT INTO CONTACTS (FIRST_NAME, LAST_NAME, CITY, STATE) VALUES (@FIRST_NAME, @LAST_NAME, @CITY, @STATE)";
    52.             dbConnection.OpenSqlConnection();
    53.             SqlCommand sqlCmd = dbConnection.CreateSQLCommand(qString);
    54.             dbConnection.CreateParameter(sqlCmd, "@FIRST_NAME", First_Name);
    55.             dbConnection.CreateParameter(sqlCmd, "@LAST_NAME", Last_Name);
    56.             dbConnection.CreateParameter(sqlCmd, "@CITY", City);
    57.             dbConnection.CreateParameter(sqlCmd, "@STATE", State);
    58.             sqlCmd.ExecuteNonQuery();
    59.             dbConnection.CloseSqlConnection();
    60.         }
    61.         catch (Exception ex)
    62.         {
    63.             throw new Exception("Data Rerieval Error", ex);
    64.         }
    65.     }
    66.  
    67.     /// <summary>
    68.     /// Updates the database record
    69.     /// </summary>
    70.     [WebMethod]
    71.     public void UpdateRecord(double ContactID, string First_Name, string Last_Name, string City, string State)
    72.     {
    73.         try
    74.         {
    75.             string qString = @"UPDATE CONTACTS SET FIRST_NAME = @FIRST_NAME, LAST_NAME = @LAST_NAME, CITY = @CITY, STATE = @STATE WHERE CONTACTID = @CONTACTID ";
    76.             dbConnection.OpenSqlConnection();
    77.             SqlCommand sqlCmd = dbConnection.CreateSQLCommand(qString);
    78.             dbConnection.CreateParameter(sqlCmd, "@CONTACTID", ContactID);
    79.             dbConnection.CreateParameter(sqlCmd, "@FIRST_NAME", First_Name);
    80.             dbConnection.CreateParameter(sqlCmd, "@LAST_NAME", Last_Name);
    81.             dbConnection.CreateParameter(sqlCmd, "@CITY", City);
    82.             dbConnection.CreateParameter(sqlCmd, "@STATE", State);
    83.             sqlCmd.ExecuteNonQuery();
    84.             dbConnection.CloseSqlConnection();
    85.         }
    86.         catch (Exception ex)
    87.         {
    88.             throw new Exception("Data Rerieval Error", ex);
    89.         }
    90.     }
    91.  
    92.     /// <summary>
    93.     /// Delete records from database
    94.     /// </summary>
    95.     [WebMethod]
    96.     public void DeleteRecord(double ContactID)
    97.     {
    98.         try
    99.         {
    100.             string qString = @"DELETE FROM    CONTACTS WHERE CONTACTID = @CONTACTID ";
    101.             dbConnection.OpenSqlConnection();
    102.             SqlCommand sqlCmd = dbConnection.CreateSQLCommand(qString);
    103.             dbConnection.CreateParameter(sqlCmd, "@CONTACTID", ContactID);
    104.             sqlCmd.ExecuteNonQuery();
    105.             dbConnection.CloseSqlConnection();
    106.         }
    107.         catch (Exception ex)
    108.         {
    109.             throw new Exception("Data Rerieval Error", ex);
    110.         }
    111.     }
    112. }
  • Set the Contacts.asmx file as start up page, and try invoking various webmethods to make sure that the new webservice is successfully running and retrieving the data.
  • This finishes the .net webservice part of the connection process.
Adobe Flex code is covered in Connecting Adobe Flex to SQL Server - Part 2 (Adobe flex)

16 comments:

  1. Dear sir, thanks for showing the way to sqlserver database, After completing all as u said, i hv stuck in loading values in arraycollection object, i'am trying to load single column of a table & it displays the following error,

    Please... Help me as this is very critical part of my Application. I would be very thankful to u for giving me some time. Thanks in advance.

    faultCode:Server.Error.Request faultString:'HTTP request error' faultDetail:'Unable to load WSDL. If Currently online, please verify the URL and/or format of WSDS (http://localhost/WebService/Service.asmx?wsdl)'

    ReplyDelete
  2. Hello Haneef,
    I would love to help you but I am not sure yet where you are stuck. Please verify that this link http://localhost/WebService/Service.asmx?wsdl link is working in your browser. If it is not, your webservice is not working. Please let me know.
    Thanks,
    Amrit

    ReplyDelete
  3. Hello sir,
    I’m not understanding what is this & how does it work for may application
    arrcoll = e.result.Tables.waiterlocations.Rows;

    i want the list of values to be stored in arraycollection from database,
    Error #1009: Cannot access a property or method of a null object reference, is displayed. but its not displaying values in arrcoll, instead its displaying list of [object object] [object object]...in arrcoll. My xml file coming from webservice has huge data with many nested tags(NewDataSet, waiterlocations, locations are element is xml file). i'm calling GetDataSet function from webservice's opertion. Please help, thanx in adv.

    ReplyDelete
  4. Hi Amrit - thank you for your great instructions. I am getting close to getting this working, but I still get the error listed below. When I opened the service link in my browser, it worked, so I believe my webservice is working. Any help would be appreciated.

    faultCode:Client.NoSuchPort faultString:'Couldn't find a matching port (service = 'null', port = 'null')' faultDetail:'null'

    ReplyDelete
  5. Hello Thomas,
    I am guessing that you are Visual Studio test server, not IIS. VS test server sets its own port every time it runs.
    Thanks,
    Amrit

    ReplyDelete
  6. Good day! I was hoping you could send me the source codes if that was okay, I have just begun learning flex and ASP.NET so this is all very new to me.

    Thank you!

    ReplyDelete
  7. Hi,

    any idea how the ASP script sould be to upload a text file to the SQL Server database?

    I have a FileReference.upload and to test the upload, the following code but I get the i/o error 2038 all the time:
    _
    Public Function importFile(ByVal f As Byte(), ByVal fileName As String) As String
    Try
    Dim ms As New MemoryStream(f)
    Dim fs As New FileStream(System.Web.Hosting.HostingEnvironment.MapPath("C:\ArcIMS\Website\CoreSet\temp") & fileName, FileMode.Create)
    ms.WriteTo(fs)
    ms.Close()
    fs.Close()
    fs.Dispose()
    Return "ok"

    Catch ex As Exception
    Return ex.Message.ToString()
    End Try

    End Function

    ReplyDelete
  8. Is there any tool present to Bind Flex components directly to the database???

    ReplyDelete
  9. how can remove the error from above coding????

    ReplyDelete
  10. Sandhya, You can definately use this post as baseline for connecting SQL server to Flex. ASP.NEWT 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 have no idea what error you are getting on this.

    Thanks,
    Amrit

    ReplyDelete
  11. Well, dear this is such awesome information you shared with us because I am searching this for a while and now got it here.
    ASP.NET Development Company | Offshore Software Development Company

    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
  12. Please checkout the updated blog at http://chbsinc.blogspot.com/2013/04/connecting-client-side-scripts-to-sql.html

    Thanks,
    Amrit

    ReplyDelete
  13. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!
    Hadoop online training

    ReplyDelete
    Replies
    1. Thanks for your feedback. I appreciate you guys taking your time and reading it. Hopefully it was helpful to you. Let me know I can help more.
      Amrit

      Delete
  14. Microsoft ASP.NET is a more valuable forum for software development and applications. .NET based applications are more secure and today is used in multiple businesses.

    ReplyDelete