Advertisements

Archive

Archive for September, 2018

SQL Server- Drop If Exists (DIE) Objects

September 28, 2018 Leave a comment

Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.

DIE_1

‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)

Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.

Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.

Drop If Exists (DIE) statement in SQL Server 2016:

From SQL Server 2016 CTP3 we can use DIE  statements to check if the Object exists and Drop before run the CREATE statements.

  • Dropping ‘Procedure’ if exists:

DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]

  • Dropping ‘Table’ if exists:

DROP TABLE IF EXISTS [Table_Name]

  • Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:

DIE_2

DROP TYPE IF EXISTS [Type_Name]

  • Delete Table’s COLUMN if Exists.

ALTER TABLE DROP COLUMN IF EXISTS

  • Delete Table’s CONSTRAINT if exists

ALTER TABLE DROP CONSTRAINT IF EXISTS

Below are the list of Objects that can be included in DIE statements.

DIE_3

Note:

  • If your SQL version is before 2016 below is the syntax

IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;

  • You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.

DIE_4.PNG

🙂

Advertisements
Categories: SQL Tags: ,

Web API Helper Code Compilation Error

September 18, 2018 Leave a comment

I was creating a console application to connect to Dynamics 365 Web API, and downloaded “Microsoft.CrmSdk.WebApi.Samples.HelperCode” NuGet package.

Compilation Error_1

I got “AcquireToken method is no longer available” compilation error, when I build the project

Compilation Error

Reason & Fix:

  • We have to use UserPasswordCredential class in ADAL v3.
  • Below is the code snippet

var credentials = new UserPasswordCredential(userName, password);
var context = new AuthenticationContext(authorityUri);
authResult = context.AcquireTokenAsync(serviceUrl, applicationId, credentials).Result;

Refer my previous article for step by step to connect to Dynamics 365 Web API.

🙂

[Fix] AADSTS65001: The user or administrator has not consented to use the application

September 18, 2018 Leave a comment

I was getting below exception when I was trying to connect to D365 Web API from console.

AAD_Access_2

Reason:

  • Issue was the Application registered on ‘Azure Active Directory’ was not granted the Permission

Fix:

  • Connect to the Azure Active Directory admin center,  using O365 credentials.
  • Select the ‘Application’ from the ‘Azure Active Directory -> App registrations’
  • Click on ‘Settings’ -> Required Permissions -> Click on ‘Grant permissions‘ button

AAD_Access_1

Refer my previous article on how to register application and connect to D365 Web API

🙂

[Code Snippet] Authenticate and Perform Operations using D365 Web API and C#

September 18, 2018 3 comments

As a continuation to my last article Different ways to connect authenticate dynamics 365 , lets see how to Authenticate Dynamics Web API using C#.

Note: Be mindful that this approach is different than connecting to Dynamics 365 using Microsoft.XRM.Tooling.Connector dll approach. This article explains how to connect to D365 using Web API which is no SDK .dll approach.

Pre-requisites:

  • Dynamics 365 subscription. Go for 30 days trail if not already have one.
  • Register an App in “Azure Active Directory” and get ‘Application ID’ and other parameters.
  • Visual Studio Console application

Steps to Register App in “Azure Active Directory”:

We need to register an application with Microsoft Azure Active Directory so that it can connect to the Microsoft Dynamics 365 server, authenticate using OAuth, and access the web services.

WebAPI_Snippet2

  • From the “Azure Active Directory admin center’, select ‘App registrations’ -> New application registration

WebAPI_Snippet3

  • Provide below details
    • Name – Provide name of the App. Can be any name minimum of 4 characters.
    • Application Type – Choose ‘Native’ as we are going to call Web API from Console application
    • Sign-on URL – Can be a valid URL. This you need to pass in the Console application.

WebAPI_Snippet4

  • Click ‘Create’ to complete the App creation
  • Post creation, open the App and copy the ‘Application ID’ which you need in Console application.

WebAPI_Snippet5

  • Click on ‘Settings’ -> Required Permissions -> Add ‘Dynamics CRM Online’ -> Enable the permission as below

WebAPI_Snippet6

  • Finally, select the App, click on ‘Endpoints’ and copy ‘OAuth 2.0 Authorization Endpoint‘ which you would need in Console Application.

