Web API with SQL Demo

The ASP.NET Web API framework allows you to create a Web service that can accept requests (usually from a Web page) and return result data (in JSON format). The only way I can understand any technology is to create a demo – here’s an example of getting data from a SQL database.

First, I needed some data. I could connect to an actual SQL Server machine, or get an existing .mdf database file and use that. I downloaded the well-known Northwind database .mdf file from http://msftdbprodsamples.codeplex.com/releases/view/93587 and saved it on my local machine.

My goal is to create a Web service that accepts a city (such as San Antonio) and returns the first line of the address for each customer in the specified city.

WebApiWithSqlDemo6

Next, I created a Web API project using Visual Studio. Briefly: File | New | Project | Templates | Visual C# | Web | (.NET 4.5) | ASP.NET Web Application, name = DummyWebApiService, then Empty (Web API checked) | OK.

Next, I added a container class to the Models folder:

namespace DummyWebApiService.Models
{
  // defines what one chunk of result data is
  // corresponds to SQL table column(s)
  public class AddressLineClass
  {
    public string city;
    public string addressLine1;
  }
}

Next, I set up a Controller (note: line breaks added):

namespace DummyWebApiService.Controllers
{
  public class AddressLinesController : ApiController
  {
    [HttpGet]  // not really necessary
    public List
      GetAddressesByCity(string city)
    {
      SqlConnection sqlConn = null;
      List result =
        new List();

      try
      {
        string connString = "Data Source=(LocalDB)\\
MSSQLLocalDB;AttachDbFilename=C:\\Data\\Junk\\
WebApiWithSQL\\AdventureWorks2012_Database\\
AdventureWorks2012_Data.mdf;
Integrated Security=True;Connect Timeout=30";
        sqlConn = new SqlConnection(connString);
        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.CommandType =
          System.Data.CommandType.Text;
        sqlCmd.CommandText = "SELECT [AddressLine1]
 from Person.Address WHERE [City]=" + "'" +
 city + "'";
       
        sqlCmd.Connection = sqlConn;
        
        sqlConn.Open();
        
        SqlDataReader sqlReader =
          sqlCmd.ExecuteReader(); // creates reader
        
        while (sqlReader.Read())
        {
          string addLine = sqlReader.GetString(0);
          Models.AddressLineClass alc =
            new Models.AddressLineClass();
          alc.addressLine1 = addLine;
          alc.city = city;
          result.Add(alc);
        }

        if (sqlConn.State ==
          System.Data.ConnectionState.Open)
          sqlConn.Close();
        return result;
      }
      catch (Exception ex)
      {
        Models.AddressLineClass alc =
          new Models.AddressLineClass();
        alc.addressLine1 = "Exception " + ex.Message;
        alc.city = city;
        result.Add(alc);

        if (sqlConn.State ==
          System.Data.ConnectionState.Open)
          sqlConn.Close();
        return result;
      }

    } // GetAddressesByCity


  } // class AddressLinesController
} // ns

My controller isn’t production quality, especially with regards to error handling; it’s just a demo. I got the connection string by adding a Connection in the Server Explorer window.

Next, I modified the WebApiConfig.cs template file to correspond to my service design:

namespace DummyWebApiService
{
  public static class WebApiConfig
  {
    public static void
      Register(HttpConfiguration config)
    {
      config.Routes.MapHttpRoute(
        name: "GetAddressesFromCityApi",
        routeTemplate: "api/{controller}/{city}"
        //defaults: 
      );
    }
  }
}

Next, I created a demo Web page to test the service (displaying as an image because my blog software hates HTML in a post — click to enlarge):

WebApiWithSqlDemoHtmlPageCode6

And (after about an hour of debugging) it works. It took me a couple of days to start feeling comfortable with Web API. Web API has it’s quirks for sure, but it’s way better than WCF.

Advertisements
This entry was posted in Machine Learning. Bookmark the permalink.