Mapping Tables and Columns

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.
Nội dung trích xuất từ tài liệu:
Mapping Tables and ColumnsMapping Tables and ColumnsIn Chapter 3, Introduction to Structured Query Language (SQL), you learned that theAS keyword is used to specify an alias for a table or column. The following example usesthe AS keyword to alias the CustomerID column as MyCustomer and also alias theCustomers table as Cust:SELECT CustomerID AS MyCustomer, CompanyName, AddressFROM Customers AS CustWHERE CustomerID = ALFKI;Figure 10.2 shows the results of this SELECT statement.Figure 10.2: Using the AS keywordThe following code uses this SELECT statement to populate a DataSet object namedmyDataSet: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 isknown as the source DataTable name.To map a DataTable in your DataSet, you create an object of the DataTableMappingclass using the Add() method; this class belongs to the System.Data.Common namespace,which you should import into your program. The following example creates aDataTableMapping object named myDataTableMapping, passing Customers and Cust tothe Add() method:DataTableMapping myDataTableMapping = mySqlDataAdapter.TableMappings.Add(Customers, Cust);Notice that the Add() method is called through the TableMappings property. TheTableMappings property returns an object of the TableMappingCollection class. Thisobject is a collection of TableMapping objects, and you use a TableMapping object tomap the source name to a different DataTable name, therefore, the previous examplemaps the source name of Customers to Cust.You can read this mapping using the SourceTable and DataSetTable properties ofmyDataTableMapping. For example:Console.WriteLine(myDataTableMapping.SourceTable = + myDataTableMapping.SourceTable);Console.WriteLine(myDataTableMapping.DataSetTable = + myDataTableMapping.DataSetTable);This example displays the following:myDataTableMapping.DataSetTable = CustmyDataTableMapping.SourceTable = CustomersYou should also change the TableName property of the DataTable object in your DataSetto 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 youve already specified the mapping from Customers to Cust earlier.Next, to alias the CustomerID column as MyCustomer, you call the Add() methodthrough the ColumnMappings property of myDataTableMapping:myDataTableMapping.ColumnMappings.Add(CustomerID, MyCustomer);The ColumnMappings property returns an object of the DataColumnMappingCollectionclass. This object is a collection of DataColumnMapping objects. You use aDataColumnMapping object to map a column name from the database to a differentDataColumn name, therefore, the previous example maps the CustomerID column namefrom the database to the DataColumn name MyCustomer.Listing 10.13 illustrates how to map table and column names using the code shown in thissection.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 = CustmyDataTableMapping.SourceTable = CustomersCustomerID = ALFKICompanyName = Alfreds FutterkisteAddress = Obere Str. 57