分 25 步创建使用 SQL Server 的概念验证 Web API 应用
4.75/5 (9投票s)
如何创建一个使用SQL Server的Web API IoC/DI应用
直接开始
你想用SQL Server作为数据存储，构建一个概念验证的ASP.NET Web API REST应用吗？那么，你来对地方了，我们将直接深入，一步一步地进行。
- 下载Adventure Works数据库。这只是一个概念验证，而不是一个庞大的数据盛宴，我只下载了LT（轻量级）数据库，即“AdventureWorksLT2012_Database.zip”文件，其中包含较少的数据和较少的复杂表。
- 解压.zip文件，将数据库文件放在只有你、你的妈妈和NSA知道的位置（可能类似C:\YourNannyWoreCombatBootsWeekends\AdventureWorksLT2012_Database \AdventureWorksLT2012_Data.mdf）。
- 在Visual Studio 2013中，选择“视图”>“服务器资源管理器”。
- 右键单击“数据连接”，然后选择“添加连接…”
- 在“选择数据源”对话框中，选择“Microsoft SQL Server 数据库文件”。
- 点击“继续”按钮。
- 在“添加连接”对话框中。
- 选择“浏览”按钮。
- 导航到保存数据库文件的位置。
- 选择它。
- 为尽可能简单起见，接受连接服务器的默认选项，“使用 Windows 身份验证”。
- 点击“测试连接”按钮。如果失败，请进入“The Who”模式，捣毁你的隔间及其内容，并攻击任何敢于偷看隔间以了解骚动原因的人。如果连接成功（“哦，太棒了！”），请转到步骤9。
- 点击“确定”按钮。
- 现在，您将在服务器资源管理器中的“数据连接”下方看到AdventureWorks数据库。您可以展开表文件夹以查看表，展开表以查看其列。如果您双击“Address”表，它最终会从以太中升起，就像瓶中的精灵或篮子里的眼镜蛇一样，并透露其设计细节如下：
要查看实际数据，请在服务器资源管理器中右键单击表，然后选择“显示表数据”。
条件回归
如果您还没有ASP.NET Web API IoC/DI项目，请使用本文创建一个，然后再回来添加SQL Server存储库。
前进吧，编程士兵们！ : IKrieg, IGuerra
- 现在您已经尽可能地设置好了，我们将继续进行。当然，还需要添加数据查询，现在就开始吧。
- 右键单击您的“Models”文件夹，然后选择“添加”>“类…”您可以将其命名为MisadventureWorks或其他能惹恼那些穿着燕尾服、讲究编程严肃性、“专业性”的纳粹分子，但在此我将其命名为“SQLServerPOC”（记住，POC代表“概念验证”——而不是“加拿大上空的翼龙”！）。
在这个简单的例子中，我们只处理几个列，所以Model将只包含几个——一些来自Address，一些来自Customer（通过CustomerID和AddressID相关联——Address表中的AddressID链接到CustomerAddress表，CustomerAddress表链接到Customer表）。
为了不陷入细节，并且使本文不至于成为一本书，我们将不处理诸如“required”或长度最小值和最大值等类成员装饰。我们只使用普通的字符串和一个整数来存储所有这些数据。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; } }将能够从AdventureWorks中的三个相关表（Customer、CustomerAddress和Address）中组装数据。 - 再次右键单击“Models”文件夹，这次选择“添加”>“接口”。
- 将其命名为“ISQLServerPOCRepository”。
- 将接口标记为public，并为其提供一些合理的实现方法，使其看起来像这样：
- 再次右键单击“Models”文件夹，这次选择“添加”>“类…”
- 将其命名为“SQLServerPOCRepository”。
- 添加代码，使具体的存储库类实现相应的接口（ISQLServerPOCRepository），然后通过右键单击接口名称并选择“实现接口”来添加接口方法。
您应该会看到类似这样的内容
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(); } } - 添加一个通用的List变量，以便存储我们将查询的数据。
private readonly List<sqlserverpoc> customerAddressData = new List<sqlserverpoc>();
- 由于它能够编译（这正是我关心的*），我们将稍后处理数据库代码，现在添加Controller。
* 开玩笑！或者应该说……疯了！！！
- 右键单击您的Controllers文件夹（您的Web API项目应该有一个Controllers文件夹，对吧？——如果没有，添加一个，或者一个名为“api”的文件夹，或者任何适合您的文件夹），然后选择“添加”>“控制器…”>“Web API 2 Controller - Empty”。
将其命名为“SQLServerPOCController”。
在它创建自己的时候，尽量不要做关于从纽约或其他糟糕的城市高层坠落洗窗户的噩梦。
添加IoC/DI/Castle Windsor之类的存储库变量和带接口参数的构造函数。
private readonly ISQLServerPOCRepository _SQLServerPOCRepository; public PeepsController(ISQLServerPOCRepository SQLServerPOCRepository) { if (SQLServerPOCRepository == null) { throw new ArgumentNullException("SQLServerPOCRepository is null!"); } _SQLServerPOCRepository = SQLServerPOCRepository; } - 添加Controller的其余代码，与您添加的存储库方法相对应。
由于Castle Windsor Framework以及方法上的Attribute Routes决定了哪个URI调用Controller中的哪个方法（该方法又从存储库获取数据），您可以随意命名这些方法——而不是GetCountOfSQLServerPOCRecords()，您可以将该方法命名为GetShorty()，或GetBackToWhereYouOnceBelonged()，或几乎任何其他名称。但这不是一个好主意！这种愚蠢的行为应该留给国会；记住那句格言：“成为父亲很容易；*做*父亲很难。”换句话说（在这里适用），你是你代码的父亲（或母亲），以后要照顾好它，你需要一切帮助，而不要屈服于瞬间的、短暂的奇思妙想（例如给方法起不直观的名称）。
您会注意到，我混合使用了完全命名的路由（例如“api/SQLServerPOC/Count”）和参数数量和类型的路由（例如“api/SQLServerPOC/{ID:int}”）。您可以选择其中任何一种。请注意，如果您有两个方法接受相同数量和类型的参数，您必须至少在其中一个上使用命名，这样路由引擎才能知道在保存匹配多个方法的URI时选择哪一个。
例如，由于我有几个方法接受单个字符串参数，它们必须与其他方法区分开，如下所示：
[Route("api/SQLServerPOC/GetByLastName/{LName}")]……以及[Route("api/SQLServerPOC/GetByCountry/{Country}")]……而GetByCustomerID()方法不需要通过路由字符串中的附加标识符来声明其独特性，因为它是唯一一个接受单个int作为参数的方法，而第二个是唯一一个接受三个字符串作为参数的方法。
以下是所有的Controller方法。
[Route("api/SQLServerPOC/Count")] public int GetCountOfSQLServerPOCRecords() { return _SQLServerPOCRepository.GetCount(); } [Route("api/SQLServerPOC/GetAll")] public IEnumerableGetAllSQLServerPOC() { 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); } - 好了，我们离上线不远了。还需要添加两件事：一行代码，让Castle Windsor路由引擎知道要为实现我们添加的接口的Controller实例化/获取数据的具体类，以及查询SQL Server数据库（SQL）的代码，然后是存储库中使用LINQ查询该查询的方法。
在RepositoriesInstaller中（如果您按照前面提到的Web API IoC/DI Castle Windsor教程操作，它应该位于DIInstallers文件夹下），添加一行如下：
. . . Component.For<<isqlserverpocrepository>().ImplementedBy<sqlserverpocrepository>().LifestylePerWebRequest(), . . .
这将注册SQLServerPOCRepository作为实现ISQLServerPOCRepository的类，当Castle Windsor路由到SQLServerPOCController时应该使用它。
24) 现在，核心部分，精彩的重头戏*，整个事情的支点：数据库代码。在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 }); } } } } }注意：如果您想在运行应用程序之前测试您的查询，以便进行调整直到正确并查看返回的数据以验证您获得的内容，我推荐使用免费的LINQPad，您可以在此处下载。
.它应该很容易弄清楚如何使用它——使用默认的LINQtoSQL驱动程序添加连接，指向您的数据库，选择SQL作为您的语言，然后输入SQL查询。
例如，当我调优查询并看到数据后，在LINQPad中看到的情况如下：
* 我知道，这个短语并不意味着我认为的意思，但它听起来像我想要的，而且，由于这是一个听觉媒介（你不是*在读*这个，对吧？！？）我选择听起来不错的东西。
- 现在我们使用LINQ从填充的通用列表中只获取所需的数据。因此，存储库方法变为：
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; }
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);
}
运行应用程序，然后在浏览器中输入相应的URI，Chrome中会以XML格式显示数据。
此处如果浏览器中的XML对您来说太难看了，您可以轻松编写一个Windows窗体实用程序来测试您的REST方法。有一个人写了一篇关于如何做到的文章在此处
以下是当过滤国家并选择“United Kingdom”时，此类实用程序可能看起来的样子，用于本文中的数据。
该Windows窗体实用程序中，用于特定（按国家）查询的代码是：
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);
}
后续
好了，您应该拥有开始使用SQL Server数据作为REST数据所需的一切，这些数据可以被您喜欢的客户端（浏览器、Windows窗体应用程序、WPF应用程序等）使用。
如果您喜欢这篇文章，请站起来，走到外面，带您的狗散步；如果您没有狗，请遛邻居的狗，或者他们的鸭嘴兽*——但要小心他后脚上的毒刺！
* 如果您不知道或不记得狗的名字，可以叫它“Spot”或“Rover”，但对于鸭嘴兽来说，更好的选择是“Tiglath-Platypeser”，据我所知，这是该物种宠物最常见的名字。