SQL in the City - My Session

I'm very excited to be speaking at SQL in the City this year. I'm going to try to run through as many SQL Server software development tips and tricks as possible. I've been thinking about the session and I think instead of doing 20 tricks, I'm going to try to fit in 50 tricks. That's a new trick every 60 - 90 seconds. Let's see if this works! I figure with our short attention spans, this should keep everyone engaged, learning, and laughing. Click on the banner on the right of the blog to sign up for SQL in the City. I can't wait!

Azure DocumentDB First Look

Step 1: Run the PowerShell Scripts

Step 2: Use the new Azure portal at portal.azure.com

Step 3: Run NuGet: Install-Package Microsoft.Azure.Documents.Client -Pre

Step 4: This is the Flashcard class

public class FlashCard
    {
        [JsonProperty(PropertyName = "id")]
        public string ID { get; set; }
        [JsonProperty(PropertyName = "question")]
        public string Question { get; set; }
        [JsonProperty(PropertyName = "answer")]
        public string Answer { get; set; }

    }

Step 5: This is the console application:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;
using System.Configuration;
namespace FlashCardDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            CreateSaveAndGet().Wait();
            //CleanUp().Wait();
        }

        public static async Task CreateSaveAndGet()
        {
            //connect database
            var client = GetClient();

            //find or create a database
            var database = await GetDatabase(client);

            //create a collection
            var collection = await GetCollection(client, database);

            Console.WriteLine("Save new flashcards...");
            var flashcard1 = await client.CreateDocumentAsync(collection.SelfLink, new FlashCard()
            {
                ID = "1",
                Question = "When did Azure DocumentDB release in preview?",
                Answer = "August 21st, 2014",
            });

            var flashcard2 = await client.CreateDocumentAsync(collection.SelfLink, new FlashCard()
            {
                ID = "2",
                Question = "What is Azure DocumentDBs twitter handle?",
                Answer = "@DocumentDB",
            });

           
            var flashcards = await Task<List<FlashCard>>.Run(() =>
            client.CreateDocumentQuery<FlashCard>(collection.DocumentsLink)
                .AsEnumerable()
                .ToList<FlashCard>());

            Console.WriteLine("Iterating through flashcards...");
            foreach (var flashcard in flashcards)
            {
                Console.WriteLine("QUESTION " + flashcard.ID.ToString() + ": " + flashcard.Question );
                Console.WriteLine("Answer: " + flashcard.Answer);
                Console.WriteLine("Press a key");
                Console.ReadKey();
            }
            Console.ReadKey();


            var flashcardGet = await Task<FlashCard>.Run(() =>
                client.CreateDocumentQuery<FlashCard>(collection.DocumentsLink)
                    .Where(d => d.ID == "1")
                    .AsEnumerable()
                .FirstOrDefault());

            Console.WriteLine(flashcardGet.Question);
            Console.ReadKey();

            var doc = client.CreateDocumentQuery<Document>(collection.DocumentsLink)
                    .Where(d => d.Id == flashcardGet.ID)
                    .AsEnumerable().FirstOrDefault();

            await client.DeleteDocumentAsync(doc.SelfLink);

            flashcardGet = await Task<FlashCard>.Run(() =>
                client.CreateDocumentQuery<FlashCard>(collection.DocumentsLink)
                    .Where(d => d.ID == "1")
                    .AsEnumerable()
                .FirstOrDefault());

            Console.WriteLine(flashcardGet.Question);
            Console.ReadKey();
        }

        private static async Task CleanUp()
        {
            //connect database
            var client = GetClient();

            //find or create a database
            var database = await GetDatabase(client);

            //create a collection
            var collection = await GetCollection(client, database);
            await client.DeleteDocumentCollectionAsync(collection.SelfLink);

        }
        private static DocumentClient GetClient()
        {
            string endpoint = ConfigurationManager.AppSettings["EndPoint"];
            string authKey = ConfigurationManager.AppSettings["AuthKey"];

            Uri endpointUri = new Uri(endpoint);
            var client = new DocumentClient(endpointUri, authKey);
            return client;
        }
        private static async Task<Database> GetDatabase(DocumentClient client)
        {
            Database database;
            var databaseName = "flashcards";
            var databases = client.CreateDatabaseQuery()
                .Where(db => db.Id == databaseName).ToArray();

            if (databases.Any())
            {
                database = databases.First();
            }
            else
            {
                database = new Database { Id = databaseName };
                database = await client.CreateDatabaseAsync(database);
            }
            return database;
        }
        private static async Task<DocumentCollection> GetCollection(DocumentClient client, Database database)
        {
            var collectionName = "flashcards";
            DocumentCollection collection;

            var collections = client.CreateDocumentCollectionQuery(database.SelfLink)
                .Where(col => col.Id == collectionName).ToArray();

            if (collections.Any())
            {
                collection = collections.First();
            }
            else
            {
                collection = await client.CreateDocumentCollectionAsync(database.SelfLink,
                    new DocumentCollection {Id = collectionName});
            }
            return collection;
        }

     

     
    }
}

Video of Learn JavaScript Properly Part 1 - SQL Pass Book Readers

SQL Tip: The Importance of Changing Schema

SQL Tip: How to share passwords over email

SQL Tip: Change coloring on status bar of SSMS

New SQL Tip - SET XACT_ABORT ON

I have a new SQL Tip about SET XACT_ABORT.  If you're unfamiliar with this setting, you should definitely spend three minutes and watch this tip:



CREATE TABLE t1(
col1 INT PRIMARY KEY)

GO

INSERT INTO t1
VALUES
(1)
,(2)
,(3)
,(4)-- Assuming SET IMPLICIT_TRANSACTIONS is OFF

INSERT INTO t1
VALUES(1)

INSERT INTO t1
VALUES(99)
-- Which rows get inserted?
SELECT * FROM t1-- Which rows get inserted for this explicit transaction?
BEGIN TRANSACTION;
   INSERT INTO t1
   VALUES
   (1)

   INSERT INTO t1
   VALUES
   (100)
COMMIT TRANSACTION;
GO-- Which rows get inserted?  Did 100 get inserted?
SELECT * FROM t1-- With SET XACT_ABORT?
SET XACT_ABORT ON;
BEGIN TRANSACTION;
   INSERT INTO t1
   VALUES
   (1)

   INSERT INTO t1
   VALUES
   (200)
COMMIT TRANSACTION;
GO
DROP TABLE t1