Building an OData Web Service on Iron Foundry

In my previous posts on Iron Foundry, I did a quick walkthrough of the tooling, and then showed how to use external libraries to communicate from the cloud to an on-premises service. One thing that I hadn’t done yet was use the various application services that are available to Iron Foundry application developers. In this post, I’ll show you how to provision a SQL Server database, create a set of tables, populate data, and expose that data via an OData web service.

The first challenge we face is how to actually interact with our Iron Foundry SQL Server service. At this point, Iron Foundry (and Cloud Foundry) doesn’t support direct tunneling to the application services. That means that I can’t just point the SQL Server 2008 Management Studio to a cloud database and use the GUI to muck with database properties. SQL Azure supports this, and hopefully we’ll see this added to the Cloud Foundry stack in the near future.

But one man’s challenge is … well, another man’s challenge. But, it’s an entirely solvable one. I decided to use the Microsoft Entity Framework to model a data structure, generate the corresponding database script, and run that against the Iron Foundry environment. I can do all of this locally (with my own SQL Server) to test it before deploying to Iron Foundry. Let’s do that.

Step 1: Generate the Data Model

To start with, I created a new, empty ASP.NET web application. This will hold our Entity model, ASP.NET web page for creating the database tables and populating them with data, and the WCF Data Service that exposes our data sets. Then, I added a new ADO.NET Data Entity Model to the project.

2012.1.16ironfoundry01

We’re not starting with an existing database here, so I chose the Empty Model option after creating this file. I then defined a simple set of entities representing Pets and Owners. The relationship indicates that an Owner may have multiple Pets.

2012.1.16ironfoundry02

Now, to make my life easier, I generated the DDL script that would build a pair of tables based on this model. The script is produced by right-clicking the model and selecting the Generate Database from Model option.

2012.1.16ironfoundry03

When walking through the Generate Database Wizard, I chose a database (“DemoDb”) on my own machine, and chose to save a connection entry in my web application’s configuration file. Note that the name used here (“PetModelContainer”) is the same name of the connection string the Entity Model expects to use when inflating the entities.

2012.1.16ironfoundry04

When this wizard finished, we got a SQL script that can generate the tables and relationships.

2012.1.16ironfoundry12

Before proceeding, open up that file and comment out all the GO statements. Otherwise, the SqlCommand object will throw an error when trying to execute the script.

2012.1.16ironfoundry05

Step 2: Add WCF Data Service

With the data model complete, I then added the WCF Data Service which exposes an OData endpoint for our entity model.

2012.1.16ironfoundry06

These services are super-easy to configure. There are really only two things you HAVE to do in order to get this service working. First the topmost statement (class declaration) needs to be updated with the name of the data entity class. Secondly, I uncommented/added statements for the entity access rules. In the case below, I provided “Read” access to all entities in the model.

public class PetService : DataService
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }

Our service is now completed! That was easy.

Step 3: Create a Web Form that Creates the Database and Loads Data

I could not yet test this application since I haven’t physically constructed the underlying data structure. Since I cannot run the database script directly against the Iron Foundry database, I needed a host that can run this script. I chose an ASP.NET Web Form that could execute the script AND put some sample data in the tables.

Before creating the web page, I added an entry in my web.config file. Specifically, I added a new connection string entry that holds the details I need to connect to my LOCAL database.

<connectionStrings>
<add name="PetModelContainer" connectionString="metadata=res://*/PetModel.csdl|res://*/PetModel.ssdl|res://*/PetModel.msl;provider=System.Data.SqlClient; provider connection string=&quot;data source=.; initial catalog=DemoDb; integrated security=True; multipleactiveresultsets=True; App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
<add name="PetDb" connectionString="data source=.; initial catalog=DemoDb; integrated security=True;" />
</connectionStrings>

I was now ready to consume the SQL script and create the database tables. The follow code instantiates a database connection, loads the database script from the file system into a SqlCommand object, and executes the command. Note that unlike Windows Azure, an Iron Foundry web application CAN use file system operations.

