Synchronizing DataBase with CRM 3.0 (updating existing and creating new accounts)

I will be synchronizing existing Database with the existing CRM Accounts. I will compare the Accounts on the basis of “Name”. If the Names are same, accounts will be updated (otherwise new account will be created in the CRM system) .
I will create Console application and then schedule it for specified interval (for example , 2 hours , 4 hours ).
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
using UpdateCRMhourlybasis.crm;
namespace UpdateCRMhourlybasis

class Program

static void Main(string[] args)

// Navn 1 and Navn will use for comparison purpose .

string navn1; // This will be the name of Account from CRM
string navn; // This will be the name of Account from Database

CrmService service = new CrmService();
service.Credentials System.Net.CredentialCache.DefaultCredentials;

// Connection to the database and retrieving “Accounts” column in .

SqlConnection con = new SqlConnection(@”Data Source=NP-1;Initial
Catalog=Emailing;Integrated Security=True”);

SqlDataAdapter thisAdapter = new SqlDataAdapter(“SELECT * FROM Accounts”, con);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, “Accounts”);

foreach (DataRow theRow in thisDataSet.Tables[“Accounts”].Rows)
// I will be using few fields for update
// Now , i will make query to retrieve accounts with the specified fields, i wish to //update.

QueryExpression query = new QueryExpression();
query.EntityName = “account”;
ColumnSet columns = new ColumnSet();

// columns to retrieve
columns.Attributes = new string[] { “accountid”, “address1_city”,
“address1_country”, “address1_fax”, “address1_line1”, “name” };
query.ColumnSet = columns;
query.Criteria = new FilterExpression();
query.Criteria.FilterOperator = LogicalOperator.And;
ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = “name”;
condition1.Operator = ConditionOperator.Equal;

// This query will retrieve the accounts from CRM, where “name” equals the name //in
//database —-AccountName

condition1.Values = new object[] { (theRow[“AccountName”].ToString()) };

query.Criteria.Conditions = new ConditionExpression[] { condition1 };
BusinessEntityCollection ret = service.RetrieveMultiple(query);

// as mentioned , “Navn” is Name of Accounts in Database
navn = (theRow[“AccountName”].ToString());

string city = (theRow[“City”].ToString());
string Emailing = (theRow[“Email”].ToString());
string Country = (theRow[“Country”].ToString());

for (int i = 0; i < ret.BusinessEntities.Length; i++)
//Retrieving all accounts , where name of Accounts in database equal the name in //CRM.
account aba1 = (account)ret.BusinessEntities[i]; = (theRow[“AccountName”].ToString());
// assiging the name of accounts (from CRM) to Navn1
navn1 =;

aba1.address1_city = (theRow[“City”].ToString());
aba1.address1_country = (theRow[“Country”].ToString());
aba1.address1_line1 = (theRow[“Address”].ToString());

aba1.address1_postalcode = (theRow[“PostalCode”].ToString());


// This part will create new accounts , if names in database do not match //with the name in CRM

if(navn !=navn1)

account k = new account(); = navn;
k.address1_city = city;
k.address1_country = Country;
k.emailaddress1 = Emailing;


catch (System.Web.Services.Protocols.SoapException ex)


3 Responses to “Synchronizing DataBase with CRM 3.0 (updating existing and creating new accounts)”

  1. 1 Hedith December 4, 2007 at 3:41 pm

    Amir I need some basic help about the crm can u give me some?? I want to know what is the sale cycle in the CRM and how it works step by step? U can post a blog entry or can contact me at

  2. 2 Tecnologias .Net December 24, 2007 at 12:18 am

    Congratulation with you Blog.
    Sync can ber tricky you now that, but dont think is good idea sync 2 3 or 4 hours, thats is not operational, if a list of vendours need to have in time info, that lag of 2 or 4 hours can be disastours, not have the oportunities correct or duplication of info.i have this issues when i make sync with CRM with others ERP. Other problems is if the network fail, or one of the 2 system fails, whats now… how to recover from failed systems, other issues, triggers (update/delete/insert) and can continue all night :P, but like what i see continue with the effort, if you want to share and you want me to help you with something can e-mail me, is in my blog.

    Good Luck and merry christmas.

  3. 3 Tanmoy January 17, 2008 at 7:46 am

    I am new to CRM and would like to convert the process of a Call Centre to CRM.
    Can you please tell me how does it maps into CRM, becoz as I see the Entity does not map directly, and does not fit properly into the Categories Sales,Marketing,Service as provided by MS CRM.
    So how to go about it.
    Please help me.
    You can mail me at
    Thanks in Advance

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: