Using Oracle CRM On Demand Web Service to query Opportunity Data in .NET

1) Create a windows application

2) Put the following code in the form load event handler

Explaination is put in as comment

private void Form1_Load(object sender, EventArgs e)

{

// Login to the CRM server

string loginUrlString = “https://servername/Services/Integration?command=login”;

// Get the valid Session id to be appended for each subsequent request

String sessionID = ManageSession.Login(loginUrlString, @”orgname/username”, “pwd”);

try

{

// Download the opportunity wsdl from the Admin section of the CRM application

// Add web reference to the wsdl

// Create the instance of the opportunity entity

Opportunity myOpp = new Opportunity();

myOpp.Url = “https://servername/Services/Integration;jsessionid=” + sessionID;

// Set the query

// To find the record with sales stage as closed/won

queryType mySalesStage = new queryType();

mySalesStage.Value = “LIKE ‘Closed/Won'”;

// Values needed back from the server

// 1) Purchase Order Date

queryType myPODate = new queryType();

myPODate.Value = “”;

// 2) Purchase Order Number

queryType myPONumber = new queryType();

myPONumber.Value = “”;

// 2) Opportunity Name

queryType myOppName=new queryType();

myOppName.Value=“”;

// Create opportunityquery instance and set the appropriate parameters

OpportunityQuery myOppQuery = new OpportunityQuery();

myOppQuery.SalesStage = mySalesStage;

myOppQuery.dtPurchase_Order_Date = myPODate;

myOppQuery.stPurchase_Order_No = myPONumber;

myOppQuery.OpportunityName = myOppName;

// Set ListOfOpportunityQuery

ListOfOpportunityQuery lstOfOppQuery = new ListOfOpportunityQuery();

lstOfOppQuery.Opportunity = myOppQuery;

// Number of records to fetch

lstOfOppQuery.pagesize = “100”;

// set OpportunityQueryPage_Input

OpportunityQueryPage_Input myOppInput = new OpportunityQueryPage_Input();

myOppInput.ListOfOpportunity = lstOfOppQuery;

// Get the output

OpportunityQueryPage_Output myOutput = myOpp.OpportunityQueryPage(myOppInput);

// Get ListOfOpportunityData

ListOfOpportunityData myOppData = myOutput.ListOfOpportunity;

OpportunityData[] oppData = myOppData.Opportunity;

// Total number of records returned

MessageBox.Show(oppData.GetLength(0).ToString());

foreach(OpportunityData oData in oppData)

{

MessageBox.Show(“Opportunity Name :-“ + oData.OpportunityName

+ “, Opp PO Date =” + oData.dtPurchase_Order_Date.ToShortDateString()

+ ” Opp PO Number =” + oData.stPurchase_Order_No);

}

catch (SoapException ex)

{

MessageBox.Show(ex.Detail.InnerXml);

}

The code for ManageSession.Login static mehtod

public static String Login(String loginUrlString, String userName, String password)

{

try

{

// create a http request and set the headers for authentication

HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(loginUrlString);

HttpWebResponse myResponse;

myRequest.Method = “POST”;

// passing username and password in the http header

// username format if it includes slash should be the forward slash /

myRequest.Headers[“UserName”] = userName;

myRequest.Headers[“Password”] = password;

myResponse = (HttpWebResponse)myRequest.GetResponse();

Stream sr = myResponse.GetResponseStream();

// retrieve session id

char[] sep = { ‘;’ };

String[] headers = myResponse.Headers[“Set-Cookie”].Split(sep);

for (int i=0; i <= headers.Length-1; i++)

{

if (headers[i].StartsWith(“JSESSIONID”))

{

sep[0] = ‘=’;

SessionID = headers[i].Split(sep)[1];

break;

}

sr.Close();

myResponse.Close();

}

catch (WebException webException)

{

}

catch (Exception ex)

{

}

// send back the session id that should be passed to subsequent calls

// to webservices

return SessionID;

}

That’s it …

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology ! View all posts by Nishant Rana

24 thoughts on “Using Oracle CRM On Demand Web Service to query Opportunity Data in .NET”

Francis says:

December 18, 2012 at 3:24 pm

Hi Rana,
I have a doubt,
how to pass date value here to filter records

// 1) Purchase Order Date

queryType myPODate = new queryType();

myPODate.Value = “”;

// 2) Purchase Order Number

queryType myPONumber = new queryType();

myPONumber.Value = “”;

in mypodate.Value , how can i pass value to filter?
myPODate.Value = “Like …???”;

Pls.Help

LikeLike

Reply
Rajesh Kumar Punjabi (@rajeshpunjabi) says:

October 2, 2012 at 6:18 am

Hi,

I am working on query command and require to fetch all fields in query. By using your sample we have to set all the fields to querytype which is OK but cannot add new field on fly. Is there any way to traverse or to get the list of fields from WSDL?

Any help in this regards?

Thank you.

LikeLike

Reply
Agraj says:

August 22, 2012 at 1:30 pm

I am not able to find Opportunity.wsdl (web service object) in Admin Section of CRM application. Any thoughts, why this could be?
Thank you in advance.

LikeLike

Reply
Kausar says:

March 1, 2012 at 3:33 am

Hello Nishant,
I am new to CRM and I am learning stuff as I come across. I have been searching on the internet for some help on Web services and I am glad to dind this blog which is very helpful. I can make a start by referencing your code here to retrieve opportunties from CRM ON demand but my question is how do I transfer /FTP the queried data to another server. For Ex I have a requirement to pull all the opportunties with sales stage = ‘Closed/Won” and send it to a server on Accounting department. Any guidance in this regard is appreciated.
Thanks,
kausar

LikeLike

Reply
navneet says:

September 14, 2011 at 6:32 am

i am getting the following errors related ManageSession class.could u please help me?

Error 1 The name ‘ManageSession’ does not exist in the current context D:\DOT NET stuff\Projects\Calculator\WindowsFormsApplication3\WindowsFormsApplication3\Form1.cs 24 30 WindowsFormsApplication3

Error 3 The name ‘sessionID’ does not exist in the current context D:\DOT NET stuff\Projects\Calculator\WindowsFormsApplication3\WindowsFormsApplication3\Form1.cs 50 29 WindowsFormsApplication3

Error 2 The type or namespace name ‘Stream’ could not be found (are you missing a using directive or an assembly reference?) D:\DOT NET stuff\Projects\Calculator\WindowsFormsApplication3\WindowsFormsApplication3\Form1.cs 41 21 WindowsFormsApplication3

LikeLike

Reply
Vishnu says:

July 5, 2011 at 8:44 am

Hi , just want to know the java package for HttpWebRequest

LikeLike

Reply
Vinod Jaiswal says:

March 3, 2011 at 5:22 am

I am new in CRMOD. I am able to retreive oppotunity data using proxy in my local machine but when i deploy code in the clients test server i don’t want to use proxy for establishing connection with CRM OD web service.

public static String Login(String loginUrlString, String userName, String password)
{
string SessionID = string.Empty;
try
{

// create a http request and set the headers for authentication

HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(loginUrlString);
HttpWebResponse myResponse;
myRequest.Method = “POST”;
myRequest.Headers[“UserName”] = userName;
myRequest.Headers[“Password”] = password;
myResponse = (HttpWebResponse)myRequest.GetResponse();

Stream sr = myResponse.GetResponseStream();

// retrieve session id

char[] sep = { ‘;’ };

String[] headers = myResponse.Headers[“Set-Cookie”].Split(sep);

for (int i = 0; i <= headers.Length – 1; i++)
{
if (headers[i].StartsWith("JSESSIONID"))
{
sep[0] = '=';
SessionID = headers[i].Split(sep)[1];
break;

}

}
sr.Close();
myResponse.Close();

}
catch (Exception ex)
{
throw ex;

}
return SessionID;

}

I am getting Error "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 141.146.149.233:443"

But it works fine when i use proxy in my local machine.

I want to know whether proxy authentication is mandatory for establishing connection with CRM OD Webservice or i am doing something wrong.

Any help would be appreciated.

Thanks,
Vinod

LikeLike

Reply
1. S Aslam says:
  
  March 4, 2011 at 3:12 pm
  
  Hi Vinod,
  
  Put your all code related to accessing CRM OD Webservices in a Class Library or DAL then add this as reference in WEB.UI folder or Winforms project then you will be
  able to deploy on clients machine without having proxy. Otherwise its mandatory.
  
  Thanks,
  Shahzad Aslam
  
  LikeLike
  
  Reply
Khizar says:

February 1, 2011 at 11:30 am

I need same opportunity code for java. can any one provide me that?

LikeLike

Reply
S Aslam says:

November 23, 2010 at 10:22 pm

Hi

I am new in CRMOD, trying to retrieve data from CRM OD. We have lot of new fields defined in CRM, I need to retrieve that. I have downloaded the wsdl file which have that fields. How can new fields be retrieved. I don’t see the new fields in the Opportunity class properties. For example if I write the following code it should also list the new fields created in CRM OD.

OpportunityInsert_Input oi = new OpportunityInsert_Input();
oi.ListOfOpportunity.Opportunity[0].

Any help would be appreciated.

Thanks.

LikeLike

Reply
1. Nishant Rana says:
  
  November 25, 2010 at 5:35 am
  
  Hi Aslam,
  Ideally the new fields should be there in wsdl file.
  Send me the wsdl file.
  
  Regards,
  Nishant Rana
  
  LikeLike
  
  Reply
Sergio says:

November 10, 2010 at 7:29 pm

I can’t do this: Opportunity myOpp = new Opportunity();

because the class Opportunity is a Interface and. Can you help me?

LikeLike

Reply
1. Nishant Rana says:
  
  November 11, 2010 at 5:25 pm
  
  Send me the wsdl then i can atleast give it a try.
  
  LikeLike
  
  Reply
2. GB says:
  
  November 23, 2011 at 5:22 pm
  
  hi – did you ever get around this? i am getting this too
  
  LikeLike
  
  Reply
  1. GB says:
    
    November 23, 2011 at 5:23 pm
    
    sorry – i meant:
    I can’t do this: Opportunity myOpp = new Opportunity();
    
    because the class Opportunity is a Interface and. Can you help me?
    
    LikeLike
    
    Reply
Owais says:

June 17, 2010 at 2:02 pm

Hi,

Is it possible to query any CRM entity doing the comparison among its attributes? For Example I would normally query like this:

Obj_OppQueryPage_Input.ListOfOpportunity[0].ModifiedDate = “”;

Empty means “Give me everything without filtering on Modifed Date”

OR

Obj_OppQueryPage_Input.ListOfOpportunity[0].ModifiedDate = “01/01/2010”;

“Give me everything matching specified modified date”

Can I specify extraction filter doing the comparison among different fields of CRM? For example “Give me everything where Date1 is greater than Date2”? where Date1 and Date2 are available in WSDL file.

Any help will be highly appreciated.

Thanks, Owais

LikeLike

Reply
1. Nishant Rana says:
  
  June 18, 2010 at 5:45 am
  
  Hi Owais,
  I am afraid i won’t be able to help you in this. It was in my previous org. that i had worked on that project. Here i don’t even have an environment to try out that.
  
  Regards,
  Nishant Rana
  
  LikeLike
  
  Reply
Mat says:

May 1, 2010 at 9:35 pm

I am trying to query the Opportunity and visual studio did not provide this option at all
queryType mySalesStage = new queryType();

LikeLike

Reply
Michal David says:

August 9, 2009 at 6:57 pm

Hi,
I’ve managed to use your code to query on opportunities – thanks.
I need to query on opportunities, and then update the opportunities in the result set.
Do you know how it is done?

Thanks!

LikeLike

Reply
1. Nishant Rana says:
  
  August 10, 2009 at 6:02 am
  
  Hi Michal,
  
  Refer to this post for updation,
  
  https://nishantrana.wordpress.com/2009/03/23/updating-account-record-using-oraclesiebel-crm-on-demand-web-service-in-net/
  
  Hope it helps !
  
  LikeLike
  
  Reply
Carlos Molina says:

July 1, 2009 at 5:58 pm

Hi!

I´m having some trouble trying to log in CRM On Demand.

I´ve translated your code to VB.Net and I´m trying to login with the same credentials I use on the CRM On Demand website, but I only get “The remote server returned an error: (500) Internal Server Error.” error message.

Since my tests with other URLs return different error messages, I´m assuming that internet connection and proxy settings are OK.

Am I missing something?

Thanks!

LikeLike

Reply
Ivan Carrillo says:

May 5, 2009 at 4:46 pm

I’m trying to do the same with the release 16, but when i do :

aqpo = cuenta.accountQueryPage(aqpi);

i get an Exception(i’m using java). Everything is exactly the same you are doing, but with Account.

Do i need to implement accountQueryPage?

Thanxs 🙂

LikeLike

Reply
Ashish Sharma says:

April 21, 2009 at 4:24 am

I am trying to push data to CRM for opportunity, but getting error “Cannot implicitly convert type ‘OpportunityData[]’ to ‘ListOfOpportunityData’ any help supplied will be great, Thks

LikeLike

Reply
1. San says:
  
  July 3, 2009 at 4:29 pm
  
  Hi,
  Can you please guide me how can I get the url for the example?
  while doing this myOpp.url, it is saying it’s a namespace ,giving error.
  
  myOpp.Url = “https://servername/Services/Integration;jsessionid=” + sessionID;
  
  Any help would be appriciated.
  
  THanks
  
  LikeLike
  
  Reply