Accessing Application Configuration Settings from SQL CLR

In some (most?) cases we don’t want to hard-code connection details. In this case it means the path to the web service. Imagine a situation where you are consuming a web service that you’ve developed yourself, or another team in your company. And the web service is running in several environments: development, acceptance, production. Of course we don’t want to change our code and recompile each time we need to deploy to a different environment. So we need a way to configure the web service location.

The answer here lies in the sqlservr.exe.config file. This is a file like any other .config file, with a possible content like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="MyWebService" value="http://MyWebServiceServer:MyWSPort/PathTo/MyWebService.asmx" />
  </appSettings>
</configuration>

This file needs to be put in the same folder as your sqlservr.exe that’s running your SQL Server instance.  By default this path is C:\Program Files\Microsoft SQL Server\MSSQL.1\{MSSQL10_50.MSSQLSERVER}\Binn\.  When you restart your SQL Server instance while this file is in that folder, SQL Server will pick up any configuration settings from that file.  These can then be accessed from your custom stored procedure in this way:

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();

One of the issues I encountered (a bug) is if the first call through ConfigurationManager is to the static AppSettings method an error is returned indicating it couldn't read the key value. That error is:

"The value of the property 'key' cannot be parsed. * The error is: Request failed. (sqlservr.exe.Config line X)".

Now, the line that causes this exception is:

ConfigurationManager.AppSettings["MyWebService"]

There’s absolutely nothing wrong with that line of code. And, there’s nothing wrong with the configuration file.

So, after some debugging, I found that if I make some “dummy” call through ConfigurationManager to another method, then I can make the call to AppSettings with absolutely no issue. So, the following line is added just to provide a kludge of a workaround :

int firstcall = ConfigurationManager.ConnectionStrings.Count;

Now, this line does nothing other than initialize ConfigurationManager and force a de-serialization of the configuration file.

int firstcall = ConfigurationManager.ConnectionStrings.Count;

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();

Steps to Create SQL CLR component

1. Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2010, create a new Database Project:

2. First, you must change the version of the.NET Framework used to 3.5 at the top of this screen or your project will not deploy. Then change the project name to MyExtendedDBFunctions

Add a reference to the Your (AdventureWorks) database by clicking Add New Reference.

    Fill out the form and click OK.

3. Select AdventureWorks and click OK. Then right click the solution in Solution Explorer and select New -> Stored Procedure

Change the name to TestUserDefinedFunction.

Change the code to look like this:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class TestUserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

   public static SqlString TestUDF(int number)

   {

    BasicHttpBinding basicHttpBinding = new BasicHttpBinding();

   EndpointAddress endpoint2 = new EndpointAddress(new Uri("http://localhost:51610/TestService2.svc"));

    TestService2Client client2 = new TestService2Client(basicHttpBinding, endpoint2);

       return new SqlString(client2.GetData(internalID));    

   }

};

Create a WCF service with the default .net Framework 4.0 and expose that service and use the existing GetData().

4. Before we build our assembly there are a couple of project settings that need modification.

Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.

5. Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence UNSAFE.

6. Server and Database Settings

USE custDB

EXEC sp_configure 'clr enabled', '1';

RECONFIGURE;

ALTER DATABASE custdb SET TRUSTWORTHY ON;

-- Adding System.Web assembly

CREATE ASSEMBLY [System.Web]

FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY  [System.SMdiagnostics]

FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'

WITH PERMISSION_SET = UNSAFE

GO

-- Adding required assemblieds for the WCF service.

CREATE ASSEMBLY  [System.IdentityModel]

FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'

WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY  [System.IdentityModel.Selectors]

FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'     

WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY  [System.Messaging]

FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'     

WITH PERMISSION_SET = UNSAFE     

GO

--Attempted to perform an operation that was forbidden by the CLR host.

CREATE ASSEMBLY  [Microsoft.Transactions.Bridge]

FROM  'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'     

WITH PERMISSION_SET = UNSAFE     

GO

7. Click Build -> Deploy Solution and you should receive the following output:

If deploy failed, please follow below steps to add your CLR component and procedure/function to the sql server

Register our SQL CLR component

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyExtendedDBFunctions')

BEGIN

     DROP ASSEMBLY [MyExtendedDBFunctions]

ALTER ASSEMBLY MyExtendedDBFunctions

FROM '{our SQLCLR project.dll path}'

WITH PERMISSION_SET = UNSAFE ;

END

ELSE

BEGIN

CREATE ASSEMBLY MyExtendedDBFunctions

