Assign value from sql query to a field in CRM using C#

Question

Im trying to create a plugin for CRM that queries a mysql db and pulls the data from the db and adds it to a field in crm. What i have so far is this below, but im not sure how to assign a table column to a field value in crm?

`namespace Microsoft.Crm.Sdk.Samples
 {
   public class AssignSMSPlugin: IPlugin
   {
    /// <summary>
    /// A plug-in that creates a follow-up task activity when a new account is created.
    /// </summary>
    /// <remarks>Register this plug-in on the Create message, account entity,
    /// and asynchronous mode.
    /// </remarks>
    public void Execute(IServiceProvider serviceProvider)
    {
        //Conect to mySQl Database
        String str ="";
        MySqlConnection con = null;
        MySqlDataReader reader = null;
        try 
        {
            con = new MySqlConnection(str);
            con.Open();
            //Select statement to query table to get certain columns
            string cmdText = "SELECT col1, col2, col3 FROM table1";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            reader = cmd.ExecuteReader();

           // while (reader.Read())
          //  {
          //      Console.WriteLine(reader.GetString(0));

         //   }
        //Extract the tracing service for use in debugging sandboxed plug-ins.
        ITracingService tracingService =
            (ITracingService)serviceProvider.GetService(typeof(ITracingService));

        // Obtain the execution context from the service provider.
        IPluginExecutionContext context = (IPluginExecutionContext)
            serviceProvider.GetService(typeof(IPluginExecutionContext));

        // The InputParameters collection contains all the data passed in the message request.
        if (context.InputParameters.Contains("Target") &&
            context.InputParameters["Target"] is Entity)
        {
            // Obtain the target entity from the input parameters.
            Entity entity = (Entity)context.InputParameters["Target"];

            // Verify that the target entity represents an account.
            // If not, this plug-in was not registered correctly.
            if (entity.LogicalName != "customer")
                return;

            try
            {
                // Create a sms activity.Assign table column to  field in crm 
                Entity assign = new Entity("SMS Message");
                assign["to"] = "";
                assign["subject"] = "";
                assign["contact"] = ;
                assign["regardingobjectid"] = ;


                // Refer to the account in the task activity.
                if (context.OutputParameters.Contains("id"))
                {
                    Guid regardingobjectid = new Guid(context.OutputParameters["id"].ToString());
                    string regardingobjectidType = "customer";

                    assign["regardingobjectid"] =
                    new EntityReference(regardingobjectidType, regardingobjectid);
                }

                // Obtain the organization service reference.
                IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                // Create the task in Microsoft Dynamics CRM.
                tracingService.Trace("AssignSMSPlugin: Creating the SMS Activity.");
                service.Create(assign);
            }
            catch (FaultException<OrganizationServiceFault> ex)
            {
                throw new InvalidPluginExecutionException("An error occurred in the AssignSMSPlugin plug-in.", ex);
            }

            catch (Exception ex)
            {
                tracingService.Trace("AssignSMSPlugin: {0}", ex.ToString());
                throw;
            }
        }
    }
}`

Answer 1

Your logic looks faulty to me.
1) Change the part here

assign["to"] = "";
assign["subject"] = "";
assign["contact"] = ;
assign["regardingobjectid"] = ;

you need to actually assign values instead of nulls/empty strings.

2) There is generally some type of primary field (usually name/subject/title) on any entity; that should be set (I assume that is the subject field, which is currently = "") or it will come back to bite you.

3) you probably should use Entity.Attributes.Contains("id") instead of "OutputParameters" for context.OutputParameters.Contains("id") as I doubt you registered the plugin to have output parameters (this would require you to be generating them from a separate plugin)

4) I don't know about accessing MySQL via C#, but I assume you need to get the data from MySQL from that "reader", and use that to populate the CRM Values.

5) in most CRM plugins, the Org Service and ServiceFactory are instantiated at the beginning along with the tracing, so I would recommend moving that up much higher (this one is more of improved readability, so you can skip it).

6) your comments keep referring to accounts, but the check at the beginning is for "customer" (again, optional as it is just poor commenting)

7) the assign "Entity" is not a valid entity name, there should not be any spaces - more likely the entity name is something like

var assign = new Entity("new_smsmessage")