public int CurrentStockLevel(string productNumber)
{
// Obtain the total stock level for the specified product.
// The stock level is calculated by summing the quantity of the product
// available in all the bins in the ProductInventory table.
// The Product and ProductInventory tables are joined over the
// ProductID column.
int stockLevel = 0;
try
{
// Connect to the AdventureWorks database by using the Entity Framework
using (AdventureWorks2008R2_DataEntities database = new AdventureWorks2008R2_DataEntities())
{
//database.Connection.Open();
// Calculate the sum of all quantities for the specified product
stockLevel = (from pi in database.ProductInventory
join p in database.Product
on pi.ProductID equals p.ProductID
where String.Compare(p.ProductNumber, productNumber) == 0
select (int)pi.Quantity).Sum();
}
}
catch
{
// Ignore exceptions in this implementation
}
// Return the stock level
return stockLevel;
}
public bool ChangeStockLevel(string productNumber, short newStockLevel, string shelf, int bin)
{
// Determine whether the user is a member of the StockControllers role
WindowsPrincipal user = new WindowsPrincipal((WindowsIdentity)Thread.CurrentPrincipal.Identity);
if (!user.IsInRole("StockControllers"))
{
throw new SecurityException("Access denied");
}
// Modify the current stock level of the selected product
// in the ProductInventory table.
// If the update is successful then return true, otherwise return false.
// The Product and ProductInventory tables are joined over the
// ProductID column.
try
{
// Connect to the AdventureWorks database by using the Entity Framework
using(AdventureWorks2008R2_DataEntities database = new AdventureWorks2008R2_DataEntities())
{
//database.Connection.Open();
// Find the ProductID for the specified product
int productID =
(from p in database.Product
where String.Compare(p.ProductNumber, productNumber) == 0
select p.ProductID).First();
// Find the ProductInventory object that matches the parameters passed
// in to the operation
ProductInventory productInventory = database.ProductInventory.First(
pi => String.Compare(pi.Shelf, shelf) == 0 &&
pi.Bin == bin &&
pi.ProductID == productID);
// Update the stock level for the ProductInventory object
productInventory.Quantity += newStockLevel;
// Save the change back to the database
database.SaveChanges();
}
}
catch
{
// If an exception occurs, return false to indicate failure
return false;
}
// Return true to indicate success
return true;
}
public ProductData GetProduct(string productNumber)
{
// Create a reference to a ProductData object
// This object will be instantiated if a matching product is found
ProductData productData = null;
try
{
// Connect to the AdventureWorks database by using the Entity Framework
using (AdventureWorks2008R2_DataEntities database = new AdventureWorks2008R2_DataEntities())
{
//database.Connection.Open();
// Find the first product that matches the specified product number
Product matchingProduct = database.Product.First(
p => String.Compare(p.ProductNumber, productNumber) == 0);
productData = new ProductData()
{
Name = matchingProduct.Name,
ProductNumber = matchingProduct.ProductNumber,
Color = matchingProduct.Color,
ListPrice = matchingProduct.ListPrice
};
}
}
catch
{
// Ignore exceptions in this implementation
}
// Return the product
return productData;
}
public List<string> ListProducts()
{
// Create a list for holding product numbers
List<string> productsList = new List<string>();
try
{
// Connect to the AdventureWorks database by using the Entity Framework
using (AdventureWorks2008R2_DataEntities database = new AdventureWorks2008R2_DataEntities())
{
//database.Connection.Open();
// Fetch the product number of every product in the database
var products = from product in database.Product
select product.ProductNumber;
productsList = products.ToList();
}
}
catch (Exception ex)
{
productsList.Add(" Exception " + ex.Message);
return productsList; // Ignore exceptions in this implementation
}
// Return the list of product numbers
return productsList;
}
public List<string> ListProducts()
{
//string userName = Thread.CurrentPrincipal.Identity.Name;
//MessageBox.Show(string.Format("Username is {0}", userName), "ProductsService Authentication", MessageBoxButton.OK);
// Create a list for holding product numbers
List<string> productsList = new List<string>();
try
{
// Connect to the AdventureWorks database by using the Entity Framework
using(AdventureWorks2008R2_DataEntities database = new AdventureWorks2008R2_DataEntities())
{
//database.Connection.Open();
// Fetch the product number of every product in the database
var products = from product in database.Product
select product.ProductNumber;
productsList = products.ToList();
}
}
catch(Exception ex)
{
// Edit the Initial Catalog in the connection string in app.config
// to trigger this exception
if (ex.InnerException is System.Data.SqlClient.SqlException)
{
//throw new FaultException(
//"Exception accessing database: " +
//ex.InnerException.Message, new FaultCode("Connect to database"));
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = ex.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
//throw new FaultException(
//"Exception reading product numbers: " +
//ex.Message, new FaultCode("Iterate through products"));
SystemFault dbf = new SystemFault
{
SystemOperation = "Iterate through products",
SystemReason = "Exception reading product numbers",
SystemMessage = ex.Message
};
throw new FaultException<SystemFault>(dbf);
}
}
// Return the list of product numbers
return productsList;
}
