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.JsonThen 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.csAdd 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!
Danilo Capuano 