Archive
C# – SQL – Bulk Insert records from Data Table and CSV
In my previous article I provided the steps to update records in single transaction using SQL “Table-Valued Parameter Types”.
In this article, lets see an easy way to bulk insert the records using SQLBulkCopy
What is SQLBulkCopy:
- SQLBulkCopy class comes with ‘System.Data’ namespace and can be used to write/Insert data to SQL Server tables.
- Though there are other ways to load data into a SQL Server table (i.e., INSERT statements, for example) but ‘SQLBulkCopy’ offers a significant performance advantage.
Below are the steps to use SQLBulkCopy with the combination of ‘Data Table’
Steps:
To simplify the explanation, I am taking a ‘Student’ table with 3 columns.
- Create a ‘Student’ table.
- In C# console application, prepare a ‘Data Table’ with the structure similar to ‘Student’ SQL table.
tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;//Add the Data Row to Table
tableStudents.Rows.Add(rowStudent);
- Instantiate and execute the ‘SQLBulkCopy’ by passing the Data Table
using (SqlBulkCopy bulkCopy = new SqlBulkCopy({SQLConnection}, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkCopy.DestinationTableName = “Student“; // Your SQL Table name//Optional mappings. Not required, if your ‘Data Table’ column names match with ‘SQL Table’ column names
bulkCopy.ColumnMappings.Add(“StudentID”, “StudentID”);
bulkCopy.ColumnMappings.Add(“StudentName”, “StudentName”);
bulkCopy.ColumnMappings.Add(“City”, “City”);
bulkCopy.WriteToServer(tableStudents);
isSuccuss = true;
}
- Execute the code which inserts rows from ‘Data Table’ to ‘Student’ SQL table.
Now lets see how to insert the CSV data to SQL table with query.
Note: You can also do the same with SQL Import Wizard
Bulk insert records from CSV to SQL Table:
- Assume that you have data available in a CSV file and want to insert them as records in SQL Table.
- Below is the query
Bulk Insert {SQL Table Name}
from ‘CSV File Path’
with
(rowterminator=’\n’,fieldterminator=’,’)
C# – Bulk Update Records – Pass Data Table to SQL Stored Procedure
I was building a console application to transact with large set of data in a SQL table and got a requirement to bulk update records.
Below are the options I got.
Option 1:
- Loop through the records in Console and make SQL update call for each record.
- This is a tedious and non-scalable for huge volumes data, as we need to make one SQL server transaction for each record from client (i.e, Console).
Option 2:
- Populate the records to update as rows in a ‘Data Table’ and pass it to a Stored Procedure, and have the Update statement in Stored Procedure.
- This is optimal as the heavy SQL updates are off loaded from Console to SQL server.
Lets see how to implement options 2.
To make the Stored Procedure accept Data Table, we need to define “Table-Valued Parameter Types” in SQL first.
What is a ‘Table-Valued Parameter Type”:
- Table-valued parameters are based on strongly-typed table structures that are defined by using SQL CREATE TYPE statements.
- You need create a “Table-Valued Type” (i.e.,StudentTableType from below scenario) with the structure similar to your Table (i.e., Student table from below scenario).
Steps to implement Option 2:
To simplify the explanation, I am taking a ‘Student’ table with 3 columns.
- Create ‘Student’ Table
- Create a ‘User-Defined Table Type’ (i.e., ‘StudentTableType‘) with ‘Student’ columns.
- Note: You don’t need to include all columns. Add columns only you need to update.
- Create a Stored Procedure with StudentTableType as Parameter
- In the Console, populate a ‘Data Table’ with ‘Student’ records as ‘Data Row’
tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;//Add the Data Row to Table
tableStudents.Rows.Add(rowStudent);
- Execute the Stored Procedure from Console by Passing ‘DataTable’ (i.e.,tableStudents) as Parameter
using (SqlConnection connection = ConnectionManager.SQLConnection)
{
SqlCommand cmdUpdateStudents = new SqlCommand(“BulkUpdateStudents”, connection)
{
CommandType = CommandType.StoredProcedure
};SqlParameter tvpParam = cmdUpdateStudents.Parameters.AddWithValue(“@tvpStudents”, tableStudents);
tvpParam.SqlDbType = SqlDbType.Structured;cmdUpdateStudents.ExecuteNonQuery();
}
Dynamics 365 – Configure ‘Relevance Search’
In Dynamics 365 online we have 2 types of Global Search
- Category Search
- Relevance Search
Category Search:
- Its a multi-entity Quick Find Search, where you can pull the matching records from all configured entities in a single screen.
Relevance Search:
- Relevance Search delivers fast and comprehensive results across multiple entities in a single list, sorted by relevance.
- It uses a dedicated search service external to Dynamics 365 (powered by Azure) to boost Dynamics 365 search performance.
Lets see how to configure ‘Relevance Search’ in Dynamics instance and enable an entity.
Enable Relevance Search:
- You have to enable Relevance Search first, Go to Settings -> Administration -> Check the ‘Enable Relevance Search’ checkbox
Set Entities Managed Property:
- To enable entity for ‘Relevance Search’, make sure Managed property set to ‘True’. This property will be ‘True’ by default.
Add Entity to the list:
- Open the ‘Default Solution’ -> Configure Relevance Search
- Add the entity to the list
Set default global search:
- You can choose the default search between ‘Categorized Search’ and ‘Relevance Search’ from your ‘Personal Settings’.
- Last search option provides the last search experience you used in the current browser client. For example, if you used Relevance Search for your last search, the system will continue using this search until you manually switch to Categorized Search.
Notes:
- ‘Relevance Search’ is only available in Dynamics 365 online.
- ‘Relevance Search’ is text-based, and can search only on fields of type Single Line of Text, Multiple Lines of Text, Option Sets, or Lookups. It doesn’t support searching in fields of Numeric or Date data type.
- You can use syntax to search with combinations. For example, type car+silver+2-door to find only matches that include all three words. Type car|silver|2-door to get results that contain car or silver or 2-door, or all three words
- There is no limit on no of entities can be included in the Relevance Search results. However, there is a limit on the total number of fields in the external search index. Currently, the maximum is 1000 searchable fields for an organization.
🙂
[Step by Step] Configure Server-to-Server (S2S) authentication using Azure AD and Application User – Dynamics 365
In this article I am going to explain, what is ‘Application User’ and how it helps to establish using Server-to-Server (S2S) authentication and Azure Active Directory
To explain the S2S authentication simpler, let’s take an integration requirement
- You have an ASP.Net Web Application
- You need pull the Contacts from a CRM organization and display in the ASP.Net Web Page
The conventional design approach for the above requirement would be
- Establish the CRM connection in your ASP.Net page by passing CRM User credentials
- Make a Retrieve call to CRM
- Read and bind the Contacts to a grid.
To implement the above design you need to have a paid CRM User credentials to interact with your Dynamics CRM organization.
So what is S2S authentication and how is it different from the legacy integration model we discussed above.
Server-to-Server (S2S) authentication:
- S2S authentication means you don’t need to use a paid Dynamics 365 user license when you connect to Dynamics 365 tenants.
- We will use a special user (i.e., Application User)
- Best part is, you can connect to D365 and make server calls from your application (i.e.,Web/Console) with no Dynamics SDK dlls and no ‘UserID/Password’.
What is this ‘Application User’:
- ‘Application User’ is a ‘systemuser’ record of type ‘Application User’
- There is no license fee for the ‘Application User’ account
How an ‘Application User’ account achieve the S2S authentication:
- ‘Application User’ with conjunction of Azure Active Directory (Azure AD) will establish S2S authentication.
- We first generates an ‘Application ID’ in Azure AD which we set in ‘Application User’ in Dynamics.
Lets see the step by step approach to achieve S2S authentication.
- Pre-requisites:
- Dynamics 365 instance
- Azure Subscription with same Office 365 account used for your D365 instance.
- High Level Steps
- Generate ‘Application ID’ and ‘Keys’ in ‘Azure’
- Add a new User in ‘Azure Active Directory’ (Azure AD)
- Create a new ‘Application User’ in Dynamics 365
Step 1 – Generate ‘Application ID’ and ‘Keys’ in ‘Azure’:
- Connect to your Azure
- Go to ‘App registrations’ service
- Create a ‘New application registration’
- Note: ‘Sign-on URL’ can be any valid URL.
- Copy the generated ‘Application ID’ (This is needed while creating ‘Application User’ in CRM)
- Generate ‘Keys’ (You need the ‘Key’ to establish connection in your Web Application/Console Application)
- Save the ‘Key’ (Note: You cannot read the key if you move away from the screen)
Step 2 – Add a new User in ‘Azure Active Directory’ (Azure AD):
- Connect to your Azure
- Go to ‘Users’ service
- Create a ‘New User’
- Note: ‘Password’ auto generates once you save. You don’t need to copy as this is not required further.
- Once the User saved, copy the ‘User Name’ (This is needed while creating ‘Application User’ in CRM)
Step 3 – Create a new ‘Application User’ in Dynamics 365:
This step we are going to create an ‘Application User’ in D365 by copying the details generated in Azure
- Connect to Dynamics 365
- Go to ‘Settings -> Security -> Users
- Switch the view to ‘Application Users’ and click ‘New’
- In the ‘New User’ screen
- Set ‘User Name’ with the ‘User Name’ copied from ‘Azure’
- Set ‘Application ID’ with the ‘Application ID’ copied from ‘Azure’
- Save the User and once saved, you notice the auto populated ‘Application ID URI’ and ‘Azure AD Object ID’
- Assign a ‘Security Role’
- ‘Security Role’ must be a Custom Security Role and you cannot assign OOB role.
- For this exercise, you might want to copy any existing OOB Security Role.
All right! We are all set and now its time to test S2S authentication from your console.
S2S Authentication Code Snippet:
Prerequisites:
- Install ‘ADAL’ and ‘NewtonSoft’ NuGet packages
Code:
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
private static async Task GetContactsAsync()
{
// Your Dynamics Web API URL
string api = “https://docmigrate.api.crm.dynamics.com/api/data/v9.0/”;AuthenticationParameters ap = AuthenticationParameters.CreateFromResourceUrlAsync(new Uri(api)).Result;
// Set ‘Application ID’ and ‘Key’ generated from Azure
var creds = new ClientCredential(“e4ac3a78-xxxx-403a-a94c-xxxxxxx”, “hEo/xxxxxxxS+LEiYHpxxxxxxxRe8xg0=”);AuthenticationContext authContext = new AuthenticationContext(ap.Authority);
var token = authContext.AcquireTokenAsync(ap.Resource, creds).Result.AccessToken;using (HttpClient httpClient = new HttpClient())
{
httpClient.Timeout = new TimeSpan(0, 2, 0);
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, token);// Retrieve Top 1 Contact
HttpResponseMessage response = await httpClient.GetAsync(api + “/contacts?$top=1”);// Parse the response
if (response.IsSuccessStatusCode)
{
JObject contact = JsonConvert.DeserializeObject<JObject>(await response.Content.ReadAsStringAsync());var contactName = contact.GetValue(“fullname”);
}
}
}
🙂
Dynamics 365 Spring Release 2018
Microsoft has released Spring ’18 release notes for Microsoft Business Applications and can be downloaded from here
Refer this link for all latest updates on Dynamics 365
Dynamics 365 – Create/Modify Views using App Designer
In this article, I am going to detail, how views can be created or modified using App Designer.
Open ‘App Designer’:
- To start off, go to ‘My Apps’ by navigating to ‘Settings –> Application –> My Apps’
- Open the ‘Sales’ app in ‘App Designer’ by clicking ‘OPEN IN APP DESIGNER’
Add/Edit Views:
Once the ‘App Designer’ loads, lets see how to modify Account’s ‘Active Accounts’ view
- From the ‘App Designer’ window, under ‘Entity View’ section, select the ‘Views’ tab from ‘Account’ row.
- On the right side ‘Public Views’ tab, select the View you want to modify (i.e., ‘Active Accounts’ in my case)
- After selecting the view from right window, back to ‘Views’ tab and expand, open the ‘Active Accounts’ to Edit
- To add new column, click on ‘Add’ button, choose Primary or Related entity to add the fields from and drag and Drop the column you want.
- Expand the ‘Filter Criteria’ tab to update the filter
- To add new view, click ‘Create New’ from your ‘App Designer’
With the ‘App Designer’, you can perform all the view customizations, which you can do with conventional view editor.
Refer article for more details.
🙂
[Code Snippet] Upload file to Azure blob – C#
In this article I am going to provide details and code snippets on how to upload attachment to Azure blob storage from console application.
Prerequisites:
Below are the prerequisites to run the code snippet and upload the file
- Azure subscription:You need an Azure subscription as the first step.
- You can spin up 30 days trail Azure subscription. Click here
- Note: You need to share valid credit card details to complete the subscription and you will be charged 2 INR.
- Storage Account:Add a storage account
- Container:
- Add a Container
- Copy the Container Name.
- Access Keys:Need the ‘Key’ to connect to Azure Blob from your C# console.
- Copy and keep below 2 values as shown in screenshot
- Storage Account Name
- Key 1
- Copy and keep below 2 values as shown in screenshot
- Nuget package:Add below nuget packages to your console project
-
- Microsoft.WindowsAzure.Storage
-
C# Code Snippet:
// Namespaces
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;private static void AddFileToBlob(){
var accountName = “{Storage Account Name}“; // Refer Prerequisites for value
var keyValue = “{key 1}“; // Refer Prerequisites for value
var useHttps = true;
var connValid = true;// Establish connection to Azure
var storageCredentials = new StorageCredentials(accountName, keyValue);
var storageAccount = new CloudStorageAccount(storageCredentials, useHttps);
var blobConString = storageAccount.ToString(connValid);// Retrieve storage account from connection string.
storageAccount = CloudStorageAccount.Parse(blobConString);// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();// Set container name
CloudBlobContainer container = blobClient.GetContainerReference(“{Container Name}“); // Refer Prerequisites for value// Set your blob name; It can be anything
CloudBlockBlob blockBlob = container.GetBlockBlobReference(“{Your desired blob name}“);// Set your file path which you want to upload to blob
using (var fileStream = System.IO.File.OpenRead(@”D:\ABC.PNG”)) {
blockBlob.UploadFromStream(fileStream);
}Console.WriteLine(“File added to Blob!!!”);
}
🙂
D365 – JScript – Deep Insert – Xrm.WebApi
Dynamics 365/Version 9.x has brought new objects to the Xrm client object model.
One of the new additions to Xrm object is ‘WebApi’ where you can perform CRUD operations.
WebAPI provides wrappers for all CRUD operations with in the framework so that we dont need to rely on 3rd party helper libraries. For example XrmServiceToolKit
In this article I am going to explain the Deep Insert using the Xrm.WebApi.
What is a ‘Deep Insert’:
- Deep Insert is a create operation where we create a primary record along with new related records.
- In simpler words, In a single ‘Create’ server call, we can create Account and related Contact and Opportunity along with a Task.
- Account (i.e., Primary)
- and Related
- Contact (and set Account.PrimaryContact)
- Opportunity (and associate with created Account)
- and Related
- Below is the syntax
Xrm.WebApi.createRecord(entityLogicalName, data).then(successCallback, errorCallback);
JScript:
Here is the sample code to create an Account and related records.
function createAccountAndRelated() {
// Define data to create primary (i.e.,Account) and related (i.e.,Contact and Oppoerunity) entity records
var data =
{
“name”: “XYZ Account”,
“creditonhold”: false,
“address1_latitude”: 47.639583,
“description”: “Creating Account along with new Primary Contact and Opportunity”,
“revenue”: 5000000,
“accountcategorycode”: 1,
“primarycontactid”:
{
“firstname”: “Rajeev”,
“lastname”: “Pentyala”
},
“opportunity_customer_accounts”:
[
{
“name”: “New Opportunity”,
“Opportunity_Tasks”:
[
{ “subject”: “Task associated to the new opportunity” }
]
}
]
}// create account record
Xrm.WebApi.createRecord(“account”, data).then(
function success(result) {
console.log(“Account created with ID: ” + result.id);
},
function (error) {
console.log(error.message);
}
);
}
🙂