It’s been kind of quiet here lately. Since leaving Dimension Data, I’ve been working on a new product called Community Engine. We’re producing a leading edge, semantic web application, with all the bells and whistles of Silverlight, Active Directory and a slew of other technologies which will produce probably the coolest web platform since apache….

We hit beta 1 last week and are currently deep in issue management driving to beta 2.

We’re running a significant web farm at a co-location facility, performing management with System Center Operations Manager 2007, System Center Configuration Manager and a ton of powershell and other features.

It’s a tough job; I’m managing a team of devs as lead architect, a bunch of UI designers and a full featured operations team.

I’ve got a little more time now and will post updates as I get them…

In the next three parts, I am going to describe how to set up a three tier architecture based application using Adobe Flex (including Adobe Air), Microsoft .NET Web Services and SQL Server 2005 to create a three tier architecture based application.

I started learning flex 24 hours ago and this application is my first stab at learning how to use flex and more specifically Adobe Air.

To complete these instructions, you will need:

• A Windows PC (Mine’s running Windows XP Service Pack 2)
• Microsoft Visual Studio 2005 Service Pack 2
• Microsoft SQL 2005 Express, including the SQL Express Management Console
• Adobe Flex Builder 3.0 Beta
• Adobe Air Runtime Beta

Once you’ve got the above things, you’ll need to step through creating and running the application. In this first part, we’re going to describe how to create the database layer.

This application is really simple, it has a database with a single table, and we’re going to create a rich, immersive UI to list the contents, add a new entry and delete an existing entry. To work quickly to create this application, we don’t use every best practice, but we do deliver something cool, which works.

First, Create a new database. In this case, we’re going to call the database hubone.

• Go to SQL Management Studio Express (Start –> All Programs –> Microsoft SQL Server 2005 –> SQL Server Management Studio Express)

The following SQL Script establishes the database for you:

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’ASPNET’)
CREATE USER [ASPNET] FOR LOGIN [.\ASPNET] WITH DEFAULT_SCHEMA=[dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Projects]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[Projects](
    [ProjectID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Projects_ProjectID]  DEFAULT (newid()),
    [Name] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](255) NULL,
    [Version] [nvarchar](50) NULL
) ON [PRIMARY]
END

In the next installment, we’ll create the ASP.NET Web service which our flex application will talk to…

If you haven’t already, please read part 1.

In this section, we’re going to establish the web service we are going to need to manage the flex application.

Open Microsoft Visual Studio.Net and create a new web service application in C#.

Firstly, add a new XML file, called crossdomain.xml. Adobe flex needs to see this file if your front end will be executed from a different domain to the web service layer. The contents of this file is:

<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
  <allow-access-from domain="*" />
</cross-domain-policy>

Save the file.

Next open Service1.asmx

at the top of the file, add the following declarations for namespaces we need in this application:

using System.Collections.ObjectModel;
using System.Data.SqlClient;

Just after the opening brace { after the class service 1, add the following private variables:

private Collection<Project> ProjectList = new Collection<Project>();
private string connectionstring = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Hubone;Data Source=nickbeauce001\sqlexpress";
private SqlConnection conn;

Edit the Data Source in the connection string to be your SQL instance (normally MACHINENAME\sqlexpress), i.e. replace nickbeauce001 with your machine name.

Next, and at the very end of the file, add a new class to hold and manipulate our entries from the database as follows:

public class Project
{
    private string ProjectName;
    private string ProjectDescription;
    private string ProjectVersion;

    public Project(string Name, string Description, string Version)
    {
        ProjectName = Name;
        ProjectDescription = Description;
        ProjectVersion = Version;
    }
    public Project()
    {
    }

    public string Name
    {
        get
        {
            return ProjectName;
        }
        set
        {
            ProjectName = Name;
        }
    }

    public string Description
    {
        get
        {
            return ProjectDescription;
        }
        set
        {
            ProjectDescription = Description;
        }
    }

    public string Version
    {
        get
        {
            return ProjectVersion;
        }
        set
        {
            ProjectVersion = Version;
        }
    }
}

Now we’ll add the core functions into the service 1 class.

First we add the constructors. We add a default constructor (else the class cannot be serialized as a web service). The constructor connects to the database and retrieves the list of rows into a collection of project objects (ProjectList)

public Service1()
        {
            conn = new SqlConnection(connectionstring);
            SqlCommand command = new SqlCommand(
            "SELECT * FROM Projects;",
          conn);
        conn.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                ProjectList.Add(new Project(reader.GetString(1), reader.GetString(2), reader.GetString(3)));

            }
        }
        else
        {
            //Console.WriteLine("No rows found.");
        }
        reader.Close();
        }

Next we add three web methods, one to retrieve the list of projects, one to add a project and one to delete a record

