Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# XML
Hi Expert,
 
I have make a User define function in Excel.
 
it takes some argument, and it make a hit from remote server fetch one res the accroding to given argument.
 
The UDF woriking fine. But when user input this function in multiple cell and calucate. then it makes slow, becase each function hit each time to remote server to fetch data.
 
I want to do it fast.
 
the idea i have.
1. Make xml for each UDF arguemnt.
       <Data>
          <Formula>
              <EmpID>A1</EmpID>
              <DataItem>Salary</Dataitem>
              <Period>1998</Period>
           </Formula>
 
         <Formula>
              <EmpID>A1</EmpID>
              <DataItem>BasicSalary</Dataitem>
              <Period>1998</Period>
           </Formula>
      <Formula>
              <EmpID>A2</EmpID>
              <DataItem>Salary</Dataitem>
              <Period>1998</Period>
           </Formula>
 
         <Formula>
              <EmpID>A2</EmpID>
              <DataItem>HRA</Dataitem>
              <Period>Current</Period>
           </Formula>
        </Data>
 
2. I want to send it via web service.
 
3 . Get Result on web service from remote server.
 
4. Return Result as xml
 

  <Data>
          <Formula>
              <EmpID>A1</EmpID>
              <DataItem>Salary</Dataitem>
              <Period>1998</Period>
              <Result>20000</Result>
           </Formula>
 
         <Formula>
              <EmpID>A1</EmpID>
              <DataItem>BasicSalary</Dataitem>
              <Period>1998</Period>
              <Result>2000</Result>
           </Formula>
      <Formula><
              <EmpID>A2</EmpID>
              <DataItem>Salary</Dataitem>
              <Period>Current</Period>
         <Result>250000</Result>
           </Formula>
 
         <Formula>
              <EmpID>A2</EmpID>
              <DataItem>HRA</Dataitem>
              <Period>Current</Period>
               <Result>9000</Result>
           </Formula>
        </Data>
 
how it is possible.
 
mainly how i can parse xml in web service, and send argument to my store procedure for each xml row.
Posted 15-Apr-13 2:23am
Edited 15-Apr-13 2:45am
(no name)151.2K
v3
Comments
tumbledDown2earth at 16-Apr-13 8:24am
   
Can you share a bit of code?
Divaker @ Emerging Programmer at 16-Apr-13 8:43am
   
