Use Filtered Views

This sample shows how to use filtered views to retrieve all invoices where the lead source was "Employee Referral".

Note    Access to the SQL database is not supported in Microsoft Dynamics CRM Online

Example

The following code shows how to connect to the Microsoft Dynamics CRM SQL database directly and query this database securely using a filtered view.

   1:  //# [Use Filtered Views ]

   2:  using System;

   3:  using System.Data;

   4:  using System.Data.SqlClient;

   5:  using Microsoft.Crm.Sdk.Utility;

   6:  using System.Web.Services.Protocols;

   7:   

   8:  namespace Microsoft.Crm.Sdk.HowTo

   9:  {

  10:      using CrmSdk;

  11:     public class FilteredViews

  12:     {

  13:          static void Main(string[] args)

  14:          {

  15:              bool success = false;

  16:   

  17:              try

  18:              {

  19:                  // TODO: Change the service URL, organization and database server name to match

  20:                  // your Microsoft Dynamics CRM server installation.

  21:                  success = FilteredViews.Run("http://localhost:5555", "AdventureWorksCycle", "localhost");

  22:              }

  23:              catch (SoapException ex)

  24:              {

  25:                  Console.WriteLine("The application terminated with an error.");

  26:                  Console.WriteLine(ex.Message);

  27:                  Console.WriteLine(ex.Detail.InnerText);

  28:              }

  29:              catch (System.Exception ex)

  30:              {

  31:                  Console.WriteLine("The application terminated with an error.");

  32:                  Console.WriteLine(ex.Message);

  33:   

  34:                  // Display the details of the inner exception.

  35:                  if (ex.InnerException != null)

  36:                  {

  37:                      Console.WriteLine(ex.InnerException.Message);

  38:   

  39:                      SoapException se = ex.InnerException as SoapException;

  40:                      if (se != null)

  41:                          Console.WriteLine(se.Detail.InnerText);

  42:                  }

  43:              }

  44:              finally

  45:              {

  46:                  Console.WriteLine("Completed successfully? {0}", success);

  47:                  Console.WriteLine("Press <Enter> to exit.");

  48:                  Console.ReadLine();

  49:              }

  50:          }

  51:   

  52:        public static bool Run(string crmServerUrl, string orgName, string databaseServer)

  53:        {

  54:              bool success = false;

  55:              

  56:           try

  57:           {

  58:                  #region Setup Data Required for this Sample

  59:   

  60:                  CrmService service = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.GetCrmService(crmServerUrl, orgName);

  61:                  service.PreAuthenticate = true;

  62:   

  63:                  WhoAmIRequest userRequest = new WhoAmIRequest();

  64:                  WhoAmIResponse user = (WhoAmIResponse)service.Execute(userRequest);

  65:   

  66:                  #endregion

  67:   

  68:                  //SDK: Guid userid = new Guid("{12765E27-7572-4e88-A7DB-AF2A80DD4A3B}");

  69:                  Guid userId = user.UserId;

  70:   

  71:              // Define the SQL Query that selects the top 10 leads that were modified

  72:              // by the current user. Because this queries against a filtered view,

  73:              // this query returns only records that the calling user has Read

  74:              // access to.

  75:                  string sqlQuery = @"SELECT Top 10 FullName 

  76:                          FROM FilteredLead 

  77:                          WHERE modifiedby = '" + userId.ToString() + "'";

  78:   

  79:              // Connect to the Microsoft Dynamics CRM database server. You must use Windows Authentication;

  80:              // SQL Authentication will not work.

  81:                  SqlConnection connection = new SqlConnection("Data Source=" + databaseServer + ";Initial Catalog=" + orgName + "_MSCRM;Integrated Security=SSPI");

  82:                  // Create a DataTable to store the results of the query.

  83:              DataTable table = new DataTable();

  84:   

  85:              // Create and configure the SQL Data Adapter that will fill the DataTable.

  86:              SqlDataAdapter adapter = new SqlDataAdapter();

  87:              adapter.SelectCommand = new SqlCommand(sqlQuery, connection);

  88:   

  89:              // Execute the query by filling the DataTable.

  90:              adapter.Fill(table);

  91:   

  92:                  #region check success

  93:   

  94:                  if(table.Rows.Count > 0)

  95:                      success = true;

  96:   

  97:                  #endregion

  98:              }

  99:              catch

 100:              {

 101:                  // You can handle an exception here or pass it back to the calling method.

 102:                  throw;

 103:              }

 104:              

 105:   

 106:              return success;

 107:        }

 108:     }

 109:  }

