Applying .Net to Web Services (Web Techniques, May 2001)

<%@ WebService Language="C#" Class="DBDemo" %>
/* dbdemo.asmx */

using System.Data.ADO;          /* Needed for ADO* objects.    */
using System.Web.Services;      /* Needed by all web services. */
using System.Xml.Serialization; /* Provides SoapServiceStyle.  */
using System.Collections;       /* Provides StringCollection.  */

[SoapService(Style=SoapServiceStyle.RPC)]
public class DBDemo: WebService {

  /*
   * Make a connection to an Access database 
   */
  public ADOConnection getConnection() {

    /* 
     * Create a connection string for the bjepson.mdb file that is
     * located in the db/ subdirectory of my virtual home directory.
     *
     */
    string mdb_file = Server.MapPath("\\bjepson\\db\\bjepson.mdb");
    string conn_str = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" +
                      mdb_file + ";Persist Security Info=False";

    /* 
     * Open a connection and return it 
     */
    ADOConnection conn = new ADOConnection(conn_str);
    conn.Open();
    return conn;

  }

  /* 
   * This WebMethod provides a list of acceptable choices that can
   * be passed to getURL().
   *
   */
  [ WebMethod ]
  public string[] enumerateChoices() {

    ADOConnection conn = getConnection();

    /* 
     * Create a command that retrieves all the sitenames from the
     * site table.
     *
     */
    ADOCommand cmd = new ADOCommand("SELECT sitename FROM site", conn);

    /*
     * Create a data reader to process each row, then execute the
     * command.
     */
    ADODataReader dr;
    cmd.Execute(out dr);

    /* Fetch each row and insert the value of sitename into
     * a StringCollection.
     */
    int i = 0;
    StringCollection sc = new StringCollection();
    while( dr.Read() ) {
      sc.Insert(i++, dr.GetString(0).Trim());
    }

    /* Close the data reader and the connection. */
    dr.Close();
    conn.Close();

    /* 
     * Convert the StringCollection to an array and return it. 
     */
    string[] result = new string[sc.Count];
    sc.CopyTo(result, 0);
    return result;

  }

  /* 
   * This WebMethod retrieves the URL that corresponds to 
   * a given site.
   */
  [ WebMethod ]
  public string getURL(string choice) {

    ADOConnection conn = getConnection();

    /*
     * Fetch the URL that corresponds to the selected sitename. 
     */
    string sql = 
      "SELECT url FROM site WHERE sitename = " + "'" + choice + "'";
    ADOCommand cmd = new ADOCommand(sql, conn);
    ADODataReader dr;
    cmd.Execute(out dr);

    /* Put the URL into a string. */
    string result;
    if( dr.Read() ) {
      result = dr.GetString(0);
    } else {
      result = "unknown site";
    }

    /* Close the data reader and the connection. */
    dr.Close();
    conn.Close();

    /* Return the result */
    return result;

  }

}