Trilight Zone

[tricore]

This Web service retrieves and submits information to the “Details” Oracle table. In Visual Studio .NET 2003, create a Microsoft Visual C# project with the ASP.NET Web Service template. Name it “OracleDetailsService”.

Add Code to the Web Service
1. Insert the following code below the automatically inserted Web method section:

[WebMethod]
public XmlNode GetXmlNodeFromString(string comm_str)
{
XmlDocument document = new XmlDocument();
document.LoadXml(comm_str);

return document;
}

[WebMethod]
public XmlNode GetData(int n)
{
System.Data.OracleClient.OracleConnection conn =
new System.Data.OracleClient.OracleConnection();

string str;

try
{
// Open the connection
String connString = "<connection string>";
conn.ConnectionString = connString;
conn.Open();

System.Data.OracleClient.OracleCommand comm = new OracleCommand();
comm.Connection = conn;

comm.CommandText = "Select * from Details where ID = " + n;
OracleDataReader reader = comm.ExecuteReader();

using(reader)
{
//Obtain the first row of data.
reader.Read();

//Obtain the LOBs
OracleLob blob = reader.GetOracleLob(2);

//Example - Reading binary data (in chunks).
byte[] buffer = new byte[blob.Length];
blob.Read(buffer, 0, buffer.Length);
str = ConvertByteToString(buffer);

}

XmlNode node = GetXmlNodeFromString(str);

return node;
}

catch(System.Data.OracleClient.OracleException oraExp)
{
return ErrorNode(oraExp);
}

catch(Exception exp)
{
return ErrorNode(exp);
}

finally
{
if(conn != null && conn.State == ConnectionState.Open)
conn.Close();
}
}

[WebMethod]
public void UpdateData(int idv, string n, XmlNode richData)
{
System.Data.OracleClient.OracleConnection conn =
new System.Data.OracleClient.OracleConnection();

string XN = richData.OuterXml;
byte[] rdata = ConvertStringToByteArray(XN);

try
{
// Open the connection
String connString = "<connection string>";
conn.ConnectionString = connString;
conn.Open();

// Create the command object
System.Data.OracleClient.OracleCommand comm = new System.Data.OracleClient.OracleCommand();
comm.Connection = conn;

string stmt;
stmt = "INSERT INTO Details(ID, NAME, BDATA)VALUES(:ID,:NAME,:BDATA)";

comm.CommandText = stmt;
System.Data.OracleClient.OracleParameter param;
// Create the parameter object, and associate with command object

param = comm.Parameters.Add("ID", OracleType.Int32);
param.Value = idv;

param = comm.Parameters.Add("NAME", OracleType.VarChar);
param.Value = n;

param = comm.Parameters.Add("BDATA", OracleType.Blob);
param.Value = rdata;

comm.ExecuteNonQuery();

}

catch(System.Data.OracleClient.OracleException oraExp)
{
SoapException soapExp =
new System.Web.Services.Protocols.SoapException(
"OracleException: " + oraExp.ToString(),
SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri,
oraExp);

throw soapExp;
}

catch(Exception exp)
{
SoapException soapExp = new SoapException(
"Exception: " + exp.ToString(),
System.Web.Services.Protocols.SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri,
exp);

throw soapExp;
}

finally
{
if(conn != null && conn.State == ConnectionState.Open)
conn.Close();
}
}

2. Insert the following code inside the private members region:
#region Private Members

private XmlNode ErrorNode(Exception exp)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml("<Error/>");
xmlDoc.DocumentElement.InnerText = exp.ToString();
return xmlDoc.DocumentElement;
}

private static string ConvertByteToString(byte[] bytesToConvert)
{
System.Text.Encoding encoding = new System.Text.UnicodeEncoding();

return encoding.GetString(bytesToConvert);
}

private static byte[] ConvertStringToByteArray(string stringToConvert)
{
return (new UnicodeEncoding()).GetBytes(stringToConvert);
}

#endregion
Note: Before submitting any data to a Blob data type we need to convert it to bytes first.
3. Make sure that you have referenced the following namespaces in your project:
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml;
using System.Data.OracleClient;
using System.Text;
using System.IO;
4. Click File, and then click Save All.
5. Click Debug, and then click Start Debugging.
6. When the Web page loads, click GetData. It will ask you to specify an integer value.
7. If you have any data stored then enter the ID field and then click the Invoke button. Or else it might just show the placeholders or throw an error message.
8. Ensure that you see XML tags and all Details information (placeholders) from the Details database, and then close both Web pages. Note that steps 3 through 6 will work only if the Web service is running on the local computer or if the Visual Studio Remote Debugging tools are installed.
9. Click the Build menu, and then click Build Solution.
10. Close the project.