Creating the data tier functions to insert historical records

The first thing you need to do is to create the Oracle Lite and SQL Server CE functions to insert historical records into the AccountHistories table. You'll need to define the following functions in the iDataLibPlugin interface:

bool InsertHistoricalRecord(Guid AccountGUID, int

OriginatingSource, string Subject, string Description);

bool InsertHistoricalRecordByPhone(string phoneNumber, int OriginatingSource, string Subject, string Description);

The following list describes what each function can do:

• insertHistoricalRecord(): This function generates a history record for a specified account.

• InsertHistoricalRecordByPhone(): This function allows you to pass in the incoming phone number. It locates an account with the matching phone number and then calls the InsertHistoricalRecord() function to generate the history record under that account.

You will also notice in the preceding functions that you need to insert an OriginatingSource value into the AccountHistories table. This is an integer value that takes on the value of 0 (incoming) or 1 (outgoing). It would be more intuitive to use an enumerated type to represent this in your code. Add the following enumerated type to the GlobalVariables class in the CRMLiveFramework project.

public enum Communications

Incoming=0, Outgoing=1

Now let's take a look at the SQL Server CE implementation for these two functions:

public bool InsertHistoricalRecord(System.Guid AccountGUID, int OriginatingSource, string Subject, string Description)

SqlCeCommand _command; bool _result;

_command = _globalConnection.CreateCommand(); _command.CommandText = "INSERT INTO

AccountHistories(AccountGUID,OriginatingSource,Subject, Description,Timestamp) VALUES (@AccountGUID, @OriginatingSource, @Subject, ©Description, GETDATE())";

_command.Parameters.Add("@AccountGUID", AccountGUID); _command.Parameters.Add("@OriginatingSource",

OriginatingSource); _command.Parameters.Add("@Subject", Subject); _command.Parameters.Add("@Description", Description);

catch (Exception ex) {

_command.Dispose(); _command = null; return _result;

public bool InsertHistoricalRecordByPhone(string phoneNumber, int OriginatingSource, string Subject, string Description)

SqlCeCommand _command; SqlCeDataReader _datareader;

Guid _accountGUID; bool _result;

//First we attempt to retrieve an account that matches the //incoming phone number passed in _command = _globalConnection.CreateCommand(); _command.CommandText = "SELECT AccountGUID FROM Accounts WHERE MobPhoneNo LIKE @PhoneNo OR ResPhoneNo LIKE @PhoneNo";

_command.Parameters.Add("PhoneNo", phoneNumber);

_datareader = _command.ExecuteReader();

//If an account is found, we create the historical record for it if (_datareader.Read() == true) {

_accountGUID = _datareader.GetGuid

(_datareader.GetOrdinal("AccountGUID")); InsertHistoricalRecord(_accountGUID,

OriginatingSource, Subject, Description);

catch (Exception ex) {

_command.Dispose(); _command = null; return _result;

Was this article helpful?

0 0

Post a comment