WebAPI_Snippet7

Steps to connect to D365 WebAPI from Console Application:

After registering App in ‘Azure Active Directory’ now its time to connect to D365 Web API from Console Application.

  • Create a new C# Console Application project
  • Add below 2 Nuget packages to the project
    • Newtonsoft.Json
    • Microsoft.IdentityModel.Clients.ActiveDirectory

WebAPI_Snippet8

Code Snippet:

In the ‘Program.cs’ file add below

  • Add Using Namespaces:

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Net;

  • Add Global Variables:

// O365 User Name and Password
private const string userName = “rajeevpentyala@exploreSept18.onmicrosoft.com”;
private const string password = “YourPasswordHere”;
// D365 Application Url
private const string serviceUrl = “https://exploresept18.crm.dynamics.com”;
// Azure APP Application Id
private const string applicationId = “1549b5b3-XXXX-XXXX-94be-7a8eeaf3e081”;
// Redirct Uri specified during registration of application
private const string RedirectUri = “https://localhost”;
// OAuth 2.0 Authorization Endpoint copied from Azure APP
private const string authorityUri = “https://login.microsoftonline.com/9e3039aa-XXXX-XXXX-80e1-f67d40bd01cf/oauth2/authorize”;

private static AuthenticationResult authResult = null;

  • Main Method:

private static void Main(string[] args){

// Code to connect to D365
var credentials = new UserPasswordCredential(userName, password);
var context = new AuthenticationContext(authorityUri);
authResult = context.AcquireTokenAsync(serviceUrl, applicationId, credentials).Result;

// Call CRUD operations

// Task.WaitAll(Task.Run(async () => await ExecuteWhoAmI()));
// Task.WaitAll(Task.Run(async () => await CreateRecord()));
// Task.WaitAll(Task.Run(async () => await RetrieveContacts()));

}

  • Code to call WhoAmIRequest:

private static async Task ExecuteWhoAmI(){
var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl),
Timeout = new TimeSpan(0, 2, 0)
};
httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

// Add this line for TLS complaience
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

// Call WhoAmI
var retrieveResponse = await httpClient.GetAsync(“api/data/v9.0/WhoAmI”);
if (retrieveResponse.IsSuccessStatusCode){
var jRetrieveResponse = JObject.Parse(retrieveResponse.Content.ReadAsStringAsync().Result);

var currUserId = (Guid)jRetrieveResponse[“UserId”];
var businessId = (Guid)jRetrieveResponse[“BusinessUnitId”];

Console.WriteLine(“My User Id – ” + currUserId);
Console.WriteLine(“My User Id – ” + businessId);
Console.ReadLine();
}
}

  • Code to Retrieve Records:

private static async Task RetrieveContacts(){
var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl),
Timeout = new TimeSpan(0, 2, 0)
};
httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

// Add this line for TLS complaience
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

// Retrieve Contacts
var retrieveResponse = await httpClient.GetAsync(“api/data/v9.0/contacts”);
if (retrieveResponse.IsSuccessStatusCode){
var jRetrieveResponse = JObject.Parse(retrieveResponse.Content.ReadAsStringAsync().Result);

dynamic collContacts = JsonConvert.DeserializeObject(jRetrieveResponse.ToString());

foreach (var data in collContacts.value){
Console.WriteLine(“Contact Name – ” + data.fullname.Value);
}

Console.ReadLine();
}
}

WebAPI_Snippet1

  • Code to Create Record:

private static async Task CreateRecord(){
JObject contact1 = new JObject{
{ “firstname”, “Peter” },
{ “lastname”, “Cambel” },
{ “annualincome”, 80000 }
};

contact1[“jobtitle”] = “Junior Developer”;

var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl + “/api/data/v9.0/”),
Timeout = new TimeSpan(0, 2, 0)
};
httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

// Add this line for TLS complaience
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, “contacts”){
Content = new StringContent(contact1.ToString(), Encoding.UTF8, “application/json”)
};

