Creating a Proof of Concept Web API App that Uses SQL Server in 25 Granular Steps

B. Clay Shannon

4.75/5 (9 votes)

Jan 23, 2014

CPOL

8 min read

58128

How to create a Web API IoC/DI App that Uses SQL Server

Jump Right In

So you wanna build a Proof Of Concept ASP.NET Web API REST app using SQL Server as your data store? Well, you've come to the right place, as we're going to dive right in, and move through this in step-by-step fashion.

Download the Adventure Works Database. As this is a PoC and not some gigantic datahead extravaganza, I just download the LT (light) database, namely the "AdventureWorksLT2012_Database.zip" file, with less data and a less complex group of tables.
Decompress the .zip file, placing the database files in a location known only to you, your mother, and the NSA (probably something like C:\YourNannyWoreCombatBootsWeekends\AdventureWorksLT2012_Database \AdventureWorksLT2012_Data.mdf)
In Visual Studio 2013, select View > Server Explorer
Right-click "Data Connections" and select "Add Connection..."
From the "Choose Data Source" dialog, select "Microsoft SQL Server Database File"
Mash the "Continue" button
In the "Add Connection" dialog:

Select the "Browse" button
Navigate to the location where you saved the database file
Select it

To keep it as simple as possible, accept the default option for logging onto the server, "Use Windows Authentication"
Mash the "Test Connection" button. If it fails, go into The Who mode, trashing your cubicle and its contents, and thrashing anyone who dares peek over the divider to see what all the commotion is about. If the connection succeeds ("shucks!"), go to step 9.
Mash the "OK" button
You will now see the AdventureWorks database listed below "Data Connections" in Server Explorer. You can expand the tables folder to see the tables, and the tables to see their columns. If you 2-click the Address table, it will eventually rise from the ether, not altogether unlike a genie from a bottle or a cobra from a basket, and divulge its design innards thus:

To see the actual data, right-click the table in Server Explorer and select "Show Table Data"

Conditional Regression

If you don't already have an ASP.NET Web API IoC/DI project, build one using this article before returning back to here to add the SQL Server repository.

Onward, Coding Soldiers! : IKrieg, IGuerra

Now that you've got that set up as well as possible, we'll pick up where we left off. Of course, the data query needs to be added, so let's do that now:
Right-click your Models folder and select Add > Class... You could name it MisadventureWorks or something else that will raise the hackles of the tuxedo-garbed coding-seriousness cum "Professionalism" Nazis, but in this case I'll just name it "SQLServerPOC" (remember, POC stands for "Proof Of Concept" - not "Pterodactyls Over Canada"!).

In this simple example, we're going to only deal with a few columns, so the Model will just contain a few - some from Address and some from Customer (related to each other via CustomerID and AddressID - AddressID in the Address table links to the CustomerAddress table, and the CustomerAddress table links to the Customer table).

So that we don't get bogged down in details, and this article doesn't become a book, we won't deal with class member decorations such as "required" or length mins and maxes and such. We simply go with plain old strings and one int to hold all this data:
```
    public class SQLServerPOC
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string City { get; set; }
        public string StateProvince { get; set; }
        public string CountryRegion { get; set; }
        public string PostalCode { get; set; }
    }
```
Will be able to assemble the data from three related tables in AdventureWorks: Customer, CustomerAddress, and Address
Right-click the Models folder again, and this time select Add > Interface
Name it "ISQLServerPOCRepository"
Mark the interface public and give it some reasonable methods to implement so that it looks like this:

    public interface ISQLServerPOCRepository
    {
        int GetCount();
        SQLServerPOC GetByCustomerId(int ID);
        IEnumerable<sqlserverpoc> GetByLastName(string LName);
        IEnumerable<sqlserverpoc> GetByCountry(string CountryName);
        IEnumerable<sqlserverpoc> GetByStateOrProvince(string StateOrProvince);
        IEnumerable<sqlserverpoc> GetByPostalCode(string PostalCode);
        IEnumerable<sqlserverpoc> GetInPostalCodeRange(int PCBegin, int PCEnd);
        IEnumerable<sqlserverpoc> GetAll();
        SQLServerPOC Add(SQLServerPOC item);
    }

Again, right-click the Models folder, this time selecting Add > Class...
Name it "SQLServerPOCRepository"

