SQL CLR - safe/unsafeand memory usage concerns

I'm running a MS SQL Server 2008 R2 installation which is in the process of being commissioned.

I've written an application which encrypts and decrypts columns in tables on demand which works fine. This application uses a DataTable object in order to process through the row开发者_运维技巧s in any given table.

In order to integrate this into SQL, I'm forced to firstly create the following assembly to satisfy the usage of the DataTable object:

create assembly [system.data.datasetextensions]
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.DataSetExtensions.dll'
with permission_set = unsafe

No problem; of course I have to declare this as being unsafe as it contains a static field not marked read only, therefore not thread safe, therefore unpredictable from execution to execution.

However, I would like to declare my main CLR DLL as being safe (obviously by signing, putting in the GAC etc) and can do this no probs. However, when I run the code, I get a nasty 'index out of range' error, which does not raise its ugly head if I declare my main CLR DLL as being unsafe also. I assume this is due to the fact that I am attempting to access an unsafe assembly, which is a no no for safe assemblies???

A second thing is if this main CLR DLL is running as unsafe, are 'expired' CLR calls to this not cleaned up by SQL Server/.Net??? The memory consumption increases when I run it, but memory never seems to be released; I really don't want to wait until the App Domain is unloaded in order for memory to be freed!!

Thanks you guys..

---

Are you sure there was no other way than using a DataTable ?

1) Can you just use a SqlCommand, like in this example ? http://msdn.microsoft.com/en-us/library/ms131094.aspx

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

public class StoredProcedures 
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PriceSum(out SqlInt32 value)
    {
        using(SqlConnection connection = new SqlConnection("context connection=true")) 
        {
           value = 0;
           connection.Open();
           SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);
           SqlDataReader reader = command.ExecuteReader();

           using (reader)
           {
              while( reader.Read() )
              {
                  value += reader.GetSqlInt32(0);
              }
           }         
        }
    }
}

2) Are your encrypt and decrypt functions writeable as CLR functions? Why do you need to look at the values in the tables from the functions? Could you read the values from a regular UPDATE statement and pass them to those functions?

If the assembly is unsafe for SQL, it is not recommended to bypass the recommendation unless you know exactly what you are doing. I would advice to look at supported ways first.