Accessing a SQL Database from a Microsoft Dynamics CRM Plug-in

Description:-

In this example we explain that how to use external SQL server database from CRM plugin and perform the operation on it.

This is the real issue I faced that I have requirement like when new lead is created in CRM at that its lead data automatically inserted to the SQL server database table. Here database is other not the same CRM database.so for that I have created one code that will allow you to insert the last created lead from CRM to SQL database table easily.

We demonstrate insert data into SQL database from the CRM or fetch the data form CRM database and insert it to other SQL database.so how to transfer data from CRM database to SQL database using CRM plug-in.

Here we explain how to insert last inserted Lead into CRM to external SQL database using web service. Or fetch last inserted lead data from CRM and insert these data into SQL database using web service.

Here we create one table Called “Lead” in the external SQL database and when new lead is inserted in CRM database at that we fire the plugin that will automatically get Lead data from the CRM database and insert it to lead table in SQL Database.

Before use this code you must remember the below points:

Sandboxed Plug-ins can access network through the HTTP and HTTPS protocols. This capability provides support for accessing popular Web resources like Social Sites, News Feeds, Web services, and more.

·         The following Web access restrictions apply to this Sandbox capability.

·         Only the HTTP and HTTPS protocols are allowed.

·         Access to localhost (loopback) is not permitted.

·         IP addresses cannot be used. You must use a named Web address that requires DNS name resolution.

Plugin Code:-

namespace kiritcheckplugin.Plugins

{

    using System;

    using System.ServiceModel;

    using Microsoft.Xrm.Sdk;

    using System.Data;

    using System.Data.SqlClient;

    using System.Web;

    using Microsoft.Xrm.Sdk.Query;

    using System.Collections.Generic;

    using kiritcheckplugin.Plugins.ServiceReference2;

    using System.Text;

    using Microsoft.Crm.Sdk;

    // using Microsoft.Crm.SdkTypeProxy;

    using System.Xml;

    /// <summary>

    /// PreValidateLead1 Plugin.

    /// </summary>   

    public class PreValidateLeadCreate : IPlugin

    {

       

        public void Execute(IServiceProvider serviceprovider)

        {

            try

            {

                ITracingService tracingService =

                  (ITracingService)serviceprovider.GetService(typeof(ITracingService));

               

                IPluginExecutionContext context = (IPluginExecutionContext)serviceprovider.GetService(typeof(IPluginExecutionContext));

              

                if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)

               {

                  

                    Entity entity = (Entity)context.InputParameters["Target"];

                    IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceprovider.GetService(typeof(IOrganizationServiceFactory));

                    //Service = access to data for modification

                    IOrganizationService service = factory.CreateOrganizationService(context.UserId);

                    // Adding Basic Http Binding and its properties.

                    BasicHttpBinding myBinding = new BasicHttpBinding();

                    myBinding.Name = "BasicHttpBinding_Service";

                    myBinding.Security.Mode = BasicHttpSecurityMode.None;

                    myBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.None;

                    myBinding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None;

                    myBinding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;

                    myBinding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;

                    myBinding.OpenTimeout = new TimeSpan(0, 10, 0);

                    myBinding.SendTimeout = new TimeSpan(0, 10, 0);

                    myBinding.ReceiveTimeout = new TimeSpan(0, 10, 0);

                    EndpointAddress endPointAddress = new EndpointAddress(@"http://capella:7093");

                    WebServiceSoapClient my = new WebServiceSoapClient(myBinding, endPointAddress);

                    my.Open();

                    string ab = my.InsertLead(entity.GetAttributeValue<string>("subject").ToString(), entity.GetAttributeValue<string>("lastname").ToString(), entity.GetAttributeValue<string>("firstname").ToString(), entity.GetAttributeValue<string>("emailaddress1").ToString());//

                    if (ab != null)

                    {

                        throw new InvalidPluginExecutionException("Success");

                    }

                    my.Close();

                }

            }

            catch (CommunicationException ex)

            {

                throw new InvalidPluginExecutionException(ex.ToString());

            }

       

       

        }

    }

}

ASMX Service Code :- 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data.SqlClient;

/// <summary>

/// Summary description for WebService

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// 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 WebService : System.Web.Services.WebService {

    public WebService () {

        //Uncomment the following line if using designed components

        //InitializeComponent();

    }

    [WebMethod]

    public string HelloWorld() {

        return "Hello World";

    }

    [WebMethod]

    public string InsertLead(string subject,string fnm,string lnm,string email)

    {

        fnm = "demo";

        string isSuccess = null;

        using (SqlConnection connection = new SqlConnection(@"Data Source=RIGEL\SQL2008R2;Initial Catalog=AX2012R2_Contoso_SPICA;Integrated Security=SSPI"))

        using (SqlCommand command = connection.CreateCommand())

        {

            command.CommandText = "Insert into Lead VALUES (@subject,@firstname,@lastname,@emailaddress)";

            command.Parameters.AddWithValue("@subject", subject);

            command.Parameters.AddWithValue("@firstname", fnm);

            command.Parameters.AddWithValue("@lastname", lnm);

            command.Parameters.AddWithValue("@emailaddress", email);

            connection.Open();

            command.ExecuteNonQuery();

            isSuccess = "Sucess";

        }

        return isSuccess;

    }

}