HttpResponseMessage response = await httpClient.SendAsync(request);if (response.StatusCode == HttpStatusCode.NoContent) //204 {
Console.WriteLine(“POST succeeded, entity created!”);
//optionally process response message headers or body here, for example:
var entityUri = response.Headers.GetValues(“OData-EntityId”).FirstOrDefault();

// Update the Contact record
Task.WaitAll(Task.Run(async () => await UpdateRecord(entityUri)));

// Delete the contact record
Task.WaitAll(Task.Run(async () => await DeleteRecord(entityUri)));
}
else{
Console.WriteLine(“Operation failed: {0}”, response.ReasonPhrase);
throw new CrmHttpResponseException(response.Content);
}
}

  • Code to Update Record:

private static async Task UpdateRecord(string contactUri){
JObject contact1Add = new JObject{
{ “annualincome”, 80000 },
{ “jobtitle”, “Junior Developer” }
};

var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl + “/api/data/v9.0/”),
Timeout = new TimeSpan(0, 2, 0)
};

httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

HttpRequestMessage updateRequest1 = new HttpRequestMessage(new HttpMethod(“PATCH“), contactUri){
Content = new StringContent(contact1Add.ToString(), Encoding.UTF8, “application/json”)
};
HttpResponseMessage updateResponse1 = await httpClient.SendAsync(updateRequest1);

if (updateResponse1.StatusCode == HttpStatusCode.NoContent) //204 {
//Console.WriteLine(“Contact ‘{0} {1}’ updated with job title” +
// ” and annual income.”, contactUri.GetValue(“firstname”),
// contactUri.GetValue(“lastname”));
}
else{
Console.WriteLine(“Failed to update contact for reason: {0}”, updateResponse1.ReasonPhrase);
throw new CrmHttpResponseException(updateResponse1.Content);
}

}

  • Code to Delete Record:

private static async Task DeleteRecord(string contactUri){
var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl + “/api/data/v9.0/”),
Timeout = new TimeSpan(0, 2, 0)
};

httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

var response = httpClient.DeleteAsync(contactUri).Result;
if (response.IsSuccessStatusCode) //200-299 {
Console.WriteLine(“Contact has been deleted!!!”);
}
else if (response.StatusCode == HttpStatusCode.NotFound) //404 {
//Entity may have been deleted by another user or via cascade delete.
}
else //Failed to delete {
Console.WriteLine(“Error while deletion; Message: ” + response.Content);
}
}

🙂

D 365 Development – Different ways to connect/authenticate Dynamics 365

September 17, 2018 1 comment

There was a question posted on my blog by a Dynamics 365 newbie developer on how to authenticate to Dynamics 365 online from his console application.

In this article I am going to detail various ways to connect to Dynamics 365.

Option 1 – Connect using Dynamics 365 SDK assemblies:

  • You need Dynamics 365 SDK assemblies, If you are creating plug-ins, custom workflow activities, or custom XAML workflows and performing operations (i.e., Create/Update/Execute/Retrieve)
  • Refer Steps to create a basic plug-in
  • Download the latest SDK assemblies from nuget.

Option 2 – Connect using XRM Tooling assemblies:

  • If you are building .Net applications (i.e., Console/Web/Windows) use the XRM Tooling assemblies to connect to the Dynamics Application.
  • XRM tooling enables you to connect to your Dynamics 365 instance by using connection strings.
  • Refer article for different types of Connection strings based on your Dynamics deployment (i.e., One-prem/IFD/Office 365 etc..)
  • Below is the sample code to connect to your instance from Console:

Prerequisites:

  • Download the latest SDK assemblies from nuget in your console application.
  • Make sure you refer below .dlls in your console class file.
    • using Microsoft.Xrm.Sdk;
    • using Microsoft.Xrm.Tooling.Connector;

App.Config:

// Add below Connection string to your console’s App.config file

<connectionStrings>

<add name=”Xrm” connectionString=”Url=https://{orgname}.crm.dynamics.com; Username=rajeevpentyala@yourdomain.onmicrosoft.com; Password=XXXXXXX;authtype=Office365;Timeout=20″ />

</connectionStrings>

Code:

// Declare the Service Variables

private static OrganizationServiceProxy _serviceProxy;

private static IOrganizationService _service;

// Read the connection string configured in App.config file

