Friday, January 13, 2012

Insert, retrieve and delete a record using SugarCrm webservices from C#/WCF

SugarCrm consists of of a core framework, MVC modules and UI widgets called dashlets, as well as a lot of other functionality. Some modules like the built in CRM modules are handcrafted, to contain very specific business logic, and other are generated using a built in tool called Module Builder. 

On top of all the modules generated through module builder, SugarCrm supplies a set of generic web services, that can be used to manipulate records in those modules, provided that you have a valid username and password. This allows for integration from other systems.

I will show how you can insert, retrieve and delete a record using C# and WCF.

First a simple supporting class for throwing exceptions
using System;

namespace SugarCrmWebserviceTests
{
 public class SugarCrmException : Exception
 {
  public SugarCrmException(string errorNumber, string description) : base (description)
  {
   ErrorNumber = errorNumber;
  }

  public string ErrorNumber { get; private set; }
 }
}

Then the full integration test with supporting helper private methods
  1. insert of a record with known values
  2. retrieve the just inserted record and compare with the known values
  3. mark the record deleted (this is also an example of how to do an record update)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.ServiceModel;
using System.Text;
using NUnit.Framework;
using SugarCrmWebserviceTests.SugarCrm;

namespace SugarCrmWebserviceTests
{
 public class WebserviceTests
 {
  // The internal sugarcrm name for the module (normally the same as the underlying table)
  private const string ModuleName = "Incident";
  
  // The key matches the module field name (database table column name) 
  // and the value is the value to be inserted/update into this field
  // id is a special field:
  // if id exists in the database then you will update that row
  // if you leave it empty (string.Empty) then you will insert a new row
  private readonly Dictionary<string,string> _incident = new Dictionary<string, string>
       {
        {"id", string.Empty},
        {"name", "Clark Kent"},
        {"description", "Something is rotten in the state of Denmark"},
        {"status", "Open"},
        {"category", "Question"}
       };

  private const string ServiceUrl = "http://somesugarcrminstall.somwhere.com/soap.php";
  private const string SugarCrmUsername = "YOUR_SUGAR_USERNAME";
  private const string SugarCrmPassword = "YOUR_SUGAR_PASSWORD";

  private readonly sugarsoapPortType _sugarServiceClient;
  private string SessionId { get; set; }
  private string RecordId { get; set; }

  public WebserviceTests()
  {
   _sugarServiceClient = ChannelFactory<sugarsoapPortType>.CreateChannel(new BasicHttpBinding(),
                      new EndpointAddress(ServiceUrl));
  }

  [TestFixtureSetUp]
  public void BeforeAnyTestsHaveRun()
  {
   // The session is valid until either you are logged out or after 30 minutes of inactivity
   Login();
  }

  [TestFixtureTearDown]
  public void AfterAllTestsHaveRun()
  {
   Logout();
  }

  [Test]
  public void InsertReadDelete()
  {
   InsertRecordInSugarCrmModule();
   RetrieveInsertedRecordFromSugarCrmModule();
   DeleteEntry();
  }

  private void InsertRecordInSugarCrmModule()
  {
   // arrange

   // act
   var result = _sugarServiceClient.set_entry(SessionId, ModuleName, _incident.Select(nameValue => new name_value { name = nameValue.Key, value = nameValue.Value }).ToArray());
   CheckResultForError(result);
   RecordId = result.id; 

   // assert
   Assert.AreEqual("0", result.error.number);
  }
  
  private void RetrieveInsertedRecordFromSugarCrmModule()
  {
   // arrange
   var fieldsToRetrieve = new[] { "id", "name", "description", "status", "category" };

   // act
   var result = _sugarServiceClient.get_entry(SessionId, ModuleName, RecordId, fieldsToRetrieve);
   CheckResultForError(result);

   // assert
   Assert.AreEqual(_incident["name"], GetValueFromNameValueList("name", result.entry_list[0].name_value_list));
   Assert.AreEqual(_incident["description"], GetValueFromNameValueList("description", result.entry_list[0].name_value_list));
   Assert.AreEqual(_incident["status"], GetValueFromNameValueList("status", result.entry_list[0].name_value_list));
   Assert.AreEqual(_incident["category"], GetValueFromNameValueList("category", result.entry_list[0].name_value_list));
  }
  
  private void DeleteEntry()
  {
   // arrange
   var deletedIncident = new Dictionary<string, string>
       {
        {"id", RecordId},
        {"deleted", "1"},
       };   

   // act
   var result = _sugarServiceClient.set_entry(SessionId, ModuleName, deletedIncident.Select(nameValue => new name_value { name = nameValue.Key, value = nameValue.Value }).ToArray());
   CheckResultForError(result);

   // assert
  }

  private void Login()
  {
   var sugarUserAuthentication = new user_auth { user_name = SugarCrmUsername, password = Md5Encrypt(SugarCrmPassword) };
   var sugarAuthenticatedUser = _sugarServiceClient.login(sugarUserAuthentication, "Some Application Name");
   SessionId = sugarAuthenticatedUser.id;
  }

  private void Logout()
  {
   //logout
   _sugarServiceClient.logout(SessionId);
  }

  private static string GetValueFromNameValueList(string key, IEnumerable<name_value> nameValues)
  {
   return nameValues.Where(nv => nv.name == key).ToArray()[0].value;
  }

  /// <summary>
  /// You can only call this method with objects that contain SugarCrm.error_value classes in the root 
  /// and the property accessor is called error. There is no compile time checking for this, if you pass
  /// an object that does not contain this then you will get a runtime error
  /// </summary>
  /// <param name="result">object contain SugarCrm.error_value class in the root and the property accessor is called error</param>
  private static void CheckResultForError(dynamic result)
  {
   if (result.error.number != "0" && result.error.description != "No Error")
   {
    throw new SugarCrmException(result.error.number, result.error.description);
   }
  }

  private static string Md5Encrypt(string valueString)
  {
   var ret = String.Empty;
   var md5Hasher = new MD5CryptoServiceProvider();
   var data = Encoding.ASCII.GetBytes(valueString);
   data = md5Hasher.ComputeHash(data);
   for (int i = 0; i < data.Length; i++)
   {
    ret += data[i].ToString("x2").ToLower();
   }
   return ret;
  }
 }
}
Post a Comment