Pogo69's Blog

May 10, 2011

CRM Web Services from SQL Server Transact SQL (SQLCLR)

Filed under: C#, CRM, SQL Server, Transact SQL — pogo69 [Pat Janes] @ 17:53

Let me preface this post by saying “bugga!!”.  Sometime last year, I assisted one of my fellow CRM Community Forum members through the process of building a SQL CLR assembly that communicates with CRM via web services.  This mechanism allows us to synchronise SQL Server updates with CRM in a supported manner by calling our assembly via Create/Update triggers in our 3rd party SQL Server database.  I created the shell for this post quite some time ago, but never got around to starting it, let alone finishing.

Now that CRM 2011 has been released, I was hoping to update the process to describe the steps necessary to create a SQLCLR assembly that communicates with the new CRM 2011 OrganizationService.  Unfortunately, this is not possible, as SQL Server 2005 and SQL Server 2008 (up to and including R2) do not support the .NET Framework v4.0.

So… back to the CRM 4.0 SOAP endpoint it is!!

Creating the SQLCLR Assembly

The example I’m going to describe will allow the creation of a new Contact record from within an SQLCLR Stored Procedure.

New Visual Studio Project

In Visual Studio:

  1. Click the ‘File’ menu
  2. Click ‘New’
  3. Click ‘Project…’
  4. In the Project window, select the ‘Visual C# SQL CLR Database Project’ in the Database / SQL Server category:

In the ‘New Database Reference’ dialog, choose or enter the name of the target SQL Server instance; then select the target database:

Choose ‘No’ for SQL/CLR debugging (or ‘Yes’ if you wish, but I’m not going to go into that here):

New Stored Procedure class

Next, we create the shell of our new SQLCLR Stored Procedure.

  1. Right-click on the Project
  2. Click ‘Add’
  3. Click ‘Stored Procedure…’

In the ‘Add New Item’ dialog, enter the name of your SQLCLR Stored Procedure:

Your newly created Stored Procedure class will look like the following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void CreateContact()
	{
		// Put your code here
	}
};

CRM Web Reference

Now, we need to add a Web Reference to the CRM 4.0 SOAP endpoint.  It is via this interface that we will communicate with the CRM.

  1. Right-click on the ‘References’ folder
  2. Click ‘Add Service Reference…’

Click on ‘Advanced…’:

Click on ‘Add Web Reference…’:

In the ‘Add Web Reference’ dialog, enter the fully qualified URL path to your CRM Service; then click on Green arrow icon next to the URL:

The CRM Web Service URL takes the following format:

http://<server>:<port>/MSCRMServices/2007/CrmService.asmx

Visual Studio will send a request to the CRM Service, retrieve the WSDL and build a Web Service proxy, which you must now name in the ‘Web reference name’ field:

Click on ‘Add Reference’ and you will see your newly added Web Reference in the Project’s ‘Web References’ folder:

Using the CRM Service to Create a Contact

Stored Procedure Signature

Next, we’ll add some parameters that can be used to create our new Contact.  Our contacts will each have:

  • First Name
  • Last Name
  • Email Address
public static void CreateContact(SqlString firstName, SqlString lastName, SqlString email)
{
	// Put your code here
}

Each of the SQL Server datatypes is represented by a SQLCLR “wrapper” object, that encapsulates the underlying .NET object.  This sample uses only SqlString, which encapsulates the .NET “string” datatype and represents a SQL Server NVARCHAR.

CrmSdk Namespace

I’ve added a “using” directive, so that we can use the CRM Web Service objects and methods directly:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using SqlServerProject1.CrmSdk;

public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void CreateContact(SqlString firstName, SqlString lastName, SqlString email)
	{
	}
};

CRM Web Service Connection and Interactivity

At this point, we can write CRM Web Service code just like we always have in our plugins, 3rd party applications and whatever we decide to implement.

  1. Obtain an Authentication Token
  2. Create a Web Service connection
  3. “Do stuff”
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using SqlServerProject1.CrmSdk;

