MSCRM自动编号最佳解决方案

在自动添加连续的客户编号中,如果要实现连续增加的不重复的编号的,方案还是比较多的, 

主要是SQL Server 没有想oracle一样的sequence可以直接使用,而且MS CRM的客户化中我们不能使用像Siebel一样的采用ROW ID 转化为row number,因为MS CRM分ID方式类似于5709C33D-758A-DE11-9269-000C2980CE14

,比较难以转化为不重复的0001,0002 …..之类的编号,

 

我尝试过将所有需要使用的自动编号都放在一个表中,每次取出一个数字之后自动增加编号,但是这个方案最终被我否定,因为在并发用户小于500时还可以,当并发用户大于1000时,高达数百的请求会出错,

 

所以最后选择了如下的方案,此方案利用了SQL Server的自动编号功能,

 

 

1、先创建一个表，其中的seqId字段自动增加

 

 

create table Acc_Seq(

      Seq_Id int identity(1,1) primary key,

      Seq_Tmp varchar(1)

)

 

 

create procedure P_GetSeq_ Acc_Seq

as

begin

          declare @NewSeqValue int

      set NOCOUNT ON

      insert into Acc_Seq (Seq_Tmp) values ('a')  

      set @NewSeqValue = scope_identity()  

      delete from SeqT_0101001 WITH (READPAST)

return @NewSeqValue

end

 

如果在sql server里面取数的话这样就可以去到

Declare @NewSeqVal int

Exec @NewSeqVal =  P_GetSeq_ Acc_Seq

Print @NewSeqVal

 

 

 

2、下面写一个plugin来增加客户编号

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.Crm.Sdk;

using Microsoft.Crm.SdkTypeProxy;

using System.Data.SqlClient;

using System.Data;

 

namespace Microsoft.Crm.Sdk.PreAccountPluginSample

{

    public class PreAccountPluginSample : IPlugin

    {

        public string ExecSql()

        {

            string connStr = @"server=WIN-3ZDVIOB3YWK; User ID = sa; password=******;database=MSCRM_MSCRM";

            SqlConnection conn = new SqlConnection(connStr);

            SqlCommand myCommand = new SqlCommand("P_GetSeq_ Acc_Seq ", conn);

            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter id = myCommand.Parameters.AddWithValue("@id", System.Data.OleDb.OleDbType.Integer);

            id.Direction = ParameterDirection.ReturnValue;

            conn.Open();

            myCommand.ExecuteNonQuery();

           return myCommand.Parameters[0].Value.ToString();

        }

        public void Execute(IPluginExecutionContext context)

        {

            string account_seq = "";

            account_seq = ExecSql();         

            if (context.InputParameters.Properties.Contains("Target") &&

                context.InputParameters.Properties["Target"] is DynamicEntity)

            {

              DynamicEntity entity = (DynamicEntity)context.InputParameters.Properties["Target"];

                if (entity.Name == EntityName.account.ToString())

                {

                    if (entity.Properties.Contains("accountnumber") == false)

                    {

                        if (account_seq == "")

                        {

                            account_seq = "error" + DateTime.Now.ToString("yyyyMMddHHmmss");

                     

                        }                     

                            StringProperty accountNumber = new StringProperty("accountnumber", account_seq);

                            entity.Properties.Add(accountNumber);                       

                    }

                    else

                    {

                        throw new InvalidPluginExecutionException("The Account Number can only be set by the system");

                    }

                }

            }

        }     

    }

}