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 !

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

  1. 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

    Like

  2. 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.

    Like

  3. 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.

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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

    Like

    1. 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

      Like

  7. 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.

    Like

      1. sorry – i meant:
        I can’t do this: Opportunity myOpp = new Opportunity();

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

        Like

  8. 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

    Like

    1. 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

      Like

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

    Like

  10. 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!

    Like

  11. 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!

    Like

  12. 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 🙂

    Like

  13. 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

    Like

    1. 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

      Like

Share your thoughts

Fill in your details below or click an icon to log in:

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