zondag 10 maart 2013

Connecting to Windows Azure SQL Database using Entity Framework

Het ontsluiten van een Windows Azure SQL Database

Wanneer jouw database in Windows Azure SQL Database staat, wil je er natuurlijk ook mee werken. .NET Framework biedt hiervoor ADO.NET aan, dat zit er al vanaf versie 1.0 in. Je zou dus zoiets kunnen doen als:

SqlConnection connection = new SqlConnection("de connectiestring");

connection.Open();
SqlCommand command =
               new SqlCommand("SELECT * FROM table", connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    object a = reader[0];
    object b = reader[1];
    // etc.
}
connection.Dispose();

Die connectiestring zou er voor Windows Azure SQL Database als volgt uit kunnen zien:


Data Source=servernaam.database.windows.net;Initial Catalog=

MijnDatabase;user id=gebruiker;password=wachtwoord;

Dat gaat dus helemaal hetzelfde als met een on-premise SQL Server database.


Ofschoon deze manier van dataontsluiting nog altijd bruikbaar is, is het niet meer van deze tijd. Nadeel van deze benadering is dat gepeuter met SQL. Met eenvoudige SQL-queries valt het allemaal nog wel mee, maar als ze ingewikkeld worden kan het nog wel eens mis gaan. Daarbij komt dat de moderne ontwikkelaar object georiënteerd wil werken. Dit betekent dus dat het resultaat van de SQL-query in objecten gestopt moet worden, en daarvoor moeten weer classen gedefinieerd worden. En als het een beetje tegen zit moet je ook nog insert-, update- en deletequeries gaan bouwen. Dat is voor kleine databases even leuk, maar bij die grote draken gaat de lol er snel vanaf.


Vanaf .NET Framework 3.5 en Visual Studio 2008 (beide met Service Pack 1) zag de eerste versie van Entity Framework het licht. Dit was een tenenkrommende versie die, met name door de Hybernate gemeenschap, schuddebuikend werd ontvangen. De volgende versie was dan ook meteen 4 (gelijk met .NET Framework 4), spoedig gevolgd door versie 5. Op dit moment zit versie 6 eraan te komen waarvan een alfa-release verkrijgbaar is.


Om te werken met Entity Framework, voeg je aan jouw project een "ADO.NET Entity Data Model" toe. Meteen start het connectievenster op waarin je de Windows Azure SQL Database server opgeeft, de credentials en de database die je wilt gebruiken (afbeelding 1). Daarna start de wizard van Entity Framework (afbeelding 2).



Afbeelding 1. Connection Wizard
Afbeelding 2. De wizard van Entity Framework

Entity Framework wil weten hoe je jouw model wilt maken. Dat kan op de volgende manieren:

  1. Op basis van een bestaande database. Er worden dan classen (entities) voor jou gegenereerd
  2. Je begint met het definieren van entities waarna de database wordt gegenereerd.
Omdat we al een database hebben kiezen we voor optie 1. Daarna volgen nog wat vragen over hoe de connectiestring moet worden opgeslagen en tenslotte welke objecten je uit de database wilt hebben. In mijn geval neem ik alle tabellen mee. Onder water worden nu duizenden regels code gegenereerd die ik anders handmatig had moeten schrijven. Bovendien wordt er een visualisatie gemaakt van het model (afbeelding 3).

Afbeelding 3. Een wirwar van entities.

Voor de komst van Entity Framework 5 moesten we het met dit diagram doen. We konden inzoomen, we konden uitzoomen en vooral scrollen, veel scrollen.

Gelukkig kunnen we met Entity Framework 5 en Visual Studio 2012 al die entities over meerdere diagrammen uitsmeren. Bovendien kunnen we de entities ook nog andere kleurtjes geven hetgeen leidt tot het resultaat in afbeelding 4.


Afbeelding 4. Diagram met een fragment van de entities. De kleuren geven in aan bij welk schema ze horen.

De data opvragen verloopt via een speciaal object, de context. Een query zou er nu als volgt uit kunnen zien:


using (IntermedEntities context = new IntermedEntities())

{
    var query = context.Products
        .Include(p=>p.Brand)
        .OrderBy(rec => rec.ID)
        .Skip(200)
        .Take(10);

    foreach (Product p in query)

    {
        Console.WriteLine("{0} {1}", p.Brand.Name, p.Name);
    }
}

IntermedEntities is zo'n gegenereerde class. Hij representeert de context en is verantwoordelijk voor de verbinding met de database, houdt bij wat er gewijzigd is en levert een aantal voorgedefinieerde queries aan (context.Products).