var connectionString = ConfigurationManager.ConnectionStrings[“Xrm”].ConnectionString;

var crmConn = new CrmServiceClient(connectionString);

using (_serviceProxy = crmConn.OrganizationServiceProxy) {

_service = _serviceProxy;

var reqWhoAmI = new WhoAmIRequest();

var resp = (WhoAmIResponse)_service.Execute(reqWhoAmI);

var buID = resp.OrganizationId.ToString();

var userID = resp.UserId.ToString();

}

  • Refer my post on steps to connect to D 365 using Xrm.Tooling.Connector

Option 3 – Connect using Dynamics 365 Web API:

  • What if you want to connect to Dynamics from a Non- .NET applications (i.e., Java/PHP applications), the solution is Web API.
  • Web API provides development experience that can be used across a wide variety of programming languages, platforms, and devices.
  • Web API uses no DLL approach; Unlike above 2 approaches (i.e., XRM Tooling/SDK Assemblies), you don’t need to refer assemblies to connect to Web API.
  • There are 3 different ways to connect to Web API
    1. Using JavaScript in from Dynamics web resources (i.e., Jscript files, HTML, Ribbon). We don’t need to include any authentication code as the logged-in user is already authenticated by the application.
    2. If your Dynamics 365 is On-premise, you can authenticate Web API by passing User’s network credentials.
    3. If your Dynamics 365 is Online or IFD, you must use OAuth to connect.
  • Refer this article on steps to connect to Web API.
  • Web API is very convenient to use and test.
  • With Postman tool you can connect to Web API and perform operations. Refer article

Below is the Flow diagram gives idea on when to use which option among the 3 options:

WebAPI_Connect.PNG

🙂

Categories: CRM, Dynamics 365 Tags: , ,

Troubleshoot and Fix – Unable to access FTP folder error

September 12, 2018 Leave a comment

I was working on a POC/Prototype to read files from FTP location using console, for which I configured a FTP Site in my Windows 10 machine’s IIS.

FTP_1

FTP site connectivity worked fine till I switched my internet connectivity from LAN cable to WIFI, I started getting unable to access the FTP location error, when I access the FTP url from my console application.

Reason & Fix:

  • During the FTP web site configuration, we have to provide the “IP address:” which is specific to the machine.

FTP_2

  • When I switched from LAN to WIFI, my machine’s “IP address” has got changed.
  • Providing the correct “IP address” in ‘FTP web site’ binding solved the issue.
  • To check the “IP Address” of the machine
    • Open the ‘Command Prompt’
    • Execute ‘ipconfig‘ command
    • Copy the IP Address from “IPv4 Address”
    • Set the copied IPv4 Address in ‘FTP web site’ binding in IIS.

FTP_3

Refer this article on how to set up FTP website.

🙂

 

Dynamics 365 Portal – October ’18 release – What’s new

September 3, 2018 Leave a comment

With October 2018 release, Dynamics Portals coming up with below features:

Embed Power BI visualizations:

  • Administrators will be able to configure and enable Power BI for a portal.
  • This will require an appropriate Power BI license.
  • Customizer’s can use liquid code to embed Power BI dashboards and reports within pages.

Restrict portal access by IP address:

  • This feature would allow administrators to define a list of IP addresses that are allowed to access your portal.
  • When a request to the portal is generated from any user, their IP address is evaluated against the allow list. If the IP address is not in the list, the portal replies with an HTTP 403 status code.

Manage SharePoint documents:

  • This feature extends document management capabilities of Dynamics 365 applications to portals.
  • SharePoint Online document libraries configured with entities in Dynamics 365 can be surfaced via portal entity and web forms.
  • This allows portal users to perform the following actions:
    • Add documents
    • View and download documents
    • Delete document
    • Create folder

Simplified customization

  • An intuitive experience abstracts data model complexities, lowers the learning curve for portal customization, and leads to increased user productivity

Self-service portal diagnostics

  • This feature provides a self-service diagnostic tool that looks at portal configuration and identifies potential configuration problems as well as provides solutions on how to resolve the issues.

Configuration migration:

  • A schema for Portal configuration migration that works with the Configuration Migration SDK tool will be available in this release.

I will elaborate these features in my next articles.

🙂