|
Last week I have been looking for the best approach to use auto numbers in
Microsoft Dynamics CRM. After discussion with my peers I have come to the
following possible options that can be used for auto numbering. Although these
may not be the efficient techniques to get auto number work in CRM but they are
all workable solutions.
Option 1: Very simple, supported and mostly acceptable way of auto
numbering is to read the attribute Max value and then add 1 to get next auto
number. For example you have a custom entity named Project and you want to auto
number Project_Ref_Number attribute. But do not retrieve all records. Just
retrieve the record with maximum number value by using following two properties
of PageInfo Class in SDK and set descending order.
PageInfo.Count = 1;
PageInfo.PageNumber = 1;
One more consideration should be taken in account to register your auto number
plugin to at PreCreate rather at PostCreate.
Issues: In multi-user environment, this approach can come up with
duplicate numbers in Project_Ref_Number when more than one user is creating
project records.
Option 2:
This technique requires a bit more work but still supported way of doing things
and all the time you will be getting 100% unique number. You need to create a
new table for project entity in a different database and not in CRM default
database.
create table dbo.ProjectNumbers
(
Project_Ref_Number int identity(1,1) primary key clustered,
Projectid uniqueidentifier not null
)
go
Create a stored procedure for inserting a record into new database. This will
increase the performance of your insert query.
create procedure dbo.proc_new_ProjectNumber
@ProjectId uniqueidentifier,
@Project_Ref_Number int output
as
insert into dbo.ProjectNumber
(
ProjectId
)
values
(
@ProjectId
)
select @Project_Ref_Number = scope_identity()
go
In your Plugin/Callout:
1. Access you database using ADO.NET
2. Execute stored procedure and get next number from returned results
3. Set the value of the returned number to target entity attribute
approperiatly.
4. All Done
Issues:
1. You have to setup database.
2. You have to read and insert into external database.
Option 3:
This option used a lock mechanism on a text file placed somewhere at your
webserver. You can create one file to store next number for all entities or you
can create one file for each entity. In your Plugin/Callout:
1. Put a lock on file.
2. Read the file and read the number there.
3. Update the number by adding 1.
4. Release the lock.
5. Use this number and set your target entity attribute appropriately.
string ProjectAutoNumber = “FilePath”
lock(this)
{
TextReader textReader = File.OpenText(ProjectAutoNumber);
AutoNumber = textReader.ReadLine();
textReader.Close();
AutoNumber = (long.Parse(AutoNumber) + 1).ToString();
TextWriter textWriter = File.CreateText(ProjectAutoNumber);
textWriter.WriteLine(AutoNumber);
textWriter.Close();
}
Issues:
1. Resource locking
2. File system Read/write cost
Although all these options work well but I am still looking for some more
robust solution. Ideally I am looking for something like GUID. GUID’s are
instantly available and 100% unique.
|