T-SQL: Get XML element names

The following little code snippet retrieves the XML element names of the nodes at the respective levels. The first query returns the root node name, and the second the root child node name.

DECLARE @xml XML,
        @rawXml VARCHAR(MAX)
SET @rawXml = '<parent><child /><parent>'
SET @xml = CONVERT(XML, @rawXml)

-- Get the root element name
SELECT DISTINCT c.value('local-name(.)', 'nvarchar(50)') RootName
FROM @xml.nodes('/*') AS r(c)

-- Get the root child element name
SELECT DISTINCT c.value('local-name(.)', 'nvarchar(50)') RootChild
FROM @xml.nodes('/*/*') AS r(c)

Entity Framework: Invalid column name 'xxx'

I was busy toying with Entity Framework (EF) and some SQL change scripts when I received this error:

SqlException (0x80131904): Invalid column name 'FullName'.

This is how the error occurred: I created my EF data model, dropped the database, added 'FullName' to one of my tables, and updated my data model.

Clearly EF lost a mapping or some DB specific configuration setting. I poked around the web for a while and could not find the exact cause. Some claims point towards schema types or collation settings.

Unfortunately, I don't have the time to figure this one out, so I removed my EF data model and its connection strings in the app.config and recreated it. This resolved the issue, but I have that uneasy feeling this is going to come back to bite me again.

Handle HTTP Form Post requests with a WCF service

I have been spending a lot of time working with WCF of late and one of my goals were to build an interface capable of handling HTTP Post requests. My inspiration comes from the fantastically simple implementation by Stripe:

curl https://api.stripe.com/v1/charges \  
   -u vtUQeOtUnYr7PGCLQ96Ul4zqpDUO4sOE: \  
   -d amount=400 \  
   -d currency=usd \  
   -d "description=Charge for site@stripe.com" \  
   -d "card[number]=4242424242424242" \  
   -d "card[exp_month]=12" \  
   -d "card[exp_year]=2012" \  
   -d "card[cvc]=123"

Let's see how we can create a similar implementation using WCF.

In Visual Studio 2010, create a new WCF Service Application, which we're going to call the Network. The following files will be created within your project:

• IService1.cs Your service contract
• Service1.svc The service implementation 
• Web.config Your service configuration information

You can rename these, but I'll leave them as they are, since renaming them can cause complications later on.

Let's start with our service interface. By default VS will generate two service methods (GetData and GetDataUsingDataContract) and a class called CompositeType. You can remove the methods and class and replace it with the following:

using System.IO;
using System.ServiceModel;
using System.ServiceModel.Web;

namespace Network
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        [WebInvoke(UriTemplate = "ping")]
        string Ping(Stream input);
    }
}

The key here is to add the WebInvoke attribute. The UriTemplate indicates which URL will be mapped to this particular method call. In our case, Ping(...) will be executed if any HTTP Post request is sent to http://localhost:port/service1.svc/ping.

Next we need to implement our service. Essentially we are just pinging our service with some message which will be echoed back.

using System;
using System.Collections.Specialized;
using System.IO;
using System.Web;

namespace Network
{
    public class Service : IService1
    {
        public string Ping(Stream input)
        {
            var streamReader = new StreamReader(input);
            string streamString = streamReader.ReadToEnd();
            streamReader.Close();

            NameValueCollection nvc = HttpUtility.ParseQueryString(streamString);
            return string.IsNullOrEmpty(nvc["message"]) 
                ? "The 'message' key value pair was not received."
                : nvc["message"];
        }
    }
}

All we need to do now is to configure our service in the web.config. The key here is to create a  webHttpBinding endpoint with a corresponding webHttp endpointBehavior:

<?xml version="1.0"?>
<configuration>
  <system.serviceModel>
    <behaviors>
      <endpointBehaviors>
        <behavior name="webEndpointBehavior">
          <webHttp/>
        </behavior>
      </endpointBehaviors>
    </behaviors>
    <services>
      <service name="Network.Service1">
        <endpoint address="" 
                  behaviorConfiguration="webEndpointBehavior" 
                  binding="webHttpBinding" 
                  bindingConfiguration="" 
                  contract="Network.IService1"/>
      </service>
    </services>
  </system.serviceModel>
</configuration>

Now we can run our service. For consistency, we'll use the VS Development Server as our host and specify a static port. Go into the project properties, select the Web tab and set the Specific Port to 8000.

Set your Service.svc as the start page, and build and debug.

You can either create a plain HTML page with a form that posts to your service or you can use a tool such as curl to do a submission. Let's put it to the test:

c:\>curl http://localhost:8000/service1.svc/ping -d message=pong
"pong"

Success!

Update:

If you'd like to test this solution from a web page, you can use the following bit of HTML code:

<html>
  <body>
    <form action="http://localhost:8000/service1.svc/ping" method="post">
      <input name="message" type="text" value="pong" />
      <input type="submit" />
    </form>
  </body>
</html>

The response received is:

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">pong</string>

One might want to get rid of the tags around the response string as well, but I'll get back to that in another post.