Please find the code
 

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.IO;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
namespace wsMyWebService
{
///
/// Summary description for Service1
///

[WebService(Namespace = "http://www.myurl.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class xmlServices : System.Web.Services.WebService
{
static SqlConnection Globalconn;
 
public xmlServices () {
 
string connStr = ConfigurationManager.ConnectionStrings["wsMyWebService_DB"].ConnectionString;
Globalconn = new SqlConnection(connStr);
//Uncomment the following line if using designed components
 
//InitializeComponent();
 
}
 
[WebMethod]
 
public System.Xml.XmlDocument UDFxmlMethod(System.Xml.XmlDocument xmldoc)
{
 
if (xmldoc != null)
{
 
//Read nodes of xml for my query
System.Xml.XmlNode root = xmldoc.DocumentElement;
 
string strIdentifier = root.SelectSingleNode("/Data/Formula/Identifier").InnerText;
string strDataItem = root.SelectSingleNode("/Data/Formula/DataItem").InnerText;
string strTimePeriod = root.SelectSingleNode("/Data/Formula/TimePeriod").InnerText;
string strTimePeriodFlag = root.SelectSingleNode("/Data/Formula/TimePeriodFlag").InnerText; ;
string strDataPeriodType = root.SelectSingleNode("/Data/Formula/DataPeriodType").InnerText; ;
string strTimePeriodType = root.SelectSingleNode("/Data/Formula/TimePeriodType").InnerText; ;
string strCurType = root.SelectSingleNode("/Data/Formula/CurType").InnerText; ;
 

//Get Result from query and return as XmlDocument
string strResult;
 
strResult = this.getData(strIdentifier, strDataItem, strTimePeriod, strTimePeriodFlag, strDataPeriodType, strTimePeriodType,strCurType);
 

 
XElement returnXml = new XElement("Data",
new XElement("Formula",
new XElement("Identifier", strIdentifier),
new XElement("DataItem", strDataItem),
new XElement("TimePeriod", strTimePeriod),
new XElement("TimePeriodFlag", strTimePeriodFlag),
new XElement("DataPeriodType", strDataPeriodType),
new XElement("TimePeriodType", strTimePeriodType),
new XElement("Result", strResult)
)
);
 


 
XmlDocument returnXmlDoc = new XmlDocument();
 
returnXmlDoc.LoadXml(returnXml.ToString());
 
return returnXmlDoc;
 

 

 

 
}
 
else
{
 
System.Xml.XmlDocument emptyXml = new System.Xml.XmlDocument();
 
return emptyXml;
 
}
 
}
 
//----------------------------------------------------------
 
public string getData(string strIdentifier, string strDataItem, string strTimePeriod, string strTimePeriodFlag, string strDataPeriodType, string strTimePeriodType, string strCurType)
{
 

 
José Amílcar Ferreira Casimiro at 16-Apr-13 11:03am
   
You should improve your question, instead of dumping code into comments area.
Divaker @ Emerging Programmer at 16-Apr-13 8:46am
   
public string getData(string strIdentifier, string strDataItem, string strTimePeriod, string strTimePeriodFlag, string strDataPeriodType, string strTimePeriodType, string strCurType)
{
 

 
SqlCommand command;
SqlDataAdapter adapter;
DataSet ds;
DataTable dt = new DataTable();
 
//@strIdentifier varchar(50),
//@strDataItem varchar(50),
//@strTimePeriod varchar(50) = 'CURRENT',
//@strDataPeriodType varchar(50) = 'TFQ',
//@strTimePeriodType varchar(50)= 'Fiscal'
 
try
{
if (Globalconn.State == ConnectionState.Closed)
{
Globalconn.Open();
}
if (IsNumeric(strTimePeriodType) == true)
{
command = new SqlCommand("spMYSP", Globalconn);
strTimePeriodFlag = "R";
}
else
{
command = new SqlCommand("spMYSP_cur", Globalconn);
}
command.CommandTimeout = 100;
command.CommandType = CommandType.StoredProcedure;
 

 
command.Parameters.Add("@strIdentifier", SqlDbType.VarChar, 50).Value = strIdentifier;
command.Parameters.Add("@strDataItem", SqlDbType.VarChar, 50).Value = strDataItem;
command.Parameters.Add("@strTimePeriod", SqlDbType.VarChar, 50).Value = strTimePeriod;
command.Parameters.Add("@strTimePeriodFlag", SqlDbType.VarChar, 50).Value = strTimePeriodFlag;
command.Parameters.Add("@strDataPeriodType", SqlDbType.VarChar, 50).Value = strDataPeriodType;
command.Parameters.Add("@strTimePeriodType", SqlDbType.VarChar, 30).Value = strTimePeriodType;
command.Parameters.Add("@strCurType", SqlDbType.VarChar, 30).Value = strCurType;
 

adapter = new SqlDataAdapter(command);
ds = new DataSet();
adapter.Fill(ds, "MYDS");
 
dt = ds.Tables["MYDS"];
 

return dt.Rows[0][0].ToString();
 

 
}
catch (Exception ex)
{
// new ProcessLogger("Exception : " + ex.Message, "[Default].getData");
return "";
}
 
finally
{
Globalconn.Close();
 
}
}
//**********************************************************************************************//
 
public static Boolean IsNumeric(string stringToTest)
{
int result;
return int.TryParse(stringToTest, out result);
}
 
}
}
José Amílcar Ferreira Casimiro at 16-Apr-13 11:03am
   
You should improve your question, instead of dumping code into comments area.
Divaker @ Emerging Programmer at 18-Apr-13 10:40am
   
Any Update????????????????????????

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 609
1 OriginalGriff 587
2 Maciej Los 325
3 Mathew Soji 195
4 BillWoodruff 190
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,712
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 16 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100