C# Web services from an Oracle database

This is another post about web services; previous one did a quick hello world app and talked more about setting up IIS on an internal web server. This one assumes a mapped drive onto the server, to which we shall upload the service apps. I’m using the F: drive in this case. The folder has been created previously. I’m using VS2008. I’m going to create a test web service to bring back XML from an Oracle database. I’ll give an example of both a SQL statement being used and a stored procedure.

The pdf of this page is OracleDBservices

To start with I’ll use the wizard to create a simple web service, File>New>Project>Web>ASP.Net Web Service. This gives a simple “Hello World” example.

oracle-service1

I changed the name from Service1 in the code and the name of the file (with a comment about source control not being applied to the original Service1 – that’s OK),

Public class FootballService : System.Web.Services.WebService

then Build>Publish to the F drive

This is the remote view,(not the index.htm – this was a test page)

oracle-service2

I had an error, so to see the details, we need to put <customErrors mode=”Off”> into the web.config file, somewhere in the <system.web> section.

I had renamed the class for the service from Service1 to FootballService. I needed to make the change in the asmx file. To open the file, just right click the asmx file and Open with the SourceCode editor, then change Service1 to your required name.

<%@ WebService Language="C#" CodeBehind="FootballService.asmx.cs" Class="FootballServices.Service1" %>

To get the Invoke button, add the following lines to the web.config file, system.web section.
<webServices>
<protocols>
<add name="HttpGet"/>
<add name="HttpPost"/>
</protocols>
</webServices>

Should then get this screen; click HelloWorld to get the next screen and Invoke should give the XML

oracle-service3

<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://tempuri.org/">Hello World</string>

It might take a few seconds while the compile occurs.

Changed the namespace to http://xxxx.com/footy/webservices as recommended

Now need to add the database parts. We shall add an Oracle data class – OracleData. Because this is common code between several (maybe) pages, we should put it into a shared App_Code folder. We can also put in there the main service and reference that file in the asmx file.

<%@ WebService Language="C#" CodeBehind="~/App_Code/FootballService.cs" Class="FootballServices.FootballService" %>

oracle-service4

This should still give the xml,
<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://bskyb.com/bc/webservices">Hello World</string>

First we’ll add a Version function, to show which version of the code and database is being used. Just some text the we shall keep up to date (!!?). Useful tho’.

Then the Oracle routines – simple SQL statement to begin with; create using a connection string, then a routine to send an SQL string and return a dataset.

public class OracleData
{
private string strConnection {get; set; }
/// <summary>
/// Create the object using this connection string
/// </summary>
/// <param name="connectionString">
public OracleData(string connectionString)
{
strConnection = connectionString;
}
/// <summary>
/// Get dataset from SQL String
/// </summary>
/// <param name="strSQLQuery">
/// <returns></returns>
public DataSet getDataSet(string strSQLQuery)
{
System.Data.DataSet ds;
using (OracleConnection conn = new OracleConnection(strConnection))
{
OracleDataAdapter dA;
ds = new System.Data.DataSet();
dA = new OracleDataAdapter(strSQLQuery, conn);
dA.Fill(ds);
}
return ds;
}
}

In the service code we will then have some properties to hold the parameters, (I’ve use our Election database for testing here). There’s a static string holding the database name, because I need to be able to switch to an alternative database. Bear in mind while consuming the service that it you put in a routine to change this value, it will be reset on a failover situation and would not be consistent if load balancing across several servers.

private FootballServices.OracleData db;
private Int32 intCounter;
private static string strDB = "ActiveDB";
private string strConnection = "Data Source=" + strDB + ";" +
"Pooling=True;" +
"User ID=xxxxx;" +
"Password=yyyyy";

Then we have a routine to call the SQL command,

/// <summary>
/// Return a list of elections available as xml.
/// This uses a SELECT statement in the program, not a procedure call.
/// </summary>
/// <returns></returns>
[WebMethod(Description = "Get Elections (SELECT)")]
public XmlDocument Elections()
{
string strSelect;
XmlDocument xmlDoc = new XmlDocument();
DataSet ds;
try
{
db = new FootballServices.OracleData(strConnection);
strSelect = "SELECT * FROM election";
// Return a dataset as XML, using a SELECT statement
ds = db.getDataSet(strSelect);
// Set these names to put the same named tags around the XML data
ds.DataSetName = "ELECTIONS";
ds.Tables[0].TableName = "ELECTION";
xmlDoc = GetXml(ds.Tables[0]);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
return null;
}
return xmlDoc;
}

I have called a getXML routine to convert dataset to XML

/// <summary>
/// This simply takes a data table and converts to xml format.
/// </summary>
/// <param name="dt">data table</param>
/// <returns>xml data</returns>
public XmlDocument GetXml(DataTable dt)
{
StringWriter sw = new StringWriter();
dt.WriteXml(sw);
sw.Close();
XmlDocument xd = new XmlDocument();
xd.LoadXml(sw.ToString());
return xd;
}

That’s it. Build>Publish to the web site; Invoke the GetElections to get an XML listing. The calling application just uses this url to get the data. Simple.

We could just duplicate this routine to get all the other SQL statements that we need, but a better way is to use stored procedures. This is somewhat more complicated. Each procedure call needs to be used with the exact data type for each parameter. This means that if procedures use different data types, they will need a different procedure call. We need to write a procedure call for each combination (or order) of parameter data types. I shall give the example of a procedure with parameters to solve that problem as well.

The routine in the main service is quite similar, but has the two parameters in the interface – electionID and regionID, which are then used in a call to runProcedure to fetch the data. (For some reason I allowed for a string on the electionID, which I convert to an integer. The data is converted to XML in the previous way.

/// <summary>
/// Return a list of all vZones in this election, region.
/// </summary>
/// <param name="electionID"></param>
/// <param name="regionID"></param>
/// <returns></returns>
[WebMethod(Description = "Get VZones in Region (PKG_ER)")]
public XmlDocument ElectionVZones(string electionID, int regionID)
{
Int32 intElection = Convert.ToInt32(electionID);
if (intElection < 100) intElection = 603;
XmlDocument xmlDoc = new XmlDocument();
DataSet ds;
try
{
db = new electionService.ElectionDB(strConnection);
// Return a dataset as XML, using a stored procedure call
ds = db.runProcedure("PKG_ELECTION_RESULTS.proc_ListVZone ",
"pElectionID", intElection,
"pRegionID", regionID);
ds.DataSetName = "ELECTION";
ds.Tables[0].TableName = "VZONE";
xmlDoc = GetXml(ds.Tables[0]);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
return null;
}
return xmlDoc;
}

The new addition here is the runProcedure method in the OracleData class,

public DataSet runProcedure(string strProcName, string param1, long value1,
string param2, long value2)
{
// This is an method to return a dataset as XML, using a stored procedure
System.Data.DataSet ds;
//
using (OracleConnection conn = new OracleConnection(strConnection))
{
OracleCommand cmd = new OracleCommand(strProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter prm1 = new OracleParameter(param1, OracleType.Number);
prm1.Direction = ParameterDirection.Input;
prm1.Value = value1;
cmd.Parameters.Add(prm1);
//
OracleParameter prm2 = new OracleParameter(param2, OracleType.Number);
prm2.Direction = ParameterDirection.Input;
prm2.Value = value2;
cmd.Parameters.Add(prm2);
//
OracleParameter prm3 = new OracleParameter("rsresults", OracleType.Cursor);
prm3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm3);
//
OracleDataAdapter da = new OracleDataAdapter(cmd);
ds = new System.Data.DataSet();
//
da.Fill(ds, "rsresults");
}
return ds;
}

This works OK but is quite verbose. I decided to add a few routines to add the parameters. They don’t save much but it reads a little better, to me.

public DataSet runProcedure(string strProcName, string param1, long value1,
string param2, long value2)
{
// This is a method to return a dataset as XML, using a stored procedure
System.Data.DataSet ds;
using (OracleConnection conn = new OracleConnection(strConnection))
{
OracleCommand cmd = new OracleCommand(strProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//
addNumberParameter(cmd, param1, value1);
addNumberParameter(cmd, param2, value2);
//
OracleParameter prm_rs = new OracleParameter("rsresults", OracleType.Cursor);
prm_rs.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm_rs);
//
OracleDataAdapter da = new OracleDataAdapter(cmd);
ds = new System.Data.DataSet();
da.Fill(ds, "rsresults");
}
return ds;
}

I have three types of parameter, you may have more, but the routines I use are,

internal void addNumberParameter(OracleCommand cmd, string param, long pvalue)
{
OracleParameter prm = new OracleParameter(param, OracleType.Number);
prm.Direction = ParameterDirection.Input;
prm.Value = pvalue;
cmd.Parameters.Add(prm);
}
//
internal void addStringParameter(OracleCommand cmd, string param, string pvalue)
{
OracleParameter prm = new OracleParameter(param, OracleType.VarChar);
prm.Direction = ParameterDirection.Input;
prm.Value = pvalue;
cmd.Parameters.Add(prm);
}
//
internal void addDateParameter(OracleCommand cmd, string param, DateTime pvalue)
{
OracleParameter prm = new OracleParameter(param, OracleType.DateTime);
prm.Direction = ParameterDirection.Input;
prm.Value = pvalue;
cmd.Parameters.Add(prm);
}

It all seems a bit awkward, so if you find a better way, let me know. Hope it helps.

Advertisements

~ by ianm on October 8, 2009.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: