Use Filtered Views
banner art

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

This sample code can be found in the following files in the SDK download:

Server\HowTo\CS\Query\FilteredViews.cs
Server\HowTo\VB\Query\FilteredViews.vb

For more information about the helper methods in the Microsoft.Crm.Sdk.Utility.CrmServiceUtility namespace, see Utility Sample Code.

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.

[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Crm.Sdk.Utility;
using System.Web.Services.Protocols;

namespace Microsoft.Crm.Sdk.HowTo
{
    using CrmSdk;
   public class FilteredViews
   {
        static void Main(string[] args)
        {
            bool success = false;

            try
            {
                // TODO: Change the service URL, organization and database server name to match
                // your Microsoft Dynamics CRM server installation.
                success = FilteredViews.Run("http://localhost:5555", "AdventureWorksCycle", "localhost");
            }
            catch (SoapException ex)
            {
                Console.WriteLine("The application terminated with an error.");
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.Detail.InnerText);
            }
            catch (System.Exception ex)
            {
                Console.WriteLine("The application terminated with an error.");
                Console.WriteLine(ex.Message);

                // Display the details of the inner exception.
                if (ex.InnerException != null)
                {
                    Console.WriteLine(ex.InnerException.Message);

                    SoapException se = ex.InnerException as SoapException;
                    if (se != null)
                        Console.WriteLine(se.Detail.InnerText);
                }
            }
            finally
            {
                Console.WriteLine("Completed successfully? {0}", success);
                Console.WriteLine("Press <Enter> to exit.");
                Console.ReadLine();
            }
        }

      public static bool Run(string crmServerUrl, string orgName, string databaseServer)
      {
            bool success = false;
            
         try
         {
                #region Setup Data Required for this Sample

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

                WhoAmIRequest userRequest = new WhoAmIRequest();
                WhoAmIResponse user = (WhoAmIResponse)service.Execute(userRequest);

                #endregion

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

            // Define the SQL Query that selects the top 10 leads that were modified
            // by the current user. Because this queries against a filtered view,
            // this query returns only records that the calling user has Read
            // access to.
                string sqlQuery = @"SELECT Top 10 FullName 
                        FROM FilteredLead 
                        WHERE modifiedby = '" + userId.ToString() + "'";

            // Connect to the Microsoft Dynamics CRM database server. You must use Windows Authentication;
            // SQL Authentication will not work.
                SqlConnection connection = new SqlConnection("Data Source=" + databaseServer + ";Initial Catalog=" + orgName + "_MSCRM;Integrated Security=SSPI");
                // Create a DataTable to store the results of the query.
            DataTable table = new DataTable();

            // Create and configure the SQL Data Adapter that will fill the DataTable.
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand(sqlQuery, connection);

            // Execute the query by filling the DataTable.
            adapter.Fill(table);

                #region check success

                if(table.Rows.Count > 0)
                    success = true;

                #endregion
            }
            catch
            {
                // You can handle an exception here or pass it back to the calling method.
                throw;
            }
            

            return success;
      }
   }
}
[Visual Basic .NET]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Crm.Sdk.Utility
Imports System.Web.Services.Protocols
Imports Microsoft.VisualBasic

Imports CrmSdk
Namespace Microsoft.Crm.Sdk.HowTo
    Public Class FilteredViews
        Shared Sub Main(ByVal args() As String)
            Dim success As Boolean = False

            Try
                ' TODO: Change the service URL, organization and database server name to match
                ' your Microsoft Dynamics CRM server installation.
                success = FilteredViews.Run("http://localhost:5555", "AdventureWorksCycle", "localhost")
            Catch ex As SoapException
                Console.WriteLine("The application terminated with an error.")
                Console.WriteLine(ex.Message)
                Console.WriteLine(ex.Detail.InnerText)
            Catch ex As System.Exception
                Console.WriteLine("The application terminated with an error.")
                Console.WriteLine(ex.Message)

                ' Display the details of the inner exception.
                If ex.InnerException IsNot Nothing Then
                    Console.WriteLine(ex.InnerException.Message)

                    Dim se As SoapException = TryCast(ex.InnerException, SoapException)
                    If se IsNot Nothing Then
                        Console.WriteLine(se.Detail.InnerText)
                    End If
                End If
            Finally
                Console.WriteLine("Completed successfully? {0}", success)
                Console.WriteLine("Press <Enter> to exit.")
                Console.ReadLine()
            End Try
        End Sub

        Public Shared Function Run(ByVal crmServerUrl As String, ByVal orgName As String, ByVal databaseServer As String) As Boolean
            Dim success As Boolean = False

            Try
                '            #Region "Setup Data Required for this Sample"

                Dim service As CrmService = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.GetCrmService(crmServerUrl, orgName)
                service.PreAuthenticate = True

                Dim userRequest As New WhoAmIRequest()
                Dim user As WhoAmIResponse = CType(service.Execute(userRequest), WhoAmIResponse)

                '            #End Region

                'SDK: Guid userid = new Guid("{12765E27-7572-4e88-A7DB-AF2A80DD4A3B}");
                Dim userId As Guid = user.UserId

                ' Define the SQL Query that selects the top 10 leads that were modified
                ' by the current user. Because this queries against a filtered view,
                ' this query returns only records that the calling user has Read
                ' access to.
                Dim sqlQuery As String = "SELECT Top 10 FullName " & ControlChars.CrLf & "                        FROM FilteredLead " & ControlChars.CrLf & "                        WHERE modifiedby = '" & userId.ToString() & "'"

                ' Connect to the Microsoft Dynamics CRM database server. You must use Windows Authentication;
                ' SQL Authentication will not work.
                Dim connection As New SqlConnection("Data Source=" & databaseServer & ";Initial Catalog=" & orgName & "_MSCRM;Integrated Security=SSPI")
                ' Create a DataTable to store the results of the query.
                Dim table As New DataTable()

                ' Create and configure the SQL Data Adapter that will fill the DataTable.
                Dim adapter As New SqlDataAdapter()
                adapter.SelectCommand = New SqlCommand(sqlQuery, connection)

                ' Execute the query by filling the DataTable.
                adapter.Fill(table)

                '            #Region "check success"

                If table.Rows.Count > 0 Then
                    success = True
                End If

                '            #End Region
            Catch
                ' You can handle an exception here or pass it back to the calling method.
                Throw
            End Try


            Return success
        End Function
    End Class
End Namespace

See Also

Concepts


© 2009 Microsoft Corporation. All rights reserved.


Page view tracker