This program is a Web service, which contains Web methods that return a dataset after querying a database (any database) given the connection string, the password, the database, the table, the user ID and the SQL commands. It also contains methods that receives a dataset object and uses them to update the database (the dataset might even be the dataset returned by one of the methods in the Web service. Each of the datasets either returned by some methods or passed in as a parameter to other methods to update the database are saved as XML. Each dataset saved as XML has a file name which contains a digit which is incremented (i.e the index variable) each time therefore giving each dataset saved a unique filename. A method EnsureCorrectIndex() rigorously crosschecks the filenames in the directory where the datasets are saved to ensure that the index variable (used in the filename) is set to the right value to prevent overwriting of files (even if you purposely copy a file into that directory). The dataset's ExtendedProperties contains the key RecordName1 (in some cases RecordName1, RecordName2 and so on) the value associated with each key is the path to the file saved as XML on the server, therefore, you can access the saved datasets through one of the webmethods (RetrieveDataSet()) given the value of a key in the dataset's metadata as a parameter to the method. Just for the purpose of demonstration, a web method (GetCurrentServerTime()) returns a custom type, in this case a time object which only contains the current time on the server.
Some methods are for MS SQL server wile others are for Oracle, Access and all those database that require managed providers. This Web service uses the default XML namespace (http://tempuri.org/) but you can change it using the WebService attribute e.g [WebService(NameSpace="http://www.abcdefgh.com/")].
You will have to add references to both System.Web and System.Web.Services, if you build this application using command line tools, you would use the /R flag to add the references but in Visual Studio .NET, you just right-click the Solution Explorer window, select References, and add the appropriate namespace references from the .NET tab, after you build this project using Visual Studio .NET, a DLL is created in the appropriate subdirectory of your internet server (e.g. c:\InetPub\wwwroot\WebSvc), a .disco file is also added to that directory. In my next article, I will write a program that consumes some of the methods in this Web service.
/**
* 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
* arraylist 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,ArrayList commands,ArrayList tables)
{
//increment the index so that the next dataset we save will have a unique name.
Interlocked.Increment(ref index);
private System.Data.SqlClient.SqlConnection xConnection;
private System.Data.DataSet xDataSet;
//creating the connection
string conString = "server=" + server + "; uid=" + userID + "; pwd=" + password +
"; database=" + database;
//The RecordName1 (or RecordName2 etc) of the dataset's ExtendedProperties is a key that contains a value which is the path to the same dataset
//saved as xml (the saved dataset can be accessed through the webmethod RetrieveDataSet
xDataSet = new DataSet("GetDataSet");
xDataSet.ExtendedProperties.Add("RecordName1", @"C:\DbaseWebService_DataSets\" + index.ToString()+sr1+".xml");
try
{
xConnection = new SqlConnection(conString);
xConnection.Open();
//creating the 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<commands.Count; i++)
{
if(!(commands[i].ToString()).StartsWith(("SELECT")||("Select")))
{
//throw the custom exception if the sql command does not start with "SELECT" or "Select".
CustomSqlException e = new CustomSqlException("The SQL Command must start with "SELECT" or "Select");
throw e;
}
System.Data.SqlClient.SqlCommand xCommand = new System.Data.SqlClient.SqlCommand();
xCommand.Connection = xConnection;
xCommand.CommandText = commands[i].ToString();
System.Data.SqlClient.SqlDataAdapter xDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
xDataAdapter.SelectCommand = xCommand;
xDataAdapter.TableMappings.Add("Table", tables[i].ToString());
xDataAdapter.Fill(xDataSet);
}
//save the dataset
KeepRecord(xDataSet, index, sr1);
//return the dataset
return xDataSet;
}
//simply rethrow any exception caught
catch(System.Data.SqlClient.SqlException)
{
throw;
}
catch(IOException e)
{
MessageBox.Show(e.Message, "Save DataSet Error")
//If an error occurs while saving the dataset, just return the dataset
//to the client application
return xDataSet;
}
catch(System.Exception)
{
throw;
}
finally
{
xConnection.Close();
}
}
/**
* This method is the same as the previous method except that it connects to an Access or Oracle
* database. The connectionString parameter is of course the connection string (whatever string is needed
* to connect to the database). The commands prameter is an arraylist 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 ot Access or Oracle database and returns
a dataset object")]
public DataSet GetManagedDataSet(string connectionString,
ArrayList commands,ArrayList tables)
{
//increment the index so that the next dataset we save will have a unique name.
Interlocked.Increment(ref index);
//connect to the database
string cn = connectionString;
//The RecordName1 (or RecordName2 etc) of the dataset's ExtendedProperties is a key that contains a value which is the path to the same dataset
//saved as xml (the saved dataset can be accessed through the webmethod RetrieveDataSet
DataSet dataset = new DataSet("GetManagedDataSet");
dataset.ExtendedProperties.Add("RecordName1", @"C:\DbaseWebService_DataSets\" + index.ToString() + sr2 + ".xml");
try
{
//iterate over the commands array and with each string in it, create a data set
//command object
for(int i = 0; i <commands.Count; i++)
{
if(!(commands[i].ToString()).StartsWith(("SELECT")||("Select")))
{
//throw the custom exception if the sql command does not start with "SELECT" or "Select".
CustomSqlException x = new CustomSqlException("The SQL Command must start with "SELECT" or "Select");
throw x;
}
string commandString = commands[i].ToString();
System.Data.OleDb.OleDbDataAdapter DAdapter = new System.Data.OleDb.OleDbDataAdapter(commandString, cn);
//fill the dataset object
DAdapter.Fill(dataset, tables[i].ToString());
}
//save the dataset
KeepRecord(dataset, index, sr2);
//return the much needed dataset object
return dataset;
}
//just rethrow any exception caught
catch(System.Data.SqlClient.SqlException)
{
throw;
}
catch(IOException e)
{
MessageBox.Show(e.Message, "Save DataSet Error")
//If an error occurs while trying to save the dataset, just return the dataset
return dataset;
}
catch(System.Exception)
{
throw;
}
/**
* This method takes as a parameter a connectionString and a selectcommand string - the connection
* string of course represents the connection string while the selectcommand represents the initial
* SELECT statement. The table parameter is the table you are querying while the dataset object parameter
* is the dataset you might have received from another method here and you now want to use it to
* update the database (propably after adjusting some Datatables in the dataset).
* This method assumes that the dataset being passed in has no errors.
*
* This method is for MS SQL server and it is assumed that the dataset object passed in has no errors
*/
[WebMethod( Description = "Updates a MS SQL server database")]
public DataSet Updatedatabase(string connectionString,string selectcommand,
string table,DataSet dset)
{
//increment the index so that the next dataset we save will have a unique name.
Interlocked.Increment(ref index);
//create the connection
string conString = connectionString;
//get records from the required table
string commandString = selectcommand;
try
{
if(!selectcommand.StartsWith(("SELECT")||("Select")))
{
//throw the custom exception if the sql command does not start with "SELECT" or "Select".
CustomSqlException c = new CustomSqlException("The SQL Command must start with "SELECT" or "Select");
throw c;
}
SqlDataAdapter DataAdapter = new SqlDataAdapter(commandString, conString);
//if the dataset contains the key RecordName1, then get all the keys that start with "RecordName"
//then get the digits at the end of "RecordName" and sort them, then increment the last (highest) one
//and append that digit to "RecordName" as a new key of the dataset's ExtendedProperties.
if(dset.ExtendedProperties.ContainsKey("RecordName1")
{
ArrayList thekeys = new ArrayList;
ICollection keys = dset.ExtendedProperties.Values;
foreach(string key in keys)
{
if(key.StartsWith("RecordName"))
{
string keynum = key.Substring(10,key.Length - 1);
int num = keynum.ToInt32();
thekeys.Add(num);
}
}
thekeys.Sort();
int i = (int)thekeys[thekeys.Count-1];
i++;
dset.ExtendedProperties.Add("RecordName"+i.ToString(), @"C:\DbaseWebService_DataSets\" + index.ToString() + sr3 + ".xml");
}
else
{
dset.ExtendedProperties.Add(RecordName1, index.ToString() + s3);
}
//Now you can update the database
DataAdapter.Update(dset, table);
dset.AcceptChanges();
//save the dataset object as Xml
KeepRecord(dset, index, sr3);
//return the dataset to refresh the control on the client application
return dset;
}
catch(System.Data.SqlClient.SqlException)
{
throw;
}
catch(IOException e)
{
MessageBox.Show(e.Message, "Save DataSet Error");
//if an error occurs while trying to save the dataset as XML, just return the dataset
return datasetx;
}
catch(System.Exception)
{
throw;
}
}
/**
* This method is the same as the previous one except that it updates an Oracle or Access database
*/
[WebMethod( Description = "Updates Oracle or Access database")]
public DataSet ManagedUpdate(string connectionString,string selectcommand,
string table,DataSet dset)
{
//increment the index so that the next dataset we save will have a unique name.
Interlocked.Increment(ref index);
//create the connection
string conString = connectionString;
//get records from the required table
string commandString = selectcommand;
try
{
if(!selectcommand.StartsWith(("SELECT")||("Select")))
{
//throw the custom exception if the sql command does not start with "SELECT" or "Select".
CustomSqlException s = new CustomSqlException("The SQL Command must start with "SELECT" or "Select");
throw s;
}
//if the dataset contains the key RecordName1, then get all the keys that start with "RecordName"
//then get the digits at the end of "RecordName" and sort them, then increment the last (highest) one
//and append that digit to "RecordName" as a new key of the dataset's ExtendedProperties.
if(dset.ExtendedProperties.ContainsKey("RecordName1")
{
ArrayList thekeys = new ArrayList;
ICollection keys = dset.ExtendedProperties.Values;
foreach(string key in keys)
{
if(key.StartsWith("RecordName"))
{
string keynum = key.Substring(10,key.Length - 1);
int num = keynum.ToInt32();
thekeys.Add(num);
}
}
thekeys.Sort();
int i = (int)thekeys[thekeys.Count-1];
i++;
dset.ExtendedProperties.Add("RecordName"+i.ToString(), @"C:\DbaseWebService_DataSets\" + index.ToString() + sr4 + ".xml");
}
else
{
dset.ExtendedProperties.Add(RecordName1, index.ToString() + s3);
}
dsetx.ExtendedProperties.Add("RecordName", index.ToString() + s4);
System.Data.OleDb.OleDbDataAdapter DataAdapter = new System.Data.OleDb.OleDbDataAdapter(commandString, conString);
//Now you can update the database
DataAdapter.Update(dsetx, table);
datasetx.AcceptChanges();
//save the dataset object as Xml
KeepRecord(dsetx, index, sr4);
//return the dataset to refresh the control on the client application
return dsetx;
}
catch(System.Data.SqlClient.SqlException)
{
throw;
}
catch(IOException e)
{
MessageBox.Show(e.Message, "Save DataSet Error");
//if an error occurs while trying to save the dataset as XML, just return the dataset
return dsetx;
}
catch(System.Exception)
{
throw;
}
}
/**
* This method allows you to retrieve a dataset from one of the datasets saved
* as XML given its filename (same as the RecordNamex of the dataset's extended property)
*/
[WebMethod(Description ="Retrieve a saved dataset]
public DataSet RetrieveDataSet(string RecordName)
{
string filename = RecordName;
DataSet xSet = new DataSet("RetrieveDataSet")
try
{
xSet.ReadXml(@"C:\DbaseWebService_DataSets\"+ filename);
return xSet;
}
catch(System.IOException e)
{
MessageBox.Show(e.Message, "Read DataSet Error");
throw e;
}
}
/**
* This method is used to save all the dataset objects passed out or received as an XML file.
*/
private void KeepRecord(DataSet ds, int s, string func)
{
//if the DbaseWebService_DataSets directory does not exist on the C drive, then create it (you may want to change
//the drive to D or E or F or X or any other).
if(!Directory.Exists(@"C:\DbaseWebService_DataSets"))
{
DirectoryInfo dir = new DirectoryInfo(@"C:\");
//make a new directory
DirectoryInfo newdir = dir.CreateSubdirectory("DbaseWebService_DataSets");
}
string fileindex = s.ToString();
//Write your entire DataSet to a file in the DbaseWebService_DataSets directory.
ds.WriteXml(@"C:\DbaseWebService_DataSets\" + fileindex + func + ".xml");
}
}
/**
* This method checks the directory where the datasets are saved and from the filenames there,
* gets the last value of the index variable (this might be useful if you restart the server)
* The method is made a bit complicated in case somebody saves another file into that directory.
*/
private void EnsureCorrectIndex(out int index)
{
if(Directory.Exists(@"C:\DbaseWebService_DataSets"))
{
DirectoryInfo info = new DirectoryInfo(@"C:\DbaseWebService_DataSets");
//get all the files in the directory
FileInfo[] fileArray = info.GetFiles();
if(fileArray.Length != 0)
{
ArrayList filenames = new ArrayList();
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;
ArrayList theindex = new ArrayList();
Regex theReg = new Regex(@"(\d+)");
MatchCollection theMatches;
for(int i = 0; i<filenames.Count; i++)
{
string test = filenames[i].ToString();
//here we test that the filename is of the format used by this program e.g 10_GetDataSet.xml
int x = test.IndexOf("_");
if(x >= 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);
//is theMatch the same as test2? if so, add test2 to the arraylist
foreach (Match theMatch in theMatches)
{
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 to index
else
{
index = 0;
}
} //End of method
} //End of web service class
} //End of Namespace