Custom Virtual Table provider with CRUD operations: Implementing CRUD plug-ins and registering the assembly – part 3 of 6

Hi,
after creating a table on Azure SQL database in the part 2, now in this third post I show you how to create a Plug-in Project in Visual Studio to manage the integration with the data source.

In this simple PoC, a single project has been created that containing all separtate classes.

Let’s start with creating the project:

Now, install the following packages into the project through nuget:

Microsoft.CrmSdk.CoreAssemblies	
Microsoft.CrmSdk.Data	
Microsoft.CrmSdk.Deployment	
Microsoft.CrmSdk.Workflow	
Microsoft.CrmSdk.XrmTooling.CoreAssembly	
Microsoft.IdentityModel.Clients.ActiveDirectory	
Microsoft.Rest.ClientRuntime	
Newtonsoft.Json

Then you need to create the classes to handle all events.

Below, all the necessary classes to prepare for the project:

Connection.cs	
Create.cs
Update.cs	
Retrieve.cs	
RetrieveMultiple.cs	
Delete.cs

Add the following using statements to all classes:

using System; 
using System.Collections.Generic; 
using System.Data.SqlClient; 
using System.Linq; 
using System.Text; 
using System.Threading.Tasks; 
using Microsoft.Xrm.Sdk; 
using Microsoft.Xrm.Sdk.Extensions; 
using Microsoft.Xrm.Sdk.Data.Exceptions; 
using Newtonsoft.Json;

This should be the result after applying these first steps that I showed you:

Let’s start now with the implementation of all the classes.

Connection.cs – used to manage the connection with Azure SQL database table, our data source. Remember to replace DataSource, UserID, Password and InitialCatalog with your values:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Data.Exceptions;
using Newtonsoft.Json;

namespace VirtualTableCRUD.Plug_ins
{
    public static class Connection
    {
        public static SqlConnection GetConnection()
        {
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = "yoururl.database.windows.net";
                builder.UserID = "youruserid";
                builder.Password = "yourpassword";
                builder.InitialCatalog = "yourdb";
                SqlConnection connection = new SqlConnection(builder.ConnectionString);
                return connection;
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
                throw;
            }
        }
    }
}

Create.cs – used to handle the create message. Remember to manage correctly your SQL query and your table and fields schema name mapping. I used my publisher prefix dc_ to indicate the table and fields that I will create later:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Data.Exceptions;
using Newtonsoft.Json;

namespace VirtualTableCRUD.Plug_ins
{
    public class Create : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = serviceProvider.Get<IPluginExecutionContext>();
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity entity = (Entity)context.InputParameters["Target"];
                Guid id = Guid.NewGuid();
                string cmdString = "INSERT INTO person (PersonId,FirstName,LastName,Email) VALUES (@PersonId,@FirstName,@LastName,@Email)";
                SqlConnection connection = Connection.GetConnection();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = cmdString;
                    command.Parameters.AddWithValue("@PersonId", id);
                    command.Parameters.AddWithValue("@FirstName", entity["dc_firstname"]);
                    command.Parameters.AddWithValue("@LastName", entity["dc_lastname"]);
                    command.Parameters.AddWithValue("@Email", entity["dc_email"]);
                    connection.Open();
                    try
                    {
                        var numRecords = command.ExecuteNonQuery();
                        Console.WriteLine("inserted {0} records", numRecords);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                context.OutputParameters["id"] = id;
            }
        }
    }
}

Update.cs – used to handle the update message. Also here, remember to manage correctly your SQL query and your table and fields schema name mapping:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Data.Exceptions;
using Newtonsoft.Json;

