Microsoft CRM Customization: Integration with third party SQL Application/Database

 by: Andrew Karasev

Microsoft CRM – Client Relationship Management package from Microsoft Business Solutions was initially designed to be customizable with Microsoft Visual Studio.Net and one of its programming languages – C#.Net or VB.Net. You can use ADO.Net, Web Service, Transact SQL scripting and stored procedures, deploy such SQL Server tools as Linked Server to all ODBC/OLEDB compliant database, including ORACLE, Sybase, Ingress, DB2, Unidata, Pervasive SQL, Ctree and even Lotus Notes/Domino. In this small article we would like to give you the clue on programming the integration with SQL third party Database.

First – use Microsoft CRM SDK to initiate communication with Microsoft CRM, we have it in C#:

String[] arr1  coll.AllKeys;
int loop1, loop2;
for (loop1  0; loop1 0?1:arr1.Length); loop1++) 
{
 String[] arr2  coll.GetValues(arr1[loop1]);
 for (loop2  0; loop2 < arr2.Length; loop2++) 
 {
 strAccountId  arr2[loop2].Replace("}",null);
 strAccountId  strAccountId.Replace("{",null);
 }
}

if (Page.IsPostBackfalse)
{
 // Server should be set with the name of the platform Web server
 string Server  ConfigurationSettings.AppSettings["ServerName"];

 // VirtualDirectory should be set with the name of the Microsoft 
 // CRM Virtual Directory on the platform Web server
 string VirtualDirectory  "mscrmservices";
 string strDir  "http://" + Server + "/" + VirtualDirectory + "/";

 // BizUser proxy object
 Microsoft.CRM.Proxy.BizUser oBizUser  new Microsoft.CRM.Proxy.BizUser ();
 oBizUser.Credentials  System.Net.CredentialCache.DefaultCredentials;
 oBizUser.Url  strDir + "BizUser.srf";

 // CRMAccount proxy object
 Microsoft.CRM.Proxy.CRMAccount account  new Microsoft.CRM.Proxy.CRMAccount ();
 account.Credentials  System.Net.CredentialCache.DefaultCredentials;
 account.Url  strDir + "CRMAccount.srf";

Then you use ADO.Net for calling stored procedure with parameters to do the integration job:

try
{
 string SQLStatement"ICSUpdateAccountPrivate '"+
 strAccountId +"' , '" + this.TextBoxWorkPerformed.Text +
 "' , "+doubleEncode(System.Double.Parse(this.TextBoxAnnualRevenue.Text))+" , "+
 intEncode(System.Int32.Parse(this.TextBoxNumberOfEmployees.Text.Replace(",","")))+" , "+
 doubleEncode(System.Double.Parse(this.TextBoxAverageGrowthRate.Text))+" , "+
 "'"+this.DropDownListOwnership.SelectedItem.Text +"' , "+
 intEncode(System.Int32.Parse(this.RadioButtonList.SelectedItem.Value))+" , "+
 intEncode(System.Int32.Parse(this.TextBoxCredit.Text.Replace(",","")))+" , '"+
 this.TextBoxComments.Text+"'";

 System.Data.SqlClient.SqlConnection tmpConnection 
 new System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings["ConnectionStringICS"]
 );