/// <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");
}
}
