Archive for December, 2007

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”);

try
{
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];
aba1.name = (theRow[“AccountName”].ToString());
// assiging the name of accounts (from CRM) to Navn1
navn1 = aba1.name;

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

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

}

// 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();
k.name = navn;
k.address1_city = city;
k.address1_country = Country;
k.emailaddress1 = Emailing;
service.Create(k);

}

}
}
catch (System.Web.Services.Protocols.SoapException ex)
{
ex.InnerException.ToString();
}
}
}
}

Advertisements