| Printable Version
Database Web Service
By Kunle Loto
|
This code is an update to the code in my previous article "PRACTICAL WEB SERVICES". Just as before, it is a webservice 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 webservice. 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, Accesss and all those databases that require managed providers. This webservice 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/")]. Feel free to report to me any bugs or errors. You can contact me at xxloto@yahoo.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 webservice.
|
/**
* 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= 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
|
