Use Filtered Views in CRM 4

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 ]
	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;
	}
	}
	}

view raw Use Filtered Views.cs hosted with ❤ by GitHub