NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Setting up Table and Column Mappings

In the examples in the previous sections, we used the source table parameter to identify the table to fill or to pull changes from. Additionally, when we allowed the DataSetCommand to create the table on the fly, we accepted the schema defined in the database.

We can change these default behaviors by creating a tablemapping and/or columnmappings. The following example shows creating a tablemapping using "Table" as the source table name and "MyAuthors" as the DataTable name.

[VB]

workDSCMD.TableMappings.Add("Table", "MyAuthors")

[C#]

workDSCMD.TableMappings.Add("Table", "MyAuthors");

What are tablemappings and what are they used for? A tablemapping provides a master mapping between the data returned from the table in the database, and the DataTable inside the DataSet.

In the above example, we've created a virtual link between the table in the database (specified in the select and given a default name of "Table") and the table in the DataSet called "MyAuthors."

In the earlier samples, when we passed in the source table name, we created a tablemapping on the fly. Its as if we actually used the source table for both parameters of the TableMappings.Add method. Only the tablemapping that we create is never added to the tablemappings collection.

So what's the advantage of creating this tablemapping? The big advantage is in the ability to change the names of the columns you work with from their names in the database. In the examples below, we map the columns from the Authors table of the Pubs database to a set of more user-friendly names in the DataSet. Since we've created the mapping, the DataSet knows how to match these back together when we update the table.

[VB]

With workDSCMD.TableMappings.Item(0).ColumnMappings
   .Add("au_id", "AuthorID")
   .Add("au_lname", "lastname")
   .Add("au_fname", "firstname")
   .Add("phone", "phone")
   .Add("address", "address")
   .Add("city", "city")
   .Add("state", "state")
   .Add("zip", "zip")
   .Add("contract", "contract")
End With

[C#]

workDSCMD.TableMappings[0].ColumnMappings.Add( "au_id", "AuthorID");
workDSCMD.TableMappings[0].ColumnMappings.Add( "au_lname", "lastname");
workDSCMD.TableMappings[0].ColumnMappings.Add( "au_fname", "firstname");
workDSCMD.TableMappings[0].ColumnMappings.Add( "phone", "phone");
workDSCMD.TableMappings[0].ColumnMappings.Add( "address", "address");
workDSCMD.TableMappings[0].ColumnMappings.Add( "city", "city");
workDSCMD.TableMappings[0].ColumnMappings.Add( "state", "state");
workDSCMD.TableMappings[0].ColumnMappings.Add( "zip", "zip");
workDSCMD.TableMappings[0].ColumnMappings.Add( "contract", "contract");

For the ADO and SQL DataSetCommands, When the Update or FillDataSet method is called without a source table, the DataSetCommand looks for a mapping with a source table named "Table." It then takes the DataSet table name from the mapping and uses it to either find the table in the DataSet object's table collection, or add a new table to the collection using that value for its name.

[VB]

Dim myDS As DataSet = New DataSet()
Dim myConnection As ADOConnection = New ADOConnection _
("Provider=SQLOLEDB;Data Source=Delphi;Initial Catalog=pubs;user id=sa")
Dim workDSCMD As ADODataSetCommand = New ADODataSetCommand _
("SELECT * FROM Authors", myConnection)
workDSCMD.TableMappings.Add("Table", "Authors")
With workDSCMD.TableMappings(0).ColumnMappings
   .Add("au_id", "AuthorID")
   .Add("au_lname", "lastname")
   .Add("au_fname", "firstname")
   .Add("phone", "phone")
   .Add("address", "address")
   .Add("city", "city")
   .Add("state", "state")
   .Add("zip", "zip")
   .Add("contract", "contract")
End With
workDSCMD.FillDataSet(myDS)

[C#]

DataSet myDS = new DataSet();
ADOConnection myConnection = new ADOConnection("Provider=SQLOLEDB;Data Source=Delphi;Initial Catalog=pubs;user id=sa");
ADODataSetCommand workDSCMD = new ADODataSetCommand("SELECT * FROM Authors", myConnection);
   workDSCMD.TableMappings.Add("Table", "Authors");      
   workDSCMD.TableMappings[0].ColumnMappings.Add( "au_id", "AuthorID");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "au_lname", "lastname");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "au_fname", "firstname");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "phone", "phone");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "address", "address");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "city", "city");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "state", "state");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "zip", "zip");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "contract", "contract");
workDSCMD.FillDataSet(myDS);

In example above, we create a tablemapping and make it the default by naming it "Table." When we call the Update or FillDataSet commands without a source table name, the DataSetCommand will use this mapping.

In more advanced situations, you may decided that you want the same DataSetCommand to support loading different tables with different mappings. We can do this simply by adding additional tablemappings.

In the example below, we create a tablemapping with a source table name of "bob" and a DataSet table name of "King."

[VB]

workDSCMD.TableMappings.Add("bob", "King")
With workDSCMD.TableMappings(0).ColumnMappings
   .Add("au_id", "AuthorID")
   .Add("au_lname", "lastname")
   .Add("au_fname", "firstname")
   .Add("phone", "phone")
   .Add("address", "address")
   .Add("city", "city")
   .Add("state", "state")
   .Add("zip", "zip")
   .Add("contract", "contract")
End With
workDSCMD.FillDataSet(myDS, "bob")

[C#]

   workDSCMD.TableMappings.Add("bob", "King");      
   workDSCMD.TableMappings[0].ColumnMappings.Add( "au_id", "AuthorID");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "au_lname", "lastname");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "au_fname", "firstname");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "phone", "phone");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "address", "address");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "city", "city");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "state", "state");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "zip", "zip");
   workDSCMD.TableMappings[0].ColumnMappings.Add( "contract", "contract");
   workDSCMD.FillDataSet(myDS, "bob");

Call the FillDataSet method, passing in an instance of a DataSet and a reference to the source table. The DataSetCommand looks in the tablemappings collection to see if a mapping with that source table name exists. If so, it uses that mapping to build and fill the table. But if the source table name doesn't exist in the collection, it will build the TableMappings on the fly.

In the example above we created a mapping that will cause the records brought back from the Select command to be mapped into a table called "King."