This chapter is from the book
This chapter is from the book
This chapter is from the book
Table and Column Mappings
Table and column mappings enable you to alter the schema of the DataTable that's dynamically created in the DataSet. In the previous listings you filled the DataSet, taking in the schema provided to you by the data store. There are certainly instances when you'll want to change this. Often, database column names can be a bit cryptic, and changing them can make writing your code easier. Table and column mappings allow you to create a master mapping between the data returned from the data store and the DataTable in the DataSet. The DataSet maintains the table and column mappings and can translate them back to their original names when reconciling the data with the data store.
Note
In the previous listings, you've been filling a DataSet with the Customers table from the Northwind database. In the following listings, you'll create a new TableMapping for the Authors table in the Pubs database. Pubs is a sample database installed with Microsoft SQL Server. I chose to switch to it because of its notoriously cryptic naming conventions.
In the following example, you'll create table and column mappings for the Authors table in the Pubs database. When you add a new TableMapping, you must pass in the TableMapping source name and the DataTable name. If you specify "Table" (the default) as the source name, when you fill the DataSet you do not need to pass in the DataTable name. The data retrieved will use the TableMappings for "Table" by default, and a DataTable with the name you specified when you created the TableMappings will be created. This is demonstrated in Listing 3.13.
Listing 3.13 Creating Table and Column Mappings for the Default Table
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04: <script runat="server">
05: Sub Page_Load(Sender As Object, E As EventArgs)
06: Dim myDataAdapter As SqlDataAdapter
07: Dim myDataSet As New DataSet
08:
09: myDataAdapter = new SqlDataAdapter("SELECT * FROM Authors",
"server=localhost; database=Pubs; uid=sa; pwd=;")
10:
11: myDataAdapter.TableMappings.Add("Table", "Authors")
12: With myDataAdapter.TableMappings("Table").ColumnMappings
13: .Add("au_id", "ID")
14: .Add("au_lname", "Last Name")
15: .Add("au_fname", "First Name")
16: .Add("phone", "Phone")
17: .Add("address", "Address")
18: .Add("city", "City")
19: .Add("state", "State")
20: .Add("zip", "Zipcode")
21: .Add("contract", "Contract")
22: End With
23:
24: myDataAdapter.Fill(myDataSet)
25:
26: myDataGrid.DataSource = myDataSet.Tables("Authors").DefaultView
27: myDataGrid.DataBind()
28: End Sub
29: </script>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04: <script runat="server">
05: void Page_Load(Object sender, EventArgs e){
06: SqlDataAdapter myDataAdapter;
07: DataSet myDataSet = new DataSet();
08:
09: myDataAdapter = new SqlDataAdapter("SELECT * FROM Authors",
"server=localhost; database=Pubs; uid=sa; pwd=;");
10:
11: myDataAdapter.TableMappings.Add("Table", "Authors");
12:
13: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("au_id", "ID");
14: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("au_lname", "Last Name");
15: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("au_fname", "First Name");
16: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("phone", "Phone");
17: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("address", "Address");
18: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("city", "City");
19: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("state", "State");
20: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("zip", "Zipcode");
21: myDataAdapter.TableMappings["Table"].
ColumnMappings.Add("contract", "Contract");
22:
23:
24: myDataAdapter.Fill(myDataSet);
25:
26: myDataGrid.DataSource = myDataSet.Tables["Authors"].DefaultView;
27: myDataGrid.DataBind();
28: }
29: </script>
[VB & C#]
30: <html>
31: <form runat="server" method="post">
32: <body>
33: <asp:DataGrid runat="server" id="myDataGrid" />
34: <asp:DataGrid runat="server" id="myOtherDataGrid" />
35: </form>
36: </body>
37: </html>
On line 09, you create a new SqlDataAdapter, selecting all of the fields in the Authors table of the Pubs database. On line 11, you add a new default table mapping by passing in the name "Table" as the first parameter. Any table created in the DataSet that doesn't have a name specified in the Fill() method will use this table mapping and will be given the name "Authors", as indicated on line 11. On lines 12–22 you create the column mappings for all of the fields in the Authors table. The first parameter passed into the ColumnMappings collection's Add() method is the name of the field in the database. The second parameter is the name you're giving the field in the DataTable.
In case you'd rather not alter the DataSet's default table and column mappings, the DataAdapter allows you to add named table and column mappings. Rather than specifying "Table" as the source name, you can provide a new source name. When you call the Fill() method, you pass in the new source name as the table parameter. The DataSet checks for a table mapping for the source name passed in. If no table mapping exists, the schema is built on-the-fly based on the data store's schema, the same as in earlier examples. If there's a table mapping for the name passed in, it's used.
11: myDataAdapter.TableMappings.Add("BookAuthors", "BookAuthors");
24: myDataAdapter.Fill(myDataSet, "BookAuthors");
Line 11 shows a table mapping named "BookAuthors", specifying the name "BookAuthors" to be used when the DataTable is created. (Lines 12[ed]23 did not change from Listing 3.13.) On line 24, you call the Fill() method, passing in the name of your table mapping ("BookAuthors"). The DataSet finds this table mapping and creates a DataTable named "BookAuthors", using the schema you created.