The code below is implemented as a web service where the input is an xml file. It calls a stored procedure to add a row of data to a table. The problem is that when there is a space between a paramter, for example if
<param> ab </param> works
but <param> a b </param> wont. It works for the stored procedure on its own but i think this is because there are ' ' are the parameters. If I do this to the parameters for the procedure they will all be added to the table with single quotes around them and i dont want that...
This is the xml that forms the input
<?xml version="1.0" encoding="ISO-8859-1"?>
<testXML xmlns:xsi="xsi:noNamespaceSchemaLocation="testXSD.xsd">
<proc>
<typeOfCall>procedure</typeOfCall>
<nameOf> add_row_to_test </nameOf>
<returnType>DataSet</returnType>
<param> 919 </param>
<param> a b </param>
<param> yyyyyy </param>
</proc>
</testXML>
this is the web service that consumes the xml
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Xml;
using System.Xml.Schema;
using System.Data.Common;
using System.Data.OleDb;
using System.Text;
using MCE.ConfigHandler;
using System.Configuration;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Drawing;
namespace WebService1
{
/// <summary>
/// Summary description for Service2.
/// </summary>
///
[WebService(Namespace="]
public class Service2 : System.Web.Services.WebService
{
private static bool isValid = true;
public static void MyValidationEventHandler(object sender,
ValidationEventArgs args)
{
isValid = false;
Console.WriteLine("Validation event\n" + args.Message);
}
public Service2()
{
//CODEGEN: This call is required by the ASP.NET Web Services Designer
InitializeComponent();
}
#region Component Designer generated code
//Required by the Web Services Designer
private IContainer components = null;
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
// WEB SERVICE EXAMPLE
// The HelloWorld() example service returns the string Hello World
// To build, uncomment the following lines then save and build the project
// To test this web service, press F5
[WebMethod]
public string TestService()
{
return "Web service working";
}
[WebMethod (Description="Read/Validate xml file")]
public string readFile(string fileLoc)
{
try
{
String loc1 = fileLoc;
loc1 = loc1.Replace(@"\",@"\\");
XmlTextReader r = new XmlTextReader(loc1);
XmlValidatingReader v = new XmlValidatingReader(r);
v.ValidationType = ValidationType.Schema;
v.ValidationEventHandler +=new ValidationEventHandler(MyValidationEventHandler);
string procParamName = "";
string typeOfCall = "";
string returnType ="";
string trim ="";
string paramList ="";
string returnValue ="";
while (v.Read())
{
// Can add code here to process the content.
if (v.NodeType == XmlNodeType.Element)
{
if (v.LocalName.Equals("typeOfCall"))
{
typeOfCall = v.ReadString();
typeOfCall = typeOfCall.Trim();
}//End inner if.
if (v.LocalName.Equals("nameOf"))
{
procParamName = v.ReadString();
procParamName = procParamName.Trim();
procParamName = typeOfCall+","+procParamName;
}//End inner if.
if (v.LocalName.Equals("returnType"))
{
returnType = v.ReadString();
returnType = returnType.Trim();
returnType = procParamName+","+returnType;
}//End inner if.
if (v.LocalName.Equals("param"))
{
trim = v.ReadString();
trim = trim.Trim();
returnType = returnType+"," + trim;
paramList = returnType;
}//End inner if.
}//End outer if.
}
v.Close();
string strParams = paramList;
string [] arguments = strParams.Split(',');
for (int i=0;i<arguments.Length;i++)
{
arguments = arguments.ToUpper();
string s = arguments;
}
//End for loop.
if(arguments[0]=="PROCEDURE")
{
getData(arguments);
returnValue = "DataSet ";
}//End outer if.
else
{
for(int i=0;i<arguments.Length;i++)
{
if (i>2)
{
arguments="'"+arguments+"'";
}
//End inner if.
}
//End for.
if(arguments[2]=="DATASET")
{
getData(arguments);
returnValue = "DataSet";
}
//End inner if.
if(arguments[2]=="STRING")
{
string i = "";
i = getString(arguments);
returnValue = "String "+1;
}
//End inner if.
if(arguments[2]=="INT")
{
string i = "";
i = getInt(arguments);
returnValue = "Int "+i;
}
//End inner if.
if(arguments[2]=="DOUBLE")
{
string i = "";
i = getDouble(arguments);
returnValue = "Double "+i;
}
//End inner if.
}//End else.
string valid = "";
if (isValid)
valid = "Document is valid";
else
valid = "Document is invalid";
return returnValue+valid;
// Check whether the document is valid or invalid.
}//end try block
catch(XmlException eff)
{
string g = eff.ToString();
return g;
}//End of catch statement.
catch (Exception eg)
{
string g = eg.ToString();
return g;
}//End of catch statement.
}
public string getInt(string[] aParams)
{
string sConn = GetConnString();
string myQuery="SELECT "+aParams[1]+"(";
for(int i=0;i<aParams.Length;i++)
{
if(i>2 && i!=aParams.Length-1)
{
myQuery = myQuery+aParams+",";
}//End if.
if(i==aParams.Length-1)
{
myQuery = myQuery+aParams;
}//End if.
}//End for loop.
myQuery = myQuery +" ) FROM DUAL";
OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd = new OracleCommand(myQuery,objConn);
string iResult = "";
objConn.Open();
OracleDataReader myReader = objCmd.ExecuteReader();
try
{
while(myReader.Read())
{
iResult = myReader.GetValue(0).ToString();
}
//End while loop.
return iResult;
}
//End try block.
catch (Exception ex)
{
string err = ex.ToString();
return err;
}
//End catch block.
finally
{
objConn.Close();
}
//End finally block.
}
public DataSet getData(string[] aParams)
{
string sConn = GetConnString();
OracleConnection oracleConn = new OracleConnection(sConn);
OracleCommand objCmd;
DataSet dsRet = new DataSet();
try
{
PopulateCommand(aParams,out objCmd); //Add the parameters to the stored procedure.
objCmd.Connection=oracleConn;
OracleDataAdapter objDA = new OracleDataAdapter(objCmd);
objDA.Fill(dsRet);
}//End try block.
catch (Exception ex)
{
string err = ex.ToString();
}
//End catch.
finally
{
oracleConn.Close();
}
//End finally.
return dsRet;
}
private void PopulateCommand(string[] aParams,out OracleCommand objCmd)
{
objCmd = new OracleCommand();
objCmd.CommandType=CommandType.StoredProcedure;
OracleParameter param;
for (int i=0;i< aParams.Length;i++)
{
String par = aParams;
if (i==1)
{
objCmd.CommandText= aParams[1];
}
//End if.
if((i!=0) && (i!=1) && (i!=2))
{
String args = aParams;
param = new OracleParameter(args,OracleDbType.Varchar2);
param.Direction = ParameterDirection.Input;
param.Value = args;
objCmd.Parameters.Add(param);
}
//End if.
}
//End for loop.
}
//End PopulateCommand method.
public string getString(string[] aParams)
{
string sConn = GetConnString();
string myQuery="SELECT "+aParams[1]+"(";
for(int i=0;i<aParams.Length;i++)
{
if(i>2 && i!=aParams.Length-1)
{
myQuery = myQuery+aParams+",";
}
//End inner if.
if(i==aParams.Length-1)
{
myQuery = myQuery+aParams;
}
//End inner if.
}
//End for loop.
myQuery = myQuery +" ) FROM DUAL";
OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd = new OracleCommand(myQuery,objConn);
string iResult="";
objConn.Open();
OracleDataReader myReader = objCmd.ExecuteReader();
try
{
while(myReader.Read())
{
iResult = myReader.GetValue(0).ToString();
}
//End while loop.
return iResult;
}//End try block.
catch (Exception ex)
{
string err = ex.ToString();
return err;
}//End catch block.
finally
{
objConn.Close();
}//End finally block.
}//End method block.
public string getDouble(string[] aParams)
{
string sConn = GetConnString();
string myQuery="SELECT "+aParams[1]+"(";
for(int i=0;i<aParams.Length;i++)
{
if(i>2 && i!=aParams.Length-1)
{
myQuery = myQuery+aParams+",";
}//End if.
if(i==aParams.Length-1)
{
myQuery = myQuery+aParams;
}//End if.
}//End for loop.
myQuery = myQuery +" ) FROM DUAL";
OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd = new OracleCommand(myQuery,objConn);
string iResult = "";
objConn.Open();
OracleDataReader myReader = objCmd.ExecuteReader();
try
{
while(myReader.Read())
{
iResult = myReader.GetValue(0).ToString();
}//End while loop.
return iResult;
}//End try block.
catch (Exception ex)
{
string err = ex.ToString();
return err;
}//End catch block.
finally
{
objConn.Close();
}//End finally block.
}
static public string GetConnString()
{
string sDBServer = SecurityConfig.GetVersionSetting("DBServer");
string sDBUser = SecurityConfig.GetVersionSetting("DBUser");
string sDBPassword = SecurityConfig.GetVersionSetting("DBPassword");
string sDBProvider = SecurityConfig.GetVersionSetting("DBProvider");
StringBuilder sb = new StringBuilder();
sb.Append("User Id=");
sb.Append(sDBUser);
sb.Append(";Password=");
sb.Append(sDBPassword);
sb.Append(";Data Source=");
sb.Append(sDBServer);
return sb.ToString();
}
//End of method.
}
}
//}
and this is the stored procedure that is called
CREATE OR REPLACE PROCEDURE add_row_to_test
(
aid IN TEST.id%TYPE,
afn IN TEST.firstname%TYPE,
aln IN TEST.surname%TYPE
)
IS
BEGIN
INSERT INTO TEST (id,firstname,surname)
values (aid,afn,aln);
dbms_output.put_line('Added a row to the table');
COMMIT;
END;
/
I know the issue is that what ever way variables are executed in .net they dont expect a variable to be
more that one word so
xxxxx will be added but xxx xxxx wont because of the space.
It works fine in the stored proc because '' protect the issue of the spaces.
I know there is a lot of code .... i dont really expect anyone to know what to do cos its a bit of a niggly issue.... but he goes anyway
<param> ab </param> works
but <param> a b </param> wont. It works for the stored procedure on its own but i think this is because there are ' ' are the parameters. If I do this to the parameters for the procedure they will all be added to the table with single quotes around them and i dont want that...
This is the xml that forms the input
<?xml version="1.0" encoding="ISO-8859-1"?>
<testXML xmlns:xsi="xsi:noNamespaceSchemaLocation="testXSD.xsd">
<proc>
<typeOfCall>procedure</typeOfCall>
<nameOf> add_row_to_test </nameOf>
<returnType>DataSet</returnType>
<param> 919 </param>
<param> a b </param>
<param> yyyyyy </param>
</proc>
</testXML>
this is the web service that consumes the xml
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Xml;
using System.Xml.Schema;
using System.Data.Common;
using System.Data.OleDb;
using System.Text;
using MCE.ConfigHandler;
using System.Configuration;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Drawing;
namespace WebService1
{
/// <summary>
/// Summary description for Service2.
/// </summary>
///
[WebService(Namespace="]
public class Service2 : System.Web.Services.WebService
{
private static bool isValid = true;
public static void MyValidationEventHandler(object sender,
ValidationEventArgs args)
{
isValid = false;
Console.WriteLine("Validation event\n" + args.Message);
}
public Service2()
{
//CODEGEN: This call is required by the ASP.NET Web Services Designer
InitializeComponent();
}
#region Component Designer generated code
//Required by the Web Services Designer
private IContainer components = null;
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
// WEB SERVICE EXAMPLE
// The HelloWorld() example service returns the string Hello World
// To build, uncomment the following lines then save and build the project
// To test this web service, press F5
[WebMethod]
public string TestService()
{
return "Web service working";
}
[WebMethod (Description="Read/Validate xml file")]
public string readFile(string fileLoc)
{
try
{
String loc1 = fileLoc;
loc1 = loc1.Replace(@"\",@"\\");
XmlTextReader r = new XmlTextReader(loc1);
XmlValidatingReader v = new XmlValidatingReader(r);
v.ValidationType = ValidationType.Schema;
v.ValidationEventHandler +=new ValidationEventHandler(MyValidationEventHandler);
string procParamName = "";
string typeOfCall = "";
string returnType ="";
string trim ="";
string paramList ="";
string returnValue ="";
while (v.Read())
{
// Can add code here to process the content.
if (v.NodeType == XmlNodeType.Element)
{
if (v.LocalName.Equals("typeOfCall"))
{
typeOfCall = v.ReadString();
typeOfCall = typeOfCall.Trim();
}//End inner if.
if (v.LocalName.Equals("nameOf"))
{
procParamName = v.ReadString();
procParamName = procParamName.Trim();
procParamName = typeOfCall+","+procParamName;
}//End inner if.
if (v.LocalName.Equals("returnType"))
{
returnType = v.ReadString();
returnType = returnType.Trim();
returnType = procParamName+","+returnType;
}//End inner if.
if (v.LocalName.Equals("param"))
{
trim = v.ReadString();
trim = trim.Trim();
returnType = returnType+"," + trim;
paramList = returnType;
}//End inner if.
}//End outer if.
}
v.Close();
string strParams = paramList;
string [] arguments = strParams.Split(',');
for (int i=0;i<arguments.Length;i++)
{
arguments = arguments.ToUpper();
string s = arguments;
}
//End for loop.
if(arguments[0]=="PROCEDURE")
{
getData(arguments);
returnValue = "DataSet ";
}//End outer if.
else
{
for(int i=0;i<arguments.Length;i++)
{
if (i>2)
{
arguments="'"+arguments+"'";
}
//End inner if.
}
//End for.
if(arguments[2]=="DATASET")
{
getData(arguments);
returnValue = "DataSet";
}
//End inner if.
if(arguments[2]=="STRING")
{
string i = "";
i = getString(arguments);
returnValue = "String "+1;
}
//End inner if.
if(arguments[2]=="INT")
{
string i = "";
i = getInt(arguments);
returnValue = "Int "+i;
}
//End inner if.
if(arguments[2]=="DOUBLE")
{
string i = "";
i = getDouble(arguments);
returnValue = "Double "+i;
}
//End inner if.
}//End else.
string valid = "";
if (isValid)
valid = "Document is valid";
else
valid = "Document is invalid";
return returnValue+valid;
// Check whether the document is valid or invalid.
}//end try block
catch(XmlException eff)
{
string g = eff.ToString();
return g;
}//End of catch statement.
catch (Exception eg)
{
string g = eg.ToString();
return g;
}//End of catch statement.
}
public string getInt(string[] aParams)
{
string sConn = GetConnString();
string myQuery="SELECT "+aParams[1]+"(";
for(int i=0;i<aParams.Length;i++)
{
if(i>2 && i!=aParams.Length-1)
{
myQuery = myQuery+aParams+",";
}//End if.
if(i==aParams.Length-1)
{
myQuery = myQuery+aParams;
}//End if.
}//End for loop.
myQuery = myQuery +" ) FROM DUAL";
OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd = new OracleCommand(myQuery,objConn);
string iResult = "";
objConn.Open();
OracleDataReader myReader = objCmd.ExecuteReader();
try
{
while(myReader.Read())
{
iResult = myReader.GetValue(0).ToString();
}
//End while loop.
return iResult;
}
//End try block.
catch (Exception ex)
{
string err = ex.ToString();
return err;
}
//End catch block.
finally
{
objConn.Close();
}
//End finally block.
}
public DataSet getData(string[] aParams)
{
string sConn = GetConnString();
OracleConnection oracleConn = new OracleConnection(sConn);
OracleCommand objCmd;
DataSet dsRet = new DataSet();
try
{
PopulateCommand(aParams,out objCmd); //Add the parameters to the stored procedure.
objCmd.Connection=oracleConn;
OracleDataAdapter objDA = new OracleDataAdapter(objCmd);
objDA.Fill(dsRet);
}//End try block.
catch (Exception ex)
{
string err = ex.ToString();
}
//End catch.
finally
{
oracleConn.Close();
}
//End finally.
return dsRet;
}
private void PopulateCommand(string[] aParams,out OracleCommand objCmd)
{
objCmd = new OracleCommand();
objCmd.CommandType=CommandType.StoredProcedure;
OracleParameter param;
for (int i=0;i< aParams.Length;i++)
{
String par = aParams;
if (i==1)
{
objCmd.CommandText= aParams[1];
}
//End if.
if((i!=0) && (i!=1) && (i!=2))
{
String args = aParams;
param = new OracleParameter(args,OracleDbType.Varchar2);
param.Direction = ParameterDirection.Input;
param.Value = args;
objCmd.Parameters.Add(param);
}
//End if.
}
//End for loop.
}
//End PopulateCommand method.
public string getString(string[] aParams)
{
string sConn = GetConnString();
string myQuery="SELECT "+aParams[1]+"(";
for(int i=0;i<aParams.Length;i++)
{
if(i>2 && i!=aParams.Length-1)
{
myQuery = myQuery+aParams+",";
}
//End inner if.
if(i==aParams.Length-1)
{
myQuery = myQuery+aParams;
}
//End inner if.
}
//End for loop.
myQuery = myQuery +" ) FROM DUAL";
OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd = new OracleCommand(myQuery,objConn);
string iResult="";
objConn.Open();
OracleDataReader myReader = objCmd.ExecuteReader();
try
{
while(myReader.Read())
{
iResult = myReader.GetValue(0).ToString();
}
//End while loop.
return iResult;
}//End try block.
catch (Exception ex)
{
string err = ex.ToString();
return err;
}//End catch block.
finally
{
objConn.Close();
}//End finally block.
}//End method block.
public string getDouble(string[] aParams)
{
string sConn = GetConnString();
string myQuery="SELECT "+aParams[1]+"(";
for(int i=0;i<aParams.Length;i++)
{
if(i>2 && i!=aParams.Length-1)
{
myQuery = myQuery+aParams+",";
}//End if.
if(i==aParams.Length-1)
{
myQuery = myQuery+aParams;
}//End if.
}//End for loop.
myQuery = myQuery +" ) FROM DUAL";
OracleConnection objConn = new OracleConnection(sConn);
OracleCommand objCmd = new OracleCommand(myQuery,objConn);
string iResult = "";
objConn.Open();
OracleDataReader myReader = objCmd.ExecuteReader();
try
{
while(myReader.Read())
{
iResult = myReader.GetValue(0).ToString();
}//End while loop.
return iResult;
}//End try block.
catch (Exception ex)
{
string err = ex.ToString();
return err;
}//End catch block.
finally
{
objConn.Close();
}//End finally block.
}
static public string GetConnString()
{
string sDBServer = SecurityConfig.GetVersionSetting("DBServer");
string sDBUser = SecurityConfig.GetVersionSetting("DBUser");
string sDBPassword = SecurityConfig.GetVersionSetting("DBPassword");
string sDBProvider = SecurityConfig.GetVersionSetting("DBProvider");
StringBuilder sb = new StringBuilder();
sb.Append("User Id=");
sb.Append(sDBUser);
sb.Append(";Password=");
sb.Append(sDBPassword);
sb.Append(";Data Source=");
sb.Append(sDBServer);
return sb.ToString();
}
//End of method.
}
}
//}
and this is the stored procedure that is called
CREATE OR REPLACE PROCEDURE add_row_to_test
(
aid IN TEST.id%TYPE,
afn IN TEST.firstname%TYPE,
aln IN TEST.surname%TYPE
)
IS
BEGIN
INSERT INTO TEST (id,firstname,surname)
values (aid,afn,aln);
dbms_output.put_line('Added a row to the table');
COMMIT;
END;
/
I know the issue is that what ever way variables are executed in .net they dont expect a variable to be
more that one word so
xxxxx will be added but xxx xxxx wont because of the space.
It works fine in the stored proc because '' protect the issue of the spaces.
I know there is a lot of code .... i dont really expect anyone to know what to do cos its a bit of a niggly issue.... but he goes anyway