EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers
select Customer;
foreach (var CustomerItem in CustomerItems)
{
Console.WriteLine(string.Format("Customer <{0}> aged <{1}> lives in <{2}> - <{3}>",
(CustomerItem.Title != null) ? CustomerItem.Title : "",
(CustomerItem.Age != null) ? CustomerItem.Age.ToString() : "-",
(CustomerItem.City != null && CustomerItem.City.Title != null) ? CustomerItem.City.Title : "",
(CustomerItem.City != null && CustomerItem.City.Country != null) ? CustomerItem.City.Country : "")
);
}
------------------------------------------------------------------------------------------------------------------------------------------
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");var CustomerItems = from Customer in Customers where Customer.City.Title == "Los Angeles" && Customer.Age > 30 select Customer; foreach (var CustomerItem in CustomerItems){ Console.WriteLine(string.Format("Customer <{0}> aged <{1}> lives in <{2}> - <{3}>", (CustomerItem.Title != null) ? CustomerItem.Title : "", (CustomerItem.Age != null) ? CustomerItem.Age.ToString() : "-", (CustomerItem.City != null && CustomerItem.City.Title != null) ? CustomerItem.City.Title : "", (CustomerItem.City != null && CustomerItem.City.Country != null) ? CustomerItem.City.Country : "") ); } ------------------------------------------------------------------------------------------------------------------------------------------
IEnumerable<IGrouping<CitiesItem, CustomersItem>> CustomersByCity = Ctx.Customers.Where(c => c.Age < 35).GroupBy(c => c.City);foreach (var CustomerCity in CustomersByCity){ CitiesItem CityGroup = CustomerCity.Key as CitiesItem; Console.WriteLine(string.Format("Number of customers aged < 35 living in {0} is {1}", CityGroup.Title, CustomerCity.Count()));}------------------------------------------------------------------------------------------------------------------------------------------
IEnumerable<IGrouping<CitiesItem, CustomersItem>> CustomersByCity = Ctx.Customers.Where(c => c.Age < 35).ToList().GroupBy(c => c.City);foreach (var CustomerCity in CustomersByCity){ CitiesItem CityGroup = CustomerCity.Key as CitiesItem; Console.WriteLine(string.Format("Number of customers aged < 35 living in {0} is {1}", CityGroup.Title, CustomerCity.Count()));}-----------------------------------------------------------------------------------------------------------------------------------------
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");EntityList<OrdersItem> Orders = Ctx.GetList<OrdersItem>("Orders"); var QueryResults = from Customer in Customers join City in Cities on Customer.City.Id equals City.Id join OrderItem in Orders on Customer.Id equals OrderItem.Customer.Id select new { CityName = City.Title, City.Country, Customer.Title, OrderTitle = OrderItem.Title };------------------------------------------------------------------------------------------------------------------------------------------
List<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers").Where(c=>c.City!=null).ToList();List<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities").ToList();List<OrdersItem> Orders = Ctx.GetList<OrdersItem>("Orders").Where(o => o.Customer != null).ToList(); var QueryResults = from Customer in Customers join City in Cities on Customer.City.Id equals City.Id join OrderItem in Orders on Customer.Id equals OrderItem.Customer.Id select new { CityName = City.Title, City.Country, Customer.Title, OrderTitle = OrderItem.Title };-----------------------------------------------------------------------------------------------------------------------------------------
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities"); var QueryResults = from Customer in Customers join City in Cities on Customer.City.Id equals City.Id select new { CityName=City.Title, City.Country, Customer.Title }; var Results = QueryResults.ToList();if (Results.Count > 0){ Results.ForEach(cc => Console.WriteLine("Customer <{0}> lives in <{1}> - <{2}>", (cc.Title != null) ? cc.Title : "-", (cc.CityName != null) ? cc.CityName : "-", (cc.Country != null) ? cc.Country : "-"));}else{ Console.WriteLine("No data found!");}-----------------------------------------------------------------------------------------------------------------------
SPList CustomerList = Web.Lists["Customers"];SPQuery CustomerCityQuery = new SPQuery();CustomerCityQuery.Joins = "<Join Type='INNER' ListAlias='Cities'>" + "<Eq>" + "<FieldRef Name='City' RefType='Id' />" + "<FieldRef List='Cities' Name='ID' />" + "</Eq>" + "</Join>";StringBuilder ProjectedFields = new StringBuilder();ProjectedFields.Append("<Field Name='CityTitle' Type='Lookup' List='Cities' ShowField='Title' />");ProjectedFields.Append("<Field Name='CityCountry' Type='Lookup' List='Cities' ShowField='Country' />");CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);foreach (SPListItem Result in Results){ SPFieldLookupValue CityTitle = new SPFieldLookupValue(Result["CityTitle"].ToString()); SPFieldLookupValue CityCountry = new SPFieldLookupValue(Result["CityCountry"].ToString()); Console.WriteLine(string.Format("Customer {0} lives in {1} - {2}", Result.Title, CityTitle.LookupValue, CityCountry.LookupValue));} ---------------------------------------------------------------------------------------------------------------------------------------
SPList CustomerList = Web.Lists["Orders"];SPQuery CustomerCityQuery = new SPQuery();CustomerCityQuery.Joins = "<Join Type='INNER' ListAlias='Customers'>" + "<Eq>" + "<FieldRef Name='Customer' RefType='Id' />" + "<FieldRef List='Customers' Name='ID' />" + "</Eq>" + "</Join>" + "<Join Type='INNER' ListAlias='Cities'>" + "<Eq>" + "<FieldRef List='Customers' Name='City' RefType='Id' />" + "<FieldRef List='Cities' Name='ID' /> " + "</Eq>" + "</Join>"; StringBuilder ProjectedFields = new StringBuilder();ProjectedFields.Append("<Field Name='CityTitle' Type='Lookup' List='Cities' ShowField='Title' />");ProjectedFields.Append("<Field Name='CityCountry' Type='Lookup' List='Cities' ShowField='Country' />");ProjectedFields.Append("<Field Name='CustomerTitle' Type='Lookup' List='Customers' ShowField='Title' />");ProjectedFields.Append("<Field Name='CustomerAge' Type='Lookup' List='Customers' ShowField='Age' />");CustomerCityQuery.ProjectedFields = ProjectedFields.ToString(); SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);foreach (SPListItem Result in Results){ SPFieldLookupValue CityTitle = new SPFieldLookupValue((Result["CityTitle"] != null) ? Result["CityTitle"].ToString() : ""); SPFieldLookupValue CityCountry = new SPFieldLookupValue((Result["CityCountry"] != null) ? Result["CityCountry"].ToString() : ""); SPFieldLookupValue CustomerTitle = new SPFieldLookupValue((Result["CustomerTitle"] != null) ? Result["CustomerTitle"].ToString() : ""); SPFieldLookupValue CustomerAge = new SPFieldLookupValue((Result["CustomerAge"] != null) ? Result["CustomerAge"].ToString() : ""); Console.WriteLine(string.Format("Customer {0} living in {1} - {2} has ordered #{3}", CustomerTitle.LookupValue, CityTitle.LookupValue, CityCountry.LookupValue, Result.Title));} ----------------------------------------------------------------------------------------------------------------------------------------
static void AddCustomer(string CustomerName){ try { EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers"); CustomersItem NewCustomer = new CustomersItem(); NewCustomer.Title = CustomerName; Customers.InsertOnSubmit(NewCustomer); Ctx.SubmitChanges(); Console.WriteLine("Customer added"); } catch (SPDuplicateValuesFoundException) { Console.WriteLine("The customer was not added because it would have created a duplicate entry"); } catch (ChangeConflictException ConflictException) { Console.WriteLine("The customer was not added because a conflict occured:" + ConflictException.Message); } catch (Exception Ex) { Console.WriteLine("The customer was not added because the following error occured:" + Ex.Message); } }-----------------------------------------------------------------------------------------------------------------------------------------
try{ EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers"); for(int i=0;i<10;i++) { CustomersItem NewCustomer = new CustomersItem(); NewCustomer.Title = String.Format("Customer {0}",i.ToString()); Customers.InsertOnSubmit(NewCustomer); } Ctx.SubmitChanges(); Console.WriteLine("Customer added");}catch (SPDuplicateValuesFoundException){ Console.WriteLine("The customer was not added because it would have created a duplicate entry");}catch (ChangeConflictException ConflictException){ Console.WriteLine("The customer was not added because a conflict occured:" + ConflictException.Message);}catch (Exception Ex){ Console.WriteLine("The customer was not added because the following error occured:" + Ex.Message);} ------------------------------------------------------------------------------------------------------------------------------------------
static void UpdateCustomer(int CustomerId, string CityName){ EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities"); var CitiesItm = from City in Cities where City.Title == CityName select City; CitiesItem CityItem = null; foreach (var Cit in CitiesItm) CityItem = Cit; if (CityItem == null) { Console.WriteLine("City not found"); Environment.Exit(0); } EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers"); var CustomerItems = from Customer in Customers where Customer.Id == CustomerId select Customer; List<CustomersItem> Results = CustomerItems.ToList(); if (Results.Count > 0) { try { Results.ForEach(CustomerItem => CustomerItem.City = CityItem); Ctx.SubmitChanges(); Console.WriteLine("Customer {0} updated with City {1}!", CustomerId.ToString(), CityName); } catch (ChangeConflictException ConflictException) { Console.WriteLine("The customer was not updated because a conflict occured:" + ConflictException.Message); } catch (Exception Ex) { Console.WriteLine("The customer was not updated because the following error occured:" + Ex.Message); } } else { Console.WriteLine("Customer {0} not found!", CustomerId.ToString()); } }------------------------------------------------------------------------------------------------------------------------------------------
static void DeleteCustomer(int CustomerId){ EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers"); var QueryResults = from Customer in Customers where Customer.Id == CustomerId select Customer; List<CustomersItem> ReturnedCustomers = QueryResults.ToList(); if (ReturnedCustomers.Count > 0) { try { Customers.DeleteOnSubmit(ReturnedCustomers[0]); Ctx.SubmitChanges(); } catch (ChangeConflictException ConflictException) { Console.WriteLine("The customers were not updated because a conflict occured:" + ConflictException.Message); } catch (Exception Ex) { Console.WriteLine("The customers were not updated because the following error occured:" + Ex.Message); } } else { Console.WriteLine("Customer {0} not found", CustomerId.ToString()); }}------------------------------------------------------------------------------------------------------------------------------------------
static void UpdateAllCustomers(string CityName){ EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities"); var CitiesItm = from City in Cities where City.Title == CityName select City; CitiesItem CityItem = null; foreach (var Cit in CitiesItm) CityItem = Cit; if (CityItem == null) { Console.WriteLine("City not found"); Environment.Exit(0); } EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers"); var CustomerItems = from Customer in Customers select Customer; List<CustomersItem> Results = CustomerItems.ToList(); if (Results.Count > 0) { try { Results.ForEach(CustomerItem => CustomerItem.City = CityItem); Ctx.SubmitChanges(); Console.WriteLine("All the customer were updated with City {0}!", CityName); } catch (ChangeConflictException ConflictException) { Console.WriteLine("The customers were not updated because a conflict occured:" + ConflictException.Message); } catch (Exception Ex) { Console.WriteLine("The customers were not updated because the following error occured:" + Ex.Message); } } else { Console.WriteLine("No customer currently exist"); }}------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment