Tutorial: Using data types with Microsoft .NET

The following tutorial demonstrates how to use the SQL Anywhere web service datatype support from within Microsoft .NET using Visual C#.

To create SOAP and DISH services

At a command prompt, run the following command to start a personal web server. Replace samples-dir with the actual location of the sample database. The -xs http(port=80) option tells the database server to accept HTTP requests on port 80. If you already have a web server running on port 80, use another port number such as 8080 for this tutorial.
dbeng11 -xs http(port=80) samples-dir\demo.db

Start Interactive SQL. Connect to the SQL Anywhere sample database as the DBA. Execute the following statements:

Define a SOAP service that lists the Employees table.

CREATE SERVICE "SASoapTest/EmployeeList"
TYPE 'SOAP'
AUTHORIZATION OFF
SECURE OFF
USER DBA
DATATYPE OUT
AS SELECT * FROM Employees;

In this example, DATATYPE OUT is specified to generate datatype information in the XML result set response. A fragment of the response from the web server is shown below. Note that the type information matches the data type of the database columns.

<xsd:element name='EmployeeID' minOccurs='0' type='xsd:int' />
<xsd:element name='ManagerID' minOccurs='0' type='xsd:int' />
<xsd:element name='Surname' minOccurs='0' type='xsd:string' />
<xsd:element name='GivenName' minOccurs='0' type='xsd:string' />
<xsd:element name='DepartmentID' minOccurs='0' type='xsd:int' />
<xsd:element name='Street' minOccurs='0' type='xsd:string' />
<xsd:element name='City' minOccurs='0' type='xsd:string' />
<xsd:element name='State' minOccurs='0' type='xsd:string' />
<xsd:element name='Country' minOccurs='0' type='xsd:string' />
<xsd:element name='PostalCode' minOccurs='0' type='xsd:string' />
<xsd:element name='Phone' minOccurs='0' type='xsd:string' />
<xsd:element name='Status' minOccurs='0' type='xsd:string' />
<xsd:element name='SocialSecurityNumber' minOccurs='0' type='xsd:string' />
<xsd:element name='Salary' minOccurs='0' type='xsd:decimal' />
<xsd:element name='StartDate' minOccurs='0' type='xsd:date' />
<xsd:element name='TerminationDate' minOccurs='0' type='xsd:date' />
<xsd:element name='BirthDate' minOccurs='0' type='xsd:date' />
<xsd:element name='BenefitHealthInsurance' minOccurs='0' type='xsd:boolean' />
<xsd:element name='BenefitLifeInsurance' minOccurs='0' type='xsd:boolean' />
<xsd:element name='BenefitDayCare' minOccurs='0' type='xsd:boolean' />
<xsd:element name='Sex' minOccurs='0' type='xsd:string' />

Create a DISH service to act as a proxy for the SOAP service and to generate the WSDL document.

CREATE SERVICE "SASoapTest_DNET"
TYPE 'DISH'
GROUP "SASoapTest"
FORMAT 'DNET'
AUTHORIZATION OFF
SECURE OFF
USER DBA;

The SOAP and DISH service must be of format DNET. In this example, the FORMAT clause was omitted when the SOAP service was created. As a result, the SOAP service inherits the DNET format from the DISH service.

Start Microsoft Visual Studio. Note that this example uses functions from the .NET Framework 2.0.

Create a new Windows Application project using Visual C#.

An empty form appears.
From the Project menu, choose Add Web Reference.
In the URL field of the Add Web Reference page, enter the following URL: http://localhost:80/demo/SASoapTest_DNET.
Click Go.

You are presented with a list of the methods available for SASoapTest_DNET. You should see the EmployeeList method.
Click Add Reference to finish.

The Solution Explorer window shows the new web reference.
From the Visual Studio Toolbox, add a listbox and a button to the form as shown in the following diagram.
Rename the button text to Employee List.

Double-click Employee List and add the following code for the button click event.

int sqlCode;

listBox1.Items.Clear();

localhost.SASoapTest_DNET proxy = new localhost.SASoapTest_DNET();