FROM '{our SQLCLR project.dll path}'

WITH PERMISSION_SET = UNSAFE;

END

Once our assemblies are created we have to create Procedure/Function in the below way.

CREATE PROCEDURE WeatherSP

    @zipCode nvarchar(10)

AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;

GO

CREATE Function TestSP(@name nvarchar(10)) returns nvarchar(150)

AS EXTERNAL NAME MyExtendedDBFunctions.TestUserDefinedFunctions.TestUDF;

Below are the possible errors and solutions 

1. Attempted to perform an operation that was forbidden by the CLR host.

    Sol: Set PERMISSION_SET to UNSAFE Because EXTERNAL_ACCESS did not work for us.

2. The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 233)

    Sol: Note that everything is configured properly and WCF client inside stored procedure should initialize just fine. The problem is somehow connected to machine.config file or better, to some initialization of types defined there.

          Looks like the exception is linked to WCF debugging feature, usually installed on development machines - this debugging service has problems with WCF client running under SQL Server.

         

        Workaround: (Temporary) disable WCF debugging by invoking this command line utility:

        [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u

        Now the WCF client inside SQL Server runs just fine. After you've done you can re-enable WCF debugging 

services by calling same exe with different parameter:

       [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -i

Note that administrator privileges are required to disable/enable it.

Calling WCF services from within Sql Server 2008 (SQLCLR)

SQLCLR(Common Language Runtime) makes us use .NET framework in SQL   Server.

I am developing a component which installed on a database server to connect WCF service on different hosts.
     Advantage of using CLR in SQL is that T-SQL itself doesn't provide the richer experienced programming environment in comparison with .net framework. We can't use arrays, collection, foreach statement and classes. By integrating CLR, makes it possible to have extensible features in stored procedure, trigger and user defined function.


My module uses .net framework 3.5 and SQL Server 2008 (sp1).
Originally, it built in .net 4.0. but 4.0 hasn't been supported SQL 2008.
So, I changed to 3.5.


Steps.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled.

     USE custDB
     EXEC sp_configure 'clr enabled', '1';
     RECONFIGURE;


2. Set the database trushworthy
ALTER DATABASE custdb SET TRUSTWORTHY ON;


3. Create below listed assemblies.

-- Adding System.Web assembly

CREATE ASSEMBLY [System.Web]
FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
WITH PERMISSION_SET = UNSAFE

-- Adding required assemblieds for the WCF service.
CREATE ASSEMBLY  [System.IdentityModel]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'
WITH PERMISSION_SET = UNSAFE
GO


CREATE ASSEMBLY  [System.IdentityModel.Selectors]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'    
WITH PERMISSION_SET = UNSAFE
GO


CREATE ASSEMBLY  [Microsoft.Transactions.Bridge]
FROM  'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'    
WITH PERMISSION_SET = UNSAFE    
GO

4.  Register our SQL CLR component


IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyStoredProcedures')
BEGIN
     DROP ASSEMBLY [MyStoredProcedures]
ALTER ASSEMBLY MyStoredProcedures
FROM '{our SQLCLR project.dll path}'
WITH PERMISSION_SET = UNSAFE ;
END
ELSE
BEGIN
CREATE ASSEMBLY MyStoredProcedures
FROM '{our SQLCLR project.dll path}'
WITH PERMISSION_SET = UNSAFE;
END

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyXmlSerializers')
BEGIN
DROP ASSEMBLY [MyXmlSerializers]
END


CREATE ASSEMBLY MyXmlSerializers
FROM{our SQLCLR project.XmlSerializers.dll path}'
WITH PERMISSION_SET = SAFE;

Once our assemblies are created we have to create Procedure/Function in the below way.


CREATE PROCEDURE WeatherSP
    @zipCode nvarchar(10)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;
GO

CREATE Function TestSP(@name nvarchar(10)) returns nvarchar(150)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.TestSP;




Finally, Don't forget that SQLCLR's data types are different to T-SQL.
For example, we should use NVARCHAR in SQLCRL instead of varchar as parameter for SQLCLR procedure.

For more info

ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "someCLR".

We get this error when we try to alter the assembly to load the exact same compile as you already have in the database  (the source code
to the assembly unchanged). If you recompile the assembly, you will not get this error.

Static Classes & Singleton

Singleton is the most widely known Design Pattern. Singleton makes sure that one only one instance of a class is created and the same instance is used by other programs and classes.An implementation of a Singleton class is shown below:


class Program
{
    static void Main(string[] args)
    {
            SingletonDemo s = SingletonDemo.GetInstance();
            s.M1();
            Console.Read();
        }
}
   
    public class SingletonDemo
    {
        //Single Instance
        private static SingletonDemo instance = new SingletonDemo();
        //Private Constructor
        private SingletonDemo() { }
        //Method to get the single instance

        public static SingletonDemo GetInstance()
        {
            return instance;
        }

        public void M1()
        {
            Console.WriteLine("SingletonDemo::M1 called");
        }
    }

This is just like any normal class, we are just preventing the creation of multiple instances by making the constructor private and the GetInstance method makes sure that the same instance is returned in all the calls to it.


Now the question is Why Can’t We Make Them Static Classes?
Let change this code to make it a static class.

class Program

{

    static void Main(string[] args)

    {

        StaticDemo.M1();

        Console.Read();

    }

}

public static class StaticDemo

{

    public static void M1()

    {

        Console.WriteLine("StaticDemo::M1 called");

    }

}

This looks all very simple. But there are some restrictions and limitations of the static classes.

Static classes cannot be instantiated.Static classes cannot have instance constructors.

Static classes can only have static constructors

Static classes are sealed. You cannot derive from a static class.

Static classes cannot derive from any other classes other than System.Object

Static classes cannot implement interfaces.

Static classes can only have static members

So from the above list of don’t haves its quite clear that static classes lack certain basic OO features like inheritance & polymorphic behavior. Static classes are containers to logically group a set of members.

The Singleton pattern has several advantages over static classes. First, a singleton can extend classes and implement interfaces, while a static class cannot.

Singleton can implement interfaces, inherit from other classes and allow inheritance.

Singleton object stores in Heap but, static object stores in stack

We can clone the object of Singleton but, we can not clone the static class object

Singleton class follow the OOP(object oriented principles) but not static class

we can implement interface with Singleton class but not with Static class.

Another advantage of a singleton is that it can easily be serialized, which may be necessary if you need to save its state to disc, or send it somewhere remotely.

Implementing Two interface having the same method signature in the same class

There can be scenario when we would have two interface with the same method name and same type of arguments, same number of arguments and even the return type can be same and we need to implement both the interface in the same class. How to implement the interface in our class? Most of you will be thinking whats so tough in this, just implement the interface and the method and move on. If the signature of the methods in the interface were different then there would have been no problem but here the signature of the methods in two different interface are same and both the interfaces are gonna be implemented in the same class. The two interface are as below.

class Test 
{
    static void Main()
    {
        SampleClass sc = new SampleClass();
        IControl ctrl = (IControl)sc;
        ISurface srfc = (ISurface)sc;

        // The following lines all call the same method.
        sc.Paint();
        ctrl.Paint();
        srfc.Paint();
    }
}


interface IControl
{
    void Paint();
}
interface ISurface
{
    void Paint();
}
class SampleClass : IControl, ISurface
{
    // Both ISurface.Paint and IControl.Paint call this method.  
    public void Paint()
    {
        Console.WriteLine("Paint method in SampleClass");
    }
}

// Output: 
// Paint method in SampleClass 
// Paint method in SampleClass 
// Paint method in SampleClass

From the above we have two interface with names IControl and ISurface and both have a single method named “Paint”. The signature of both the methods are same and we need to implement the interfaces in our class say “SampleClass”. One way of impelementing the interface is as shown above i.e. just having a “public” implementation of the interface method only once.

The above code implementation has got a limitation i.e the method “Paint” is considered to be a common method for all i.e common method for the class, and for the interfaces IControl and ISurface.

If you want to give different implementation to the methods in interface IControl and ISurface  what will you do? Its also simple, just have two impelementation of the same method and prefix the method names with the interface name as shown below.

public class SampleClass : IControl, ISurface
{
    void IControl.Paint()
    {
        System.Console.WriteLine("IControl.Paint");
    }
    void ISurface.Paint()
    {
        System.Console.WriteLine("ISurface.Paint");
    }
}
The class member IControl.Paint is only available through the IControl interface, and ISurface.Paint is only available through ISurface. Both method implementations are separate, and neither is available directly on the class. For example:
// Call the Paint methods from Main.
SampleClass obj = new SampleClass();
//obj.Paint();  // Compiler error.

IControl c = (IControl)obj;
c.Paint();  // Calls IControl.Paint on SampleClass.

ISurface s = (ISurface)obj;
s.Paint(); // Calls ISurface.Paint on SampleClass. 

// Output: 
// IControl.Paint 
// ISurface.Paint