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<sqlserverpoc> 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<sqlserverpoc> GetSQLServerPOCByLastName(string LName)
{
return _SQLServerPOCRepository.GetByLastName(LName);
}
[Route("api/SQLServerPOC/GetByCountry/{Country}")]
public IEnumerable<sqlserverpoc> GetSQLServerPOCByCountry(string Country)
{
return _SQLServerPOCRepository.GetByCountry(Country);
}
[Route("api/SQLServerPOC/GetByStateOrProvince/{StateOrProvince}")]
public IEnumerable<sqlserverpoc> GetSQLServerPOCByStateOrProvince(string StateOrProvince)
{
return _SQLServerPOCRepository.GetByStateOrProvince(StateOrProvince);
}
[Route("api/SQLServerPOC/GetByPostalCode/{PostalCode}")]
public IEnumerable<sqlserverpoc> GetSQLServerPOCByPostalCode(string PostalCode)
{
return _SQLServerPOCRepository.GetByPostalCode(PostalCode);
}
</sqlserverpoc></sqlserverpoc></sqlserverpoc></sqlserverpoc></sqlserverpoc>
- 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;
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);
}
}
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<jarray>(s);
}
</jarray>
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.
