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)
- Open Visual Studio 2008. Click File –> New Website. Select ASP.NET Web Service as project template and rename webservice(.asmx) file as Contacts.asmx.
- For this example I have created a new database named “Sample” with a table called “Contacts”. The scheme of the table is shown below.
- 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.
- <connectionStrings>
- <add name="SampleDBConn" connectionString="Data Source=SERVER_NAME\SQLEXPRESS;Initial Catalog=SAMPLE;Integrated Security=True" providerName="System.Data.SqlClient" />
- </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.
- using System;
- using System.Configuration;
- using System.Data;
- using System.Data.SqlClient;
- /// <summary>
- /// Helper class to provide access to database
- /// </summary>
- public class DBConn
- {
- private string connString;
- private SqlConnection sqlConn;
- public DBConn()
- {
- try
- {
- connString = ConfigurationManager.ConnectionStrings["SampleDBConn"].ConnectionString;
- }
- catch (Exception ex)
- {
- throw new ConfigurationErrorsException(
- "Required database isn't configured. Please add connection info to Web.config for 'AdventureWorks' database.", ex);
- }
- sqlConn = new SqlConnection(connString);
- }
- /// <summary>
- /// Open Connection for SQL transactions
- /// </summary>
- public void OpenSqlConnection()
- {
- if(sqlConn.State == ConnectionState.Closed)
- sqlConn.Open();
- }
- /// <summary>
- /// Close connection after transactions
- /// </summary>
- public void CloseSqlConnection()
- {
- if (sqlConn.State == ConnectionState.Open)
- sqlConn.Close();
- }
- /// <summary>
- /// Creates new SqlCommand for given query string
- /// </summary>
- /// <param name="queryString">Query String</param>
- /// <returns>SqlCommand</returns>
- public SqlCommand CreateSQLCommand(string queryString)
- {
- return new SqlCommand(queryString, sqlConn);
- }
- /// <summary>
- /// Creates and adds parameter to SqlCommand
- /// </summary>
- /// <param name="command">SqlCommand</param>
- /// <param name="parameterName">Paramater Name</param>
- /// <param name="value">Paramater Value</param>
- public void CreateParameter(SqlCommand command, string parameterName, object value)
- {
- SqlParameter param = command.CreateParameter();
- param.ParameterName = parameterName;
- param.Value = value;
- command.Parameters.Add(param);
- }
- /// <summary>
- /// Creates a new dataset and fills it with data for given query
- /// </summary>
- /// <param name="selectCmd">Select Query</param>
- /// <param name="tabName">TableName</param>
- /// <returns>DataSet</returns>
- public DataSet ReturnDataSet(string selectCmd, string tabName)
- {
- sqlConn.Open();
- DataSet dSet = new DataSet();
- SqlDataAdapter sqlDBAdapter = new SqlDataAdapter(selectCmd, sqlConn);
- sqlDBAdapter.Fill(dSet, tabName);
- sqlConn.Close();
- return dSet;
- }
- }
- 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.
- using System;
- using System.Data;
- using System.Data.SqlClient;
- using System.Text;
- using System.Web.Services;
- /// <summary>
- /// Summary description for Contact
- /// </summary>
- [WebService(Namespace = "http://tempuri.org/")]
- [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
- public class Contacts : WebService
- {
- private DBConn dbConnection;
- private readonly string[] FieldNames = {"CONTACTID, FIRST_NAME, LAST_NAME, CITY, STATE"};
- public Contacts()
- {
- dbConnection = new DBConn();
- }
- /// <summary>
- /// Get data as DataSet
- /// </summary>
- /// <returns>DataSet</returns>
- [WebMethod]
- public DataSet GetDataSet()
- {
- try
- {
- StringBuilder sql = new StringBuilder(128);
- sql.Append("SELECT ");
- sql.Append(String.Join(",", FieldNames));
- sql.Append(" FROM CONTACTS");
- return dbConnection.ReturnDataSet(sql.ToString(), "Contacts");
- }
- catch (Exception ex)
- {
- throw new Exception("Data Rerieval Error", ex);
- }
- }
- /// <summary>
- /// Inserts a new record
- /// </summary>
- [WebMethod]
- public void InsertRecord(string First_Name, string Last_Name, string City, string State)
- {
- try
- {
- string qString = @"INSERT INTO CONTACTS (FIRST_NAME, LAST_NAME, CITY, STATE) VALUES (@FIRST_NAME, @LAST_NAME, @CITY, @STATE)";
- dbConnection.OpenSqlConnection();
- SqlCommand sqlCmd = dbConnection.CreateSQLCommand(qString);
- dbConnection.CreateParameter(sqlCmd, "@FIRST_NAME", First_Name);
- dbConnection.CreateParameter(sqlCmd, "@LAST_NAME", Last_Name);
- dbConnection.CreateParameter(sqlCmd, "@CITY", City);
- dbConnection.CreateParameter(sqlCmd, "@STATE", State);
- sqlCmd.ExecuteNonQuery();
- dbConnection.CloseSqlConnection();
- }
- catch (Exception ex)
- {
- throw new Exception("Data Rerieval Error", ex);
- }
- }
- /// <summary>
- /// Updates the database record
- /// </summary>
- [WebMethod]
- public void UpdateRecord(double ContactID, string First_Name, string Last_Name, string City, string State)
- {
- try
- {
- string qString = @"UPDATE CONTACTS SET FIRST_NAME = @FIRST_NAME, LAST_NAME = @LAST_NAME, CITY = @CITY, STATE = @STATE WHERE CONTACTID = @CONTACTID ";
- dbConnection.OpenSqlConnection();
- SqlCommand sqlCmd = dbConnection.CreateSQLCommand(qString);
- dbConnection.CreateParameter(sqlCmd, "@CONTACTID", ContactID);
- dbConnection.CreateParameter(sqlCmd, "@FIRST_NAME", First_Name);
- dbConnection.CreateParameter(sqlCmd, "@LAST_NAME", Last_Name);
- dbConnection.CreateParameter(sqlCmd, "@CITY", City);
- dbConnection.CreateParameter(sqlCmd, "@STATE", State);
- sqlCmd.ExecuteNonQuery();
- dbConnection.CloseSqlConnection();
- }
- catch (Exception ex)
- {
- throw new Exception("Data Rerieval Error", ex);
- }
- }
- /// <summary>
- /// Delete records from database
- /// </summary>
- [WebMethod]
- public void DeleteRecord(double ContactID)
- {
- try
- {
- string qString = @"DELETE FROM CONTACTS WHERE CONTACTID = @CONTACTID ";
- dbConnection.OpenSqlConnection();
- SqlCommand sqlCmd = dbConnection.CreateSQLCommand(qString);
- dbConnection.CreateParameter(sqlCmd, "@CONTACTID", ContactID);
- sqlCmd.ExecuteNonQuery();
- dbConnection.CloseSqlConnection();
- }
- catch (Exception ex)
- {
- throw new Exception("Data Rerieval Error", ex);
- }
- }
- }
- 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.
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,
ReplyDeletePlease... 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)'
Hello Haneef,
ReplyDeleteI 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
Hello sir,
ReplyDeleteI’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.
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.
ReplyDeletefaultCode:Client.NoSuchPort faultString:'Couldn't find a matching port (service = 'null', port = 'null')' faultDetail:'null'
Hello Thomas,
ReplyDeleteI am guessing that you are Visual Studio test server, not IIS. VS test server sets its own port every time it runs.
Thanks,
Amrit
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.
ReplyDeleteThank you!
Hi,
ReplyDeleteany 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
Is there any tool present to Bind Flex components directly to the database???
ReplyDeletehow can remove the error from above coding????
ReplyDeleteSandhya, 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.
ReplyDeleteAlso i have no idea what error you are getting on this.
Thanks,
Amrit
Well, dear this is such awesome information you shared with us because I am searching this for a while and now got it here.
ReplyDeleteASP.NET Development Company | Offshore Software Development Company
Please checkout the updated blog at http://chbsinc.blogspot.com/2013/04/connecting-client-side-scripts-to-sql.html
DeleteThanks,
Amrit
Please checkout the updated blog at http://chbsinc.blogspot.com/2013/04/connecting-client-side-scripts-to-sql.html
ReplyDeleteThanks,
Amrit
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!
ReplyDeleteHadoop online training
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.
DeleteAmrit
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.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi, You have given valuable information. It is a great class for those who want to learn something about connecting Adobe Flex to SQL Server and resolution for that.
ReplyDeleteThank you!...
Vicky from Way2Smile - Trusted Digital Transformation Company in Dubai
Excellent blog post. Thanks for sharing this nice article.
ReplyDeleteAre you looking for Mobile App Development Company in UK
? Reach Way2Smile Solutions UK.
Nice Blog post. Thanks for sharing this one.
ReplyDeleteIf anyone looking for a secure app development company, Reach Way2Smile Solutions Mobile App Development Company in Chennai.
Thanks for sharing this useful information. I was really impressed. thanks for sharing like this.
ReplyDeleteBulk SMS Service in Chennai
bulk sms service chennai
sms service provider in chennai
bulk sms price in chennai
bulk sms provider chennai
bulk sms service provider in Chennai
best bulk sms service provider in chennai