SQL Queries from Transactional Plugin Pipeline

Sometimes the LINQ, query Expressions or Fetch just doesn ' t give you the ability-quickly Query your data in the the-the-do Want to. A good example of the lack of the outer join support if you want a WHERE clause to filter results based on the JO ined entity. Sometime, you just need to query your the database using good old T-SQL. In the CRM 4 you could does this fairly easily by simply opening a connection directly and doing what's need to.

Since transactional pipelines were introduced with CRM2011, I ' ve been dancing a jig every time I don ' t has write manual Rollback compensation Code–but–if You try a SQL query from a transactional pipeline that queries the same entity You're updating/inserting, you'll get a blocking lock that'll 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 hooks into the CRM Transaction and execute your query from within.

Note:i should say that this could being considered breaking the following rule in the SDK so defines what is supported or Not

"The use of application programming interfaces (APIs) and 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's familiar with the System.Data library, so I ' m just posing what to get a sqltransaction, and you C A 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);D Ataset result = SqlHelper.ExecuteDataset (TX, CommandType.Text, "SELECT ...");

　　

You can also call a stored procedure in a different database then points back to the MSCRM database if you have complex qu Eries. You'll need to use ' SET trustworthy on ' to ensure, the security context is passed between the 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

SQL Queries from Transactional Plugin Pipeline