This morning I got into a IM discussion with Alexander Pfingstl. He works for for a BP in Germany. We were discussing the ability to handle custom address entry from the Add Contact Account screen in SalesLogix web. Since the format of German address layout is different then that of North America custom work needed to be done. Given that the current incarnation of the address control does not allow for customization it was not possible to make the changes there. Also using the Add/Edit address dialog was not possible because it works off an existing entity (account/contact) and not one that has yet to be created.

I had suggested that he just place the address details directly on the Add Contact screen and bind it directly. I have done this before and it works like a charm.

The next question that came up was how to call a business rule with out an entity. It seems that he has code that does a City lookup based on ZIP/Postal information. I am sure that we all have some form of this code around. What struck with me is that this code is not specifically entity bound and is more a utility method then a business rule. Really when you look at it from a consultant role this code should be as generalist as possible for maximum reuse.

As with most things I do, I recommended to create an external library in Visual Studio. I know, outside if AA where you may be saying that we should try to keep inside of the SalesLogix dev environment. This is were I would disagree. The goal is to create value for both the current customer, and others in the future.

In our discussions we talked about the DataService. With this service it is possible to get the underlying connection string to the SalesLogix database. You can also get a connection but I shy away from that as I like to know when it is created and destroyed so using the connection string gives me this flexibility.

So finally I opened up VS and a web portal and started to chunk out a code sample for Alexander and provided this code:

   1: public string GetCityFromZip(string zip)

   2: {

   3:     string result = string.Empty;

   4:     IDataService service = ApplicationContext.Current.Services.Get<IDataService>();

   5:     using (var connection = new OleDbConnection   (service.GetConnectionString()))

   6:     {   

   7:         connection.Open();   

   8:         using (var command = connection.CreateCommand())

   9:         {         

  10:             command.CommandText = "Select City from CityZipTable where Zip = ? "; 

  11:             command.Parameters.Add(new OleDbParameter("@Zip", zip));           

  12:             result = (string)command.ExecuteScalar();      

  13:         }           

  14:     }    

  15:     return result; 

  16: }

Now this code is specific to SalesLogix web as it uses the data service. To truly make it universal what should be done is a simple refactor to pass in the connection string instead of deriving it from the Data Service.

   1: public string GetCityFromZip(string connectionString, string zip)

   2: {   

   3:     string result = string.Empty;    

   4:     using (var connection = new OleDbConnection(connectionString))   

   5:     {      

   6:         connection.Open();       

   7:         using (var command = connection.CreateCommand())      

   8:         {         

   9:             command.CommandText = "Select City from CityZipTable where Zip = ? ";

  10:             command.Parameters.Add(new OleDbParameter("@Zip", zip));

  11:             result = (string)command.ExecuteScalar();      

  12:         }   

  13:     }   


  15:     return result;

  16: }


So now this method can be use from SalesLogix or an external application. Note the use of parameterized query to ensure that we do not get a SQL injection issue. So to call it from a SalesLogix web you can just create the following code:

   1: public void OnZipChanged(object sender, EventArgs args)

   2: {   

   3:     AddressUtilities utilities = new AddressUtilities(); 

   4:     string connectionString = ((IDataService)ApplicationContext.Current.Services.Get<IDataService>).GetConnectionString();    

   5:     txtCity.Text = utilities.GetCityFromZip(connectionString, txtZipPostal.Text);

   6: }

And using it from an external application its as simple as:

   1: public void UpdateCityBasedOnZip(string zip)

   2: {   

   3:     string connectionString = "<connection string here>";   

   4:     AddressUtilities utilities = new AddressUtilities();   

   5:     txtCity.Text = utilities.GetCityFromZip(connectionString, zip);

   6: }

So there you go, a library approach.

Hope this helps.

Leave a Reply