public partial class StoredProcedures
{
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void CreateContact(SqlString firstName, SqlString lastName, SqlString email)
	{
		CrmAuthenticationToken token = new CrmAuthenticationToken();
		token.AuthenticationType = 0;
		token.OrganizationName = "<uniqueorgname>";

		using (CrmService crmService = new CrmService())
		{
			crmService.Url = "http://<server>:<port>/MSCRMServices/2007/CrmService.asmx";
			crmService.Credentials = new System.Net.NetworkCredential("<username>", "<password>", "<domain>");
			crmService.CrmAuthenticationTokenValue = token;
			crmService.UnsafeAuthenticatedConnectionSharing = true;
			crmService.PreAuthenticate = true;

			contact contact = new contact();
			contact.firstname = firstName.Value;
			contact.lastname = lastName.Value;
			contact.emailaddress1 = email.Value;

			crmService.Create(contact);
		}
	}
};

Side Issues

Exception Handling

The code above has no exception handling.  Your production code should.  But I’m leaving that as an exercise for the reader, as it is largely an application-dependant architectural issue.

Web Service Connection Parameters

The Web Service connection parameters (credentials, URL, Organisation Name) etc are hard-coded.  Again, configurable alternatives are left as an exercise for the reader, although I may come back to this issue at a later time.  For now, some options to consider include:

  • Passing in Connection data as additional parameters
  • Storing Connection data in the Database
  • Storing Connection data in the Registry or File System

XML Serialisation Assembly

One of the restrictions of SQLCLR is that you cannot make use of any .NET Assembly that has not explicitly been loaded into the target SQL Server database, other than a very restricted set of .NET Framework assemblies that have been deemed “safe”.

A side-effect of this, is that an assembly such as ours that utilises Web Services will not work unless we manually generate an XML Serialisation Assembly.

The .NET runtime uses a Serialisation assembly to package Web Service requests into XML packets for transfer via HTTP to the Web Service host.  Ordinarily, serialisation assemblies are generated “on the fly” by the Framework, however we cannot make use of this mechanism as there is no way to dynamically load the resultant assembly into SQL Server.

To generate a Serialisation assembly from within Visual Studio:

  1. Right-click on the Project
  2. Click ‘Properties’
  3. Navigate to the ‘Build’ tab
  4. Change ‘Generate serialization assembly’ to ‘On’

Using sgen.exe from the Command Line

If you experience any issues with the generation of serialisation assemblies from within Visual Studio, you can use sgen.exe directly from the command line.  I do this for all of my serialisation code because:

  • There is no way to specify sgen.exe parameters from within the Visual Studio GUI
  • It takes a ridiculously long time for sgen.exe to complete using whatever parameters Visual Studio chooses to throw at it

I create a post-build task that runs the command from within the build process; the command line I use is as follows:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\sgen.exe" /a:"<drive>:\<path to solution>\<project folder>\bin\Debug\SqlServerProject1.dll" /p /force

I then wrap the sgen.exe command up in a batch file.  You can configure the Post Build Event in the Project properties as follows:

Integration with SQL Server

Now, build your Project and locate the resultant assemblies.  The names will reflect the name you gave your assembly, but mine are:

  • SqlServerProject1.dll
  • SqlServerProject1.XmlSerializers.dll

Enabling CLR in SQL Server

By default, CLR interoperability is not enabled in SQL Server.  To enable it, run the following in SQL Server Management Studio:

USE master;
GO
EXEC sp_configure 'CLR Enabled', 1;
go
RECONFIGURE WITH OVERRIDE
GO

It’s a Matter of Trust

For security reasons, SQL CLR assemblies cannot by default be loaded into a SQL Server database.  In order to enable the uploading of our assemblies, there are two alternatives.

Setting TRUSTWORTHY ON for the Target Database

ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON

This is useful for development and/or testing because it’s quick and easy.  However, it does open up the database to the possibility of malicious assemblies being loaded as all assemblies will now be trusted.  For production environments you should instead give “trust” only to your assemblies.

Trusting Individual Assemblies

