Manage Web Services With Data Mapping

Use ADO.NET’s mapping classes to streamline the interaction between your Web Services and databases.

asp:feature

Languages: VB

Technologies: ADO.NET | Datasets | Web Services | Data Mapping

 

Manage Web Services With Data Mapping

Use ADO.NET's mapping classes to streamline the interaction between your Web Services and databases.

 

By Fabio Claudio Ferracchiati

 

ADO.NET includes a new set of classes, the mapping classes, which were added to the .NET Framework library to manage the interaction between Web Services and databases. You might find the mapping classes useful, however, anytime you need to deal with foreign databases or strange column names. When you define a mapping mechanism in your code using the mapping classes, you map the database column names to the dataset column names.

 

 

The ADO.NET Mapping Mechanism

The code in FIGURE 1 is fairly typical code you might use to retrieve records from a table into a dataset using a DataAdapter.

 

FIGURE 1: This code retrieves the first name, last name, and city of every user in the tabUser table.

 

When the code in FIGURE 1 uses the Fill method provided by the DataAdapter class, the mapping mechanism is executed automatically. The method looks into the TableMappings collection searching for one or more mapped tables to use. When no table mappings classes have been added to the collection, the code uses the names of the default tables and columns specified in the SQL query statement. In FIGURE 1, when the code enters the loop used to display each record in the table, the default column names retrieve the respective column values.

 

The column names used in the tabUser table are difficult to remember. This might be a good candidate for using the mapping classes.

 

Here are the basic steps involved in using the mapping mechanism:

 

  1. Import the System.Data.Common namespace.
  2. Create a DataColumnMapping object for each column you wish to map.
  3. Create a DataTableMapping object for each table.
  4. Add the DataColumnMapping objects to the DataTableMapping object's ColumnMapping collection.
  5. Add the DataTableMapping objects to the DataAdapter object's TableMappings collection.

 

If you follow these five simple rules, you can see in FIGURE 2 how easy it is to change the strange column names to more readable ones.

 

