460
Hi,
I have to retrieve data from SQL and want to insert/update into CRM. but, everytime update, it always around 50 record that can be insert or update.
is there any limitation to retrieve the SQL and insert/update into CRM.
*This post is locked for comments
By default Retrieve multiple returns 5000 records, and if you want to get more you have to use paging cookie (for example msdn.microsoft.com/.../gg327917.aspx), but this is not 50
Hi Pawel, here is the code...
Share
Report
All responses (
Answers (
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Client;
using System.Net;
using System.IO;
using System.Data;
using System.Data.SqlClient;
namespace CustomerSync
{
public class CustomerSync
{
public void Start()
{
#region Variables
string strCRMUserName = string.Empty;
string strCRMUserPassword = string.Empty;
string strCRMDomain = string.Empty;
string strCRMURL = string.Empty;
string strCRMType = string.Empty;
string SQLServer = string.Empty;
string SQLDatabase = string.Empty;
string Outlet_Number = string.Empty;
string Outlet_Name = string.Empty;
int Outlet_Phone = 0;
int Outlet_Fax = 0;
string Outlet_Segment = string.Empty;
int Outlet_Type = 0;
string Outlet_Alamat = string.Empty;
string Outlet_Regional = string.Empty;
string Outlet_Area = string.Empty;
string Outlet_City = string.Empty;
int Outlet_Status = 0;
string Outlet_NIK = string.Empty;
string Outlet_NPWPNo = string.Empty;
string Outlet_NPWPName = string.Empty;
string Outlet_TaxAddress = string.Empty;
string CRM_Outlet_Segment = string.Empty;
OrganizationServiceProxy orgService;
Guid SegmentGUID = new Guid();
Guid RegionGUID = new Guid();
Guid AreaGUID = new Guid();
Guid CityGUID = new Guid();
IBIZ_Helper.TextLogger textLogger;
string strLogFilePath = string.Empty;
string strLogFileName = string.Empty;
int intLogLevel = 0;
#endregion
#region Read CRM Setting
strCRMUserName = System.Configuration.ConfigurationManager.AppSettings["Customer.CRMUserName"].ToString();
strCRMUserPassword = System.Configuration.ConfigurationManager.AppSettings["Customer.CRMUserPassword"].ToString();
strCRMDomain = System.Configuration.ConfigurationManager.AppSettings["Customer.CRMDomain"].ToString();
strCRMType = System.Configuration.ConfigurationManager.AppSettings["Customer.CRMType"].ToString();
strCRMURL = System.Configuration.ConfigurationManager.AppSettings["Customer.CRMURL"].ToString();
#endregion
#region Read SQL Setting
SQLServer = System.Configuration.ConfigurationManager.AppSettings["Customer.SQLServer"].ToString();
SQLDatabase = System.Configuration.ConfigurationManager.AppSettings["Customer.SQLDatabase"].ToString();
#endregion
#region Read Configuration
try
{
strLogFilePath = System.Configuration.ConfigurationManager.AppSettings["Customer.FilePath"].ToString();
strLogFileName = System.Configuration.ConfigurationManager.AppSettings["Customer.FileName"].ToString() + System.DateTime.UtcNow.ToString();
strLogFileName = strLogFileName.Replace(":", "_");
strLogFileName = strLogFileName.Replace(" ", "_");
strLogFileName = strLogFileName.Replace("-", "_");
strLogFileName = strLogFileName.Replace(".", "_");
strLogFileName = strLogFileName.Replace("\\", "_");
strLogFileName = strLogFileName.Replace("/", "_");
strLogFileName = strLogFilePath + strLogFileName + ".log";
intLogLevel = System.Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Customer.LogLevel"].ToString());
textLogger = new IBIZ_Helper.TextLogger();
textLogger.LogLevel = intLogLevel;
textLogger.LogFileName = strLogFileName;
}
catch (Exception ex)
{
throw new Exception("[Customer Sync][Read App Settings][Exception]" + ex.ToString());
}
textLogger.LogIt("Information", "Retrieve App Settings....");
textLogger.LogIt("Information", "FilePath : " + strLogFilePath);
textLogger.LogIt("Information", "FileName : " + strLogFileName);
textLogger.LogIt("Information", "Retrieve App Settings Completed");
#endregion
#region Connect to CRM
try
{
textLogger.LogIt("Information", "Connecting to CRM Instance....");
//CrmConnection crmConnection = CrmConnection.Parse()
orgService = IBIZ_Helper.IBIZ_Helper.createServiceInstance(strCRMURL, strCRMUserName, strCRMUserPassword, strCRMType, strCRMDomain);
textLogger.LogIt("Information", "Connected to CRM Instance");
}
catch (Exception ex)
{
textLogger.LogIt("ERROR", "Cannot Connect to CRM Instance because " + ex.ToString());
return;
}
//orgService = IbizHelper.IBIZ_Helper.createServiceInstance(strCRMURL, strCRMUserName, strCRMUserPassword, strCRMType, strCRMDomain);
#endregion
#region Processing
try
{
using (SqlConnection conn = new SqlConnection())
{
#region Connect to SQL
conn.ConnectionString = "Server=" + SQLServer + ";Database=" + SQLDatabase + ";Trusted_Connection=true";
conn.Open();
textLogger.LogIt("Information", "Connected to SQL Server");
#endregion
#region Start Query from SQL
SqlCommand command = new SqlCommand("SELECT * FROM Outlet_Testing where Customer_Status = @Name", conn);
//command.Parameters.Add(new SqlParameter("@0", "11101200002"));
command.Parameters.Add(new SqlParameter("@Name", "1"));
#endregion
SqlDataReader reader = command.ExecuteReader();
textLogger.LogIt("Information", "Mapping Data from SQL");
while (reader.Read())
{
#region Mapping Outlet Information
Outlet_Number = Convert.ToString(reader[0]);
Outlet_Name = Convert.ToString(reader[1]);
Outlet_Phone = Convert.ToInt32(reader[2]);
Outlet_Fax = Convert.ToInt32(reader[3]);
Outlet_Segment = Convert.ToString(reader[4]);
Outlet_Type = Convert.ToInt32(reader[5]);
Outlet_Alamat = Convert.ToString(reader[6]);
Outlet_Regional = Convert.ToString(reader[7]);
Outlet_Area = Convert.ToString(reader[8]);
Outlet_City = Convert.ToString(reader[9]);
Outlet_Status = Convert.ToInt32(reader[10]);
Outlet_NIK = Convert.ToString(reader[11]);
Outlet_NPWPNo = Convert.ToString(reader[12]);
Outlet_NPWPName = Convert.ToString(reader[13]);
Outlet_TaxAddress = Convert.ToString(reader[14]);
#endregion
#region Check Outlet Number
textLogger.LogIt("Information", "Search Outlet [" + Outlet_Number + "] in CRM Database");
ConditionExpression checkOutletNumber = new ConditionExpression();
checkOutletNumber.AttributeName = "accountnumber";
checkOutletNumber.Operator = ConditionOperator.Equal;
checkOutletNumber.Values.Add(Outlet_Number);
FilterExpression filterOutletNumber = new FilterExpression();
filterOutletNumber.Conditions.Add(checkOutletNumber);
QueryExpression queryOutletNumber = new QueryExpression("account");
queryOutletNumber.ColumnSet = new ColumnSet(true);
queryOutletNumber.Criteria.AddFilter(filterOutletNumber);
EntityCollection resultOutletNumber = orgService.RetrieveMultiple(queryOutletNumber);
if (resultOutletNumber.Entities.Any())
{
//Update Outlet Information
foreach (Entity eOutlet in resultOutletNumber.Entities)
{
if (Outlet_Segment == "TM-TM")
{
CRM_Outlet_Segment = "TM";
}
else if (Outlet_Segment == "WHS-WHS")
{
CRM_Outlet_Segment = "WHS";
}
else if (Outlet_Segment == "WHS-SO")
{
CRM_Outlet_Segment = "WHS";
}
else if (Outlet_Segment == "PZ")
{
CRM_Outlet_Segment = "PZ";
}
else if (Outlet_Segment == "FP")
{
CRM_Outlet_Segment = "FP";
}
else if (Outlet_Segment == "CHO")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "SD")
{
CRM_Outlet_Segment = "SD";
}
else if (Outlet_Segment == "MF")
{
CRM_Outlet_Segment = "MF";
}
else if (Outlet_Segment == "FKTV")
{
CRM_Outlet_Segment = "FKTV";
}
else if (Outlet_Segment == "AZ")
{
CRM_Outlet_Segment = "AZ";
}
else if (Outlet_Segment == "KTV-KTVK")
{
CRM_Outlet_Segment = "FKTV";
}
else if (Outlet_Segment == "TM-SO")
{
CRM_Outlet_Segment = "TM";
}
else if (Outlet_Segment == "SD-SDC")
{
CRM_Outlet_Segment = "SD";
}
else if (Outlet_Segment == "HO-HOO")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "FP-FPNE")
{
CRM_Outlet_Segment = "FP";
}
else if (Outlet_Segment == "KTV-KTVF")
{
CRM_Outlet_Segment = "FKTV";
}
else if (Outlet_Segment == "PHO")
{
CRM_Outlet_Segment = "PHO";
}
else if (Outlet_Segment == "MM-MM")
{
CRM_Outlet_Segment = "MM";
}
else if (Outlet_Segment == "SD-SDP")
{
CRM_Outlet_Segment = "SD";
}
else if (Outlet_Segment == "FP-FPE")
{
CRM_Outlet_Segment = "FP";
}
else if (Outlet_Segment == "AZ-AZG")
{
CRM_Outlet_Segment = "AZ";
}
else if (Outlet_Segment == "HO-HOB")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "HE-HE")
{
CRM_Outlet_Segment = "PHO";
}
else if (Outlet_Segment == "CL-CL")
{
CRM_Outlet_Segment = "PZ";
}
else if (Outlet_Segment == "AZ-AZO")
{
CRM_Outlet_Segment = "AZ";
}
else if (Outlet_Segment == "HR-HR")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "DLX")
{
CRM_Outlet_Segment = "DLX";
}
else if (Outlet_Segment == "SPO-SPOP")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "AZ-AZR")
{
CRM_Outlet_Segment = "AZ";
}
else
{
CRM_Outlet_Segment = "CHO";
}
textLogger.LogIt("Information", "Search Segment [" + CRM_Outlet_Segment + "]");
ConditionExpression checkOutletSegment = new ConditionExpression();
checkOutletSegment.AttributeName = "ibizcs_code";
checkOutletSegment.Operator = ConditionOperator.Equal;
checkOutletSegment.Values.Add(CRM_Outlet_Segment);
FilterExpression filterOutletSegment = new FilterExpression();
filterOutletSegment.Conditions.Add(checkOutletSegment);
QueryExpression queryOutletSegment = new QueryExpression("ibizcs_segment");
queryOutletSegment.ColumnSet = new ColumnSet(true);
queryOutletSegment.Criteria.AddFilter(filterOutletSegment);
EntityCollection resultOutletSegment = orgService.RetrieveMultiple(queryOutletSegment);
if (resultOutletSegment.Entities.Any())
{
foreach (Entity eSegment in resultOutletSegment.Entities)
{
SegmentGUID = eSegment.GetAttributeValue<Guid>("ibizcs_segmentid");
ConditionExpression checkOutletRegion = new ConditionExpression();
checkOutletRegion.AttributeName = "ibizcs_name";
checkOutletRegion.Operator = ConditionOperator.Equal;
checkOutletRegion.Values.Add(Outlet_Regional.ToUpper());
FilterExpression filterOutletRegion = new FilterExpression();
filterOutletRegion.Conditions.Add(checkOutletRegion);
QueryExpression queryOutletRegion = new QueryExpression("ibizcs_region");
queryOutletRegion.ColumnSet = new ColumnSet(true);
queryOutletRegion.Criteria.AddFilter(filterOutletRegion);
EntityCollection resultOutletRegion = orgService.RetrieveMultiple(queryOutletRegion);
if (resultOutletRegion.Entities.Any())
{
foreach (Entity eRegion in resultOutletRegion.Entities)
{
RegionGUID = eRegion.GetAttributeValue<Guid>("ibizcs_regionid");
ConditionExpression checkOutletArea = new ConditionExpression();
checkOutletArea.AttributeName = "fullname";
checkOutletArea.Operator = ConditionOperator.Equal;
checkOutletArea.Values.Add(Outlet_Area);
FilterExpression filterOutletArea = new FilterExpression();
filterOutletArea.Conditions.Add(checkOutletArea);
QueryExpression queryOutletArea = new QueryExpression("systemuser");
queryOutletArea.ColumnSet = new ColumnSet(true);
queryOutletArea.Criteria.AddFilter(filterOutletArea);
EntityCollection resultOutletArea = orgService.RetrieveMultiple(queryOutletArea);
if (resultOutletArea.Entities.Any())
{
foreach (Entity eArea in resultOutletArea.Entities)
{
AreaGUID = eArea.GetAttributeValue<EntityReference>("ibizcs_area").Id;
eOutlet["name"] = Outlet_Name;
eOutlet["telephone1"] = Convert.ToString(Outlet_Phone);
eOutlet["fax"] = Convert.ToString(Outlet_Fax);
eOutlet["ibizcs_segment"] = new EntityReference("ibizcs_segment", SegmentGUID);
if (Outlet_Type == 1)
{
eOutlet["ibizcs_customertype"] = true;
}
else
{
eOutlet["ibizcs_customertype"] = false;
}
eOutlet["address1_composite"] = Outlet_Alamat;
eOutlet["ibizcs_region"] = new EntityReference("ibizcs_region", RegionGUID);
eOutlet["ibizcs_area"] = new EntityReference("ibizcs_area", AreaGUID);
eOutlet["ibizcs_nik"] = Outlet_NIK;
eOutlet["ibizcs_npwpno"] = Outlet_NPWPNo;
eOutlet["ibizcs_npwpname"] = Outlet_NPWPName;
eOutlet["ibizcs_taxaddress"] = Outlet_TaxAddress;
orgService.Update(eOutlet);
textLogger.LogIt("Information", "Update Outlet [" + Outlet_Number + "] in CRM");
using (SqlConnection NewConn = new SqlConnection())
{
NewConn.ConnectionString = "Server=" + SQLServer + ";Database=" + SQLDatabase + ";Trusted_Connection=true";
NewConn.Open();
SqlCommand Update = new SqlCommand("UPDATE Outlet_Testing set Customer_Status = '0' where Customer_Number = @0", NewConn);
Update.Parameters.Add(new SqlParameter("@0", Outlet_Number));
Update.CommandTimeout = 0;
SqlDataReader eksekusi = Update.ExecuteReader();
NewConn.Close();
}
textLogger.LogIt("Information", "Update SQL");
}
}
else
{
textLogger.LogIt("ERROR", "No Area found.");
continue;
}
}
}
else
{
textLogger.LogIt("ERROR", "No Region found. Please check your CRM data");
continue;
}
}
}
else
{
textLogger.LogIt("ERROR", "No Segment found. Please check your CRM master data");
continue;
}
}
}
else
{
ExecuteTransactionRequest requestToCreateRecords = null;
requestToCreateRecords = new ExecuteTransactionRequest()
{
Requests = new OrganizationRequestCollection(),
ReturnResponses = true
};
//Create New Outlet
#region check Segment
if (Outlet_Segment == "TM-TM")
{
CRM_Outlet_Segment = "TM";
}
else if (Outlet_Segment == "WHS-WHS")
{
CRM_Outlet_Segment = "WHS";
}
else if (Outlet_Segment == "WHS-SO")
{
CRM_Outlet_Segment = "WHS";
}
else if (Outlet_Segment == "PZ")
{
CRM_Outlet_Segment = "PZ";
}
else if (Outlet_Segment == "FP")
{
CRM_Outlet_Segment = "FP";
}
else if (Outlet_Segment == "CHO")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "SD")
{
CRM_Outlet_Segment = "SD";
}
else if (Outlet_Segment == "MF")
{
CRM_Outlet_Segment = "MF";
}
else if (Outlet_Segment == "FKTV")
{
CRM_Outlet_Segment = "FKTV";
}
else if (Outlet_Segment == "AZ")
{
CRM_Outlet_Segment = "AZ";
}
else if (Outlet_Segment == "KTV-KTVK")
{
CRM_Outlet_Segment = "FKTV";
}
else if (Outlet_Segment == "TM-SO")
{
CRM_Outlet_Segment = "TM";
}
else if (Outlet_Segment == "SD-SDC")
{
CRM_Outlet_Segment = "SD";
}
else if (Outlet_Segment == "HO-HOO")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "FP-FPNE")
{
CRM_Outlet_Segment = "FP";
}
else if (Outlet_Segment == "KTV-KTVF")
{
CRM_Outlet_Segment = "FKTV";
}
else if (Outlet_Segment == "PHO")
{
CRM_Outlet_Segment = "PHO";
}
else if (Outlet_Segment == "MM-MM")
{
CRM_Outlet_Segment = "MM";
}
else if (Outlet_Segment == "SD-SDP")
{
CRM_Outlet_Segment = "SD";
}
else if (Outlet_Segment == "FP-FPE")
{
CRM_Outlet_Segment = "FP";
}
else if (Outlet_Segment == "AZ-AZG")
{
CRM_Outlet_Segment = "AZ";
}
else if (Outlet_Segment == "HO-HOB")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "HE-HE")
{
CRM_Outlet_Segment = "PHO";
}
else if (Outlet_Segment == "CL-CL")
{
CRM_Outlet_Segment = "PZ";
}
else if (Outlet_Segment == "AZ-AZO")
{
CRM_Outlet_Segment = "AZ";
}
else if (Outlet_Segment == "HR-HR")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "DLX")
{
CRM_Outlet_Segment = "DLX";
}
else if (Outlet_Segment == "SPO-SPOP")
{
CRM_Outlet_Segment = "CHO";
}
else if (Outlet_Segment == "AZ-AZR")
{
CRM_Outlet_Segment = "AZ";
}
else
{
CRM_Outlet_Segment = "CHO";
}
ConditionExpression checkOutletSegment = new ConditionExpression();
checkOutletSegment.AttributeName = "ibizcs_code";
checkOutletSegment.Operator = ConditionOperator.Equal;
checkOutletSegment.Values.Add(CRM_Outlet_Segment);
FilterExpression filterOutletSegment = new FilterExpression();
filterOutletSegment.Conditions.Add(checkOutletSegment);
QueryExpression queryOutletSegment = new QueryExpression("ibizcs_segment");
queryOutletSegment.ColumnSet = new ColumnSet(true);
queryOutletSegment.Criteria.AddFilter(filterOutletSegment);
EntityCollection resultOutletSegment = orgService.RetrieveMultiple(queryOutletSegment);
if (resultOutletSegment.Entities.Any())
{
foreach (Entity eSegment in resultOutletSegment.Entities)
{
SegmentGUID = eSegment.GetAttributeValue<Guid>("ibizcs_segmentid");
ConditionExpression checkOutletRegion = new ConditionExpression();
checkOutletRegion.AttributeName = "ibizcs_name";
checkOutletRegion.Operator = ConditionOperator.Equal;
checkOutletRegion.Values.Add(Outlet_Regional.ToUpper());
FilterExpression filterOutletRegion = new FilterExpression();
filterOutletRegion.Conditions.Add(checkOutletRegion);
QueryExpression queryOutletRegion = new QueryExpression("ibizcs_region");
queryOutletRegion.ColumnSet = new ColumnSet(true);
queryOutletRegion.Criteria.AddFilter(filterOutletRegion);
EntityCollection resultOutletRegion = orgService.RetrieveMultiple(queryOutletRegion);
if (resultOutletRegion.Entities.Any())
{
foreach (Entity eRegion in resultOutletRegion.Entities)
{
RegionGUID = eRegion.GetAttributeValue<Guid>("ibizcs_regionid");
ConditionExpression checkOutletArea = new ConditionExpression();
checkOutletArea.AttributeName = "fullname";
checkOutletArea.Operator = ConditionOperator.Equal;
checkOutletArea.Values.Add(Outlet_Area);
FilterExpression filterOutletArea = new FilterExpression();
filterOutletArea.Conditions.Add(checkOutletArea);
QueryExpression queryOutletArea = new QueryExpression("systemuser");
queryOutletArea.ColumnSet = new ColumnSet(true);
queryOutletArea.Criteria.AddFilter(filterOutletArea);
EntityCollection resultOutletArea = orgService.RetrieveMultiple(queryOutletArea);
if (resultOutletArea.Entities.Any())
{
foreach (Entity eArea in resultOutletArea.Entities)
{
AreaGUID = eArea.GetAttributeValue<EntityReference>("ibizcs_area").Id;
Entity outletToCreate = new Entity("account");
outletToCreate["accountnumber"] = Outlet_Number;
outletToCreate["name"] = Outlet_Name;
outletToCreate["telephone1"] = Convert.ToString(Outlet_Phone);
outletToCreate["fax"] = Convert.ToString(Outlet_Fax);
outletToCreate["ibizcs_segment"] = new EntityReference("ibizcs_segment", SegmentGUID);
if (Outlet_Type == 1)
{
outletToCreate["ibizcs_customertype"] = true;
}
else
{
outletToCreate["ibizcs_customertype"] = false;
}
outletToCreate["address1_composite"] = Outlet_Alamat;
outletToCreate["ibizcs_region"] = new EntityReference("ibizcs_region", RegionGUID);
outletToCreate["ibizcs_area"] = new EntityReference("ibizcs_area", AreaGUID);
outletToCreate["ibizcs_nik"] = Outlet_NIK;
outletToCreate["ibizcs_npwpno"] = Outlet_NPWPNo;
outletToCreate["ibizcs_npwpname"] = Outlet_NPWPName;
outletToCreate["ibizcs_taxaddress"] = Outlet_TaxAddress;
CreateRequest createRequest = new CreateRequest { Target = outletToCreate };
requestToCreateRecords.Requests.Add(createRequest);
var responseForCreateRecords = (ExecuteTransactionResponse)orgService.Execute(requestToCreateRecords);
using (SqlConnection NewConn = new SqlConnection())
{
NewConn.ConnectionString = "Server=" + SQLServer + ";Database=" + SQLDatabase + ";Trusted_Connection=true";
NewConn.Open();
SqlCommand Update = new SqlCommand("UPDATE Outlet_Testing set Customer_Status = '0' where Customer_Number = @0", NewConn);
Update.Parameters.Add(new SqlParameter("@0", Outlet_Number));
Update.CommandTimeout = 0;
SqlDataReader eksekusi = Update.ExecuteReader();
NewConn.Close();
}
textLogger.LogIt("Information", "Insert new Outlet [" + Outlet_Number + "] into CRM");
}
}
else
{
textLogger.LogIt("ERROR", "No Area found");
continue;
}
}
}
else
{
textLogger.LogIt("ERROR", "No Region found");
continue;
}
}
}
else
{
textLogger.LogIt("ERROR", "No Segment found");
continue;
}
#endregion
}
#endregion
}
}
}
catch(Exception ex)
{
textLogger.LogIt("ERROR", "Cannot Connect to SQL Instance because " + ex.ToString());
}
#endregion
}
}
}
#1
André Arnaud de Cal...
292,865
Super User 2025 Season 1
#2
Martin Dráb
231,723
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
