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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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!).

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

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.

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.