Mapping Tables and Columns

In Chapter 3, "Introduction to Structured Query Language (SQL)," you learned that the AS keyword is used to specify an alias for a table or column. The following example uses the AS keyword to alias the CustomerID column as MyCustomer and also alias the Customers table as Cust:

 SELECT CustomerID AS MyCustomer, CompanyName, Address FROM Customers AS Cust WHERE CustomerID = 'ALFKI'; 

Figure 10.2 shows the results of this SELECT statement.

Figure 10.2: Using the AS keyword

The following code uses this SELECT statement to populate a DataSet object named myDataSet:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "SELECT CustomerID AS MyCustomer, CompanyName, Address " +   "FROM Customers AS Cust " +   "WHERE CustomerID = 'ALFKI'"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close(); 

Notice the Fill() method specifies the name of the DataTable as Customers, which is known as the source DataTable name.

To map a DataTable in your DataSet, you create an object of the DataTableMapping class using the Add() method; this class belongs to the System.Data.Common namespace, which you should import into your program. The following example creates a DataTableMapping object named myDataTableMapping, passing Customers and Cust to the Add() method:

 DataTableMapping myDataTableMapping =   mySqlDataAdapter.TableMappings.Add("Customers", "Cust"); 

Notice that the Add() method is called through the TableMappings property. The TableMappings property returns an object of the TableMappingCollection class. This object is a collection of TableMapping objects, and you use a TableMapping object to map the source name to a different DataTable name, therefore, the previous example maps the source name of Customers to Cust.

You can read this mapping using the SourceTable and DataSetTable properties of myDataTableMapping. For example:

 Console.WriteLine("myDataTableMapping.SourceTable = " +   myDataTableMapping.SourceTable); Console.WriteLine("myDataTableMapping.DataSetTable = " +   myDataTableMapping.DataSetTable); 

This example displays the following:

 myDataTableMapping.DataSetTable = Cust myDataTableMapping.SourceTable = Customers 

You should also change the TableName property of the DataTable object in your DataSet to keep the names consistent:

 myDataSet.Tables["Customers"].TableName = "Cust"; 

Tip 

It is important that you change the TableName since it will otherwise keep the original name of Customers, which is a little confusing when you've already specified the mapping from Customers to Cust earlier.

Next, to alias the CustomerID column as MyCustomer, you call the Add() method through the ColumnMappings property of myDataTableMapping:

 myDataTableMapping.ColumnMappings.Add("CustomerID", "MyCustomer"); 

The ColumnMappings property returns an object of the DataColumnMappingCollection class. This object is a collection of DataColumnMapping objects. You use a DataColumnMapping object to map a column name from the database to a different DataColumn name, therefore, the previous example maps the CustomerID column name from the database to the DataColumn name MyCustomer.

Listing 10.13 illustrates how to map table and column names using the code shown in this section.

Listing 10.13: MAPPINGS.CS

 /*   Mappings.cs illustrates how to map table and column names */ using System; using System.Data; using System.Data.SqlClient; using System.Data.Common; class Mappings {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "SELECT CustomerID AS MyCustomer, CompanyName, Address " +       "FROM Customers AS Cust " +       "WHERE CustomerID = 'ALFKI'";     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     mySqlDataAdapter.SelectCommand = mySqlCommand;     DataSet myDataSet = new DataSet();     mySqlConnection.Open();     mySqlDataAdapter.Fill(myDataSet, "Customers");     mySqlConnection.Close();     // create a DataTableMapping object     DataTableMapping myDataTableMapping =       mySqlDataAdapter.TableMappings.Add("Customers", "Cust");     // change the TableName property of the DataTable object     myDataSet.Tables["Customers"].TableName = "Cust";     // display the DataSetTable and SourceTable properties     Console.WriteLine("myDataTableMapping.DataSetTable = " +       myDataTableMapping.DataSetTable);     Console.WriteLine("myDataTableMapping.SourceTable = " +       myDataTableMapping.SourceTable);     // map the CustomerID column to MyCustomer     myDataTableMapping.ColumnMappings.Add("CustomerID", "MyCustomer");     DataTable myDataTable = myDataSet.Tables["Cust"];     foreach (DataRow myDataRow in myDataTable.Rows)     {       Console.WriteLine("CustomerID = " + myDataRow["MyCustomer"]);       Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);       Console.WriteLine("Address = " + myDataRow["Address"]);     }   } } 

The output from this program is as follows:

 myDataTableMapping.DataSetTable = Cust myDataTableMapping.SourceTable = Customers CustomerID = ALFKI CompanyName = Alfreds Futterkiste Address = Obere Str. 57