CREATE ASYMMETRIC KEY CrmKey
FROM EXECUTABLE FILE = '<full path to your assembly>\<assemblyname>.dll'
GO

Now create a login associated with the CrmKey and grant it External Access rights.

CREATE LOGIN [CrmLogin] FROM ASYMMETRIC KEY [CrmKey];
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO [CrmLogin];
GO

Finally add a user for your login in your database:

USE <your database>
GO
CREATE USER [CrmLogin] FOR LOGIN [CrmLogin];
GO

Deployment

Project Database Properties

When creating the SQLCLR Project in Visual Studio, we created a connection to the target SQL Server database.  On the ‘Database’ tab in the Project Properties, we can influence the deployment of our SQLCLR assembly.

Set the Permission level to ‘External’ – we need this, as our assembly will require access outside of SQL Server to communicate with CRM Web Services.

Set the Assembly Owner to either:

  • dbo – if you set TRUSTWORTHY ON in the database
  • CrmLogin – if you are assigning trust only to your assemblies and created the CrmLogin as per the instructions above

Deploying the Serialisation Assembly

The deployment process will only automatically deployment your SQLCLR assembly.  To deploy the serialisation assembly, we create a Post-Deployment script in the PostDeployScript.sql file:

CREATE ASSEMBLY [SqlServerProject1.XmlSerializers]
AUTHORIZATION [dbo] FROM '<solution path>\<project folder>\bin\Debug\SqlServerProject1.XmlSerializers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

NB: Because I am deploying my assemblies to a SQL Server on another machine, I had to configure the post deployment script above to use a UNC path to the serialisation assembly.  This is necessary, as the SQL Server must be able to gain file system access to the file.

Build and Deploy

Now… build your Project.  Assuming it builds successfully, you can Deploy it (the Deploy process actually does a build before it Deploys, but it can’t hurt to test the build first).

To deploy:

  1. Right-click on the Project
  2. Click ‘Deploy’

If it all goes as expected, your assemblies will be uploaded into the target database and you will be able to find them in SQL Server Management Studio by:

  1. Expanding the target database
  2. Expanding ‘Programmability’
  3. Expanding ‘Assemblies’

As you can see in the image above, the Deployment process also creates a “wrapper” Stored Procedure for the SQLCLR Stored Procedure implemented in my assembly.  The definition of that Stored Procedure is as follows:

CREATE PROCEDURE [dbo].[CreateContact]
	@firstName [nvarchar](4000),
	@lastName [nvarchar](4000),
	@email [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject1].[StoredProcedures].[CreateContact]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'CreateContact.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CreateContact'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'13' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CreateContact'
GO

To call our new Stored Procedure, simply use the following:

DECLARE	@return_value int

EXEC	@return_value = [dbo].[CreateContact]
		@firstName = N'Testy',
		@lastName = N'Tester',
		@email = N'testiest@test.com'

SELECT	'Return Value' = @return_value

With the following result:

Conclusion

I hope this post makes sense.  It’s rather long, rather involved and I’ve had a really long day.  So, although I’ve read through it to make sure it isn’t obviously unintelligible I can’t guarantee that it is without issue(s).  If anyone spots anything that requires clarification and/or modification, let me know.

Enjoy.

Advertisements

