Hi, sorry for late answer, but i faced the same problem and here is solution ive come with:
(what youre looking for is metadata.ExternalName and attribute.ExternalName)
public static class LmsMySqlConnection
{
public static MySqlConnection GetConnection(string server, string db, string user, string pwd, int port = 3306) {
try {
return new MySqlConnection($"Server={server ?? string.Empty};Port={port};Database={db ?? string.Empty};User={user ?? string.Empty};Password={pwd ?? string.Empty};");
}
catch (MySqlException e) {
Console.WriteLine(e.ToString());
throw;
}
}
}
public class MySqlRetrieve : IPlugin
{
public void Execute(IServiceProvider serviceProvider) {
var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
var factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
var service = factory.CreateOrganizationService(context.UserId);
if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is EntityReference)
{
EntityReference entRef = (EntityReference)context.InputParameters["Target"];
ColumnSet columns = (ColumnSet)context.InputParameters["ColumnSet"];
/*... getting metadata ...*/
var resp = (RetrieveEntityResponse)service.Execute(new RetrieveEntityRequest() { LogicalName = entRef.LogicalName, EntityFilters = EntityFilters.Attributes });
var metadata = resp.EntityMetadata;
string extname = metadata.ExternalName;
string primattr = metadata.PrimaryIdAttribute;
Entity initiatingProvider = service.Retrieve("lms_provider_mysql", metadata.DataSourceId.Value, new ColumnSet(true));
initiatingProvider.TryGetAttributeValue("lms_server", out string srv);
initiatingProvider.TryGetAttributeValue("lms_port", out int? port);
initiatingProvider.TryGetAttributeValue("lms_database", out string db);
initiatingProvider.TryGetAttributeValue("lms_user", out string user);
initiatingProvider.TryGetAttributeValue("lms_password", out string pwd);
var props_map = new Dictionary<string, string>();
foreach (AttributeMetadata attr in metadata.Attributes)
if (columns.Columns.Contains(attr.LogicalName))
props_map.Add(attr.LogicalName, attr.ExternalName);
string cmdString = string.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", string.Join(",", props_map.Values), extname, props_map[primattr], entRef.Id);
Entity e = new Entity(entRef.LogicalName);
using (MySqlConnection conn = LmsMySqlConnection.GetConnection(srv, db, user, pwd, port ?? 3306)) {
conn.Open();
using (MySqlCommand command = new MySqlCommand(cmdString, conn)) {
var reader = command.ExecuteReader();
if (reader.Read()) {
for (int i = 0; i < props_map.Count(); i += 1) {
object val = props_map.Keys.ToArray()[i] == primattr ? reader.GetGuid(i) : reader.GetValue(i);
val = val is uint ? Convert.ToInt32(val) : val;
e.Attributes.Add(props_map.Keys.ToArray()[i], val);
}
}
}
}
context.OutputParameters["BusinessEntity"] = e;
}
}
}
public class MySqlRetrieveMultiple : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
var factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
var service = factory.CreateOrganizationService(context.UserId);
if (context.InputParameters.Contains("Query") && context.InputParameters["Query"] is QueryExpression)
{
/*... getting metadata ...*/
var resp = (RetrieveEntityResponse)service.Execute(new RetrieveEntityRequest() { LogicalName = context.PrimaryEntityName, EntityFilters = EntityFilters.Attributes });
var metadata = resp.EntityMetadata;
string extname = metadata.ExternalName;
string primattr = metadata.PrimaryIdAttribute;
Entity initiatingProvider = service.Retrieve("lms_provider_mysql", metadata.DataSourceId.Value, new ColumnSet(true));
initiatingProvider.TryGetAttributeValue("lms_server", out string srv);
initiatingProvider.TryGetAttributeValue("lms_port", out int? port);
initiatingProvider.TryGetAttributeValue("lms_database", out string db);
initiatingProvider.TryGetAttributeValue("lms_user", out string user);
initiatingProvider.TryGetAttributeValue("lms_password", out string pwd);
var ext_attributes = metadata.Attributes.Where(attr => !string.IsNullOrEmpty(attr.ExternalName));
var query = context.InputParameters["Query"] as QueryExpression;
var props_map = new Dictionary<string, string>();
foreach (AttributeMetadata attr in metadata.Attributes)
if (query.ColumnSet.Columns.Contains(attr.LogicalName))
props_map.Add(attr.LogicalName, attr.ExternalName);
//string cmd = "SELECT km_passaccessid,dirty,status,password_hash,email FROM users";
string cmdString = string.Format("SELECT {0} FROM {1}", string.Join(",", props_map.Values), extname);
EntityCollection collection = new EntityCollection();
using (MySqlConnection conn = LmsMySqlConnection.GetConnection(srv, db, user, pwd, port ?? 3306)) {
conn.Open();
using (MySqlCommand command = new MySqlCommand(cmdString, conn)) {
var reader = command.ExecuteReader();
while (reader.Read()) {
Entity e = new Entity(query.EntityName);
var val0 = reader.GetValue(0);
var val1 = reader.GetValue(1);
var val2 = reader.GetValue(2);
var val3 = reader.GetValue(3);
var val4 = reader.GetValue(4);
for (int i = 0; i < props_map.Count(); i += 1) {
object val = props_map.Keys.ElementAt(i) == primattr ? reader.GetGuid(i) : reader.GetValue(i);
e.Attributes.Add(props_map.Keys.ElementAt(i), val);
}
collection.Entities.Add(e);
}
}
}
context.OutputParameters["BusinessEntityCollection"] = collection;
}
}
}
