internal static int Create()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = new ProductCategory()
{
Name = "Category"
};
ProductSubcategory subcategory = new ProductSubcategory()
{
Name = "Subcategory"
};
category.ProductSubcategories.Add(subcategory);
adventureWorks.ProductCategories.InsertOnSubmit(category);
Trace.WriteLine(category.ProductCategoryID); // 0.
Trace.WriteLine(subcategory.ProductCategoryID); // null.
Trace.WriteLine(subcategory.ProductSubcategoryID); // 0.
adventureWorks.SubmitChanges();
Trace.WriteLine(category.ProductCategoryID); // 5.
Trace.WriteLine(subcategory.ProductCategoryID); // 5.
Trace.WriteLine(subcategory.ProductSubcategoryID); // 38.
return(subcategory.ProductSubcategoryID);
}
}
internal static void DbTransaction()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
using (DbConnection connection = adventureWorks.Connection)
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
adventureWorks.Transaction = transaction;
ProductCategory category = new ProductCategory()
{
Name = "Transaction"
};
adventureWorks.ProductCategories.InsertOnSubmit(category);
adventureWorks.SubmitChanges();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM [Production].[ProductCategory] WHERE [Name] = N'Transaction'";
command.Transaction = transaction;
Trace.WriteLine(command.ExecuteNonQuery()); // 1.
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
internal static void Delete()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories
.Single(entity => entity.Name == "Category");
adventureWorks.ProductCategories.DeleteOnSubmit(category);
adventureWorks.SubmitChanges();
}
}
internal static void UpdateWithNoChange()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Product product = adventureWorks.Find <Product>(999);
product.ListPrice += product.ListPrice;
product.ListPrice /= 2; // Change tracked entity then change back.
Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False.
adventureWorks.SubmitChanges();
}
}
internal static void UntrackedChanges()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.ObjectTrackingEnabled = false;
IQueryable <Product> products = adventureWorks.Products.Take(10);
adventureWorks.Products.DeleteAllOnSubmit(products);
adventureWorks.SubmitChanges();
// InvalidOperationException: Object tracking is not enabled for the current data context instance.
}
}
internal static void DeleteWithAssociation()
{
Create(); // Insert ProductCategory "Category" and ProductSubcategory "Subcategory".
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories
.Single(entity => entity.Name == "Category");
ProductSubcategory subcategory = adventureWorks.ProductSubcategories
.Single(entity => entity.Name == "Subcategory");
adventureWorks.ProductCategories.DeleteOnSubmit(category);
adventureWorks.ProductSubcategories.DeleteOnSubmit(subcategory);
adventureWorks.SubmitChanges();
}
}
internal static void DeleteWithNoQuery(int subcategoryId)
{
ProductSubcategory subcategory = new ProductSubcategory()
{
ProductSubcategoryID = subcategoryId
};
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.ProductSubcategories.Attach(subcategory, false);
adventureWorks.ProductSubcategories.DeleteOnSubmit(subcategory);
adventureWorks.SubmitChanges();
}
}
internal static void TransactionScope()
{
using (TransactionScope scope = new TransactionScope())
using (AdventureWorks adventureWorks = new AdventureWorks())
using (DbConnection connection = adventureWorks.Connection)
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [Production].[ProductCategory] ([Name]) VALUES (N'Transaction')";
Trace.WriteLine(command.ExecuteNonQuery()); // 1.
}
ProductCategory category = adventureWorks.ProductCategories.Single(entity => entity.Name == "Transaction");
adventureWorks.ProductCategories.DeleteOnSubmit(category);
adventureWorks.SubmitChanges();
scope.Complete();
}
}
internal static void Update()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories.First();
ProductSubcategory subcategory = adventureWorks.ProductSubcategories
.Single(entity => entity.Name == "Subcategory");
Trace.WriteLine(subcategory.Name); // Subcategory.
Trace.WriteLine(subcategory.ProductCategoryID); // 5.
subcategory.Name = "Update"; // Update property.
subcategory.ProductCategory = category; // Update association.
adventureWorks.SubmitChanges();
Trace.WriteLine(subcategory.Name); // Subcategory update.
Trace.WriteLine(subcategory.ProductCategoryID); // 4.
}
}
internal static void Default()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories.First();
Trace.WriteLine(category.Name); // Accessories.
category.Name = "Update";
ProductSubcategory subcategory = adventureWorks.ProductSubcategories.First();
Trace.WriteLine(subcategory.ProductCategoryID); // 1.
subcategory.ProductCategoryID = -1;
try
{
adventureWorks.SubmitChanges();
}
catch (SqlException exception)
{
Trace.WriteLine(exception);
// SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_ProductSubcategory_ProductCategory_ProductCategoryID". The conflict occurred in database "D:\ONEDRIVE\WORKS\DRAFTS\CODESNIPPETS\DATA\ADVENTUREWORKS_DATA.MDF", table "Production.ProductCategory", column 'ProductCategoryID'. The statement has been terminated.
adventureWorks.Refresh(RefreshMode.OverwriteCurrentValues, category, subcategory);
Trace.WriteLine(category.Name); // Accessories.
Trace.WriteLine(subcategory.ProductCategoryID); // 1.
}
}
}