4/12/2006 Upgrading To Sequel 2005 Lecture Summery

Yesterday I gave a 3 hours talk at the Israeli SQL Server user group. It want great!

The room was literally packed. We filled the "Dekel 0" room. all chairs were full, the stairs didn't have any more place to sit on and there even people standing near both doors outside the room looking in.
There were 120~ people which is an awesome turn-out for an Israeli User group!!!

I hope everyone had a great time!

I gave two parts of a three part talk:

1. T-SQL Changes - 99% of feedbacks gave this part 5 stars (out of 5).
2. XML Data Type - was not presented due to changes in the schedule.
3. .Net Integration - Where hardcore .Net 2.0 meets SQL server 2005. Note to self, never demo Async programming at 9 PM O'clock again.

Files for download:

• http://www.JustinAngel.Net/files/UpgradingToSequel2005.pptx (power point 2007, if you need a 2003 version drop me a comment)
• http://www.JustinAngel.Net/files/UpgradingToSequel2005Scripts.zip
• http://www.JustinAngel.Net/files/UpgradingToSql2005Project.zip

Couple of questions the arose during the presentation and I said I'd get back to the audience:

1. Is it possible to use the new Async Methods (SqlCommand.BeginExcuteReader, SqlCommand.EndExcuteReader, SqlCommand.BeginExcuteNonQuery & SqlCommand.EndNonQuery) with SQLCLR?
   
   The Answer is - No. It is not possible to do use Async methods in any SQLCLR scenario.
   The Reason being that in order to Execute those methods we need to have a connection defined on the SqlCommand (as we are not using the SqlContext.Pipe). That connection has to be a "current context = true" connection string as we are actually inside the DB at that time.
   
   In order to use Async SqlCommand methods we need to add "Async = true" in the connection string.
   However, Custom attributes are not allowed in "current context" connection strings.
   

   "Restrictions on Regular and Context Connections 

   No other connection string keywords can be used when you use "context connection=true"."

   http://msdn2.microsoft.com/en-us/library/ms131101.aspx

    

2. Why are we using struct as  SQLCLR Aggregates and not a class?
   OK, This is a really interesting question. There actually isn't any "real" reason to not use classes in stand of structs. However, let's look at this those piece of code:
   
   

   public class EmailConcatClass : IBinarySerialize

   {

       public StringBuilder sb = new StringBuilder();

       public void Init()

       {

           //  sb = new StringBuilder();

       }

   }

   
   We assume that the only way to access a class is to initlize it by calling the constructor. In which point all variables inside the class who have a default value are also initlized. However, in this case SQLCLR does not call the constructor (or something of the sort) and the internal variables are not initlized. Which is extremely unusual for a class.
   
   So yes, we CAN use a class. But we would have to manually initlize it's variables like a struct. So why not use a struct from the starting point?

If you've got any more questions feel free to ask them in the comments or by email.