Global Azure Bootcamp in Manchester

Last Saturday a group of community group leaders and helpers in Manchester joined forces with hundreds of other people for the 2018 Global Azure Bootcamp run in association with Microsoft. At the event we had four sessions covering various parts of the Azure ecosystem, some with hands-on labs so people could get a good feel how Azure solutions come together.

So huge thanks to our session leaders who came together and devise an agenda in just a couple of weeks:
Luce Carter, organiser of the Xamarin user group who did our Intro to Azure and using the portal session.
Aden Earnshaw, who did a great job on his user group presenting debut and covered Web Apps.
Martin Boam, who recently was awarded a Microsoft MVP award and runs the Cloud User Group gave a session on Serverless computing.
Jim Bennett – a Cloud Developer Advocate at Microsoft – came all the way from Reading and delivered a great demo about using Azure AI to augment mobile apps, despite the curse of the demo gods striking!
(And I did one on CosmosDB / DocumentDb too)

We also thank Auto Trader for providing the venue for us and providing coffee and tea for the whole day – especially Helen, the venue event co-ordinator who is always helpful.

This was our first go at running the Bootcamp, it was hectic but very rewarding to work with some new people in the run up and to see everyone learning something new on the day – hopefully we will be doing another one next year too! But I won’t be thinking about it too hard for a couple of months.

Using Table Valued Parameters in Dapper

Today I discovered a neat way of passing values into a SQL Server stored procedure with Dapper using a Table Valued Parameter. Up to now, I’d been using a DataTable, adding the columns programatically and then populating it row-by-row. Whilst re-reading Erland Sommarskog’s detailed post about dynamic SQL, I noticed something I hadn’t picked up on before – that there was another way of doing this. Some more research via Leonard Lobel’s site lead me to this solution, which is much neater that the DataTable shenanigans.

Firstly we need to create a table-valued parameter in SQL Server: In your database, open a new query and type:

CREATE TYPE dbo.SimpleKeyList AS TABLE( [KeyId] INT NULL)

Next we create a procedure that will accept the TVP as a parameter:


CREATE PROC dbo.EchoKey
@KeyList dbo.SimpleKeyList READONLY
AS
SELECT KeyId FROM dbo.SimpleKeyList ORDER BY KeyId DESC

This procedure will just return all the keys sent in but in reverse numerical order, so we can see that some processing has occurred.

Now we need to do a bit with Dapper;

This is our basic method:


        public List<int> EchoSomeKeys(KeyList inputData)
        {
            using (var db = new SqlConnection(_connString))
            {
                var data = db.Query<int>("EXEC Core.EchoKey @KeysIn", 
                    new {KeysIn = inputData.AsTableValuedParameter("dbo.SimpleKeyList") }
                );
                return data.ToList();
            }
            
        }

Looks simple doesn’t it? Now all we need is our input class KeyList:


public class KeyDto
{
    public int KeyId { get; set; }
}
public class KeyList : List<KeyDto>, IEnumerable<SqlDataRecord>
{
  IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
  {
    var sdr = new SqlDataRecord(new SqlMetaData("KeyId", SqlDbType.Int));

    foreach (KeyDto item in this)
    {
      sdr.SetInt32(0, item.KeyId);
      yield return sdr;
    }
  }
}

You can see that a little bit of magic is injected via the implementation of

IEnumerable<SqlDataRecord>

Dapper provides the AsTableValuedParameter method which iterates over the enumerator provided by the interface and populates the parameter TVP.

I knocked up a quick NUnit test to check it all works:


[TestFixture]
public class TestTableValuedParam
{
  [Test]
  public void TVP1()
  {
    var a = new Tvps();
    KeyList keysIn = new KeyList();
    keysIn.Add(new KeyDto { KeyId = 68});
    keysIn.Add(new KeyDto { KeyId = 12 });
    keysIn.Add(new KeyDto { KeyId = 42 });

    var m= a.EchoSomeKeys(keysIn);
    Assert.That(m.Count, Is.EqualTo(3));
    foreach (var i in m)
    {
      Console.WriteLine(i);
    }
  }
}

Which verifies that we get the values returned in reverse order.