//create connection
            string connString = ConfigurationManager.ConnectionStrings["PetDb"].ConnectionString;
            SqlConnection c = new SqlConnection(connString);

            //load generated SQL script into a string
            FileInfo file = new FileInfo(Server.MapPath("PetModel.edmx.sql"));
            string tableScript = file.OpenText().ReadToEnd();

            c.Open();
            //execute sql script and create tables
            SqlCommand command = new SqlCommand(tableScript, c);
            command.ExecuteNonQuery();
            file.OpenText().Close();
            c.Close();

            command.Dispose();
            c.Dispose();

            lblStatus.Text = "db table created";

Cool. So after this runs, we should have real database tables in our LOCAL database. Next up, I wrote the code necessary to add some sample data into our tables

 //create connection
            string connString = ConfigurationManager.ConnectionStrings["PetDb"].ConnectionString;
            SqlConnection c = new SqlConnection(connString);
            c.Open();

            string commandString = "";
            SqlCommand command;
            string ownerId;
            string petId;

            //owner command
            commandString = "INSERT INTO Owners VALUES ('Richard Seroter', '818-232-5454', 0);SELECT SCOPE_IDENTITY();";
            command = new SqlCommand(commandString, c);
            ownerId = command.ExecuteScalar().ToString();

            //pet command
            commandString = "INSERT INTO Pets VALUES ('Watson', 'Dog', 'Corgador', '31 lbs', 'Do not feed wet food', " + ownerId + ");SELECT SCOPE_IDENTITY();";
            command = new SqlCommand(commandString, c);
            petId = command.ExecuteScalar().ToString();

 		//add more rows

		c.Close();
            command.Dispose();
            c.Dispose();

            lblStatus.Text = "rows added";

Step 4: Local Testing

I’m ready to test this application. After pressing F5 in Visual Studio 2010 and running this web application in a local web server, I saw my Web Form buttons for creating tables and seeding data. After clicking the Create Database button, I checked my local SQL Server. Sure enough, I found my new tables.

2012.1.16ironfoundry07

Next, I clicked the Seed Data button on my form and saw three rows added to each table. With my tables ready and data loaded, I could now execute the OData service. Hitting the service address resulted in a list of entities that the service makes available.

2012.1.16ironfoundry08

And then, per typical OData queries, I could drill into the various entities and relationship. With this simple query, I can show all the pets for a particular owner.

2012.1.16ironfoundry09

At this point, I had a fully working, LOCAL version of the this application.

Step 5: Deploy to Iron Foundry

Here’s where the rubber meets the road. Can I take this app, as is, and have it work in Iron Foundry? This answer is “pretty much.” The only thing that I really need to do is update the connection string for my Iron Foundry instance of SQL Server, but I’m getting ahead of myself. I first had to get this application up to Iron Foundry so that I could associate it with a SQL instance. Since I’ve had some instability with the Visual Studio plugin for Iron Foundry, I went ahead and “published” my ASP.NET application to my file system and ran the vmc client to upload the application.

2012.1.16ironfoundry11

With my app uploaded, I then bound my application to a SQL Server application service. I used the bind-service command to bind my SQL Server service to my application.

2012.1.16ironfoundry14

Now I needed to view my web.config file that was modified by the Iron Foundry engine. When this binding occurred, Iron Foundry provisioned a SQL Server space for me and updated my web.config file with the valid connection string. I’m going to need those connection string values (server name, database name, credentials) for my application as well. I wasn’t sure how to access my application files from the vmc tool, so I switched back to the Cloud Explorer where I can actually browse an app.

2012.1.16ironfoundry15

My web.config file now contained a “Default” connection string added by Iron Foundry.

<connectionStrings>
    <add name="PetModelContainer" connectionString="metadata=res://*/PetModel.csdl|res://*/PetModel.ssdl|res://*/PetModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=DemoDb;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;"
      providerName="System.Data.EntityClient" />
    <add name="PetDb" connectionString="data source=.;initial catalog=DemoDb;integrated security=True;" />
    <add name="Default" connectionString="Data Source=XXXXXX;Initial Catalog=YYYYYYY;Integrated Security=False;User ID=ABC;Password=DEF;Connect Timeout=30" />
  </connectionStrings>

