locked
CRM 4.0: Accessing data with a Web Service RRS feed

  • Question

  • Hi.

    I want to develop a Web Service that giving a name, it will compare the list of customers from CRM if it already exists.

    Can someone help me?


    Best regards, Maria Cunha.
    Saturday, June 4, 2011 5:51 PM

Answers

All replies

  • My original idea is the following, but gives error on SqlConnection.
    Anyone know why?
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using System.Configuration;
    using System.Data;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Net;
    using Microsoft.Crm.Sdk;
    using Microsoft.Crm.SdkTypeProxy;
    using System.Text;
    using System.Data.Sql;
    
    
    namespace OMeuSite
    {
      
      [WebService(Namespace = "http://tempuri.org/")]
      [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
      [System.ComponentModel.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 Ver_Cliente: System.Web.Services.WebService
      {
    
        [WebMethod]
        public string Ver_Cliente(String nome, String cidade)
        {
          //Retrieve the organization name from the query string. 
          string orgname = "ISI-EquipaB2";
    
    
          Microsoft.Crm.SdkTypeProxy.CrmService service = new Microsoft.Crm.SdkTypeProxy.CrmService();
          service.Credentials = new System.Net.NetworkCredential("admin", "pass", "isi.local");
          service.Url = "http://win-0jgk5ro1cut:5555" + "/MSCRMServices/2007/CrmService.asmx";
    
          CrmAuthenticationToken token = new CrmAuthenticationToken();
          token.OrganizationName = orgname;
          service.CrmAuthenticationTokenValue = token;
          service.PreAuthenticate = true;
    
    
         
    
          // Define the SQL Query that selects the top 10 leads that were modified
          // by the current user. Because this queries against a filtered view,
          // this query returns only records that the calling user has Read
          // access to.
          string sqlQuery = @"SELECT Name 
                FROM AccountBase
                WHERE Name = '" + nome + "'";
    
          // Connect to the Microsoft Dynamics CRM database server. You must use Windows Authentication;
          // SQL Authentication will not work.
            SqlConnection connection = new SqlConnection("Data Source=" + databaseServer + ";Initial Catalog=" + orgName + "_MSCRM;Integrated Security=SSPI");
            // Create a DataTable to store the results of the query.
          DataTable table = new DataTable();
    
          // Create and configure the SQL Data Adapter that will fill the DataTable.
          SqlDataAdapter adapter = new SqlDataAdapter();
          adapter.SelectCommand = new SqlCommand(sqlQuery, connection);
    
          // Execute the query by filling the DataTable.
          adapter.Fill(table);
    
            #region check success
    
            if(table.Rows.Count > 0)
              success = true;
    
            #endregion
          }
          catch
          {
            // You can handle an exception here or pass it back to the calling method.
            throw;
          }
          
    
          
       }
    
      }
    }
    

    Best regards, Maria Cunha.
    Saturday, June 4, 2011 7:43 PM
  • Hi,

    If you are comparing a list of customers with CRM system then i would recommend to compare using SQL Query because it will be much faster then comapring records by retrieving using CRM SDK MultipleRetrieve request. It also depend upon the list of customer that are required to be compared if they are few upto 100 then i would suggest to use CRM webservie to retrieve and compare.

    In above query, are you trying to compare customer data with Name attribute? (name could be similar for different customers) data should be compare by unique values i.e. Customer Chamber of Commerce Registraiton Number, Organizaiton Number, Account Number or if it is individual then could also be with his/her national identity code number.

    In web services you can use SQL Database conenction, try to pass user name and password in conenction string (you need to comapre customer list and use unqiue values column for comparison)

    Here are few links that shows example of SDK Retrieve Multiple, Query By Expression and fetchXML:

    http://arvindcsit.wordpress.com/2009/01/20/using-the-retrievemultiple-method-in-microsoft-dynamics-crm-40/

    http://msdn.microsoft.com/en-us/library/bb928434.aspx

    http://msdn.microsoft.com/en-us/library/cc151200.aspx

    I hope this will help.


    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile

     



    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".

    Saturday, June 4, 2011 8:36 PM
  •  

    Thanks for the tip. How to design an academic, I'll use via SQL, so that my connection is not made ​​via Windows Authentication, how can I put in the code SQLauthentication, user and pass?

    SqlConnection connection = new SqlConnection("Data Source=" + databaseServer + ";Initial Catalog=" + orgname + "_MSCRM;Integrated Security=SSPI");


    Best regards, Maria Cunha.
    Saturday, June 4, 2011 8:53 PM
  • Hi,

    The connection string below does not use Windows Authenticaiton and it uses SQL Mixed Mode authentication, you need to check or create a new user in SQLManagement Studio->Security->Logins->user. Also make sure that the user have atleast data readonly privillages (Right Click on User, Select properties and Check User Database Mapping ) for MSCRM organization database.

    SqlConnection connection = new SqlConnection("Data Source=" + databaseServer + ";Initial Catalog=" + orgname + "_MSCRM;User ID=testuser1;password=pass@word1;");

     


    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile

    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".

    Saturday, June 4, 2011 9:03 PM
  • Hi,

    If your question is answered then please  "Vote as Helpful" and "Mark As Answer".


    Thank You,
    Jehanzeb Javeed,
    http://worldofdynamics.blogspot.com
    Linked-In Profile |CodePlex Profile



    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".
    Sunday, June 5, 2011 9:43 AM
  • You will want to download the SDK.  It has a lot of useful code samples and documentation in it's include .CHM file.

    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=420f0f05-c226-4194-b7e1-f23ceaa83b69

     

     


    Jamie Miley
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Monday, June 6, 2011 1:48 PM
    Moderator