DataSet results = proxy.EmployeeList(out sqlCode);
DataTableReader dr = results.CreateDataReader();
while (dr.Read())
{
  for (int i = 0; i < dr.FieldCount; i++)
  {
    string columnName = "(" + dr.GetDataTypeName(i) 
                      + ")" 
                      + dr.GetName(i);
    if (dr.IsDBNull(i))
    {
      listBox1.Items.Add(columnName + "=(null)");
    }
    else {
      System.TypeCode typeCode = 
          System.Type.GetTypeCode(dr.GetFieldType(i));
      switch (typeCode)
      {
        case System.TypeCode.Int32:
          Int32 intValue = dr.GetInt32(i);
          listBox1.Items.Add(columnName + "=" 
              + intValue);
          break;
        case System.TypeCode.Decimal:
          Decimal decValue = dr.GetDecimal(i);
          listBox1.Items.Add(columnName + "=" 
              + decValue.ToString("c"));
          break;
        case System.TypeCode.String:
          string stringValue = dr.GetString(i);
          listBox1.Items.Add(columnName + "=" 
              + stringValue);
          break;
        case System.TypeCode.DateTime:
          DateTime dateValue = dr.GetDateTime(i);
          listBox1.Items.Add(columnName + "=" 
              + dateValue);
          break;
        case System.TypeCode.Boolean:
          Boolean boolValue = dr.GetBoolean(i);
          listBox1.Items.Add(columnName + "=" 
              + boolValue);
          break;
        case System.TypeCode.DBNull:
          listBox1.Items.Add(columnName 
              + "=(null)");
          break;
        default:
          listBox1.Items.Add(columnName 
              + "=(unsupported)");
          break;
      }
    }
  }
  listBox1.Items.Add("");
}
dr.Close();

This example is designed to illustrate the fine control the application developer can have over the datatype information that is available.

Build and run the program.

The XML response from the web server includes a formatted result set. The first row of the formatted result set is shown below.

<row>
 <EmployeeID>102</EmployeeID>
 <ManagerID>501</ManagerID>
 <Surname>Whitney</Surname>
 <GivenName>Fran</GivenName>
 <DepartmentID>100</DepartmentID>
 <Street>9 East Washington Street</Street>
 <City>Cornwall</City>
 <State>NY</State>
 <Country>USA</Country>
 <PostalCode>02192</PostalCode>
 <Phone>6175553985</Phone>
 <Status>A</Status>
 <SocialSecurityNumber>017349033</SocialSecurityNumber>
 <Salary>45700.000</Salary>
 <StartDate>1984-08-28-05:00</StartDate>
 <TerminationDate xsi:nil="true" />
 <BirthDate>1958-06-05-05:00</BirthDate>
 <BenefitHealthInsurance>1</BenefitHealthInsurance>
 <BenefitLifeInsurance>1</BenefitLifeInsurance>
 <BenefitDayCare>0</BenefitDayCare>
 <Sex>F</Sex>
</row>

There are a few things to note about the XML result set response.

All column data is converted to a string representation of the data.
Columns that contain date and/or time information include the offset from UTC of the web server. In this example, the offset is -05:00 which is 5 hours to the west of UTC (in this case, North American Eastern Standard Time).
Columns containing date only are formatted as follows:yyyy-mm-dd-HH:MM or yyyy-mm-dd+HH:MM. A zone offset (-HH:MM or +HH:MM) is suffixed to the string.
Columns containing time only are formatted as follows:hh:mm:ss.nnn-HH:MM or hh:mm:ss.nnn+HH:MM. A zone offset (-HH:MM or +HH:MM) is suffixed to the string.
Columns containing date and time are formatted as follows:yyyy-mm-ddThh:mm:ss.nnn-HH:MM or yyyy-mm-ddThh:mm:ss.nnn+HH:MM. Note that the date is separated from the time using the letter 'T'. A zone offset (-HH:MM or +HH:MM) is suffixed to the string.

The listbox will display the EmployeeList result set as (type)column name=value pairs. The result from processing the first row of the result set is shown below.

(Int32)EmployeeID=102
(Int32)ManagerID=501
(String)Surname=Whitney
(String)GivenName=Fran
(Int32)DepartmentID=100
(String)Street=9 East Washington Street
(String)City=Cornwall
(String)State=New York
(String)Country=USA
(String)PostalCode=02192
(String)Phone=6175553985
(String)Status=A
(String)SocialSecurityNumber=017349033
(String)Salary=$45,700.00
(DateTime)StartDate=28/08/1984 0:00:00 AM
(DateTime)TerminationDate=(null)
(DateTime)BirthDate=05/06/1958 0:00:00 AM
(Boolean)BenefitHealthInsurance=True
(Boolean)BenefitLifeInsurance=True
(Boolean)BenefitDayCare=False
(String)Sex=F

There are a couple of things to note about the results.

Columns that contain nulls are returned as type DBNull.
The Salary amount has been converted to the client's currency format.
Columns that contain date but no time values assume a time of 00:00:00 or midnight.

Discuss this page in DocCommentXchange.
Send feedback about this page using email.