Codemuch 01010101011101101010110001...

The 6th page of the OfficeTest General Tests tutorial covers manually creating and linking datasets and assigning them to a GridView. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

This topic is more just for interest sake and to provide a simple example of manually manipulating datasets. I've seen other discussions on other sites related to this topic but honestly I don't see a lot of use for this functionality.

6. Manually creating and linking datasets.

The HTML code for this example is very simple. It just contains a single simple GridView. We won't waste any additional time reviewing the HTML. The purpose here is to show how datasets can be loaded for separate tables and then a new result set built which combines the relationship data in memory. Really there are many ways to achieve the end result of this example and most are easier and more obvious than this method. The idea here however is to use the internal functionality already provided in the DataSet object.

Populate
The Populate() method loads the entire results of the Employee table and then the entire results of the JobPosition table. The data is loaded into separate named views within the dataset. Once the data is loaded the Relations functionality of the dataset is used to create a merged dataset which contains the Employee data with the JobPosition.JobTitle added in place of JobPositionID.

NOTE: There is a typo in the current code. The JobPositionID in the JoinedTables was suppose to be the EmployeeID. This doesn't really change anything or cause any problems.

Loading the data


DataSet myDataSet = new DataSet();

string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
FbConnection con = new FbConnection(connectionString);
try {
  con.Open();
  FbCommand cmd = new FbCommand("SELECT \"EmployeeID\", \"FirstName\", \"LastName\", \"HireDT\", \"JobPositionID\" FROM \"Employee\"", con);
  FbDataReader reader = cmd.ExecuteReader();
  myDataSet.Load(reader,LoadOption.OverwriteChanges,new string[]{"Employee"});
  reader.Close();

  cmd = new FbCommand("SELECT \"JobPositionID\",\"JobTitle\",\"JobDesc\" FROM \"JobPosition\"", con);
  reader = cmd.ExecuteReader();
  myDataSet.Load(reader, LoadOption.OverwriteChanges, new string[] { "JobPosition" });
  reader.Close();
} finally {
  con.Close();
}
   

The code snippet shown above creates the DataSet (myDataSet) which will stored all the data. It then used FbCommand and FbDataReader to select the data from the Employee and JobPosition tables. myDataSet.Load is used to load the entire results of the reader into the dataset. Notice that the table name is used for the 3rd parameter to the Load method. This assigns an alias to that view of the corresponding data. In this case all the Employee table data is in the "Employee" view in the DataSet. All the JobPosition data is stored separately in the same dataset but under the "JobPosition" alias.
DataTable employee = myDataSet.Tables["Employee"];
DataColumn employeeJobPositionID = employee.Columns["JobPositionID"];

DataTable jobPosition = myDataSet.Tables["JobPosition"];
DataColumn jobPositionJobPositionID = jobPosition.Columns["JobPositionID"];

myDataSet.Relations.Add("JobPositionEmployee", jobPositionJobPositionID, employeeJobPositionID);
   

Here a pointer to the Employee table within the dataset is retrieved and used to get a pointer to the column definition related to the foreign key to the JobPosition table. Likewise a pointer to the JobPosition table is retrieved and used to get a pointer to the column definition for the primary key for the JobPosition table.

Next a relation is added to the dataset linking the JobPosition.JobPositionID primary key to the Employee.JobPositionID foreign key column. An alias ("JobPositionEmployee") is assigned to the relationship.

myDataSet.Tables.Add(new DataTable("JoinedTables"));
myDataSet.Tables["JoinedTables"].Columns.Add("EmployeeID");
myDataSet.Tables["JoinedTables"].Columns.Add("FirstName");
myDataSet.Tables["JoinedTables"].Columns.Add("LastName");
myDataSet.Tables["JoinedTables"].Columns.Add("HireDT");
myDataSet.Tables["JoinedTables"].Columns.Add("JobTitle");
   

Here we manually add a new table with alias ("JoinedTables") to the our existing dataset. The columns must also be manually created. Note I changed the first column back to EmployeeID from JobPositionID since it was a typo. This column structure is basically the column structure for the Employee table except we removed JobPositionID and replaced it with JobTitle.
foreach(DataRow row in myDataSet.Tables["JobPosition"].Rows) {
  DataRow[] joinRows = row.GetChildRows("JobPositionEmployee");
  foreach (DataRow joinRow in joinRows) {
    DataRow newRow = myDataSet.Tables["JoinedTables"].NewRow();
    newRow["EmployeeID"] = joinRow["EmployeeID"];
    newRow["FirstName"] = joinRow["FirstName"];
    newRow["LastName"] = joinRow["LastName"];
    newRow["HireDT"] = joinRow["HireDT"];
    newRow["JobTitle"] = row["JobTitle"];
    myDataSet.Tables["JoinedTables"].Rows.Add(newRow);
  }
}
   

This loop goes over each row in the JobPosition table we loaded from the database and checks for child rows created in the relationship "JobPositionEmployee" we built earlier. One Employee table row should exist, as a child of the current JobPosition row, in the relationship "JobPositionEmployee" for each Employee using that JobPosition. Since the child rows are from the Employee table the joinRow contains all the columns needed for the employee data.

For each matching child row we simply manually assign the data to a new row we created for the "JoinedTables" table we created in the dataset. The "JobTitle" must be retrieved from the "JobPosition" table row since the child rows are from the Employee table which don't have the JobTitle.

gvEmployees.DataSource = myDataSet.Tables["JoinedTables"].DefaultView;
gvEmployees.DataBind();
   

Finally the new "JoinedTables" table in the dataset is assigned as the data source to our GridView and DataBind is called to force it to refresh.

Summary
This code is really very straight forward except perhaps the follow:


myDataSet.Relations.Add("JobPositionEmployee", jobPositionJobPositionID, employeeJobPositionID);
   

The relations functionality is setup to work based on the master/primarykey table. This means you build the relationship so that Employee data is added as children of the JobPosition data not the other way around.

As I mentioned before there are many ways the same results could be achieved. We could have simply selected the data in that format from the database to start with. We could also just manually loop through the Employees and record by record lookup the corresponding JobPosition. In this example I only wanted to demonstrate how the DataSet Relations functionality could be used. In some cases it may also improve performance. If you already have Employee and JobPosition data in cached DataSets and don't want to retrieve a new joined result from the database this method might prove useful and faster.

See next Tutorial...Employee: Select Procedure web page which calls a stored procedure with a result set directly and through a select SQL query.