Geek Methods ®: 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

Posts

Sunday, September 23, 2012

Data Relations in XSD Schema