13 Comments »

  1. Hi Pogo

    Very Information article. Thanks for sharing.

    Gave me a satisfied feeling of a new learning activity.

    I would also recommend you to share this information in CRM Wiki, if you have not done it 🙂

    Thanks & Regards
    Vinoth

    Comment by Vinoth — May 20, 2011 @ 19:08

    • Cheers mate.

      Ye, I keep open a browser window with the CRM Wiki in it, as a reminder to start adding content, but I never seem to get around to it. Took me long enough to start (semi-)regularly posting here.

      Comment by pogo69 — May 20, 2011 @ 20:25

  2. Please continue blogging. Yours articles are truly gems!

    Comment by vel — June 3, 2011 @ 13:47

  3. Is a cracking article mate, and quite unique as I don’t think there is much online about using SQLCLR with Dynamics CRM so imagine will be very useful to people who approach the topic.

    Comment by crmconsultancy — June 8, 2011 @ 19:19

  4. hi

    this is very nice article.

    but am getting the error..

    System.Security.SecurityException: Request for the permission of type ‘System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.
    System.Security.SecurityException:
    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
    at System.Security.CodeAccessPermission.Demand()
    at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
    at System.Net.HttpRequestCreator.Create(Uri Uri)
    at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
    at System.Net.WebRequest.Create(Uri requestUri)
    at System.Web.Services.Protocols.WebClientProtocol.GetWebRequest(Uri uri)
    at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebRequest(Uri uri)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.GetWebRequest(Uri uri)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    while calling the webservice…

    can u help me out..

    Comment by sadhana — July 8, 2011 @ 01:51

  5. Hi Pogo

    Do you have any benchmark statistics on this? Against No of Records Vs Time? If so, please share with me.

    Hope it will not new CRM Service for each of the new record it creates. Am i Right? In case I want to insert 100 records present in my Legacy Database. Will it initialize 100 CRM Connections?

    Thanks & Regards
    Vinoth

    Comment by Vinoth — August 22, 2011 @ 06:37

    • Hi Vinoth,

      No, I have no benchmarks. However, your fears are justified – it will create a new Web Service connection each time and there is no mechanism within SQL Server that I can think of to ‘persist’ an open connection.

      If you need to make mass updates, you may have to re-architect your SQL CLR stored procedure to take a table parameter or similar in order to process all of the data within one operation.

      Comment by pogo69 — June 14, 2012 @ 08:56

  6. Hi Pogo,

    I hope you still visit this part of your blog. I’ve followed your instructions and now I am encountering the following error:

    Msg 6522, Level 16, State 1, Procedure createPMTCalcItem, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate “createPMTCalcItem”:
    System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
    System.IO.FileLoadException:
    at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
    at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
    at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
    at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
    at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompile

    System.InvalidOperationException:
    at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
    at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
    at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
    at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
    at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
    at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
    at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
    at PMTBatchSQLCLR.DSWDCrm.CrmService..ctor()
    at UserDefinedF…

    I hope you can help me ASAP.

    Kind regards,
    Tim

    Comment by TIM — June 13, 2012 @ 18:08

    • Hi Tim,

      The error message is referring to the inability of SQL Server to use “on the fly” generates Xml Serialisation assemblies. This is the serialisation code built by the .NET framework to marshall the object/properties etc within your Web Service requests into XML packets for transmission via HTTP with each Request.

      Because SQL Server can only access assemblies that have been explicitly registered with it, these “on the fly” generates assemblies remain inaccessible.

      You error message suggest that something went awry with the steps described in the sections above; ‘Xml Serialisation Assembly’ and ‘Deploying the Serialisation Assembly’. Do you see your Serialisation Assembly listed in the target database in SQL Server?

      Comment by pogo69 — June 14, 2012 @ 08:53

      • Pogo,

        Yep I can see my serialization assembly listed in my target database as well as my functions and assemblies. I already followed every step in your blog. Though I could not create an ASYMMETRIC KEY, i set the database permission to external and owner to dbo. I’m still stuck in the same error.

        -Tim

        Comment by TIM — June 14, 2012 @ 19:14

      • hi,

        I solved the problem I was talking about. I signed the SQLCLR and created the serialization with the key.
        http://footheory.com/blogs/bennie/archive/2006/12/07/invoking-a-web-service-from-a-sqlclr-stored-procedure.aspx

        Thanks!!!

        Comment by TIM — June 15, 2012 @ 15:26

      • glad to hear you got it sorted, mate.

        and thanks for posting the fix back here – will help anyone else who runs across the same issue.

        Comment by pogo69 — June 18, 2012 @ 08:29

  7. […] CRM Web Services from SQL Server Transact SQL (SQLCLR) | Pogo69’s Blog […]

    Pingback by Thinking Enterprise Solutions — October 14, 2016 @ 22:11


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: