public string CRMLookupValue(string ssoApplicationName, string guidField, string entityName, string keyField, string valueIn)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
string returnValue = string.Empty;
try
{
System.Diagnostics.Debug.Write("CRM Functoid IN");
// SQL / Local Web Services Or CRM Online
string connectionType = SsoServices.Read(ssoApplicationName, "CrmConnectionType");
if (connectionType.ToLower() == "sql")
{
// create and open a connection object
conn = new SqlConnection(SsoServices.Read(ssoApplicationName, "CrmDatabaseConnectionString"));
conn.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand("BizTalkCRMLookup", conn);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 4. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@guidField", guidField));
cmd.Parameters.Add(new SqlParameter("@entityName", entityName));
cmd.Parameters.Add(new SqlParameter("@keyField", keyField));
cmd.Parameters.Add(new SqlParameter("@valueIn", valueIn));
// execute the command
rdr = cmd.ExecuteReader();
//read data
if (rdr.Read())
{
returnValue = rdr[0].ToString();
}
}
else
{
string userName = SsoServices.Read(ssoApplicationName, "CrmUserName");
string userPassword = SsoServices.Read(ssoApplicationName, "CrmUserPassword");
string uri = SsoServices.Read(ssoApplicationName, "CrmUri");
AuthenticationCredentials credentials = new AuthenticationCredentials();
if (uri.ToLower().Contains("dynamics.com"))
{
credentials.ClientCredentials.UserName.UserName = userName;
credentials.ClientCredentials.UserName.Password = userPassword;
}
else
{
string userDomain = SsoServices.Read(ssoApplicationName, "CrmUserDomain");
credentials.ClientCredentials.Windows.ClientCredential = new NetworkCredential(userName, userPassword, userDomain);
}
Uri organizationUri = new Uri(uri);
Uri homeRealmUri = new Uri(uri);
OrganizationServiceProxy organizationService = new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials.ClientCredentials, null);
QueryExpression query = new QueryExpression(entityName);
query.Criteria.AddCondition(keyField, ConditionOperator.Equal, valueIn);
query.ColumnSet.AddColumn(guidField);
Entity resultEntity = organizationService.RetrieveMultiple(query).Entities.First();
returnValue = resultEntity.Attributes[guidField].ToString();
}
}
catch (Exception exc)
{
System.Diagnostics.Debug.Write("CRM Functoid error - " + exc.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
System.Diagnostics.Debug.Write("CRM Functoid OUT");
}
return returnValue;
}