Sub Page_Load(ByVal obj As Object, ByVal e As EventArgs)

  Try

      ' Create the connection object

      Dim dbConn As New SqlConnection()

 

      ' Set the connection string

      dbConn.ConnectionString = "data source=.;" & _

       initial catalog=asp.netPRO_DB;user id=sa;"

 

      ' Create the data adapter object

      Dim daUsers As New SqlDataAdapter( _

       "SELECT fn,ln,cty FROM tabUser", dbConn)

 

      ' Create the DataSet object

      Dim dsUsers As New DataSet("Users")

 

      ' Create each column to map database columns

      ' to dataset columns

      Dim dcmFirstName As New DataColumnMapping("fn", _

       "FirstName")

      Dim dcmLastName As New DataColumnMapping("ln", _

       "LastName")

      Dim dcmCity As New DataColumnMapping("cty", "City")

 

      ' Create a TableMapping object to contain

      ' the mapped columns

      Dim dtmUser As New DataTableMapping("Table", _

       "tabUser")

 

      ' Add the mapped columns to the ColumnMappings

      ' collection of the DataTableMapping object

      dtmUser.ColumnMappings.Add(dcmFirstName)

      dtmUser.ColumnMappings.Add(dcmLastName)

      dtmUser.ColumnMappings.Add(dcmCity)

 

      ' Add the mapped table object into

       ' the TableMappings collection

       ' provided by the DataAdapter object.

       daUsers.TableMappings.Add(dtmUser)

 

       ' Fill the dataset with the records

       ' retrieved by the DataAdapter object

       daUsers.Fill(dsUsers)

 

       ' Loop through the records printing

        ' them to video.

       Dim r As DataRow

       For Each r In dsUsers.Tables(0).Rows

         Response.Write("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-" & _

          "=-=-=-=-=-=-=-=-=-=-=-=-=-=" & "
")

         Response.Write("First Name: " & r("FirstName") & _

          "
")

         Response.Write("Last Name: " & r("LastName") & _

          "
")

         Response.Write("City: " & r("City") & "
")

       Next

   Catch ex As Exception

       ' An exception occurred. Display the error

       ' message.

       Response.Write(ex.Message)

   End Try

End Sub

FIGURE 2: The columns have more readable names using the mapping classes.

 

FIGURE 2 shows you how to provide the DataColumnMapping constructor with the correct parameters to map the database column's name to the dataset's. Then it shows how you can create a DataTableMapping object using its constructor to inform the DataAdapter class about the source table name and the dataset mapped table name. Note that when you use the Fill method without specifying the source table parameter, the Table default name is used. Finally, after adding each mapped column into the ColumnMappings collection, it shows how you can add the object to the TableMappings collection exposed by the DataAdapter object.

 

Once the mapping mechanism has been defined and assigned to the DataAdapter object, you can accomplish every database operation - such as queries and record management - using the mapped columns and tables. For example, the code in FIGURE 3 adds a record to the database using the mapped column names.

 

' Add a new row to the table

Dim r As DataRow

r = dsUsers.Tables(0).NewRow()

r("FirstName") = "Fabio Claudio"

r("LastName") = "Ferracchiati"

r("City") = "Rome"

dsUsers.Tables(0).Rows.Add(r)

 

' Automatically define the SQL INSERT

' command

Dim cb As New SqlCommandBuilder(daUsers)

 

' Update the database with the new row

daUsers.Update(dsUsers.GetChanges(DataRowState.Added))

 

' Update the dataset confirming the addition

dsUsers.AcceptChanges()

FIGURE 3: Once you've defined the mapped column names, you can add new rows to the database easily.

 

Combine Mapping With Web Services

As I mentioned previously, the mapping classes are most useful when used in conjunction with data provided by Web Services. When consuming data from a Web Service, you won't have control over the names of columns used in the service, which might cause problems in your client application. This makes a great case for using the mapping classes.

 

The X Methods Web site (http://www.xmethods.com) is an online Web Service repository. It contains the SearchMusicTeacher Web Service, which uses the FindMusicTeachers() method to retrieve a list of musical teachers in a particular U.S. city specified by ZIP code. The method will return a string containing the dataset's structure with the results of the required city. In FIGURE 4, you can see a snippet of a search result for a fictional ZIP code.

 

  

    55555

    0

    

    0

    0

    43

    10

  

  

    Byron Thomas

    

NA

    435-555-1234

    [email protected]

    Private Teacher

     Drums

    

    free to $30/ hour

  

  . . .

  . . .

FIGURE 4: A portion of the music teachers in ZIP Code 55555, returned by the SearchMusicTeacher Web Service.

 

The Web Service returns a dataset formed by two tables: the Summary and Details tables. The former will contain some of the required parameters such as the ZIP code, the instruments of expertise, and so forth with the addition of the total count of the records contained in the ResultsCount tag. The latter will include the teacher data, including name, address, and phone number.

 

Let's say you have a "Musical Institute" Web site that offers a "find a teacher" search service that points to your local data store. In FIGURE 5, you can see the table's structure that maintains the music teacher data.

 


FIGURE 5: Here is the tabTeacher local database table's structure.

 

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.Common" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.IO" %>

Example4

FIGURE 6: This is the code to insert the Web Service's data into your local database using the mapping classes.

 

The code in FIGURE 6 begins by defining an object for each column to map to the Web Service's provided dataset. Then you must create a DataTableMapping object that points to the correct table within the source DataSet object. Next, the code informs the mapping classes used by the DataAdapter object to map the columns to the Details table of the dataset.

 

These mapping classes are used by the DataAdapter object when the Update method is invoked in the final portion of the code in FIGURE 6.

 

Fix the Glitches

Imagine that the SearchMusicTeacher Web Service provider decides to add a new column to the resulting DataSet string structure. Then the next time your application is launched an error occurs. The ADO.NET DataAdapter class provides two properties to tell the mapping mechanism what to do when something strange happens. The MissingMappingAction property tells the DataAdapter object what to do when a column in the source dataset is not mapped. FIGURE 7 shows the enumeration values it can assume.

 

MissingMappingAction Value

DataAdapter Action

Passthrough

(Default) Assumes the column name specified in the SQL statement.

Error

Raises a SystemException exception.

Ignore

Ignores the missing column.

FIGURE 7: This table shows the enumeration values the DataAdapter object can assume when a column in the source dataset is not mapped.

 

MissingSchemaAction Value

DataAdapter Action

Add

(Default) When the schema is missing for the current column the DataAdapter will add it to the dataset's schema. Note, no primary key or unique column information will be maintained.

AddWithKey

Similar to the Add value but with the primary key and unique column creation. If the primary key contains identity values, you must inform the DataAdapter object about the identity seed.

Ignore

When the schema is missing for the current column, the DataAdapter ignores it.

Error

When the schema is missing for the current column, the DataAdapter raises a SystemException exception.

FIGURE 8: This table shows the enumeration values the DataAdapter object can assume when the dataset used by either the Fill or Update methods doesn't contain one or more columns in the schema.

 

The code in FIGURE 6 uses the MissingMappingAction property, set to Error, in order to raise an exception when the Web Service's resulting dataset changes its structure.

 

The MissingSchemaAction property tells the DataAdapter what to do when the dataset used by either the Fill() or Update() methods doesn't contain one or more columns in its schema. FIGURE 8 shows the enumeration values it can assume.

 

In this article you have seen how the mapping classes resolve common issues like the interaction between Web Services and data store when table and column names don't match. In addition, you have seen how the mapping mechanism can be useful when databases have strange column names.

 

Once you have fully acquired the Mapping mechanism and class functionalities, you'll find them useful and intuitive - especially when you receive data from a Web Service and you want to store its data in your database. By giving more significant names to columns, you create more readable code that helps you during all your code development.

 

The files referenced in this article are available for download.

 

Fabio Claudio Ferracchiati is a software developer and technical writer and works in Rome for CPI Progetti Spa (http://www.cpiprogetti.it) where he develops Internet/intranet solutions using Microsoft technologies. He has worked with Visual Basic and Visual C++ and now dedicates his attention to the Internet and related technologies. He's also co-author of four books from Wrox Press: Professional Commerce Server 2000, ADO.NET Programmer's Reference, Professional ADO.NET Programming, and Data-centric .NET Programming with C#. E-mail Fabio at mailto:[email protected].

 

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish