Search This Blog

Thursday, June 28, 2012

Simples queries

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");
        }
}
------------------------------------------------------------------------------------------------------------------------------------------
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)