Archive
SQL Server- Drop If Exists (DIE) Objects
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.
‘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:
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.
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.
🙂
Web API Helper Code Compilation Error
I was creating a console application to connect to Dynamics 365 Web API, and downloaded “Microsoft.CrmSdk.WebApi.Samples.HelperCode” NuGet package.
I got “AcquireToken method is no longer available” compilation error, when I build the project
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
I was getting below exception when I was trying to connect to D365 Web API from console.
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
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#
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.
- Connect to Microsoft Office Admin Center.
- From the ‘Admin Centers’ menu, select ‘Azure Active Directory’.
- From the “Azure Active Directory admin center’, select ‘App registrations’ -> New application registration
- 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.
- Click ‘Create’ to complete the App creation
- Post creation, open the App and copy the ‘Application ID’ which you need in Console application.
- Click on ‘Settings’ -> Required Permissions -> Add ‘Dynamics CRM Online’ -> Enable the permission as below
- Finally, select the App, click on ‘Endpoints’ and copy ‘OAuth 2.0 Authorization Endpoint‘ which you would need in Console Application.
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
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();
}
}
- 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
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
- 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.
- Refer this article
- If your Dynamics 365 is On-premise, you can authenticate Web API by passing User’s network credentials.
- If your Dynamics 365 is Online or IFD, you must use OAuth to connect.
- The recommended authentication API for use with the Dynamics 365 Web API is Azure Active Directory Authentication Library (ADAL)
- 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.
- 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:
🙂
Troubleshoot and Fix – Unable to access FTP folder error
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 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.
- 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.
Refer this article on how to set up FTP website.
🙂
Dynamics 365 Portal – October ’18 release – What’s new
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.
- 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
- 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.
- 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.
🙂