public int Products_Delete(int productid)
{
using (var context = new NorthwindSystemContext())
{
//two types of deletes
////physical: physically removal of the record from the database
////get the record from the database by the pkey
//var exists = context.Products.Find(productid);
////stage the removal
//context.Products.Remove(exists);
////commit
//return context.SaveChanges();
//logical: one sets a property of the class to a specified
// value to indicate the record "logically" does not
// "exist" on the database anymore
var exists = context.Products.Find(productid);
//the setting of the property should be done within this method
// and NOT dependent on the user remembering to do the action
exists.Discontinued = true;
//stage
context.Entry(exists).State = System.Data.Entity.EntityState.Modified;
//commit and return of the number rows affected
return(context.SaveChanges());
}
}
public Supplier Suppliers_FindByID(int supplierid)
{
using (var context = new NorthwindSystemContext())
{
return(context.Suppliers.Find(supplierid));
}
}
//lookup all records
public List <Product> Product_ListAll()
{
using (var context = new NorthwindSystemContext())
{
return(context.Products.ToList());
}
}
[DataObjectMethod(DataObjectMethodType.Select, false)] //Exposure of the Method
public Category Categories_FindByID(int categoryid)
{
using (var context = new NorthwindSystemContext())
{
return(context.Categories.Find(categoryid));
}
}
public int Products_Add(Product item) //We are returning an integer
{
//At some point in time you need to create an instance of your entity class
//This can be done either on the web page OR within this method
//In this example the instance was created and loaded on the web page
using (var context = new NorthwindSystemContext())
{
//Step ONE
//Staging
//During stagin the entity will be loaded with the instance to add to the database
//Staging is done in current memory
//Record is NOT yet physically added to the database
context.Products.Add(item);
//Step TWO
//Cause the physically addition of the staged record to the databse
//If this statement fails, any changes attempted against the database are ROLLEDBACK and appropriate error mesasge is issued by the system
//If this statement is successfull, THEN the NEW primary key will be available for your within the entity instance
//During this statement executin ANY validation in the entity class definition are executed
context.SaveChanges(); //Data has been physically send to database and it is trying to add it
//Optionally
return(item.ProductID);
//NOTE: public int indicates the returntype.... If it would say public void that means NO return type
}
}
//lookup by primary key
public Product Product_FindByID(int productid)
{
using (var context = new NorthwindSystemContext())
{
return(context.Products.Find(productid));
}
}
public List <Category> Category_ListAll()
{
using (var context = new NorthwindSystemContext())
{
return(context.Categories.ToList());
}
}
[DataObjectMethod(DataObjectMethodType.Select, false)] //Exposure of the Method
public List <Supplier> Suppliers_List()
{
using (var context = new NorthwindSystemContext())
{
return(context.Suppliers.ToList());
}
}
public int Product_Add(Product item)
{
using (var context = new NorthwindSystemContext())
{
//optionally you may have business rules to implement
// in this method
//this would be coded logic to ensure the data is valid
//in our example there are no additional busincess rules
//staging
//place your entity instance into your DbSet for processing
// by EntityFramework
//This data is in memory NOT yet on your sql database
//This means that the identity primary key has NOT YET been created
//The primary key is created WHEN the data is sent to the database
context.Products.Add(item);
//commit your transaction to the database
//If the following command aborts, then your data record is NOT
// on the database, the transaction is AUTOMATICALLY Rolled Back
//After the succes of the following command the instance will be
// loaded with your new primary key identity value
//IF you have entity VALIDATION ANNOTATON then when the following
// command is executed, the entity validation annotation will be
// EXECUTED
context.SaveChanges();
//if you succesful execute SaveChanges your transaction is committed
//At this point your entity instance will have the new primary identity value
//returning the pkey value is optionally and part of your design process.
return(item.ProductID);
} //closes the sql connection
}
public Product Products_FindByID(int productid)
{
using (var context = new NorthwindSystemContext())
{
//the .Find() method does a primary key lookup of the sql table
return(context.Products.Find(productid));
}
}
public List <Product> ListAllProducts()
{
using (var context = new NorthwindSystemContext())
{
//This method will you to retrieve all the records of your DbSet
//DbSet is not a list so you have to convert it to a list to return
return(context.Products.ToList());
}
}
public List <Product> Products_GetByPartialProductName(string productname)
{
using (var context = new NorthwindSystemContext())
{
IEnumerable <Product> results = context.Database.SqlQuery <Product>("Products_GetByPartialProductName @PartialName",
new SqlParameter("PartialName", productname));
return(results.ToList());
}
}
public List <Region> Region_ListAll()
{
using (var context = new NorthwindSystemContext())
{
//This extension method will allow you to retreive
//all the records of you your DbSet<T>
return(context.Regions.ToList());
}
}
public Region Regions_FindByID(int regionid)
{
using (var context = new NorthwindSystemContext())
{
//the .Find() method does a primary key lookup of the
// sql table
return(context.Regions.Find(regionid));
}
}
public int Products_Update(Product item)
{
using (var context = new NorthwindSystemContext())
{
//stage
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
//commit and return of the number rows affected
return(context.SaveChanges());
}
}
//The BLL is the interface to the system and contains methods to be used from the outside user (Presentation Layer) like the webpage
//These methods must be public
//Generally 2 types of lookups
//1. Find a record by the PK
//2. Get all records in a particular table
//Lookup by PK
public Region FindRegionByID(int regionID)
{
//Using closes and destroys resources when you are done with them
using (var context = new NorthwindSystemContext())
{
//There are methods in EF that allow you to do some common queries
//This example will allow you to search by PK
//So since this is a common search something was created already in EF for us to do this search
return(context.Regions.Find(regionID));
}
}
public List <ProductsInCategories> Views_ProductsOfCategory(string category)
{
//the context connects one to the database
using (var context = new NorthwindSystemContext())
{
//this is not an entity extension query
//this will need a SqlQuery<T>
var results = context.Database.SqlQuery <ProductsInCategories>(
"ProductsOfCategory @category", new SqlParameter("category", category));
return(results.ToList());
}
}
public Region Regions_FindByID(int regionid)
{
using (var context = new NorthwindSystemContext())
{
//the .Find() method does a primary key lookup of the
// sql table
return(context.Regions.Find(regionid)); //Finds a specific record on the Region table
//it finds a specific record on the region table
//the specific record we are looking for is done by the primary key
//pu the primary key in the ()
//it will return the primary record; if not it will return null
}
}
//the interface is a set of methods that can be called by
//an outside user
//these methods must be public
//generally there is two types of lookups
//a) find a specific record by primary key
//b) get all records for a specific table
//lookup by primary key
public Region Region_FindByID(int regionid)
{
//create a section of code that will ensure the sql
//connection will be closed when the method
//is complete: using(...)(...)
using (var context = new NorthwindSystemContext())
{
//there are extension methods within EntityFramework
//that allow you to do some very common queries
//This extension method will allow you to search
//your DbSet<T> by primary key value
return(context.Regions.Find(regionid));
}
}
public List <Product> Products_List()
{
//ensure the sql connection is closed at the end of the query process
using (var context = new NorthwindSystemContext())
{
//Use an extension method of EntityFrame to get
//All of the data from the sql table Region
//Use the property DbSet that maps the Region sql table to the application
//The dbset T type is Region which describes a single record
//The actual collection type that is returned from EntityFramework is either IEnumerable or IQuerable
//Change the collection type to a List<T> using .ToList()
return(context.Products.ToList());
}
}
public int Product_Delete(int productid)
{
using (var context = new NorthwindSystemContext())
{
int rowsaffected = 0;
//find the current record by primary key
Product exists = context.Products.Find(productid);
//verify that you actually have an instance (object)
// of the Product entity
if (exists == null)
{
throw new Exception("Product no longer on file. Refresh your search.");
}
else
{
//scenario LOGICAL DELETE
//you are really probably going to alter a value on the instance
// then do an update
//DO NOT rely on the user to actually set the attribute
// indicating "deletion"
//INSTEAD do it by the program
exists.Discontinued = true;
//stage the update
//a scecond techinque for updating is to update a specific field
// WITHOUT needing to update the entire entity
context.Entry(exists).Property("Discontinued").IsModified = true;
//sceanrio PHYISCAL DELETE
//stage the delete
//the record is phyiscally removed from the database
//context.Products.Remove(exists);
//commit
//changes save to database
//return the value from the update/delete of rowsaffected
rowsaffected = context.SaveChanges();
//return the value
return(rowsaffected);
}
}
}
public List <Product> Products_FindByCategory(int categoryid)
{
using (var context = new NorthwindSystemContext())
{
//call to an Sql Procedure
//the call returns a datatype of IEnumerable<T>
//.SqlQuery<T>(execution string[, list of SqlParameter instances])
//execution string >> "procedurename parameterlist"
//a parameter is specified using SqlParameter("parametername", value)
//SqlParameter() requires using System.Data.SqlClient; namespace
IEnumerable <Product> results = context.Database.SqlQuery <Product>("Products_GetByCategories @CategoryID",
new SqlParameter("CategoryID", categoryid));
//convert the IEnumerable<T> to a List<T>
return(results.ToList());
}
}
public List <Region> Regions_List() //it returns a List of all records on the Region Table
{
//ensure the sql connection is closed at the end
// of the query process
using (var context = new NorthwindSystemContext())
{
//use an extension method of EntityFramework to get
// all of the data from the sql table Region
//use the property DbSet that maps the Region sql table to
// the application
//the dbset T type is Region which describes a single record
//the actual collection type that is returned from
// EntityFramework is either IEnumerable or IQuerable
//Change the collection type to a List<T> using .ToList()
return(context.Regions.ToList()); //context is the new instance of the class that we created on the page (on the top)
//it will take the property "Regions" and change it to a list and will than return it
}
}
public int Product_Update(Product item)
{
//logical code for any business rules
//stage your update
//the entire entity on the database will be updated,
//all fields except the primary key
using (var context = new NorthwindSystemContext())
{
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
//commit of update
//changes the database
//the returned value of the execution is the RowsAffected on the database
int rowsaffected = context.SaveChanges();
//return context.SaveChanges();
//optional (technically) actions afterwards
return(rowsaffected);
}
}
[DataObjectMethod(DataObjectMethodType.Select, false)] //Expose it so we can use it with the ODS
public List <Product> Products_FindByCategory(int categoryid) //We pass in an int categoryid
{
using (var context = new NorthwindSystemContext()) //connect to context class which conntects us to the entity framework which is responsible to look up the data
{
//call to an Sql Procedure
//the call returns a datatype of IEnumerable<T>
//.SqlQuery<T>(execution string[, list of SqlParameter instances])
//execution string >> "procedurename parameterlist"
//a parameter is specified using SqlParameter("parametername", value)
//SqlParameter() requires using System.Data.SqlClient; namespace --> siehe on top of the page the using namespace
IEnumerable <Product> results = context.Database.SqlQuery <Product>("Products_GetByCategories @CategoryID", //IEnumerable is the datatype; //Interprete the data coming in as a Product record
new SqlParameter("CategoryID", categoryid)); //"CategoryID" is the name of the parameter from the stored procedure //the name of the int categoryid that we passed in in the () when creating this method.
//Products_GetByCategories is the procedure name
//@CategoryID is the parameter that is to be found in the database stored procedure under the name that we provided in the () and when we expand it we find the @parameter that we have to provide in the ()
//Don't forget to add the Using clause on the SqlParameter
//convert the IEnumerable<T> to a List<T>
return(results.ToList());
//This way we look up anything on a table that is not a primary key
}
}
public int Products_Add(Product item)
{
using (var context = new NorthwindSystemContext())
{
//Staged the action for the database
//Staging is done in local memory
//data needs to be in an instance of the class
context.Products.Add(item);
//phsyical action of saving your instance to the database
//at this time any validation in the class definition is executed
//if the save is successful :Commit
//if the save is not successful : Rollback
//if the pkey is an identiy pkey, then it is at this
// point the pkey value is create by the database
// and is placed in the class instance
context.SaveChanges();
//returning the new pkey
return(item.ProductID);
}
}
public CategoryRepository(NorthwindSystemContext dbContext)
{
_dbContext = dbContext;
}
public SupplierRepository(NorthwindSystemContext dbContext)
{
_dbContext = dbContext;
}
public ProductRepository(NorthwindSystemContext dbContext)
{
_dbContext = dbContext;
}