static void FilteredQuery() { using (var db = new BikeStoreContext()) { var modelName = new SqlParameter("@ModelName", "tape"); var partNumberName = new SqlParameter("@PartNumberName", DBNull.Value); var manufacturerCode = new SqlParameter("@manufacturercode", DBNull.Value); var inventoryPartNumber = new SqlParameter("@InventoryPartNumber", DBNull.Value); var manufacturerPartNumber = new SqlParameter("@ManufacturerPartNumber", DBNull.Value); var categoryName = new SqlParameter("@categoryname", DBNull.Value); var description = new SqlParameter("@description", DBNull.Value); var features = new SqlParameter("@features", DBNull.Value); var minListPrice = new SqlParameter("@minListPrice", DBNull.Value); var maxListPrice = new SqlParameter("@maxListPrice", DBNull.Value); var statusName = new SqlParameter("@statusName", DBNull.Value); var manufacturerName = new SqlParameter("@manufacturerName", DBNull.Value); var models = db.Database.SqlQuery<PartNumberSelectFilter_Result>("product.PartNumberSelectFilter @ModelName,@PartNumberName, @manufacturercode,@InventoryPartNumber,@ManufacturerPartNumber, @categoryname,@description,@features,@minListPrice,@maxListPrice,@statusName, @manufacturerName", modelName, partNumberName, manufacturerCode, inventoryPartNumber,manufacturerPartNumber, categoryName, description, features, minListPrice, maxListPrice, statusName, manufacturerName).ToList(); Console.WriteLine("All Bar Tape Products in the Database"); foreach (var item in models) { Console.WriteLine(item.ModelId + " | " + item.ModelName + " | " + item.PartNumberName + " | " + item.InventoryPartNumber + " | " + item.ListPrice + " | " + item.CategoryName); } } }
static void DeleteModel(int modelId) { using (var db = new BikeStoreContext()) { var model = new Model { ModelId = modelId }; db.Models.Attach(model); db.Models.Remove(model); db.SaveChanges(); } }
static int CreateModel() { int modelId = 0; using (var db = new BikeStoreContext()) { // Use stored procedure mapping var model = new Model { Name = "Domane 5.2", ListPrice = 3499.99m }; db.Models.Add(model); db.SaveChanges(); modelId = model.ModelId; // Call sproc directly - not recommended ... // to get the following to work, need to // A) remove @Modelid output param from product.modelInsert // B) change the identity select after insert to select all fields for loading into model /* var name = new SqlParameter("@name","Domane 5.2"); var manufacturerCode = new SqlParameter("@manufacturercode", DBNull.Value); var categoryid = new SqlParameter("@categoryid", System.Data.SqlDbType.Int); categoryid.Value = DBNull.Value; var description = new SqlParameter("@description",DBNull.Value); var features = new SqlParameter("@features", DBNull.Value); var statusid = new SqlParameter("@statusid", System.Data.SqlDbType.Int); statusid.Value = DBNull.Value; var manufacturerid = new SqlParameter("@manufacturerid", System.Data.SqlDbType.Int); manufacturerid.Value = DBNull.Value; var listprice = new SqlParameter("@listprice", 3499.99m); var imagecollection = new SqlParameter("@imagecollection", DBNull.Value); var CategoryCustomData = new SqlParameter("@CategoryCustomData", DBNull.Value); var ManufacturerCustomData = new SqlParameter("@ManufacturerCustomData", DBNull.Value); var result = db.Database.SqlQuery<Model>("product.modelInsert @name, @manufacturercode, @categoryid, @Description, @features,@statusid, @manufacturerid, @listprice,@imagecollection,@CategoryCustomData, @ManufacturerCustomDAta", name, manufacturerCode, categoryid, description, features, statusid, manufacturerid, listprice, imagecollection, CategoryCustomData, ManufacturerCustomData).SingleOrDefault(); modelId = result.ModelId; */ } Console.WriteLine("New Model: " + modelId); return modelId; }
static void UpdateModel(int modelId) { using (var db = new BikeStoreContext()) { var modelIdParam = new SqlParameter("@modelid", modelId); // ModelSelectByKey_Result //var model = db.Database.SqlQuery<ModelSelectByKey_Result>("product.ModelSelectByKey @modelid", modelIdParam).SingleOrDefault(); var model = db.Database.SqlQuery<Model>("product.ModelSelectByKey @modelid", modelIdParam).SingleOrDefault(); if (model != null) { /* //Load Model // note that model is of type ModelSelectByKey_Result, so we can't // attach this to db.Models // additionally, we can't load Model m = new Model(); m.ModelId = model.ModelId; m.Name = model.Name; m.ManufacturerCode = model.ManufacturerCode; m.CategoryId = model.CategoryId; m.Description = model.Description; m.Features = model.Features; m.StatusId = model.StatusId; m.ManufacturerId = model.ManufacturerId; m.ListPrice = model.ListPrice; m.ImageCollection = model.ImageCollection; m.CategoryCustomData = model.CategoryCustomData; m.ManufacturerCustomData = model.ManufacturerCustomData; db.Models.Attach(m); // make the change - needs to happen after the attach, otherwise the change // will not be registered m.Features = "500 Series OCLV Frame"; */ //unlike edmx, need to attach the model entity to the db context, as //SqlQuery() does not do this db.Models.Attach(model); model.Features = "500 Series OCLV Frame"; db.SaveChanges(); } } }
static void SelectSingleModel(int modelId) { using (var db = new BikeStoreContext()) { var modelIdParam = new SqlParameter("@modelid", modelId); var model = db.Database.SqlQuery<ModelSelectByKey_Result>("product.ModelSelectByKey @modelid", modelIdParam).SingleOrDefault(); Console.WriteLine("Selected Model:"); if (model != null) { Console.WriteLine(model.ModelId + " | " + model.Name); } } }
static void SelectAllModels() { using (var db = new BikeStoreContext()) { var name = new SqlParameter("@name", DBNull.Value); var manufacturerCode = new SqlParameter("@manufacturercode", DBNull.Value); var categoryName = new SqlParameter("@categoryname", DBNull.Value); var description = new SqlParameter("@description", DBNull.Value); var features = new SqlParameter("@features", DBNull.Value); var minListPrice = new SqlParameter("@minListPrice", DBNull.Value); var maxListPrice = new SqlParameter("@maxListPrice", DBNull.Value); var statusName = new SqlParameter("@statusName", DBNull.Value); var manufacturerName = new SqlParameter("@manufacturerName", DBNull.Value); var models = db.Database.SqlQuery<ModelSelectFilter_Result>("product.ModelSelectFilter @name, @manufacturercode, @categoryname,@description,@features,@minListPrice,@maxListPrice,@statusName, @manufacturerName", name,manufacturerCode, categoryName, description, features, minListPrice,maxListPrice,statusName,manufacturerName).ToList(); foreach (var model in models) { Console.WriteLine(model.ModelId + " | " + model.Name); } } }