Use a Join to Retrieve Activities by Participant

This sample demonstrates doing a simple JOIN to the activityparty entity using a query expression.

   1:  //# [Use a Join to Retrieve Activities by Participant ]

   2:  using System;

   3:  using CrmSdk;

   4:  using Microsoft.Crm.Sdk.Utility;

   5:   

   6:  namespace Microsoft.Crm.Sdk.HowTo

   7:  {

   8:        /// <summary>

   9:        /// This sample shows how to retrieve all activities where the user is a participant.

  10:        /// </summary>

  11:        public class RetrieveActivitiesByParticipant

  12:        {

  13:              static void Main(string[] args)

  14:              {

  15:                    // TODO: Change the server URL and Organization to match your CRM Server and CRM Organization

  16:                    RetrieveActivitiesByParticipant.Run("http://localhost:5555", "CRM_SDK");

  17:              }

  18:   

  19:              public static bool Run(string crmServerUrl, string orgName)

  20:              {

  21:                    // Set up the CRM Service.

  22:                    CrmService service = CrmServiceUtility.GetCrmService(crmServerUrl, orgName);

  23:   

  24:                    #region Setup Data Required for this Sample

  25:   

  26:                    bool success = false;

  27:   

  28:                    #endregion

  29:   

  30:                    try

  31:                    {

  32:                          // Get the user information.

  33:                          WhoAmIRequest userRequest = new WhoAmIRequest();

  34:                          WhoAmIResponse user = (WhoAmIResponse) service.Execute(userRequest);

  35:   

  36:                          // Create the ConditionExpression.

  37:                          ConditionExpression condition = new ConditionExpression();

  38:   

  39:                          // Set the condition for the retrieval to retrieve all activities that belong to the current user.

  40:                          condition.AttributeName = "partyid";

  41:                          condition.Operator = ConditionOperator.Equal;

  42:                          condition.Values = new string [] {user.UserId.ToString()};

  43:   

  44:                          // Build the filter based on the condition.

  45:                          FilterExpression filter = new FilterExpression();

  46:                          filter.FilterOperator = LogicalOperator.And;

  47:                          filter.Conditions = new ConditionExpression[] {condition};

  48:   

  49:                          // Create a LinkEntity to link the activity participant to the activity.

  50:                          LinkEntity link = new LinkEntity();

  51:   

  52:                          // Set the properties of the LinkEntity.

  53:                          link.LinkCriteria = filter;

  54:   

  55:                          // Set the linking entity to be the activity.

  56:                          link.LinkFromEntityName = EntityName.activitypointer.ToString();

  57:   

  58:                          // Set the attribute being linked to to be the activityid.

  59:                          link.LinkFromAttributeName = "activityid";

  60:   

  61:                          // Set the entity being linked to to be the activityparty.

  62:                          link.LinkToEntityName = EntityName.activityparty.ToString();

  63:                    

  64:                          // Set the attribute linking to the activityparty to be the activityid.

  65:                          link.LinkToAttributeName = "activityid";

  66:                    

  67:                          // Create the query.

  68:                          QueryExpression query = new QueryExpression();

  69:   

  70:                          // Set the properties of the query.

  71:                          query.EntityName = EntityName.activitypointer.ToString();

  72:                          // Be aware that using AllColumns may adversely affect

  73:                          // performance and cause unwanted cascading in subsequent 

  74:                          // updates. A best practice is to retrieve the least amount of 

  75:                          // data required.

  76:                          query.ColumnSet = new AllColumns();

  77:                          query.LinkEntities = new LinkEntity[] {link};

  78:   

  79:                          // Create the request object.

  80:                          RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

  81:   

  82:                          // Set the properties of the request object.

  83:                          retrieve.Query = query;

  84:              

  85:                          // Execute the request.

  86:                          RetrieveMultipleResponse retrieved = (RetrieveMultipleResponse) service.Execute(retrieve);

  87:   

  88:                          #region check success

  89:   

  90:                          if ((retrieved.BusinessEntityCollection.EntityName.ToLower().Equals("activitypointer")))

  91:                          {

  92:                                success = true;

  93:                          }

  94:   

  95:                          #endregion

  96:                    }

  97:                    catch (System.Web.Services.Protocols.SoapException)

  98:                    {

  99:                          // Add your error handling code here.

 100:                    }

 101:   

 102:                    return success;

 103:              }

 104:        }

 105:  }