Projects Method: Returns the project list.

[WebMethod]
        public Collection<Project> Projects()
        {
            return ProjectList;
        }

DeleteProject Method: Deletes the specified entry from the database.

[WebMethod]
        public int DeleteProject(string Name, string Description, string Version)
        {
            string querystring = "DELETE FROM PROJECTS WHERE Name=’"
                + Name + "’ AND Description=’"
                + Description + "’ AND Version=’"
                + Version + "’";
            conn = new SqlConnection(connectionstring);
            SqlCommand command = new SqlCommand(querystring, conn);
            conn.Open();
            int retval;
            retval = command.ExecuteNonQuery();
            conn.Close();
            return (retval);
        }

AddProject Method: Adds a project to the database.

[WebMethod]
        public int AddProject(string Name, string Description, string Version)
        {
            string commandstring = "INSERT INTO PROJECTS (Name, Description, Version) VALUES (’"
                + Name + "’, ‘"
                + Description + "’, ‘"
                + Version + "’)";
            conn = new SqlConnection(connectionstring);
            SqlCommand command = new SqlCommand(
            commandstring,
          conn);
            conn.Open();
            int a;
            a=command.ExecuteNonQuery();
            conn.Close();
            ProjectList.Add(new Project(Name, Description, Version));
            return (a);
        }

Now to test your web service. In Visual Studio, click run. A web page should appear from which you can exercise all three methods.

To deploy the web service, Create a new IIS Virtual Directory and Enable it for .Net 2.x Copy the Bin Folder, Crossdomain.xml and the service asmx file into the folder. You should then be able to point a web browser at the new location and exercise the web service. Add comments if you have issues, and I’ll send you my source.

Next Part III, the flex application.

In this section, we’re going to create the following UI in 60 lines of code:

Flex is amazingly powerful. In this case we connect to the web service and use flex to do most of the heavy lifting. This is a very quick and cute UI.

Open Flex Browser and create a new Flex Project (Either for Adobe AIR or not, it doesn’t matter) and paste in the following code between the <mx:Application ></mx:Application> tags. (Note in an AIR Application, the tags are <mx:WindowedApplication>

   <mx:WebService id="ProjectRequest"
     wsdl="http://localhost/huboneservices/Service1.asmx?wsdl">
        <mx:operation name="Projects" resultFormat="object"
            result="remotingCFCHandler(event)"/>
            <mx:operation name="AddProject"
            result="insertCFCHandler()"/>
            <mx:operation name="DeleteProject"
            result="insertCFCHandler()"/>
    </mx:WebService>
     <mx:DataGrid id="dgUserRequest" x="48" y="55" width="496" click="Igotaclick()">
<mx:columns>
<mx:DataGridColumn id="Name" headerText="Name" dataField="Name"/>
<mx:DataGridColumn id="Description" headerText="Description" dataField="Description"/>
<mx:DataGridColumn id="Version" headerText="Version" dataField="Version"/>
</mx:columns>
</mx:DataGrid>
    <mx:Script>
        <![CDATA[
        import mx.rpc.events.ResultEvent;
        private function insertCFCHandler():void
        {
            ProjectRequest.Projects();
        }

              private function Igotaclick():void
              {
                  txtName.text = dgUserRequest.selectedItem.Name;
                  txtDescription.text = dgUserRequest.selectedItem.Description;
                  txtVersion.text = dgUserRequest.selectedItem.Version;
              }
              private function remotingCFCHandler(e:ResultEvent):void
                {
                    dgUserRequest.dataProvider = e.result;
                }

        ]]>
    </mx:Script>
    <mx:Accordion x="48" y="205" width="497" height="200">
        <mx:Canvas label="Project Administration" width="100%" height="100%">
            <mx:TextInput id="txtName" x="102" y="10"/>
            <mx:Label x="59" y="12" text="Name"/>
            <mx:TextInput id="txtDescription" x="102" y="39"/>
            <mx:Label x="30" y="41" text="Description"/>
            <mx:TextInput id="txtVersion" x="102" y="69"/>
            <mx:Label x="48" y="71" text="Version"/>
            <mx:Button x="409" y="10" label="Add"  click="{ProjectRequest.AddProject(txtName.text, txtDescription.text, txtVersion.text)}"/>
            <mx:Button x="409" y="39" label="Delete" click="{ProjectRequest.DeleteProject(txtName.text, txtDescription.text, txtVersion.text)}"/>
            <mx:Button x="409" y="69" label="Update"/>
        </mx:Canvas>
        <mx:Canvas label="Project Details" width="100%" height="100%">
        </mx:Canvas>
    </mx:Accordion>

That’s it… It’s as easy as that…

Have fun coding 3 tier applications in flex!