Een nieuw record toevoegen gaat als volgt:


Product p = new Product
{
    Name = "Halfvolle melk",
    Brand = new Brand { Name = "Zuivelbedrijf" }
};
using (IntermedEntities context = new IntermedEntities())
{
    context.Products.Add(p);
    context.SaveChanges();
}

De SaveChanges() zorgt ervoor dat de wijzigingen naar de database worden weggeschreven.

Code-First

Tegenwoordig is de Code-First benadering helemaal hot. Het komt erop neer dat je code begint te kloppen en als je het programma een keer draait, wordt er op magische wijze een database aangemaakt. Dat trucje wil ik wel eens uitproberen, en natuurlijk wil ik dat die database in de cloud wordt gegenereerd.
Om te beginnen smijten we er eerst maar eens een lap code tegenaan.


class Program
{
    static void Main(string[] args)
    {
        Person p = new Person
        {
             FirstName = "Patrick",
             LastName = "Schmidt" 
        };
        Hobby h = new Hobby        {
             Description = "Computers"
      
 };
        p.Hobbies.Add(h);

        using (HobbyModel context = new HobbyModel())
        {
            context.People.Add(p);
            context.SaveChanges();
            foreach(Person person in context.People)
            {
                Console.WriteLine(person.LastName);
                foreach (Hobby hobby in person.Hobbies)
                {
                    Console.WriteLine(hobby.Description);
                }
            }
        }
        Console.ReadLine();
    }
}

public class HobbyModel : DbContext
{
    public DbSet<Person> People { getset; }
    public DbSet<Hobby> Hobbies { getset; }

    public HobbyModel()
        : base("DataContext")
    { }
}

public class Person
{
    public int ID { getset; }
    public string FirstName { getset; }
    public string LastName { getset; }
    public virtual ICollection<Hobby> Hobbies { getset; }
    public Person()
    {
        Hobbies = new HashSet<Hobby>();
    }
}

public class Hobby
{
    public int ID { getset; }
    public string Description { getset; }
    public virtual ICollection<Person> People { getset; }
    public Hobby()
    {
        People = new HashSet<Person>();
    }
}

Hier zijn drie classen gedefinieerd; Person, Hobby en HobbyModel. De eerste twee zijn entities en laatste is verantwoordelijk voor de verbinding met de database en houdt de wijzigingen van de entities in de gaten. In de "Main" functie worden eerst instanties van Person en Hobby gemaakt die vervolgens met elkaar in verband worden gebracht. Daarna wordt de context (HobbyModel) geïnitialiseerd. Via context.Persons.Add() worden de nieuwe objecten in de context geregistreerd en wanneer de SaveChanges() wordt aangeroepen, moet de hele zaak in de database verdwijnen (die nog altijd niet bestaat).
Wanneer we dit programma starten, zal na de aanroep van SaveChanges() de database worden aangemaakt. De vraag is alleen waar?
Standaard zal dat een lokale SqlExpress versie van SQL Server zijn. Wanneer die niet te vinden is, zal LocalDb (VS2012) gebruikt worden. Wij hebben in de constructor van HobbyModel echter "DataContext" gespecificeerd. Dat is een verwijzing naar een connectionstring in de configuratiefile en daar staat:

<connectionStrings>
   <add name="DataContext"
             connectionString="Data Source=servernaam.database.windows.net;
                 Initial Catalog=MyDataBase;
                 user id=username;
                 password=password;"
             providerName="System.Data.SqlClient" />
</connectionStrings>

Daar wordt nu verwezen (Data Source) naar een Windows Azure SQL Database server, zodat de database nu in de cloud wordt aangemaakt (afbeelding 5). 



Afbeelding 5. In de cloud.
We hebben nu gezien hoe we Entity Framework kunnen gebruiken met Windows Azure SQL Database. Daarnaast is bekeken of Code-first ook werkt op Windows Azure SQL Database. Het maakt nauwelijks verschil met een lokale database.

Entity Framework 6

Binnenkort komt Entity Framework 6 uit. Op dit moment is een alpha-release te bewonderen. Hier volgt een korte beschrijving van een paar functionaliteiten.


1) Async Query and Save. Queries en save acties kunnen asynchroon worden uitgevoerd. Let wel op dat dergelijke operaties niet thread safe zijn. Hier een asynchrone uitwerking van het eerdere voorbeeld waarbij gebruik wordt gemaakt van SaveChangesAsync() en de extensie ForEachAsync()



