Представления с использованием API

using System;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using System.Data.SqlClient;
using Microsoft.Crm.Sdk.Query;

namespace CustomQueryAPI
{
    public class QueryFetchPlugin : IPlugin
    {
        private string connectionString;
        IPluginExecutionContext pluginContext;

        public QueryFetchPlugin(string unsecure, string secure)
        {
            //"Data Source=CRMDEV;Initial Catalog=ICS_MSCRM;Integrated Security=SSPI"
            connectionString = secure;
        }

        public void Execute(IPluginExecutionContext context)
        {
            this.pluginContext = context;

            DynamicEntity query = (DynamicEntity)pluginContext.OutputParameters[ParameterName.BusinessEntity];
            string api = (string)query.Properties["description"];
            if (String.IsNullOrEmpty(api)) return;

            // Для систематизации и удобства наращивания api группируются в наборы ClassAPI.MetodAPI
            string[] paths = api.Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
            if (paths.Length < 2) return;



            string className = paths[0];
            string methodName = paths[1];

            string myFetch = String.Empty;

            switch (className)
            {
                case "Contact":
                    myFetch = retrieveContact(methodName);
                    break;
                case "Account":
                    myFetch = retrieveAccount(methodName);
                    break;
            }

            if (myFetch != null) query.Properties["fetchxml"] = myFetch;
        }

        private string queryExpressionToFetch(QueryBase query)
        {
            QueryExpressionToFetchXmlRequest request = new QueryExpressionToFetchXmlRequest();
            request.Query = query;

            ICrmService service = this.pluginContext.CreateCrmService(true);
            QueryExpressionToFetchXmlResponse responce = (QueryExpressionToFetchXmlResponse)service.Execute(request);

            return responce.FetchXml;
        }


        private string sqlToFetch(string entity, string primarykey, string sql)
        {
            string fetchPattern = @"<fetch version=""1.0"" output-format=""xml-platform"" mapping=""logical"" distinct=""false"">
                                            <entity name=""{0}"">
                                                <attribute name=""{1}""/>
                                                <filter type=""and"">
                                                    <condition attribute=""{1}"" operator=""in"">
                                                            {2}
                                                    </condition>
                                                </filter>
                                            </entity>
                                    </fetch>";

            string filterValues = String.Empty;

            using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
            {
                sqlConnection.Open();
                SqlCommand cmd = new SqlCommand(sql, sqlConnection);

                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        string id = ((Guid)reader[0]).ToString();
                        filterValues += "<value>" + id + "</value>\n";
                    }
                }
                finally
                {
                    reader.Close();
                }

            }
            return String.Format(fetchPattern, entity, primarykey, filterValues);
        }

        private string retrieveAccount(string methodName)
        {
            string fetch = String.Empty;
            switch (methodName)
            {
                case "NoOpportunitys":
                    string sqlQuery = @"select account.accountid from account
                                        left outer join opportunity
                                        on account.accountid = opportunity.customerid
                                        where   (opportunity.OpportunityId is null)";

                    fetch = sqlToFetch("account", "accountid", sqlQuery);
                    break;
            }

            return fetch;
        }

        private string retrieveContact(string methodName)
        {
            string fetch = String.Empty;
            switch (methodName)
            {
                case "BDayThisMonth":
                    string sqlQuery = "SELECT Contactid FROM Contact WHERE MONTH(GETDATE()) = MONTH(BirthDate)";

                    fetch = sqlToFetch("contact", "contactid", sqlQuery);
                    break;
            }
            return fetch;
        }

    }
}