Good morning!

May anybody tell me which is the best practice and why when I program inside a CLR stored procedure or CLR function or Other CLR?

I may have my code like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace test
{
    public class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void test
        (
            // Some parameters
        )
        {
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //Some TSQL code
            }
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //Some TSQL code
            }
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //Some TSQL code
            }
            // Some .Net code
        }
    }
}

Or I may have my code something like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace test
{
    public class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void test
        (
            // Some parameters
        )
        {
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //Some TSQL code
                //Some .Net code
                //Some TSQL code
                //Some .Net code
                //Some TSQL code
            }
            // Some .Net code
        }
    }
}

As you may see I may have several connections inside the CLR or I may have a single connection. Wich is the best practice and why?

Thanks a lot for your valuable help.

Regards,

Jamesit0

Using the context connection doesn't open a connection to SQL Server as such, a simplistic explanation is that it just provides access to the connection you already have, to hang things like SqlCommand objects from. Except for micro-differences that come from having more code when you reference it multiple times, there no difference as far as the performance of the connection object goes; your second piece of code is less code. You should get an error if you try and use multiple internal connections at the same time, however.

Also, on the subject of best practices, if all the stored procedure does is issue T-SQL statements, it is a best practice to write that stored procedure in T-SQL rather than in SQLCLR. Such a procedure will be 3-5 times faster written in T-SQL.

Cheers, Bob 

• Marked as answer by Jamesit0 13 hours 16 minutes ago
• Unmarked as answer by Jamesit0 4 hours 33 minutes ago

Good afternoon Bob!

Thanks a lot for your reply.

Regards,

Jamesit0.

Using the context connection doesn't open a connection to SQL Server as such, a simplistic explanation is that it just provides access to the connection you already have, to hang things like SqlCommand objects from. Except for micro-differences that come from having more code when you reference it multiple times, there no difference as far as the performance of the connection object goes; your second piece of code is less code. You should get an error if you try and use multiple internal connections at the same time, however.

Also, on the subject of best practices, if all the stored procedure does is issue T-SQL statements, it is a best practice to write that stored procedure in T-SQL rather than in SQLCLR. Such a procedure will be 3-5 times faster written in T-SQL.

Cheers, Bob 

• Marked as answer by Jamesit0 Thursday, June 25, 2015 5:50 PM
• Unmarked as answer by Jamesit0 Friday, June 26, 2015 2:33 AM

Good night Bob!

I'm reviewing your answer and I think I wasn't enought clear. I mean using full connection like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace test
{
    public class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void test
        (
            // Some parameters
        )
        {
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.Text;
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
				cmd.CommandText = //Some TSQL code;
				cmd.Connection = conn;
				conn.Open();
				cmd.ExecuteNonQuery();
            }
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
				cmd.CommandText = //Some TSQL code;
				cmd.Connection = conn;
				conn.Open();
				cmd.ExecuteNonQuery();
            }
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
				cmd.CommandText = //Some TSQL code;
				cmd.Connection = conn;
				conn.Open();
				cmd.ExecuteNonQuery();
            }
            // Some .Net code
        }
    }
}

And do the same with a single connection like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace test
{
    public class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void test
        (
            // Some parameters
        )
        {
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.Text;
            // Some .Net code
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
				cmd.CommandText = //Some TSQL code;
				cmd.Connection = conn;
				conn.Open();
				cmd.ExecuteNonQuery();
				// Some .Net code
				cmd.CommandText = //Some TSQL code;
				cmd.ExecuteNonQuery();
				// Some .Net code
				cmd.CommandText = //Some TSQL code;
				cmd.ExecuteNonQuery();
				// Some .Net code
            }
        }
    }
}

Pleas forgive my mistake with my question.

Regards,

Jamesit0.

I understood what you were asking...

In general, it's a best practice to clean up (Close, Dispose) either explicitly or implicitly (by using the "using" construct) objects like SqlDataReader and SqlCommand, especially if you're going to use more than one of these per connection. Even though, if you only do one SQL query in the SQLCLR procedure, the objects do get cleaned up at procedure end.

In SQL Server in general you can't have multiple open resultsets (DataReader) or queries per connection without a special feature called MARS, and MARS is not supported in SQLCLR. And, as I said in previous answer, you can't have multiple context connections (i.e. only one context connection can be open at a time).

And, at the expense of repeating myself, a procedure that include a lot of T-SQL activity (there are 3 SQL statements in your example) and anything other than LOTS of .NET code (or something that can ONLY be done in .NET), should really be written in T-SQL rather than SQLCLR.

Cheers, Bob

• Edited by Bob BeaucheminMVP, Moderator 2 hours 52 minutes ago

I understood what you were asking...

In general, it's a best practice to clean up (Close, Dispose) either explicitly or implicitly (by using the "using" construct) objects like SqlDataReader and SqlCommand, especially if you're going to use more than one of these per connection. Even though, if you only do one SQL query in the SQLCLR procedure, the objects do get cleaned up at procedure end.

In SQL Server in general you can't have multiple open resultsets (DataReader) or queries per connection without a special feature called MARS, and MARS is not supported in SQLCLR. And, as I said in previous answer, you can't have multiple context connections (i.e. only one context connection can be open at a time).

And, at the expense of repeating myself, a procedure that include a lot of T-SQL activity (there are 3 SQL statements in your example) and anything other than LOTS of .NET code (or something that can ONLY be done in .NET), should really be written in T-SQL rather than SQLCLR.

Cheers, Bob

• Edited by Bob BeaucheminMVP, Moderator Friday, June 26, 2015 4:14 AM
• Marked as answer by Jamesit0 18 hours 23 minutes ago

Good morning Bob!

Thanks a lot again for your reply!

I'm agree with you doing it inside TSQL and not inside CLR but in my case that isn't an option because I don't have the server control, because the server belong to the client and I need to protect better the code and the "WITH ENCRYPTION" option isn't enought. If you know another way I'm listening.

Thanks a lot again.

Regards,

Jamesit0.

SQLCLR doesn't provide any more protection of the code than T-SQL. For any obfuscator, there's always eventually a de-obfuscator. Or people can read the assembly code that's eventually produced by an intermediate language-based system. The difference is only in the ease of cracking and you're correct that WITH ENCRYPTION is trivial to crack.

But, if your code is issuing SQL anyway, best to factor the SQL statements out into T-SQL anyhow, because profiler/extended events exist to trace that. And ways to ensure the T-SQL doesn't appear in those utilities also cause hassles with, for example, query plan visibility.

In addition, AFAIK, obfuscated SQLCLR code is not officially supported, even if the obfuscation you're using does technically work. So if the code causes a problem for one of the customer you're trying to obfuscate the code away from, they'll need to ask you for a de-obfuscated version to obtain support. 

My 2 cents...

Cheers, Bob

because the server belong to the client and I need to protect better the code and the "WITH ENCRYPTION" option isn't enought. If you know another way I'm listening.

In two words: license agreement.

And if you truly don't want the client to be able access the code at all, host the solution, for instance in Windows Azure.

Writing all SQL Server code in the CLR as an obfustication measure is a serious mistake in my opinion. It can't see how this could ever turn out well.