Step 6: Update Application with Iron Foundry Connection Details and then Test the Solution

With these connection string values in hand, I had two things to update. First, I updated my generated T-SQL script to “use” the appropriate database.

2012.1.16ironfoundry16

Finally, I had to update the two previously created connection strings. I updated my ORIGINAL web.config and not the one that I retrieved back from Iron Foundry. The first (“PetDb”) connection string was used by my code to run the T-SQL script and create the tables, and the second connection string (“PetModelContainer”) is leveraged by the Entity Framework and the WCF Data Service. Both were updated with the Iron Foundry connection string details.

<connectionStrings>
    <add name="PetModelContainer" connectionString="metadata=res://*/PetModel.csdl|res://*/PetModel.ssdl|res://*/PetModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=XXXXX;initial catalog=YYYYYY;Integrated Security=False;User ID=ABC;Password=DEF;multipleactiveresultsets=True;App=EntityFramework&quot;"
      providerName="System.Data.EntityClient" />
    <add name="PetDb" connectionString="data source=XXXXX;initial catalog=YYYYYY;Integrated Security=False;User ID=ABC;Password=DEF;" />
   </connectionStrings>

With these updates in place, I rebuilt the application and pushed a new version of my application up to Iron Foundry.

2012.1.16ironfoundry17

I was now ready to test this cat out. As expected, I could now hit the public URL of my “setup” page (which I have since removed so that you can’t create tables over and over!).

2012.1.16ironfoundry18

After creating the database (via Create Database button), I then clicked the button to load a few rows of data into my database tables.

2012.1.16ironfoundry19

For the grand finale, I tested my OData service which should allow me to query my new SQL Server database tables. Hitting the URL http://seroterodata.gofoundry.net/PetService.svc/Pets returns a list of all the Pets in my database.

2012.1.16ironfoundry20

As with any OData service, you can now mess with the data in all sorts of ways. This URL (http://seroterodata.gofoundry.net/PetService.svc/Pets(2)/Owner) returns the owner of the second pet. If I want to show the owner and pet in a single result set, I can use this URL (http://seroterodata.gofoundry.net/PetService.svc/Owners(1)?$expand=Pets). Want the name of the 3rd pet? use this URL (http://seroterodata.gofoundry.net/PetService.svc/Pets(3)/Name).

Summary

Overall, this is fairly straightforward stuff. I definitely felt a bit handicapped by not being able to directly use SQL Server Management Studio, but at least it forced me to brush up on my T-SQL commands. One interesting item was that it APPEARS that I am provisioned a single database when I first bind to an application service and that same database is used for subsequent bindings. I had built a previous application that used the SQL Server application service and later deleted the app. When I deployed the application above, I noticed that the tables I had created earlier were still there! So, whether intentionally or not, Iron Foundry points me to the same (personal?) database for each app. Not a big deal, but this could have unintended side effects if you’re not aware of it.

Right now, developers can use either the SQL Server application service or MongoDB application service. Expect to see more show up in the near future. While you need to programmatically provision your database resources, that doesn’t seem to be a big deal. The Iron Foundry application services are a critical resource in building truly interesting web applications and I hope you enjoyed this walkthrough.



Categories: Cloud, Cloud Foundry

6 replies

  1. Great walkthrough! Thank you!

  2. Thanks for post. I too ported Nerddinner to IronFoundry to see how the platform works. The blog is available at http://chandermani.blogspot.in/2012/02/porting-nerddinner-on-ironfoundry.html.
    I derived the database creation technique for you post, so thanks for that🙂

Trackbacks

  1. Three Software Updates to be Aware Of « Richard Seroter's Architecture Musings
  2. Windows Azure and Cloud Computing Posts for 4/6/2012+ - Windows Azure Blog
  3. Three software updates to be aware of - The Microsoft Enterprise Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: