Search This Blog

Thursday, November 11, 2021

Create Active directory User in Dynamics CRM using c#

Add following assemblies in your project:

using System.DirectoryServices;
using System.DirectoryServices.AccountManagement;


        private void GetADUsers()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[2]  
            {  
                new DataColumn("AccountName", typeof (string)),  
                    new DataColumn("GivenName", typeof (string))  
            });

            var context = new PrincipalContext(ContextType.Domain, "domainname");
            var usr = new UserPrincipal(context);
            var filter = new Filters(usr);
            filter.CreatedAfter(DateTime.UtcNow.AddDays(-50));//get last 50 days created users
            foreach (var result in new PrincipalSearcher(usr).FindAll())
            {
                DirectoryEntry de = result.GetUnderlyingObject() as DirectoryEntry;
                var clsuser = new ClsUsers();
                dt.Rows.Add(Convert.ToString(de.Properties["samAccountName"].Value), Convert.ToString(de.Properties["givenName"].Value));
                createuser(Convert.ToString(de.Properties["samAccountName"].Value), Convert.ToString(de.Properties["givenName"].Value));//create user in crm
                break;
            }
            
            GridView1.DataSource = dt;
            GridView1.DataBind();        
        }

 

protected void createuser(String username, String fullname)
        {
            String dom = @"UMT\";
            IOrganizationService service  = GetCRMService();
            Entity user = new Entity("systemuser");
            user["domainname"] = dom + username;
            user["firstname"] = "A";
            user["lastname"] = fullname;
            user["businessunitid"] = new EntityReference("businessunit", new Guid(""));
            service.Create(user);
        }

 

public class Filters : AdvancedFilters// this class is used for to apply filters
    {
        public Filters(Principal p)
            : base(p)
        {
        }
        public void CreatedAfter(DateTime value)
        {
            var strValue = value.ToString("yyyyMMddHHmmss.0Z");
            this.AdvancedFilterSet("WhenCreated", strValue, typeof(string), MatchType.GreaterThanOrEquals);
        }
    }


    public class ClsUsers
    {
        public string AccountName { get; set; }
        public string DisplayName { get; set; }
    }

Thursday, September 23, 2021

Get Database Tables size using SQL query

 This will return tables size, Group them in excel to get exact figure.


SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
--where t.Name='email'
GROUP BY t.Name, s.Name, p.Rows
ORDER BY Used_MB desc

Tuesday, September 21, 2021

Update bulk deletion job status when stuck

 Following are the status and status reason list of jobs:


StatusCode
0     Waiting For Resources
10     Waiting
20     In Progress
21     Pausing
22     Canceling
30     Succeeded
31     Failed
32     Canceled


StateCode
0     Ready
1     Suspended
2     Locked
3     Completed

Query to Update:



 UPDATE AsyncOperationBase SET StateCode = 3 -- Completed  
 , StatusCode = 32 -- Canceled  
 , [Message] = 'WorkflowErrorPatched'  
 WHERE StatusCode = 20 -- In Progress  
 OR StatusCode = 0 -- Waiting For Resources


Friday, September 17, 2021

Delete CRM Audit Partition using sql query

 To delete CRM Audit partition, we can call stroreprocedure. Use below statment:

exec p_DeleteSingleAuditPartition '12/31/2015 11:59:59.997 PM'

 

Note: This is 'To' Date in Audit log management. Format of date should be same as above.

 

 

Thursday, September 16, 2021

Call Dynamics 365 Online Webapi in c#

private static string serviceUri = "https://crmdemo.crm.dynamics.com";
private static string redirectUrl = "https://crmdemo.api.crm.dynamics.com/api/data/v9.2/";
private const string Authority = "https://login.microsoftonline.com/d034267c-c30c-4ccf-b0e7-f03149844f21";  // Tenant Id after app registration
private const string clientId = "17802964-c859-410a-a908-76bc97fbe4cd";     // Client ID after app registration

private const string userName = "username";
private const string password = "password";

 

Call Token methods:

string bearerToken = Task.Run(async () => await GetAuthToken()).Result;   // Get bearer token

Get Token Method

        public static async Task<string> GetAuthToken()
        {
            AuthenticationResult result = null;
            try
            {
                AuthenticationContext authContext = new AuthenticationContext(Authority, false);
                UserCredential cred = new UserPasswordCredential(userName, password);
                result = await authContext.AcquireTokenAsync(serviceUri, clientId, cred);
            }
            catch (Exception ex)
            {
                throw;
            }
            return result.AccessToken;
        } 



Retrieve Accounts

        public static void RetrieveAccounts(string authToken)
        {
            HttpClient httpClient = null;
            httpClient = new HttpClient();
            //Default Request Headers needed to be added in the HttpClient Object
            httpClient.DefaultRequestHeaders.Add("OData-MaxVersion", "4.0");
            httpClient.DefaultRequestHeaders.Add("OData-Version", "4.0");
            httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
            //Set the Authorization header with the Access Token received specifying the Credentials
            httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", authToken);

            httpClient.BaseAddress = new Uri(redirectUrl);
            var response = httpClient.GetAsync("accounts?$select=name&$top=5").Result;
            if (response.IsSuccessStatusCode)
            {
                var accounts = response.Content.ReadAsStringAsync().Result;
                var jRetrieveResponse = JObject.Parse(response.Content.ReadAsStringAsync().Result);
                dynamic collContacts = JsonConvert.DeserializeObject(jRetrieveResponse.ToString());
                foreach (var data in collContacts.value)
                {
                    Console.WriteLine("Contact Name – " + data.name.Value);
                }
            }        
        }

 

Call Create Record Method

string statusss = Task.Run(async () => await CreateRecord(bearerToken)).Result;

Create Record Method

        private static async Task<string> CreateRecord(string authToken)
        {
            JObject acc = new JObject { };
            acc["name"] = "Test1";
            var httpClient = new HttpClient
            {
                BaseAddress = new Uri(serviceUrl + "/api/data/v9.2/"),
                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", authToken);

            // Add this line for TLS complaience
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
            HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, "accounts")
            {
                Content = new StringContent(acc.ToString(), Encoding.UTF8, "application/json")
            };
            HttpResponseMessage response = await httpClient.SendAsync(request);
            if (response.StatusCode == HttpStatusCode.NoContent)
            {
                return "204";
            }
            return "";
        }

Tuesday, August 11, 2020

Get Lookup text from WebAPI

 Use expand to and Schema name of lookup field to get Lookup text


https://organizationurl/api/data/v9.1/clients?$select=name,_state_value,_city_value&$expand=City($select=name),State($select=name)

Thursday, November 7, 2019

Refresh Form/Record using javascript in dynamics 365 v9 Unified Interface

setTimeout(function () {          
        top.callN = null;
        Xrm.Utility.openEntityForm(formContext.data.entity.getEntityName(), formContext.data.entity.getId());
      }, 2000);

Wednesday, October 16, 2019

Get and Set Value from Custom Ribbon Button using javascript Dynamics 365 V9

function Set(PrimaryControl)
{
    var formContext = PrimaryControl;
        formContext.getAttribute("schedule").setValue(true);
    formContext.data.entity.save();
}

Tuesday, August 6, 2019

Deserialize Json Array Object to DataTable

Use Below Code

protected void GetCustomerType()
        {
            String serverurl = "API URL";
            Uri myUri = new Uri(serverurl);
            HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(myUri);
            httpWebRequest.ContentType = "application/json";
            httpWebRequest.Method = "GET";

            HttpWebResponse httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
            Stream dataStream = httpResponse.GetResponseStream();
            StreamReader reader = new StreamReader(dataStream);
            string sResponse = reader.ReadToEnd();

            DataTable dt = JsonStringToDataTable(sResponse);
            foreach (DataRow dr in dt.Rows)
            {
                String name = dr[""].ToString();
                String telephone = dr[""].ToString();
                String email = dr[""].ToString();
            }

                    }
        public DataTable JsonStringToDataTable(string jsonString)
        {
            DataTable dt = new DataTable();
            string[] jsonStringArray = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");
            List<string> ColumnsName = new List<string>();
            foreach (string jSA in jsonStringArray)
            {
                string[] jsonStringData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
                foreach (string ColumnsNameData in jsonStringData)
                {
                    try
                    {
                        int idx = ColumnsNameData.IndexOf(":");
                        string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"", "");
                        if (!ColumnsName.Contains(ColumnsNameString))
                        {
                            ColumnsName.Add(ColumnsNameString);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
                    }
                }
                break;
            }
            foreach (string AddColumnName in ColumnsName)
            {
                dt.Columns.Add(AddColumnName);
            }
            foreach (string jSA in jsonStringArray)
            {
                string[] RowData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
                DataRow nr = dt.NewRow();
                foreach (string rowData in RowData)
                {
                    try
                    {
                        int idx = rowData.IndexOf(":");
                        string RowColumns = rowData.Substring(0, idx - 1).Replace("\"", "");
                        string RowDataString = rowData.Substring(idx + 1).Replace("\"", "");
                        nr[RowColumns] = RowDataString;
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
                dt.Rows.Add(nr);
            }
            return dt;
        }

Tuesday, May 21, 2019

Get Sum/Aggregate from Related entity using c# in Dynamics CRM

public int GetTotal()
{
FetchExpression f1 = new FetchExpression("<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true' aggregate='true' >" +
                    "    <entity name='account' >" +
                    "        <filter type='and' >" +
                    "            <condition attribute='statuscode' operator='in' >" +
                    "                <value>" +
                    "                    1" +
                    "                </value>" +
                    "                <value>" +
                    "                    175650000" +
                    "                </value>" +
                    "            </condition>" +
                    "        </filter>" +
                    "        <link-entity name='batch' from='batchid' to='batchid' visible='false' link-type='outer' alias='bc' >" +
                    "            <attribute name='credithour' aggregate='sum' alias='totalcredithours' />" +
                    "        </link-entity>" +
                    "    </entity>" +
                    "</fetch>");

EntityCollection AccountCollection = service.RetrieveMultiple(f1);
                int total = 0;
                if (AccountCollection != null)
                {
                    AccountCount = AccountCollection.Entities.Count;
                    if (AccountCount > 0)
                    {
                        foreach (var entity in AccountCollection.Entities)
                        {
                            if (entity.Attributes.Contains("totalcredithours"))
                            {
                                total = Convert.ToInt16(((AliasedValue)entity.Attributes["totalcredithours"]).Value);
                            }
                            return total;
                        }
                    }
                }
}


Monday, May 20, 2019

Insert in List Item in Shairpoint online using c#

 protected void InsertItem()
        {
            try
            {
                SecureString Password = new SecureString();
                Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                foreach (char c in "password")
                {
                    Password.AppendChar(c);
                }
                    ClientContext clientcontext = new ClientContext(url);//"https://orgname.sharepoint.com/sites/MainListSite"
                    clientcontext.Credentials = new SharePointOnlineCredentials(username, Password);

                    List olist = clientcontext.Web.Lists.GetByTitle("FirstMainList");
                    ListItemCreationInformation listiteminfor = new ListItemCreationInformation();
                    Microsoft.SharePoint.Client.ListItem olistitem = olist.AddItem(listiteminfor);

                    olistitem["Title"] = "1";
              
                    olistitem.Update();
                    clientcontext.ExecuteQuery();

            }
            catch(Exception ex)
            {
                Label1.Text = ex.Message;
            }
        }

Retrieve List Items form Shairpoint Online in c#

protected void GetListItem()
        {
            try
            {
                SecureString Password = new SecureString();
                Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                foreach (char c in "password")
                {
                    Password.AppendChar(c);
                }
                using (ClientContext context = new ClientContext(url))//https://orgname.sharepoint.com/sites/MainListSite"
                {
                    context.Credentials = new SharePointOnlineCredentials(username, Password);

                    List list = context.Web.Lists.GetByTitle("FirstMainList");//list name
                    context.Load(list);
                    context.ExecuteQuery();
                    Microsoft.SharePoint.Client.View view = list.Views.GetByTitle("All Items");//view name

                    context.Load(view);
                    context.ExecuteQuery();
                    CamlQuery query = new CamlQuery();
                    query.ViewXml = view.ViewQuery;

                    Microsoft.SharePoint.Client.ListItemCollection items = list.GetItems(query);
                    context.Load(items);
                    context.ExecuteQuery();
                    //Console.WriteLine("Total Count: " + items.Count);

                    foreach (Microsoft.SharePoint.Client.ListItem itm in items)
                    {
                        Label1.Text = itm["Title"].ToString() + itm["Mobile"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                Label1.Text = "Exception " + ex.Message;
            }
        }

Monday, March 11, 2019

Encrypt and Decrypt Connection String using CMD


Encrypt:


cd C:\Windows\Microsoft.Net\Framework\v4.0.30319
ASPNET_REGIIS -pef "appSettings" "folder path where web.config"


DeCrypt:

cd C:\Windows\Microsoft.Net\Framework\v4.0.30319
ASPNET_REGIIS -pdf "appSettings" "folder path where web.config"





Wednesday, March 6, 2019

SQL Query to Get size of Table

EXEC sp_spaceused N'User.ContactInfo';

SQL Query to Get Table Index fragmentation

Query:

SELECT * FROM sys.dm_db_index_physical_stats(dbid, table_objectid, NULL, NULL , 'LIMITED')
ORDER BY avg_fragmentation_in_percent DESC