Tuesday, April 19, 2011

Table Valued Parameter procedures with SQL Azure

Yes, it’s supported and it’s fairly easy to use a Table Value Parameter in stored procedures with SQL Azure. And here I will show you a quick introduction on how to do this.
In order to use a table value parameter in stored procedure we first need to create a custom user defined table type (UDT). Here is my very simple table UDT:
CREATE TYPE ReferenceIds AS TABLE
(
 Id INT
)

Now let’s create a stored procedure that accepts that type:

CREATE PROCEDURE [dbo].[upGetRefIds]
(
 @references ReferenceIds readonly
)
AS
BEGIN
 SELECT Count(Id) From @references
END

It is important to note that when using UDT as parameter, it can only be input parameter, and it must be explicitly set as read only.
Finally let’s write some ADO.NET:

            using (SqlConnection con =                          new SqlConnection(                             ConfigurationManager.                             ConnectionStrings["AzureSQL"].ConnectionString))             {
                 using (SqlCommand cmd = con.CreateCommand())                 {
                     cmd.CommandText = "upGetRefIds";
                     cmd.CommandType = CommandType.StoredProcedure;
                     DataTable dt = new DataTable();
                     dt.Columns.Add("Id", typeof(int));
                     dt.Rows.Add(2);
                     dt.Rows.Add(12);
                     dt.Rows.Add(2342);






                    con.Open();



                    cmd.Parameters.AddWithValue("@references", dt);
                     var result = cmd.ExecuteScalar();
                     System.Diagnostics.Debug.WriteLine(result.ToString());
                 }
             }

Maybe you already noted – the @references parameter is passed as regular DataTable, which has 1 column defined of type integer (same as our user defined type). This is the only “special” trick to make the magic work!

That’s it. As simple as that!

Saturday, April 2, 2011

Microsoft MVP for Windows Azure

Yes! It is not a April’s fool joke! It’s a fact! It is a great honor for me to be awarded with the Microsoft MVP Award for Windows Azure!

And of course, when there are awards and winners and prices, there are also “thanks”. My great thanks go for Martin Kulov (Microsoft Regional Director & Microsoft MVP for ALM) who is a great guy and incredible Microsoft influencer Smile! I am proud to know him! Of course also a huge gratitude to my family for supporting me in all mine initiatives!

So what’s next? Even more Windows Azure User Group meetings and even more community activities. Stay tuned for updates!

Slides and code from Microsoft Days’ 2011 Bulgaria / SQL Azure Agent

And here there are. PowerPoint presentation can be downloaded from: SqlAzureAgent_MSDays2011_20110330.pptx  And the code is located at: http://sqlazureagent.codeplex.com/. Go for it! Download, build, run, change, play! If you have questions: just ask!