namespace VirtualTableCRUD.Plug_ins
{
    public class Update : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = serviceProvider.Get<IPluginExecutionContext>();
            Guid id = Guid.Empty;
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity entity = (Entity)context.InputParameters["Target"];
                string cmdString = "UPDATE person SET {0} WHERE PersonId=@PersonId";
                SqlConnection connection = Connection.GetConnection();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.Parameters.AddWithValue("@PersonId", entity["dc_personid"]);
                    List<string> setList = new List<string>();
                    if (entity.Attributes.Contains("dc_firstname"))
                    {
                        command.Parameters.AddWithValue("@FirstName", entity["dc_firstname"]);
                        setList.Add("FirstName=@FirstName");
                    }
                    if (entity.Attributes.Contains("dc_lastname"))
                    {
                        command.Parameters.AddWithValue("@LastName", entity["dc_lastname"]);
                        setList.Add("LastName=@LastName");
                    }
                    if (entity.Attributes.Contains("dc_email"))
                    {
                        command.Parameters.AddWithValue("@Email", entity["dc_email"]);
                        setList.Add("Email=@Email");
                    }
                    command.CommandText = string.Format(cmdString, string.Join(",", setList)); connection.Open();
                    try
                    {
                        var numRecords = command.ExecuteNonQuery();
                        Console.WriteLine("updated {0} records", numRecords);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
    }
}

Retrieve.cs – used to handle the retrieve message. Also here, remember to manage correctly your SQL query and your table and fields schema name mapping:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Data.Exceptions;
using Newtonsoft.Json;

namespace VirtualTableCRUD.Plug_ins
{
    public class Retrieve : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = serviceProvider.Get<IPluginExecutionContext>();
            Guid id = Guid.Empty;
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is EntityReference)
            {
                EntityReference entityRef = (EntityReference)context.InputParameters["Target"];
                Entity e = new Entity("dc_person");
                string cmdString = "SELECT PersonId, FirstName, LastName, Email FROM person WHERE PersonId=@PersonId";
                SqlConnection connection = Connection.GetConnection();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = cmdString;
                    command.Parameters.AddWithValue("@PersonId", entityRef.Id);
                    connection.Open();
                    try
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                e.Attributes.Add("dc_personid", reader.GetGuid(0));
                                e.Attributes.Add("dc_firstname", reader.GetString(1));
                                e.Attributes.Add("dc_lastname", reader.GetString(2));
                                e.Attributes.Add("dc_email", reader.GetString(3));
                            }
                        }
                    }
                    finally
                    {
                        connection.Close();
                    } 
                }
                context.OutputParameters["BusinessEntity"] = e;
            }
        }
    }
}

RetrieveMultiple.cs – used to handle the retrieve multiple message. Also here, remember to manage correctly your SQL query and your table and fields schema name mapping:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Data.Exceptions;
using Newtonsoft.Json;

namespace VirtualTableCRUD.Plug_ins
{
    public class RetrieveMultiple : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = serviceProvider.Get<IPluginExecutionContext>();
            EntityCollection collection = new EntityCollection();
            string cmdString = "SELECT PersonId, FirstName, LastName, Email FROM person";
            SqlConnection connection = Connection.GetConnection();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = cmdString;
                connection.Open();
                try
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Entity e = new Entity("dc_person");
                            e.Attributes.Add("dc_personid", reader.GetGuid(0));
                            e.Attributes.Add("dc_firstname", reader.GetString(1));
                            e.Attributes.Add("dc_lastname", reader.GetString(2));
                            e.Attributes.Add("dc_email", reader.GetString(3));
                            collection.Entities.Add(e);
                        }
                    }
                }
                finally
                {
                    connection.Close();
                }
                context.OutputParameters["BusinessEntityCollection"] = collection;
            }
        }
    }
}

Delete.cs – used to handle the delete message. Also here, remember to manage correctly your SQL query and your table and fields schema name mapping:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Data.Exceptions;
using Newtonsoft.Json;

namespace VirtualTableCRUD.Plug_ins
{
    public class Delete : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = serviceProvider.Get<IPluginExecutionContext>();
            Guid id = Guid.Empty;
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is EntityReference)
            {
                EntityReference entityRef = (EntityReference)context.InputParameters["Target"];
                id = entityRef.Id;
                string cmdString = "DELETE person WHERE PersonId=@PersonId";
                SqlConnection connection = Connection.GetConnection();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = cmdString; command.Parameters.AddWithValue("@PersonId", id);
                    connection.Open();
                    try
                    {
                        var numRecords = command.ExecuteNonQuery();
                        Console.WriteLine("deleted {0} records", numRecords);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

    }
}

Once the development phase is completed, the plug-in must be registered.

Make sure that you have the latest version of the Plugin Registration Tool that provides support for CRUD operations in Virtual Tables!

After registering the Plug-in, this should be the result of the five messages registered:

Hope it helps, follow this serie and happy reading!

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.