Lesson 2: Create a Visual C# application to communicate with the web server database

Note that this example uses functions from the .NET Framework 2.0.

To create SOAP and DISH services

Start Visual Studio.
Create a new Visual C# Windows form application project.

An empty form appears.
Add a web reference to the project.
1. From the Project menu, choose Add Service Reference.
2. In the Add Service Reference window, choose Advanced.
3. In the Service Reference Settings window, choose Add Web Reference.
4. In the Add Web Reference window, type http://localhost:8082/demo/SASoapTest_DNET in the URL field.
5. Click Go.
  
  Visual Studio lists the EmployeeList method available from the SASoapTest_DNET service.
6. Click Add Reference.
  
  Visual Studio adds the localhost web reference to the project. You can select it from the Solution Explorer pane.
Populate the empty form with the desired objects for web client application.

From the Toolbox pane, drag ListBox and Button objects onto the form and update the text attributes so that the form looks similar to the following diagram:

Write a procedure that accesses the web reference and uses the available methods.

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();

Run the application.

From the Debug menu, choose Start Debugging.

Communicate with the web database server.

Click Employee List.

The ListBox object displays the EmployeeList result set as (type)name=value pairs. The following output illustrates how an entry appears in the ListBox object:

(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

The Salary amount is converted to the client's currency format.

Values that contain null are returned as DBNull. Values that contain a date with no time are assigned a time of 00:00:00 or midnight.

The XML response from the web server includes a formatted result set. All column data is converted to a string representation of the data. The following result set illustrates how result sets are formatted when they are sent to the client:

<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>

Columns containing date or time information include the offset from UTC of the web server. In the above result set, the offset is -05:00 which is 5 hours to the west of UTC (North American Eastern Standard Time).

Columns containing only the date are formatted as 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 only the time are formatted as 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 both date and time are formatted as 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 DATATYPE ON clause is specified to generate data type 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' />

Discuss this page in DocCommentXchange.