Data Relations in XSD Schema

The most common way to display our data sets is in two grids in mode master-detail

In this example we will focus on this set of data of a company represented by the next diagram

We will display related data between Employees and Projects in two different grids, synchronizing both of them

To achieve this we will create a view with the data corresponding to the Projects of the Employee, and two stored procs, one for retrieve data from our Employees table, and the other one from our Projects view

    CREATE VIEW VIEW_PROJECTS AS
        SELECT E.IdEmployee, P.Name AS Project, P.Description, 
               EP.AssignmentDate, P.DueDate, C.Name AS Customer 
        FROM C_EMPLOYEES E 
             JOIN C_EMPLOYEES_PROJECTS EP 
               ON E.IdEmployee = EP.IdEmployee
             JOIN C_PROJECTS P 
               ON EP.IdProject = P.IdProject 
             JOIN C_CUSTOMERS C
               ON P.IdCustomer = C.IdCustomer
    GO

    CREATE PROCEDURE SELECT_EMPLOYEES
    AS BEGIN
        SELECT * FROM C_EMPLOYEES
        ORDER BY FirstName ASC, LastName ASC
    END
    GO

    CREATE PROCEDURE SELECT_PROJECT_DATA
    AS BEGIN
        SELECT * FROM VIEW_PROJECTS
    END
    GO

Now we will generate the XSD (XML Schema Definition) of our DataSet

The process includes setting up the connection, and adding two TableAdapters from the Toolbox

We include our database objects

And now we add the first TableAdapter, for Employees

We do the same process for adding the View of Projects, choosing the stored procedure SELECT_PROJECT_DATA

The last step is set up the Relation between tables

And this is the schema once complete

The XML Definition of the Relation in our Schema is the next

  <xs:annotation>
    <xs:appinfo>
      <msdata:Relationship 
   name="EMPLOYEES_PROJECTS_Relation" 
   msdata:parent="SELECT_EMPLOYEES" 
   msdata:child="SELECT_PROJECT_DATA" 
   msdata:parentkey="IdEmployee" 
   msdata:childkey="IdEmployee" 
   msprop:Generator_UserChildTable="SELECT_PROJECT_DATA" 
   msprop:Generator_ChildPropName="GetSELECT_PROJECT_DATARows" 
   msprop:Generator_ParentPropName="SELECT_EMPLOYEESRow" 
   msprop:Generator_UserRelationName="EMPLOYEES_PROJECTS_Relation" 
   msprop:Generator_RelationVarName="relationEMPLOYEES_PROJECTS_Relation" 
   msprop:Generator_UserParentTable="SELECT_EMPLOYEES" 
   />
    </xs:appinfo>
  </xs:annotation>

