public void Update()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO existing = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
if (existing == null)
{
this.Create();
existing = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
}
Assert.NotNull(existing);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
ProductRowApiO updated = existing;
updated.UnitPrice = 123;
RestResult <ProductRowApiO> apiResult = restClient.Execute <ProductRowApiO>("Product", RestSharp.Method.PATCH, jsonBody: updated, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 200, apiResult.Content);
Assert.True(apiResult.Result.UnitPrice == 123, "The updated value was not returned");
}
ProductRowApiO dbValue = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
Assert.True(dbValue.UnitPrice == 123, "The updated value was not returned");
}
}
public void UpdatePartial()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO existing = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
if (existing == null)
{
this.Create();
existing = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
}
Assert.NotNull(existing);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
string partialJson = "{ \"ProductId\": " + existing.ProductId + ", \"CategoryId\": 2, \"SupplierId\": 1, \"UnitPrice\": 32.1 }";
RestResult <ProductRowApiO> apiResult = restClient.Execute <ProductRowApiO>("Product", RestSharp.Method.PATCH, jsonBodyPartial: partialJson, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 200, apiResult.Content);
ProductRowApiO dbValue = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
Assert.True(dbValue.UnitPrice == (decimal)32.1, "Incorrect text was saved.");
}
}
}
public void Create()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
CategoryRowApiO existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
if (existingCategory != null)
{
this.Delete();
}
using (RESTClient restClient = new RESTClient(BaseUrl))
{
CategoryRowApiO newCategory = new CategoryRowApiO()
{
CategoryName = TestCategoryName, Description = "Some text about the item"
};
RestResult <CategoryRowApiO> apiResult = restClient.Execute <CategoryRowApiO>("Category", RestSharp.Method.PUT, jsonBody: newCategory, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 201, apiResult.Content);
CategoryRowApiO sqlResult = sqlClient.Fill <CategoryRowApiO>("SELECT CategoryID as CategoryId, CategoryName, Description FROM Categories WHERE CategoryName = '" + newCategory.CategoryName + "' ORDER BY CategoryID");
Assert.True(sqlResult.CategoryName == apiResult.Result.CategoryName &&
sqlResult.Description == apiResult.Result.Description &&
sqlResult.CategoryId == apiResult.Result.CategoryId);
}
}
}
public void Create()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO existing = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
if (existing != null)
{
this.Delete();
}
using (RESTClient restClient = new RESTClient(BaseUrl))
{
ProductRowApiO newItem = new ProductRowApiO()
{
ProductName = TestProductName, CategoryId = 2, QuantityPerUnit = "4 in a box", UnitPrice = (decimal)12.37, SupplierId = 1
};
RestResult <ProductRowApiO> apiResult = restClient.Execute <ProductRowApiO>("Product", RestSharp.Method.PUT, jsonBody: newItem, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 201, apiResult.Content);
ProductRowApiO sqlResult = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
Assert.True(sqlResult.ProductName == apiResult.Result.ProductName &&
sqlResult.CategoryId == apiResult.Result.CategoryId &&
sqlResult.QuantityPerUnit == apiResult.Result.QuantityPerUnit &&
sqlResult.ProductId == apiResult.Result.ProductId);
}
}
}
public void Delete()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO existingCategory = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
if (existingCategory == null)
{
this.Create();
existingCategory = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
}
Assert.NotNull(existingCategory);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
List <KeyValuePair <string, string> > routeParams = new List <KeyValuePair <string, string> >
{
new KeyValuePair <string, string>("key", existingCategory.ProductId.ToString())
};
RestResult <ProductRowApiO> apiResult = restClient.Execute <ProductRowApiO>("Product/{key}", RestSharp.Method.DELETE, routeParameters: routeParams, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 301, apiResult.Content);
}
ProductRowApiO dbValue = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
Assert.Null(dbValue);
}
}
public void UpdatePartialBadly()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
CategoryRowApiO existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
if (existingCategory == null)
{
this.Create();
existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
}
Assert.NotNull(existingCategory);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
string partialJson = "{ \"CategoryId\": " + existingCategory.CategoryId + " }";
RestResult <CategoryRowApiO> apiResult = restClient.Execute <CategoryRowApiO>("Category", RestSharp.Method.PATCH, jsonBodyPartial: partialJson, headerParameters: Headers);
// the assert should be false. The model we are working with is simple and has only 1 required parameter.
Assert.True(apiResult.StatusCode == 200, apiResult.Content);
}
CategoryRowApiO dbCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
Assert.False(dbCategory.CategoryName == string.Empty, "Incorrect text was saved.");
}
}
public void UpdatePartial()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
CategoryRowApiO existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
if (existingCategory == null)
{
this.Create();
existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
}
Assert.NotNull(existingCategory);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
string partialJson = "{ \"CategoryId\": " + existingCategory.CategoryId + ", \"Description\": \"All about more text\" }";
RestResult <CategoryRowApiO> apiResult = restClient.Execute <CategoryRowApiO>("Category", RestSharp.Method.PATCH, jsonBodyPartial: partialJson, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 200, apiResult.Content);
}
CategoryRowApiO dbCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
Assert.True(dbCategory.Description == "All about more text", "Incorrect text was saved.");
}
}
public void Update()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
CategoryRowApiO existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
if (existingCategory == null)
{
this.Create();
existingCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
}
Assert.NotNull(existingCategory);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
CategoryRowApiO updatedCategory = existingCategory;
updatedCategory.Description += " More text.";
RestResult <CategoryRowApiO> apiResult = restClient.Execute <CategoryRowApiO>("Category", RestSharp.Method.PATCH, jsonBody: updatedCategory, headerParameters: Headers);
Assert.True(apiResult.StatusCode == 200, apiResult.Content);
Assert.True(apiResult.Result.Description.EndsWith(" More text."), "The updated text was not returned");
}
CategoryRowApiO dbCategory = sqlClient.Fill <CategoryRowApiO>(GetRecordSql);
Assert.True(dbCategory.Description.EndsWith(" More text."), "The updated text was not returned");
}
}
public void UpdatePartialBadly()
{
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO existingCategory = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
if (existingCategory == null)
{
this.Create();
existingCategory = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
}
Assert.NotNull(existingCategory);
using (RESTClient restClient = new RESTClient(BaseUrl))
{
string partialJson = "{ \"ProductId\": " + existingCategory.ProductId + " }";
RestResult <ProductRowApiO> apiResult = restClient.Execute <ProductRowApiO>("Product", RestSharp.Method.PATCH, jsonBodyPartial: partialJson, headerParameters: Headers);
Assert.False(apiResult.StatusCode == 200, apiResult.Content);
}
ProductRowApiO dbValue = sqlClient.Fill <ProductRowApiO>(GetRecordSql);
Assert.False(dbValue.ProductName == string.Empty, "Incorrect text was saved.");
}
}
public void TestFillOne()
{
using (MSSQLDbClient client = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO productRowApiOs = client.Fill <ProductRowApiO>("SELECT TOP 1 * FROM PRODUCTS");
Assert.True(productRowApiOs.ProductId == 1, "No products were found.");
}
}
public void TestFillMany()
{
using (MSSQLDbClient client = new MSSQLDbClient(ConnectionString))
{
List <ProductRowApiO> productRowApiOs = client.Fill <List <ProductRowApiO> >("SELECT * From Products");
Assert.True(productRowApiOs.Count > 0, "No products were found.");
}
}
public void RunStoredProcComplexTypeWithParameters()
{
using (NorthwindContext context = this.NorthwindContext())
{
List <SalesByCategory> salesByCategory = context.SalesByCategory("Beverages", 1998);
using (MSSQLDbClient client = new MSSQLDbClient(ConnectionString))
{
List <SalesByCategory> salesByCategoryDirect = client.Fill <List <SalesByCategory> >("exec [SalesByCategory] 'Beverages', 1998");
Assert.True(salesByCategory.Count == salesByCategoryDirect.Count, "Sales by category returned incorrect results.");
}
}
}
/// <summary>
/// Create and return a DbClient appropriate for the provider.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public static DbClient GetAdvancedClient(this DbContext context)
{
DbClient result = default(DbClient);
switch (context.Database.ProviderName)
{
case "Microsoft.EntityFrameworkCore.SqlServer":
result = new MSSQLDbClient(context.Database.GetDbConnection());
break;
default:
throw new NotImplementedException(string.Format("{0} has not been implemented", context.Database.ProviderName));
break;
}
return(result);
}
public void GetAll()
{
using (RESTClient restClient = new RESTClient(BaseUrl))
{
RestResult <List <CategoryRowApiO> > apiResult = restClient.Execute <List <CategoryRowApiO> >("Category", RestSharp.Method.GET, headerParameters: Headers);
Assert.True(apiResult.Success, apiResult.Content);
Assert.NotNull(apiResult.Result);
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
List <CategoryRowApiO> sqlResult = sqlClient.Fill <List <CategoryRowApiO> >("SELECT CategoryID, CategoryName, Description FROM Categories ORDER BY CategoryID");
Assert.NotNull(sqlResult);
Assert.True(apiResult.Result.Count == sqlResult.Count, "The record counts do not match.");
}
}
}
public void GetAll()
{
using (RESTClient restClient = new RESTClient(BaseUrl))
{
RestResult <List <ProductRowApiO> > apiResult = restClient.Execute <List <ProductRowApiO> >("Product", RestSharp.Method.GET, headerParameters: Headers);
Assert.True(apiResult.Success, apiResult.Content);
Assert.NotNull(apiResult.Result);
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
List <ProductRowApiO> sqlResult = sqlClient.Fill <List <ProductRowApiO> >("SELECT ProductID as ProductId, ProductName, SupplierID as SupplierId, CategoryID as CategoryId, QuantityPerUnit, "
+ "UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products ORDER BY ProductID");
Assert.NotNull(sqlResult);
Assert.True(apiResult.Result.Count == sqlResult.Count, "The record counts do not match.");
}
}
}
public void GetOne()
{
using (RESTClient restClient = new RESTClient(BaseUrl))
{
List <KeyValuePair <string, string> > routeParams = new List <KeyValuePair <string, string> >
{
new KeyValuePair <string, string>("CategoryId", "2")
};
RestResult <CategoryRowApiO> apiResult = restClient.Execute <CategoryRowApiO>("Category/{CategoryId}", RestSharp.Method.POST, routeParameters: routeParams, headerParameters: Headers);
Assert.True(apiResult.Success, apiResult.Content);
Assert.NotNull(apiResult);
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
CategoryRowApiO sqlResult = sqlClient.Fill <CategoryRowApiO>("SELECT CategoryID as CategoryId, CategoryName, Description FROM Categories WHERE CategoryId = 2 ORDER BY CategoryID");
Assert.NotNull(sqlResult);
Assert.True(apiResult.Result.CategoryId == sqlResult.CategoryId && apiResult.Result.CategoryName == sqlResult.CategoryName, "The records do not match.");
}
}
}
public void GetOne()
{
using (RESTClient restClient = new RESTClient(BaseUrl))
{
List <KeyValuePair <string, string> > routeParams = new List <KeyValuePair <string, string> >
{
new KeyValuePair <string, string>("key", "2")
};
RestResult <ProductRowApiO> apiResult = restClient.Execute <ProductRowApiO>("Product/{key}", RestSharp.Method.POST, routeParameters: routeParams, headerParameters: Headers);
Assert.True(apiResult.Success, apiResult.Content);
Assert.NotNull(apiResult);
using (MSSQLDbClient sqlClient = new MSSQLDbClient(ConnectionString))
{
ProductRowApiO sqlResult = sqlClient.Fill <ProductRowApiO>("SELECT ProductID as ProductId, ProductName, SupplierID as SupplierId, CategoryID as CategoryId, QuantityPerUnit, "
+ "UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductId = 2 ORDER BY ProductID");
Assert.NotNull(sqlResult);
Assert.True(apiResult.Result.ProductId == sqlResult.ProductId && apiResult.Result.ProductName == sqlResult.ProductName, "The records do not match.");
}
}
}