Add code so that the concrete repository class implements the corresponding interface (ISQLServerPOCRepository) and then add the interface methods by right-clicking the interface name and selecting "implement interface"

You should see something like this:

    public class SQLServerPOCRepository : ISQLServerPOCRepository
    {
        public int GetCount()
        {
            throw new NotImplementedException();
        }

        public SQLServerPOC GetByCustomerId(int ID)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<sqlserverpoc> GetByLastName(string LName)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<sqlserverpoc> GetByCountry(string CountryName)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<sqlserverpoc> GetByStateOrProvince(string StateOrProvince)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<sqlserverpoc> GetByPostalCode(string PostalCode)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<sqlserverpoc> GetInPostalCodeRange(int PCBegin, int PCEnd)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<sqlserverpoc> GetAll()
        {
            throw new NotImplementedException();
        }

        public SQLServerPOC Add(SQLServerPOC item)
        {
            throw new NotImplementedException();
        }
    }

Add a generic List variable so you will have a container for the data we will query:
```
private readonly List<sqlserverpoc> customerAddressData = new List<sqlserverpoc>();
```
Since it compiles (that's all I care about*), we'll leave the database code for later and add the Controller now.

* Psyche! Or should it be... psycho!!!
Right-click on your Controllers folder (your Web API project does have a Controllers folder, doesn't it? - if not, add one, or one named "api" or whatever works for you), and select Add > Controller... > Web API 2 Controller - Empty.

Name it "SQLServerPOCController"

While it's creating itself, try not to have any daymares about falling from a highrise in New York or some other godawful burg while washing windows.

Add the IoC/DI/Castle Windsor-esque repository variable and interface-arg'd constructor:
```
    private readonly ISQLServerPOCRepository _SQLServerPOCRepository;

        public PeepsController(ISQLServerPOCRepository SQLServerPOCRepository)
        {
            if (SQLServerPOCRepository == null)
            {
                throw new ArgumentNullException("SQLServerPOCRepository is null!");
            }
            _SQLServerPOCRepository = SQLServerPOCRepository;
        }
```
Add the rest of the Controller code, corresponding with the Repository methods you added:

Since the Castle Windsor Framework, along with the Attribute Routings on the methods, are determining which URI invokes which method in the Controller (which in turn gets the data from the Repository), you could name these methods anything you want - instead of GetCountOfSQLServerPOCRecords(), you could have name that method GetShorty(), or GetBackToWhereYouOnceBelonged() or just about anything else. Not a good idea, though! Such tomfoolishness should be left to Congress; remember the adage: "Becoming a father is easy; *being* a father is difficult." IOW (as it applies here) you are the father (or mother) of your code, and to take care of it later, you need all the help you can get without giving in to some fanciful and fleeting whimsy of the moment (such as giving the methods non-intuitive names).

You will note, though, that I kind of mixed and matched the usage of fully named routes (such as "api/SQLServerPOC/Count") and argument count and type routing (such as "api/SQLServerPOC/{ID:int}"). You can do either one. Just note that if you have two methods that take the same count and type of arguments, you will have to use the naming on at least one of them, so the routing engine can know which one to choose when it saves a URI that matches multiple methods.

For example, since I have several methods that take a single string argument, they have to be differentiated from each other, as in:
```
[Route("api/SQLServerPOC/GetByLastName/{LName}")]
```
...and:
```
[Route("api/SQLServerPOC/GetByCountry/{Country}")]
```
...whereas the GetByCustomerID() method doesn't need to proclaim their uniqueness via an added identifier in the routing string, as the first is the only method with a single int as an arg, and the second is the only method with three strings for args.

Here are all the Controller methods:
```
        [Route("api/SQLServerPOC/Count")]
        public int GetCountOfSQLServerPOCRecords()
        {
            return _SQLServerPOCRepository.GetCount();
        }

        [Route("api/SQLServerPOC/GetAll")]
        public IEnumerable GetAllSQLServerPOC()
        {
            return _SQLServerPOCRepository.GetAll();
        }

        [Route("api/SQLServerPOC/{ID:int}")]
        public SQLServerPOC GetSQLServerPOCByCustomerId(int ID)
        {
            return _SQLServerPOCRepository.GetByCustomerId(ID);
        }

        [Route("api/SQLServerPOC/GetByLastName/{LName}")]
        public IEnumerable GetSQLServerPOCByLastName(string LName)
        {
            return _SQLServerPOCRepository.GetByLastName(LName);
        }

        [Route("api/SQLServerPOC/GetByCountry/{Country}")]
        public IEnumerable GetSQLServerPOCByCountry(string Country)
        {
            return _SQLServerPOCRepository.GetByCountry(Country);
        }

        [Route("api/SQLServerPOC/GetByStateOrProvince/{StateOrProvince}")]
        public IEnumerable GetSQLServerPOCByStateOrProvince(string StateOrProvince)
        {
            return _SQLServerPOCRepository.GetByStateOrProvince(StateOrProvince);
        }

        [Route("api/SQLServerPOC/GetByPostalCode/{PostalCode}")]
        public IEnumerable GetSQLServerPOCByPostalCode(string PostalCode)
        {
            return _SQLServerPOCRepository.GetByPostalCode(PostalCode);
        }
```

Okay, we're getting pretty close to showtime. Two more things to add: a line of code to let the Castle Windsor routing engine know which concrete class to instantiate/grab data from for the controller that implements the interface we added, and the code to query the SQL Server database (SQL) and then the Repository's methods that query that query using LINQ.

In RepositoriesInstaller (which will exist beneath the DIInstallers folder if you played along with the Web API IoC/DI Castle Windsor tutorial referenced earlier), add a line like so:

. . .
Component.For<<isqlserverpocrepository>().ImplementedBy<sqlserverpocrepository>().LifestylePerWebRequest(),
. . .

That will register SQLServerPOCRepository as the class that implements ISQLServerPOCRepository that should be used when Castle Windsor routes to the SQLServerPOCController.

24) Now, the crux of the biscuit, the Pièce de résistance*, the fulcrum of the whole shebang: the database code. Add this constructor in the SQLServerPOCRepository:

        public SQLServerPOCRepository()
        {
            const int CUSTOMERID_OFFSET = 0;
            const int FIRSTNAME_OFFSET = 1;
            const int MIDDLENAME_OFFSET = 2;
            const int LASTNAME_OFFSET = 3;
            const int ADDRESS1_OFFSET = 4;
            const int ADDRESS2_OFFSET = 5;
            const int CITY_OFFSET = 6;
            const int STATE_OFFSET = 7;
            const int ZIP_OFFSET = 8;
            const int COUNTRY_OFFSET = 9;

            // Values that may be null are "special" and have to be checked for null to prevent a minor explosion
            string address2 = string.Empty;
            string middleName = string.Empty;

            using (var conn = new SqlConnection(
                @"Data Source=(LocalDb)\v11.0;AttachDBFilename=C:\HoldingTank\AdventureWorksLT2012_Database

\AdventureWorksLT2012_Data.MDF;Integrated Security=True;"))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = 
		@"SELECT C.CustomerID, C.FirstName, C.MiddleName, C.LastName, 
                                            A.AddressLine1, A.AddressLine2, A.City, A.StateProvince, 
                                            A.PostalCode, A.CountryRegion 
                                        FROM  SalesLT.CustomerAddress U 
                                            INNER JOIN SalesLT.Address A ON A.AddressID = U.AddressID
                                            INNER JOIN  SalesLT.Customer C ON U.CustomerID = C.CustomerID  
                                        ORDER BY C.LastName, C.FirstName";
                    cmd.CommandType = CommandType.Text;
                    conn.Open();
                    using (SqlDataReader sqlD8aReader = cmd.ExecuteReader())
                    {
                        while (sqlD8aReader != null && sqlD8aReader.Read())
                        {
                            int custID = sqlD8aReader.GetInt32(CUSTOMERID_OFFSET);
                            string firstName = sqlD8aReader.GetString(FIRSTNAME_OFFSET);
                            if (!sqlD8aReader.IsDBNull(MIDDLENAME_OFFSET))
                            {
                                middleName = sqlD8aReader.GetString(MIDDLENAME_OFFSET);
                            }
                            string lastName = sqlD8aReader.GetString(LASTNAME_OFFSET);
                            string address1 = sqlD8aReader.GetString(ADDRESS1_OFFSET);
                            if (!sqlD8aReader.IsDBNull(ADDRESS2_OFFSET))
                            {
                                address2 = sqlD8aReader.GetString(ADDRESS2_OFFSET);
                            }
                            string city = sqlD8aReader.GetString(CITY_OFFSET);
                            string stateOrProvince = sqlD8aReader.GetString(STATE_OFFSET);
                            string postalCode = sqlD8aReader.GetString(ZIP_OFFSET);
                            string country = sqlD8aReader.GetString(COUNTRY_OFFSET);                            
	       Add(new SQLServerPOC { CustomerID = custID, FirstName = firstName, MiddleName = middleName, LastName = 	

            lastName, AddressLine1 = address1, AddressLine2 = address2, City = city, StateProvince = stateOrProvince, 
                                PostalCode = postalCode, CountryRegion = country });
                        }
                    }
                }
            }
        }

Note: If you want to test your query before running your app, so as to tweak it until its right and see the data returned to verify you're getting what you expect, I recommend using the free LINQPad, which you can download here

It should be pretty easy to suss out how to use it - Add a Connection using the default LINQtoSQL driver, point out to your database, select SQL as your language, and then enter the SQL query.

For example, here's what I saw in LINQPad once I got my query right:

* I know, that phrase doesn't mean what I seem to think it means, but it sounds like what I'm looking for and, as this is an aural medium (you're not *reading* this, are you?!?) I'm going with what sounds good.

Now we use LINQ to get just the desired data from the generic list that was populated. The Repository methods thus become:

        public int GetCount()
        {
            return customerData.Count;
        }

        public SQLServerPOC GetByCustomerId(int ID)
        {
            return customerData.FirstOrDefault(c => c.CustomerID == ID);
        }

        public IEnumerable<sqlserverpoc> GetByLastName(string LName)
        {
            return customerData.Where(c => c.LastName == LName);
        }

        public IEnumerable<sqlserverpoc> GetByCountry(string CountryName)
        {
            return customerData.Where(c => c.CountryRegion == CountryName);
        }

        public IEnumerable<sqlserverpoc> GetByStateOrProvince(string StateOrProvince)
        {
            return customerData.Where(c => c.StateProvince == StateOrProvince);
        }

        public IEnumerable<sqlserverpoc> GetByPostalCode(string PostalCode)
        {
            return customerData.Where(c => c.PostalCode == PostalCode);
        }

        public IEnumerable<sqlserverpoc> GetAll()
        {
            return customerData;
        }

        public SQLServerPOC Add(SQLServerPOC item)
        {
            if (item == null)
            {
                throw new ArgumentNullException("item arg was null");
            }
            if (customerData != null) customerData.Add(item);
            return item;
        }

Running the app and then entering the appropriate URI in the browser produces data in XML in Chrome:

If the browser XML is too ugly for you, you can easily write a Windows forms util to test your REST methods. Some cat wrote an article on how to do that here

Here's what such a util might look like this for the data in this article, when filtering by country and selecting "United Kingdom":

The code in the Windows forms util behind that particular (by country) query is:

        private void buttonAdvWorksCountry_Click(object sender, EventArgs e)
        {
            string country = comboBoxAdvWorksCountry.SelectedItem.ToString();
            string uri = string.Format("sqlserverpoc/GetByCountry/{0}", country);
            Popul8TheGrid(uri);
        }

        private void Popul8TheGrid(string uri)
        {
            try
            {
                dataGridView1.DataSource = GetRESTData(BASE_URI + uri);
            }
            catch (WebException webex)
            {
                MessageBox.Show("Eek, a mousey-pooh! ({0})", webex.Message);
            }
        }

        // Uses Newtonsoft's JSON.NET
        private JArray GetRESTData(string uri)
        {
            var webRequest = (HttpWebRequest) WebRequest.Create(uri);
            var webResponse = (HttpWebResponse) webRequest.GetResponse();
            var reader = new StreamReader(webResponse.GetResponseStream());
            string s = reader.ReadToEnd();
            return JsonConvert.DeserializeObject(s);
        }

Later Daze

So that should be all you need to start using SQL Server data as REST data that can be consumed in the client of your delight (browser, Windows forms app, WPF app, whatever).

If you like this article, get up and go outside and take your dog for a walk; if you don't have a dog, walk a neighbor's dog, or their Duckbilled Platypus* - but watch out for the poisonous toe on his hind foot!

*Whereas you can call the dog "Spot" or "Rover" if you don't know or remember his name, a better bet for Duckbilled Platypi is "Tiglath-Platypeser" which, AFAIK, is the most common name for pets of that species.