SQL Connection to CRM Database

Dynamics CRM has very efficient web services using which you can retrieve data such as Organization service Or WebApi. However if you still need to retrieve data from database here is a quick code helper – which i am using in a console application.

//Initiate the connection
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString))
{
//create a command variable
using (var command = connection.CreateCommand())
{
//add query to your command
command.CommandText = "your SQLquery goes here";

// open the connection first and then execute the query using Execute Reader
connection.Open();
result = command.ExecuteReader();

here is the app.config which i have used , i am using the windows authentication hence you donot need to mention credentials.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="SQLServerConnection" connectionString="Server=.;Database=events.website;Trusted_Connection=True;"/>
</connectionStrings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2"/>
</startup>
</configuration>

By Now you will have your dataset in “result”; here is another piece of code to read the data:

 if (result.HasRows)
{
 while (result.Read())
{
string contactid= result["columnName"].ToString();
string contact = result["columnName"].ToString();
DateTime createdonDate= (DateTime)result["createdon"];
string type = result["columnName"].ToString();
}

}
// close the result
 if (result != null)
result.Close();


 

I hope this helps!

Cheers!

Advertisement

Work with Alternate Keys

Concept of Alternate Keys has been introduced with Microsoft Dynamics CRM Version 2016.

All Microsoft Dynamics CRM records have unique identifiers defined as GUIDs. These are the primary key for each entity. However if you have alot integrated systems in your organization – it would be very difficult for other systems to retrieve or update records using the guids as they wont necessarily store CRM’s internal GUIDs. Hence you can create an alternate key which is actually a data in other systems – they can easily update or retrieve data from CRM using this.

Only below field types can be added as an alternate key :

  1. Decimal Number
  2. Whole Number
  3. Single Line of Text
  4. Date Time [ Only with Dynamics 365 v9 or above]
  5. Lookup [ Only with Dynamics 365 v9 or above]
  6. Picklist [ Only with Dynamics 365 v9 or above]

Point to note is that for a single entity, only 5 alternate keys are allowed in CRM.

Apart from Above , Alternate keys will stop creating duplicate records in the system, be it normal creation , excel import , or from external service.Wow how cool is that!

To Create an alternate key , go to customization > Entity> Expand the entity and look for “Alternate Keys” option > click on New and give display name and schema name > select the field for which you want to enable it > click on the right arrow > click on Save.

when you save an alternate key it, you are actually creating an async job which goes through following status :

  • Pending
  • In Progress
  • Active
  • Failed

alternatekey

Once status shows “Active” it means its ready to be used:

alternatekey1

you can very well use alternate keys with WebApi , take reference from the below :

https://community.dynamics.com/crm/b/dutchcrmandsharepoint/archive/2016/12/06/using-alternate-keys-with-the-webapi

Write to me if you have any questions on this topic.

I hope this helps!

Cheers!

Create Text log file – Simplest way possible

We all need to log errors, information, warnings when writing your console application.

while there are several third party tools/methods available which you use in your solution such as Nlog, Log4net… etc. I would still prefer to create my custom code to generate the log file and capture all details as those tools might work on some machine and on some wont also depends badly on the permissions on the system.

below is simple code that i use :

using System;
using System.IO;

namespace YourNameSpace
{
 public static class ErrorLog
 {

public static void Logger(string ErrorCode, string ErrorMsg)
 {
 try
 {
 string path = Directory.GetCurrentDirectory();
 path = path + "\\Logs\\";

// check if directory exists
 if (!Directory.Exists(path))
 {
 Directory.CreateDirectory(path);
 }
 path = path + DateTime.Today.ToString("dd-MMM-yy") + ".log";
 // check if file exist
 if (!File.Exists(path))
 {
 File.Create(path).Dispose();
 }
 // log the error now
 using (StreamWriter writer = File.AppendText(path))
 {
 string error = "\n" + DateTime.Now.ToString() + ":" + ErrorCode + ":" + ErrorMsg;
 writer.WriteLine(error);
 writer.Flush();
 writer.Close();
 }

}
 catch (Exception ex)
 {
 throw new Exception(ex.Message);
 }
 }
 }
}

add this file to your code and later you can use it in your solution anywhere like below :

 ErrorLog.Logger("Info","yourtexthere");

kindly note that this will generate the logs file on your directory where the code is running in that case in the “BIN” folder. You can tweak the code to put it somewhere else if you need.

logs

Hope this helps!

Cheers!

Learn Microsoft Dynamics CRM – Basics, Customization & Configuration

aads-education-offers-microsoft-dynamics-crm-training-2-638

In office, while i am blogging or in dynamics community ; i always get this question that how a fresher can start learning CRM. Well let me tell you learning CRM is very easy since it is a Microsoft technology in which we have been living & using from our outlook to windows.

Well on internet ; information is pretty much distributed and in bits & pieces but i have collected the below information in sequence on how you  can you start from zero and go till 100 🙂

  1. First things first you should know what is CRM , well according to microsoft definition is :
             crm definition.PNG

    “CRM solutions streamline processes and increase profitability in your sales, marketing, and service divisions. A strong CRM solution is a multifaceted platform where everything crucial to developing, improving, and retaining your customer relationships is stored. Without the support of an integrated CRM solution, you may miss growth opportunities and lose revenue because you’re not maximizing your business relationships.”

  2. Next i would suggest you to see above everything in action :  https://www.chalkstreet.com/microsoft-dynamics-crm-tutorial    ,  https://mva.microsoft.com/en-us/training-courses/introduction-to-microsoft-dynamics-365-17593?l=J6OdOYtqD_906312570
  3. Once you have done this – i think you will be pretty much comfortable having your own CRM environment to get your hands dirty in, therefore go ahead and get your trial :https://trials.dynamics.com/
  4. Time to know how CRM can be available to you(to be technical “deployment options available in CRM”) :  https://www.powerobjects.com/2015/10/21/upgrading-crm-crm-online-vs-on-premises-finding-the-best-fit-for-your-organization/
  5. Once you understood all above, my suggestion for you is to now understand the core concepts which makes the whole CRM :

https://www.tutorialspoint.com/microsoft_crm/microsoft_crm_overview.htm

In this tutorial you will learn about what are the core components of  CRM like Entities , Fields, Forms, Business process flows etc. This blog also pretty much explains how you can customize the crm.

6. Now go though this blog and do same in your trial version :

https://crmbook.powerobjects.com/system-administration/customization/

i will talk about customizing, fields, forms , entities , charts, dashboards,solutions etc.

7. Security model – Now this is a major topic which you must understand very well without which it will be very difficult for you to master dynamics crm else you will be missing out on very small/minor things during your career

https://www.youtube.com/watch?v=_vfp3C11a3M &  https://www.youtube.com/watch?v=An-VgsHEp9A

8. Now , one of the main features of crm is “Processes” , processes are the main component in CRM which helps you to perform actions based on your needs :

a. business rules :  http://www.inogic.com/blog/2016/12/business-rules-dynamics-365/

b. Workflows :  https://crmbook.powerobjects.com/system-administration/processes/workflows/

c.Plugins :  https://crmbook.powerobjects.com/extending-crm/plug-in-development-and-workflow-extensions/plug-ins/developing-a-plug-in/

9.Apart from it most of the stuff you would do through JavaScript which you coudnt  using business rule, mainly javascript is used to perform complex logics else i always recommend you to use business rules  :  https://www.youtube.com/watch?v=wxMB-cIQPUw

 

if you go through all of the above carefully , you will be in a position to work on dynamics crm confidently.

Please leave your comment if i have missed out anything major or you have any question, need guidance on any of the topics above.

happy crm learning, cheers!