Finally, in our code, we will program the next method to initialize the grids

    private void InitializeDataSet()
    {
        try
        {
            DataSetCompany dsCompany = new DataSetCompany();                

            SELECT_EMPLOYEESTableAdapter daEmployees = new SELECT_EMPLOYEESTableAdapter();
            daEmployees.Fill(dsCompany.SELECT_EMPLOYEES);

            SELECT_PROJECT_DATATableAdapter daProjects = new SELECT_PROJECT_DATATableAdapter();
            daProjects.Fill(dsCompany.SELECT_PROJECT_DATA);

            gridEmployees.DataSource = dsCompany;
            gridEmployees.DataMember = "SELECT_EMPLOYEES";

            gridProjects.DataSource = dsCompany;
            gridProjects.DataMember = "SELECT_EMPLOYEES.EMPLOYEES_PROJECTS_Relation";

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

The result is as follows

<METHOD SOFTWARE © 2012>

LINQ and Lambda Expressions

LINQ is a powerful tool, which combined with lambda expressions becomes in many cases the best way to querying our data sets

In this example we have a database with data of a business and we have defined the next schema of LINQ to SQL Classes

Now we have fully typed our classes

These are the variables and methods exposed in this example, the approach of this example is fill the data sets with Linq queries, and since that moment make different operations on the obtained lists with lambda expressions, instead of keep querying the data sets through the context

     
    static List<B_CUSTOMER> AllCustomers;
    static List<B_SALE> Sales;
    static List<B_PRODUCT> Products;
    static List<B_PROVIDER> Providers;
    static List<B_PROVIDERS_PRODUCT> ProvidersProducts;
    static List<B_SALE> HighestSales;

    static void Main(string[] args)
    {
        // will query the context
        GetBusinessData();
        // will query the collections
        GetPurchases(); 
        GetCustomersWithoutSales(); 
        GetHighestSales();
        GetProvidersForHighestSales();
        Console.ReadKey();
    }
    

The first method we implement is GetBusinessData, which fills our typed lists

     
    using (BusinessClassesDataContext context = new BusinessClassesDataContext())
    {
        AllCustomers = (from cust in context.B_CUSTOMERs
                        orderby cust.FirstName ascending, cust.LastName ascending
                        select cust).ToList<B_CUSTOMER>();

        Sales = (from sale in context.B_SALEs
                    orderby sale.Cuantity descending
                    select sale).ToList<B_SALE>();

        Products = (from prod in context.B_PRODUCTs
                    orderby prod.Description ascending
                    select prod).ToList<B_PRODUCT>();

        Providers = (from prov in context.B_PROVIDERs
                        orderby prov.Name
                        select prov).ToList<B_PROVIDER>();

        ProvidersProducts = (from provprod in context.B_PROVIDERS_PRODUCTs
                                select provprod).ToList<B_PROVIDERS_PRODUCT>();

    }

This is the implementation of the GetPurchases method:
We select from the set AllCustomers, those who are in the Sales set, and after expose them in hierarchy with their purchases

     
    Console.WriteLine("\nPurchases:");

    foreach (B_CUSTOMER customer in Sales.Select(sd => sd.B_CUSTOMER)
        .Distinct())
    {
        Console.WriteLine(customer.FullName + " : ");
        foreach (string purchase in Sales
            .Where(s => s.B_CUSTOMER == customer)
            .Select(sd => sd.B_PRODUCT.Description + " : " +
                sd.Cuantity.ToString() + " unit(s) " +
                sd.Price.ToString() + " $"))
            Console.WriteLine(" - " + purchase);
    }
    

This is the method GetCustomersWithoutSales, which performs a fast set operation to discard from AllCustomers those who don't have made purchases

     
    List<B_CUSTOMER> customersWithoutSales = AllCustomers
        .Except(Sales.Select(sd => sd.B_CUSTOMER).Distinct())
        .ToList<B_CUSTOMER>();

    Console.WriteLine("\nCustomers without sales:");
    foreach (B_CUSTOMER customer in customersWithoutSales)
        Console.WriteLine(" - " + customer.FullName);

The implementation of the method GetHighestSales is as follows, notice that the condition might be dynamic

     
    HighestSales = Sales
        .Where(s => s.Price >= 400)
        .OrderBy(s => s.Price).ToList();

    Console.WriteLine("\nHighest Sales:");
    foreach (B_SALE saleData in HighestSales)
        Console.WriteLine(" - " + saleData.B_CUSTOMER.FullName + " : " +
            saleData.B_PRODUCT.Description + " : " + 
            saleData.Cuantity + " unit(s) " +
            saleData.Price.ToString() + " $");

Finally we have defined the method GetProvidersForHighestSales, which gets those providers which provide of the products with highest sales

     
    Console.WriteLine("\nProviders of products with highest sales:");
    foreach (B_PRODUCT product in HighestSales.Select(ht => ht.B_PRODUCT))
    {
        Console.WriteLine("- Providers of " + product.Description);
        foreach (B_PROVIDER provider in ProvidersProducts
            .Where(pr => product.IdProduct == pr.IdProduct)
            .OrderBy(pr => pr.B_PRODUCT.Description)
            .Select(pr => pr.B_PROVIDER).Distinct())
            Console.WriteLine("   - " + provider.Name);
    }
    

This is the output of our application, by combining LINQ and lambdas, we did not have to make a new Linq instruction for each query, taking adventage of the potential of Generics

<METHOD SOFTWARE © 2012>