SQL Queries from Transactional Plugin Pipeline
Sometimes the LINQ, Query Expressions or Fetch just doesn't give you the ability to quickly query your data in the way you want to. A good example of this is the lack of left outer join support if you want a where clause to filter results based on the joined entity. Sometime, you just need to query your database using good old T-SQL. In CRM 4 you could do this fairly easily by simply opening a connection directly and doing what you need to.
Since Transactional Pipelines were introduced with CRM2011 , I've been dancing a jig every time I don't have write manual rollback compensation code – but – if you try a SQL query from a transactional pipeline that queries the same entity that you are updating/inserting, you'll get a blocking lock that will cause the operation to time out.
To get around this, you have a number of options:
1) Call the SQL from outside a transaction in the PreValidation or an Async Pipeline
2) Use the following method to hook into the CRM Transaction and execute your query from within that.
Note: I should say that this could be considered breaking the following rule in the SDK that defines what is supported or not:
"The use of application programming interfaces (APIs) other than the documented APIs in the Web services DeploymentService, DiscoveryService, Organization Data Service, SOAP endpoint for Web Resources and OrganizationService."
I'm assuming that you are familiar with the System.Data library, so I'm just posing how to get a SqlTransaction, and you can do the rest:
Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
object platformContext = context.GetType().InvokeMember("PlatformContext", System.Reflection.BindingFlags.GetProperty, null, context, null);
SqlTransaction tx = (SqlTransaction)platformContext.GetType().InvokeMember("SqlTransaction", System.Reflection.BindingFlags.GetProperty, null, platformContext, null);
DataSet result = SqlHelper.ExecuteDataset(tx, CommandType.Text, "SELECT ...");
You can also call a stored procedure in a different database that points back to the MSCRM database if you have complex queries. You'll need to use 'SET TRUSTWORTHY ON' to ensure that the security context is passed between the two databases.
My advice would be to only use this method only where using the SDK is just not possible or performs too slowly.
Hope this helps.
from: http://www.develop1.net/public/post/SQL-Queries-from-Transactional-Plugin-Pipeline.aspx
最新文章
- 让tomcat启动更快的设置
- HTTP和HTTPS
- .NET开源插件内核
- 使用spring的特殊bean完成配置
- Metro UI 菜单(Winform)
- node js 常用模块
- C#获取网页中某个元素的位置,并模拟点击
- 关于HTML的总结
- libtcmalloc 简单使用
- java--工具方法
- 【原创】leetCodeOj --- Binary Search Tree Iterator 解题报告
- tomcat session失效时间
- JDK1.8源码(十)——java.util.LinkedHashSet类
- SQL注入之Sqli-labs系列第四十六关(ORDER BY注入)
- 配置wildfly10为linux的服务,并开机启动
- 微信小程序- 提示不在以下合法域名列表中
- cubieboard安装小记
- kali 2018.1安装教程
- Swift3.0字符串相关操作
- JMeter学习笔记(五)-总结