/// <summary> /// Demonstrates how to insert into multiple tables in a single transaction with EF Core linq syntax /// </summary> /// <remarks>This is the preferable way over a stored proc, since it keeps the logic in the application and works naturally with Entity Framework</remarks> /// <param name="model"></param> /// <returns></returns> private async Task <int> CreateMSPWithEFEntities(MovieSellerPriceViewModel model) { //Create movie var newMovie = new Movie() { Title = model.Title, ReleaseDate = model.ReleaseDate, Genre = model.Genre, Price = model.MSRPPrice }; //Add seller and price if indicated if (!string.IsNullOrEmpty(model.SellerName)) { var newSeller = new Seller() { Address1 = model.Address1, City = model.City, Name = model.SellerName, Phone = model.Phone, State = model.State, URL = model.URL, Zip = model.Zip }; var newSellerPrice = new MoviePrice() { Price = model.SellerPrice, }; //Seller Price has a seller, so add seller to that collection newSellerPrice.Seller = newSeller; //Movies has a Prices collection. Add sellerprice to movie. This creates a graph of Movie->SellerPrice->Seller that //EF can traverse to figure out the relationships and populate the Foreign keys during creation newMovie.Prices = new List <MoviePrice>(); newMovie.Prices.Add(newSellerPrice); } _context.Movie.Add(newMovie); return(await _context.SaveChangesAsync()); }
/// <summary> /// Demonstrates how to use a stored proc from EF core. /// </summary> /// <param name="model"></param> /// <returns></returns> private async Task <int> CreateMSPWithStoredProc(MovieSellerPriceViewModel model) { //Setup parameters var title = new SqlParameter("Title", System.Data.SqlDbType.NVarChar); title.Value = stringToDbString(model.Title); var releaseDate = new SqlParameter("ReleaseDate", System.Data.SqlDbType.DateTime2); releaseDate.Value = model.ReleaseDate; var genre = new SqlParameter("Genre", System.Data.SqlDbType.NVarChar); genre.Value = stringToDbString(model.Genre); var msrp = new SqlParameter("MSRP", System.Data.SqlDbType.Decimal); msrp.Value = model.MSRPPrice; var sellerName = new SqlParameter("SellerName", System.Data.SqlDbType.VarChar); sellerName.Value = stringToDbString(model.SellerName); var url = new SqlParameter("URL", System.Data.SqlDbType.NVarChar); url.Value = stringToDbString(model.URL); var address = new SqlParameter("Address", System.Data.SqlDbType.NVarChar); address.Value = stringToDbString(model.Address1); var city = new SqlParameter("City", System.Data.SqlDbType.NVarChar); city.Value = stringToDbString(model.City); var state = new SqlParameter("State", System.Data.SqlDbType.NVarChar); state.Value = stringToDbString(model.State); var zip = new SqlParameter("Zip", System.Data.SqlDbType.NVarChar); zip.Value = stringToDbString(model.Zip); var phone = new SqlParameter("Phone", System.Data.SqlDbType.NVarChar); phone.Value = stringToDbString(model.Phone); var sellerPrice = new SqlParameter("SellerPrice", System.Data.SqlDbType.Decimal); sellerPrice.Value = model.SellerPrice; if (model.SaveWithStoredProcEFCore) { var sqlparams = new object[] { title, releaseDate, genre, msrp, sellerName, url, address, city, state, zip, phone, sellerPrice }; //Call stored procedure to save //Here we are using an EF Core convenience method. We could also do this with ADO.NET classes (SQLCommand, SQLConnection, SQLParameter) instead. return(await _context.Database.ExecuteSqlRawAsync("EXECUTE dbo.AddNewMovieFromPage @Title, @ReleaseDate, @Genre, @MSRP, @SellerName, @URL, @Address, @City, @State, @Zip, @Phone, @SellerPrice", sqlparams)); } else if (model.SaveWithStoredProcADONET) { //Classic ADO.NET Logic SqlCommand cmd = new SqlCommand(); SqlConnection cn = null; try { cn = new SqlConnection(_config.GetConnectionString("MvcMovieContext")); await cn.OpenAsync(); cmd.Connection = cn; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "dbo.AddNewMovieFromPage"; cmd.Parameters.AddRange(new SqlParameter[] { title, releaseDate, genre, msrp, sellerName, url, address, city, state, zip, phone, sellerPrice }); return(await cmd.ExecuteNonQueryAsync()); } finally { if (cmd != null) { cmd.Connection = null; await cmd.DisposeAsync(); } if (cn != null) { await cn.CloseAsync(); await cn.DisposeAsync(); } } } else { throw new ArgumentException("Called to use Stored Proc without a valid selection"); } }