Practical Web Services

By Kunle Loto

This is a web service which contains web methods that return a dataset object after querying a database, given the sql command(s), the table name(s), the password, the name of the database, the userID and the password. Some methods are for MS SQL Server while others are for other databases like Oracle or MS Access as you will see in the code. The datasets returned can also be passed in as a parameter to some of the web methods to update the database(UpdateDatabase or ManagedUpdate depending on whether it is a MS SQL Server database or another like access)

The web methods take the sql command and the table name as string arrays (instead of just a string) in case you want to combine datatables. If any of the sql commands passed in does not start with “SELECT” or “Select” a custom exception is thrown. Each web method calls a method (KeepRecord) which first, checks if a directory exists, if it doesn’t, it is created and then a copy of the dataset returned from the method is saved in the directory as XML. If the directory exists, the dataset is just saved in the directory as XML. The filename of the dataset saved as XML begins with a digit (from the index variable) which is incremented (that is the index variable is incrementrd) each time these methods are invoked by the client application so that the filenames are different. Another method (EnsureCorrectIndex) in the constructor of the DataService class checks the directory where the datasets are saved and determines the starting digits of the filename of the last dataset saved and assigns this to the index variable thus preventing overwriting of files.

Just for the demonstration of it, a web method is included which returns a custom type (in this case, a Time object).

/**
* Code for the webservice
*/

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Xml.Serialization;     //for serializing the custom class (XML representation)
using System.Xml;            //for saving the dataset as Xml
using System.Threading;            //needed for the index variable
using System.IO;            //for DirectoryInfo and Directory definition
using System.Text;            //for Substring definition
using System.Text.RegularExpressions;    //needed for Regex definition
using System.Windows.Forms;        //needed for MessageBox Definition

namespace DbaseWebService
{
   /**
    * This class is a custom exception class which throws an exception (as you will see in the
    * rest of the code) when an Sql command which does not begin with "SELECT" or "Select" is
    * passed in.
   public class CustomSqlException : System.ApplicationException
   {
       public CustomSqlException(string message) : base(message)
       {
       }
   }

   /**
    * This class just returns an object which holds the current time on the server
   */
   [XmlInclude(typeof(Time))]
   public class Time
   {
       public Time()
       {
       }

       public Time(System.DateTime dt)
       {
           Year = dt.Year;
           Month = dt.Month;
           Date = dt.Day;
           Hour = dt.Hour;
           Minute = dt.Minute;
           Second = dt.Second;
       }

       public readonly int Year;
       public readonly int Month;
       public readonly int Date;
       public readonly int Hour;
       public readonly int Minute;
       public readonly int Second;
   }

   /**
    * This web service in general returns a dataset object after querying a database.
    * It also saves the datasets as XML in a directory(@"C:\DbaseWebService_DataSets")
    *
    * There is also included just for demonstration a custom class which returns a DateTime object
   */
   [WebService(Description = "Query a database and receive a dataset object")]
   public class DataService : System.Web.Services.WebService
   {
       public DataService()
       {
           InitializeComponent();

           EnsureCorrectIndex(out index);
        }

       private void InitializeComponent()
       {
       }

       public override void Dispose()
       {
       }

       private const string sr1=  "_GetDataSet";
       private const string sr2 = "_GetManagedDatSet";
       private const string sr3 = "_Updatedatabase";
       private const string sr4 = "_ManagedUpdate";

       //the index variable is used in the file names  of the datasets saved as XML
       private int index;

       /**
        * This web method simply returns a Time object
       */
       [WebMethod( Description = "Get the current time and date")]
       public Time GetCurrentServerTime()
       {
           System.DateTime currentTime = System.DateTime.Now;
           Time t = new Time(currentTime);
           return t;
       }

       /**
        * This method returns a datset after querying a database. The server, userID, password and
        * database parameters are used to form the connection string. The commands prameter is an
        * array of SQL select command strings while the tables parameter is the table being queried
        * by the command in the commands parameter. Therefore the SQL statement in the commands parameter
        * must have the same index as the table it is querying in the tables parameter or else, an exception
        * might be thrown.
       */
       [WebMethod( Description = "Connects to a database and returns a dataset")]
       public DataSet GetDataSet(string server,
                                         string userID,
                                         string password,
                                         string database,
                                         string[]commands,
                                         string[] tables)
       {
           //increment the index so that the next dataset we save will have a unique name.
           Interlocked.Increment(ref index);

           try
           {
               private System.Data.SqlClient.SqlConnection xConnection;
               private System.Data.DataSet xDataSet;

               //creating the connection
               string conString = "server=" + server + "; uid=" + userID + "; pwd=" + password +
                                       "; database=" + database;
               xConnection = new SqlConnection(conString);
               xConnection.Open();

               //creating the dataset
               xDataSet = new DataSet();

               //iterate over the commands array, make sure each starts with "SELECT" or "Select"
               //and then setup the command and DataSet command for each of the tables you are querying.
               for(int i = 0; i< fileArray.Length; i++)
                   {
                       //if a file is an xml file, add it to the arraylist
                       if(fileArray[i].Name.EndsWith(".xml"))
                       {
                           filenames.Add(fileArray[i].Name);
                       }
                   }

                    //Regex theReg = new Regex(@"(\D+)");
                    //MatchCollection theMatches;

                   Regex theReg = new Regex(@"(\d+)");
                   MatchCollection theMatches ;
                   ArrayList theindex = new ArrayList();

                   for(int i = 0; i= 1)
                       {
                            string test2 = test.Substring(0, x-1);

                           //this makes sure that the characters before the "_" are digits
                          // theMatches  = theReg.Matches(test2);

                           //if(theMatches.Count == 0)
                           //{
                               //theindex.Add(test2);
                           //}

                           theMatches = theReg.Matches(test2);

                           foreach (Match theMatch in theMatches)
                           {

                               //is theMatch the same as test2? if so, add test2 to the arraylist
                               if ((theMatch.Length != 0)&&(theMatch.Length == test2.Length))
                               {
                                   theindex.Add(test2);
                               }
                           }
                       }
                   }

                   if(theindex.Count != 0)
                   {
                       //sort the arraylist then take the last object in the arraylist
                       theindex.Sort();
                       string s = theindex[theindex.Count - 1].ToString();
                       int toindex = s.ToInt32();
                       index = toindex;
                   }
               }

                //if there are no files in the directory, assign 0 to index.
               else
               {
                    index = 0;
               }
           }

           //if the directory does not exist, assign 1 to index
           else
           {
                index = 0;
           }
       }            //End of method
   }                //End of web service class
}