Proverb Web Service: Creating the Web Service - Part 1

Introduction
My previous 'Horoscope Web Service' example got a tremendous feedback from you all - Thanks!!
One request I constantly got was that even though the example was good, everyone wanted something that is updated regularly, something they can add to their web sites!!
Well, Your wish My Command :) !!

Here I have another Web Service example for you all, the difference being that this web service gives you more interactivity!! The scenario is the same, that of content provider. This time I will serve "proverbs" to you, also you will be able to add your own proverbs to this web service (neat ... so if you want the data to be updated often, better make it a point to add some proverbs regularly!) . The current database has 18 proverbs ( 18 till I die....) thanks to my friend Ridhish for helping me with the content!

Proverbs Service Model
The service currently supports two methods
1) GetProverb - This method will send a random proverb to the client
2) AddProverb - This method is used to add a proverb to the service database for moderation.

Database Structure - Ms Access 2000
Table 1 - Proverb

Table 2 - Moderate

Code
ProverbService.asmx - Proverb's Web Service File

<%@ WebService class="Proverbs" Language="C#" %>

using System;
using System.Data;
using System.Data.OleDb ;
using System.Web.Services;


[WebService(Namespace="http://www.mastercsharp.com/WebService")]
public class Proverbs: WebService
{
  [WebMethod]
  public string GetProverb()
  {
    try
    {
      //Declare the connection and SQL string
      string conString=@"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=";
             conString+=Server.MapPath(".\\db\\proverb.mdb");
      string sqlString ="SELECT * FROM Proverb";
      OleDbDataAdapter proAdapter= new OleDbDataAdapter(sqlString,conString);
      DataSet proSet = new DataSet();
      //Fill the DataSet
      proAdapter.Fill(proSet,"Proverb");
      //Create a Random Number Object
      Random ranNum = new Random();
      //Get a Random Number between 0 and the number of
      //records within the DataSet
      int num = ranNum.Next(proSet.Tables[0].Rows.Count);
      //Return the Random proverb
      //Remember to add 1 to the random number since it starts from 0
      //and the DataBase id starts from 1
      return proSet.Tables[0].Rows[++num]["Content"].ToString() ;
    }
    catch(Exception ed)
    {
      return "Sorry Service Unavailable!";
    }
  }

  [WebMethod]
  public string AddProverb(string userProverb)
  {
    if(userProverb!="")
    {
      try
      {
        //Declare the connection and SQL string
        string conString=@"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=";
               conString+=Server.MapPath(".\\db\\proverb.mdb");
        string sqlString ="INSERT INTO moderate (Content,Dt) VALUES ('"+userProverb;
  	       sqlString+="', '"+DateTime.Now+"')" ;
        //Create a Command and Connection
        OleDbConnection proConnection= new OleDbConnection(conString);
        OleDbCommand proCommand = new OleDbCommand(sqlString,proConnection);
        proConnection.Open();
        //Enter the Data into the Moderate table for moderation
        proCommand.ExecuteNonQuery();
        proConnection.Close();
        return "Thank You!";
      }
      catch
      {
        return "Sorry Service Unavailable!";
      }
    }
    else
    {
      return "Please Input a Proverb!";
    }
  }
}

Deploy the Web Service
Once you have the above code saved to the file 'ProverbService.asmx', create a Virtual Directory by the name 'ProverbService'. Copy the 'ProverbService.asmx' file in this directory and the 'proverb.mdb' database file in the 'db' directory within the 'ProverbService' Virtual Directory.

Once that's done, you can call the Web Service from any browser by typing the full URI to the service i.e. http://localhost/ProverbService/ProverbService.asmx (considering that you are deploying on a local server).

You can view the WSDL of the Web Service by typing the full URI of the Web Service along with WSDL as a Query String i.e. http://localhost/ProverbService/ProverbService.asmx?WSDL. 

Conclusion
In this part we saw how to create our web service, in the coming parts I will describe how to create clients and Administration interfaces for this Web Service.
Till then, feel free to create your own client, if you know how to consume the service.
Waiting for your Proverbs .....