class Program
{
    static void Main(string[] args)
    {
        AsyncTask();
        Console.ReadLine();
    }
    private static async void AsyncTask()
   {         
        Person
 p = new Person{FirstName = "Patrick", LastName = "Schmidt" };
        Hobby h = new Hobby { Description = "Computers" };
        p.Hobbies.Add(h);
        await
 CreateAsync(p);
        await
 ShowAsync();
    }
    private static async Task ShowAsync()
    {
        using
 (HobbyModel context = new HobbyModel())
        {             
            await
 context.People.ForEachAsync(person =>
                      {
                          Console
.WriteLine(person.LastName);
                          foreach
 (Hobby hobby in person.Hobbies)
                          {
                              Console
.WriteLine(hobby.Description);
                          }
                      });
        }
    }
    public static async Task CreateAsync(Person p)
    {
        using (HobbyModel context = new HobbyModel())
        {
            context.People.Add(p);
            await context.SaveChangesAsync();
        }
    }
}

2) Custom Code First Conventions. Hiermee hebben we controle over hoe de database wordt gegenereerd bij gebruik van Code-first. Bijvoorbeeld, in het eerdere voorbeeld werden alle stringproperties vertaald naar nvachar(max). Met CCFC kun je dat gedrag aanpassen. Hieronder zie je hoe dat gaat. De class StringTypeConvention wordt in de OnModelCreating geïnjecteerd.

public class StringTypeConvention : IConfigurationConvention<PropertyInfo
                                                              StringPropertyConfiguration>
{     
    public
 void Apply(PropertyInfo memberInfo, 
                            Func
<StringPropertyConfiguration> configuration)
    {
        configuration().MaxLength = 1024;
    }
}
public class HobbyModel : DbContext
{     
    public
 DbSet<Person> People { getset; }
    public DbSet<Hobby> Hobbies { getset; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new StringTypeConvention());
    }
    public HobbyModel() : base("DataContext")
    {
    }
}

3) Code First Mapping to Insert/Update/Delete Stored Procedures. Het is nu ook mogelijk om de mapping van inserts, updates en deletes via stored procedures te laten verlopen. Helaas worden de stored procedures zelf nog niet gegenereerd. Het volgende stukje code laat zien hoe dat gaat. Let op dat de namen en argumenten onderhevig zijn aan naamconventies. Natuurlijk heb je daar ook weer invloed op.

protected override void OnModelCreating(DbModelBuilder< modelBuilder)
{
    modelBuilder.Entity<Hobby>().MapToStoredProcedures();
    modelBuilder.Entity<Person>().MapToStoredProcedures();
}

4) Connection Resiliency. Met de groeiende populariteit van Windows Azure SQL Database, waarbij het netwerk minder betrouwbaar is, is ook de vraag naar een goede afhandelingen bij netwerkproblemen toegenomen. Je kunt hierbij denken aan retry-acties. In EF 6 is het mogelijk om logica te injecteren die op dergelijke verbindingsproblemen anticipeert. Aan de grondslag hiervan liggen een drietal interfaces; IExecutionStrategy, IRetriableExceptionDetector en IRetryDelayStrategy die je moet implementeren.
Voor Windows Azure SQL Database is al een kant-en-klare strategie bijgevoegd (SqlAzureExecutionStrategy in System.Data.Entity.SqlServer). In zijn meest eenvoudige vorm voeg je een class, die erft van DbConfiguration, toe aan dezelfde assembly als waarin jouw DbContext gedefinieerd is. Via Dependency Resolution (ook nieuw in EF 6) wordt MyDbConfiguration ingeladen (SqlAzureDbConfiguration erft van DbConfiguration).

public class MyDbConfiguration : SqlAzureDbConfiguration
{
    public MyDbConfiguration()
    {
        AddDependencyResolver(
            new SqlExecutionStrategyResolver(
                   () => new SqlAzureExecutionStrategy(), null));
    }
}
public class HobbyModel : DbContext
{
    public DbSet<Person> People { getset; }
    public DbSet<Hobby> Hobbies { getset; }
    public HobbyModel() : base("DataContext")
    { }
}

Natuurlijk kun je ook jouw eigen strategie implementeren.
Voor meer nieuws, zie de wiki pagina voor Entity Framework 6

1 opmerking:

  1. Ik werk nu al een tijdje met EF 4.5 Code First. Werkt als een trein! Alleen voor een wat meer geavanceerdere op mijn site heb ik wat met sql gedaan, de rest zit allemaal in mijn codebase.

    Leuke blog, ik blijf hem volgen!

    BeantwoordenVerwijderen