public string addProduct(string name, string productNumber, string color, short safetyStockLevel, decimal standardCost, string size, decimal?weight)
{
Product p = new Product();
p.ProductID = random.Next(10000);
p.Name = name;
p.ProductNumber = productNumber;
p.Color = color;
p.SafetyStockLevel = safetyStockLevel;
p.StandardCost = standardCost;
p.Size = size;
p.Weight = weight;
dataContext.Product.InsertOnSubmit(p);
try
{
dataContext.SubmitChanges();
}
catch (Exception e)
{
dataContext.Product.DeleteOnSubmit(p);
return("Product already exists in database");
}
return("Product sucessfuly added");
}
static void Main(string[] args)
{
string connString = "Server=(LocalDb)\\MSSQLLocalDb;Integrated Security=true;database=AdventureWorksDW2014";
DataContext context = new DataContext(connString);
var tableProd = context.GetTable <Product>();
var products = from prod in tableProd
where prod.Color == "Black"
select prod;
foreach (Product p in products)
{
Console.WriteLine(p.EnglishProductName);
}
Product product = products.First();
product.Color = "Red";
context.SubmitChanges();
AdventureWorksDataContext awdc = new AdventureWorksDataContext(connString);
var categories = awdc.DimProductCategories;
foreach (DimProductCategory pc in categories)
{
Console.WriteLine(pc.EnglishProductCategoryName);
foreach (var sub in pc.DimProductSubcategories)
{
Console.WriteLine(sub.EnglishProductSubcategoryName);
foreach (var pd in sub.DimProducts)
{
Console.WriteLine(" {0}", pd.EnglishProductName);
}
}
}
var query = from cat in categories
from sub in cat.DimProductSubcategories
from prod in sub.DimProducts
where cat.EnglishProductCategoryName.Contains("Bikes")
select prod;
foreach (var p in query)
{
Console.WriteLine("{0}", p.EnglishProductName);
}
awdc.DimProducts.First().Color = "Dark Red";
awdc.SubmitChanges();
DimProductCategory newCat = new DimProductCategory();
newCat.EnglishProductCategoryName = "New Category 1";
newCat.SpanishProductCategoryName = "Nueva Categoria 1";
newCat.FrenchProductCategoryName = "Nouvelle Categorie 1";
awdc.DimProductCategories.InsertOnSubmit(newCat);
awdc.SubmitChanges();
}
public void CreateReview(ProductReview review)
{
review.ReviewerName = "Automat";
review.ReviewDate = DateTime.Now;
review.EmailAddress = "*****@*****.**";
review.ModifiedDate = DateTime.Now;
db.ProductReview.InsertOnSubmit(review);
db.SubmitChanges();
}
public void CreateProduct(Product product)
{
product.rowguid = Guid.NewGuid();
product.SafetyStockLevel = 123;
product.ReorderPoint = 123;
product.ModifiedDate = DateTime.Now;
product.SellStartDate = DateTime.Now;
db.Product.InsertOnSubmit(product);
db.SubmitChanges();
}
public static string AddProduct(Product product)
{
dataContext.Product.InsertOnSubmit(product);
try
{
dataContext.SubmitChanges();
}
catch (Exception e)
{
dataContext.Product.DeleteOnSubmit(product);
return("Product already exists");
}
return("Product added");
}
public ActionResult Create([Bind(Include = "FirstName,LastName,Title")] Person person)
{
if (ModelState.IsValid)
{
using (var pp = new AdventureWorksDataContext())
{
person.ModifiedDate = DateTime.Now;
person.PersonType = "EM";
person.BusinessEntityID = 1;
var businessEntity = new BusinessEntity();
businessEntity.ModifiedDate = DateTime.Now;
businessEntity.Person = person;
businessEntity.rowguid = Guid.NewGuid( );
person.BusinessEntity = businessEntity;
pp.BusinessEntities.InsertOnSubmit(businessEntity);
pp.Persons.InsertOnSubmit(person);
pp.SubmitChanges();
}
return(RedirectToAction("Index"));
}
return(View(person));
}
//<Snippet6>
public static void Delete(int salesOrderID, int salesOrderDetailID)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
SalesOrderDetail SalesOrderDetail =
(from SalesOrderDetails in dataContext.SalesOrderDetails.AsEnumerable().Take(20)
where SalesOrderDetails.SalesOrderID == salesOrderID &&
SalesOrderDetails.SalesOrderDetailID == salesOrderDetailID
select SalesOrderDetails).Single();
dataContext.SalesOrderDetails.DeleteOnSubmit(SalesOrderDetail);
dataContext.SubmitChanges();
}
//</Snippet8>
public static SalesOrderHeader Create(SalesOrderHeader newSalesOrder)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
SalesOrderHeader salesOrder = new SalesOrderHeader();
// salesOrder.SalesOrderID = newSalesOrder.SalesOrderID;
salesOrder.ContactID = newSalesOrder.ContactID;
dataContext.SalesOrderHeaders.InsertOnSubmit(salesOrder);
dataContext.SubmitChanges();
return(salesOrder);
}
private void InsertHistory(DependentTransaction dt,
SalesOrderDetail salesDetail)
{
try
{
using (AdventureWorksDataContext dc =
new AdventureWorksDataContext())
{
//use the dependent transaction if there is one,
//or suppress a transaction
using (TransactionScope scope = (dt != null ?
new TransactionScope(dt) :
new TransactionScope(TransactionScopeOption.Suppress)))
{
var historyRow = new TransactionHistory();
historyRow.ProductID = salesDetail.ProductID;
historyRow.ModifiedDate = DateTime.Now;
historyRow.Quantity = salesDetail.OrderQty;
historyRow.TransactionDate = salesDetail.ModifiedDate;
historyRow.TransactionType = 'S';
historyRow.ReferenceOrderID = salesDetail.SalesOrderID;
historyRow.ReferenceOrderLineID
= salesDetail.SalesOrderDetailID;
dc.TransactionHistories.InsertOnSubmit(historyRow);
dc.SubmitChanges();
Console.WriteLine(
"Product {0}: Added history for Qty of {1} ",
salesDetail.ProductID, salesDetail.OrderQty);
scope.Complete();
}
}
}
finally
{
//the DependentTransaction must be completed otherwise
//the ambient transaction will block on complete
if (dt != null)
{
dt.Complete();
dt.Dispose();
}
}
}
private void UpdateInventory(DependentTransaction dt,
SalesOrderDetail salesDetail)
{
try
{
using (AdventureWorksDataContext dc =
new AdventureWorksDataContext())
{
//use the dependent transaction if there is one,
//or suppress the creation of a new transaction
using (TransactionScope scope = (dt != null ?
new TransactionScope(dt) :
new TransactionScope(TransactionScopeOption.Suppress)))
{
var inventoryRow =
(from pi in dc.ProductInventories
where pi.ProductID == salesDetail.ProductID &&
pi.LocationID == 7 //finished goods storage
select pi).SingleOrDefault();
if (inventoryRow != null)
{
inventoryRow.Quantity -= salesDetail.OrderQty;
inventoryRow.ModifiedDate = DateTime.Now;
Console.WriteLine(
"Product {0}: Reduced by {1}",
inventoryRow.ProductID, salesDetail.OrderQty);
dc.SubmitChanges();
}
scope.Complete();
}
}
}
finally
{
//the DependentTransaction must be completed otherwise
//the ambient transaction will block on complete
if (dt != null)
{
dt.Complete();
dt.Dispose();
}
}
}
// If your activity returns a value, derive from CodeActivity<TResult>
// and return the value from the Execute method.
protected override void Execute(CodeActivityContext context)
{
SalesOrderDetail salesDetail = SalesDetail.Get(context);
using (AdventureWorksDataContext dc = new AdventureWorksDataContext())
{
var historyRow = new TransactionHistory();
historyRow.ProductID = salesDetail.ProductID;
historyRow.ModifiedDate = DateTime.Now;
historyRow.Quantity = salesDetail.OrderQty;
historyRow.TransactionDate = salesDetail.ModifiedDate;
historyRow.TransactionType = 'S';
historyRow.ReferenceOrderID = salesDetail.SalesOrderID;
historyRow.ReferenceOrderLineID = salesDetail.SalesOrderDetailID;
dc.TransactionHistories.InsertOnSubmit(historyRow);
dc.SubmitChanges();
Console.WriteLine("Product {0}: Added history for Qty of {1} ", salesDetail.ProductID, salesDetail.OrderQty);
}
}
//</Snippet4>
//<Snippet5>
public static void Update(Contact contact)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
var contactToUpdate = (from contacts in dataContext.Contacts
where contacts.ContactID == contact.ContactID
select contacts).Single();
contactToUpdate.FirstName = contact.FirstName;
contactToUpdate.LastName = contact.LastName;
contactToUpdate.EmailAddress = contact.EmailAddress;
contactToUpdate.Phone = contact.Phone;
contactToUpdate.EmailPromotion = contact.EmailPromotion;
contactToUpdate.NameStyle = contact.NameStyle;
contactToUpdate.PasswordHash = contact.PasswordHash;
contactToUpdate.PasswordSalt = contact.PasswordSalt;
contactToUpdate.ModifiedDate = DateTime.Now;
contactToUpdate.rowguid = Guid.NewGuid();
dataContext.SubmitChanges();
}
//</Snippet3>
//<Snippet4>
public static Contact Create(Contact newContact)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
Contact contact = new Contact();
contact.FirstName = newContact.FirstName;
contact.LastName = newContact.LastName;
contact.EmailAddress = newContact.EmailAddress;
contact.Phone = newContact.Phone;
contact.EmailPromotion = newContact.EmailPromotion;
contact.NameStyle = newContact.NameStyle;
contact.PasswordHash = newContact.PasswordHash;
contact.PasswordSalt = newContact.PasswordSalt;
contact.ModifiedDate = DateTime.Now;
contact.rowguid = Guid.NewGuid();
dataContext.Contacts.InsertOnSubmit(contact);
dataContext.SubmitChanges();
return(contact);
}