locked
Getting Data into CRM 2011 from external SQL Server RRS feed

  • Question

  • I am trying to connect from CRM 2011 to an external MS sql server database to retrieve records to then insert into a custom form that I have designed. What is the best way of doing that dynamically. This is not bulk import. its querying the external db and getting one record to insert in. Any guidance or examples will be appreciated 
    Thursday, February 2, 2012 4:08 PM

All replies

  • Hi,

    Could you outline the end user experience you want to achieve - step by step?

    Scott


    www.develop1.net
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"
    Thursday, February 2, 2012 7:19 PM
    Answerer
  • Thanks for asking. So I have developed a form with new fields. One of these fields is a numeric field which will be used to retireve a record from an external  data base. I need to put a button on this form that performs the following action:

     

    1- Take the value of the numeric field

    2- Send a query to the external database

    3- Based on the value of the numeric field retrieve the record from the external database

    4- Populate the form with the field values from that external db

    5- The user can then save the form and the values will be saved permanently in the CRM record.

    Hope this helps

    Thanks

    Said

    Thursday, February 2, 2012 8:30 PM
  • Hi,

    There are a couple of practical options:

    1. Ask the user to save the form when they have entered the numeric field. Write a Create/Update Plugin that detects the change in the numeric field and performs the retrieve using ADO.NET, and populates the target entity values. When the form is refreshed after the save, the fields will be populated.

    2. Create a ribbon button named 'Lookup External Record' or something that makes sense to the users, and call a custom javascript function that saves the form with a hidden field set as well as the user provided numeric field. When the plugin fires, it can then detect the change in the hidden field and perform the lookup

    Information on writing plugins can be found at http://msdn.microsoft.com/en-us/library/gg334724.aspx

    hth,

    Scott


    www.develop1.net
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"
    Thursday, February 2, 2012 9:09 PM
    Answerer
  • Thanks. That makes sense. Will try it
    Friday, February 3, 2012 5:26 AM
  • Hi,

    Im try to do the same process with a external database but i cant

    1) I have a wcf service, that return a person object:

    public class Service1 : IService1{ 

    public persona consultarPersona(String pcedula){  

    persona objetoPersona = new persona();       

    objetoPersona.cedulaPersona = pcedula;  

    gestor objetoGestor = new gestor();  

    List<persona> personas = objetoGestor.consultarPersona(objetoPersona);       

    objetoPersona = personas.First();  

    return objetoPersona;   

    }

    }

    2) This is web services connection string

    When i try with the CRM BD the connection is successful and retrive the information that i need, but when i try connect to my BD return null

    private string cadena = "Data Source=ADCRM\\ADCRM;Initial Catalog=ADCRM_MSCRM;Integrated Security=True"; // CRM DB
    private string cadena = "Data Source=ADCRM\\ADCRM;Initial Catalog=ADCRM_MSCRM;Integrated Security=True";// My DB

    3)The plugin consumming the web service

    public class AccountNumberPlugin: IPlugin
    	{
            private OrganizationServiceProxy _serviceProxy;
    
    
           // private Guid contactId= new Guid();
           
    
            public void Execute(IServiceProvider serviceProvider)
    		{
                Microsoft.Xrm.Sdk.IPluginExecutionContext miContexto = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
                    serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
                if (miContexto.InputParameters.Contains("Target") &&
                    miContexto.InputParameters["Target"] is Entity)
                {
                    Entity entity = (Entity)miContexto.InputParameters["Target"];
                    Guid guid = entity.Id;
                    String cedula= (String)entity.Attributes["psi_numidentificacion"];
                    if (entity.LogicalName == "contact") 
                    {
                     
                        IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                        IOrganizationService service = serviceFactory.CreateOrganizationService(miContexto.UserId);
                        configuracion config = new configuracion();
                        using (_serviceProxy = new OrganizationServiceProxy(config.OrganizationUri,config.HomeRealmUri,config.Credentials,config.DeviceCredentials))
                        {
                                _serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
                                BasicHttpBinding myBinding = getBinding();
                                EndpointAddress endPointAddress = new EndpointAddress("http://localhost:8081/Service1.svc");
                                ServiceReference1.Service1Client myClient = new ServiceReference1.Service1Client(myBinding, endPointAddress);
                                ServiceReference1.persona result = myClient.consultarPersona(cedula);
                                String strValor = Convert.ToString(result.idPersona);
                                String miId = Convert.ToString(entity.Id);
                                entity.Attributes["firstname"] = result.nombrePersona;
                                entity.Attributes["lastname"] = result.primerApellidoPersona;
                                entity.Attributes["psi_secondlastname"] = result.segundoApellidoPersona;
                        }		
    				}
    			}
    		}
    
            public BasicHttpBinding getBinding()
            {
    
                BasicHttpBinding myBinding = new BasicHttpBinding();
                myBinding.Security.Mode = BasicHttpSecurityMode.None;
                myBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.None;
                myBinding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None;
                myBinding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
    
                return myBinding;
            }
    }

    Thanks,

    Friday, March 9, 2012 4:40 PM