Microsoft Dynamics CRM 2011 中 SQL 语句总结

一、查询用户和团队

  插入的类型：OwnerIdType 可以为用户或团队：

 1.团队： select top 1 ObjectTypeCode from metadataschema.entity where logicalname='team'     结果为：8

 2.用户： select top 1 ObjectTypeCode from metadataschema.entity where logicalname='systemuser'    结果为：9 

二、CRM的一个实体（比如为new_entity,）是用两个表组成的，分别为new_entityBase(基本表)和new_entityExtensionBase（扩充基本表），new_entity 在数据库里面是以视图形式存在的。

对new_entity实体增删改操作，需要考虑事物，要么都成功，一个失败那边都失败。

SQL 新建一条记录： 先 insert into new_entityBase           后 insert into new_entityExtensionBase

SQL 删除一条记录： 先insert into new_entityExtensionBase 后  insert into new_entityBase 

SQL 修改一条记录：update new_entityExtensionBase 就可以了

三、常见SQL处理方法

    1.金额取小数点后两位：CAST(金额字段名 as decimal(20,2)) as Price  也可以用C#代码处理 String.Format("{0:f}","金额值")，个人建议用sql语句处理

    2.取数据字段类型为两个选项，选项集 可以用 ：

(select Value from StringMap S where AttributeName='字段名' and ObjectTypeCode = (select ObjectTypeCode from MetadataSchema.Entity
where LogicalName='实体名' and LangId='语言编码（比如中文2052）' and AttributeValue=a.字段名)) 

  3. 获取TransactionCurrencyId ： select TransactionCurrencyId from transactioncurrencyBase where ISOCurrencyCode = 'CNY' 

      获取ExchangeRate（汇率）：select ExchangeRate from transactioncurrencyBase where ISOCurrencyCode='CNY'

  4. 如果CRM的基础货币会变化，修改或新增金额的时候，需要金额除汇率，比如： isnull(金额字段的值,0.00)/(select ExchangeRate from transactioncurrencyBase where ISOCurrencyCode='CNY')

  5.获取时间  case when A.new_purchasedate is null then null else convert(varchar(10),DATEADD(hh, 8, 时间字段值),111) end as 时间1

    说明：查询: DATEADD(HOUR,8,ph.CreatedOn) as CreateDate在基础上加8个小时 

            修改: DATEADD(HOUR,-8,GETUTCDATE()), 在基础上减去8个小时

    C#代码格式化时间 String.Format("{0:yyyy/MM/dd HH:mm:ss}", "时间字段值"); 这里需要yyyy/MM/dd HH:mm:ss（一般是用这个，24小时制） 和yyyy/MM/dd （12小时制）hh:mm:ss 是有区别的。