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; }