Trasnactions

 What is transaction?

• Transactions is a sequence of actions (database operations) executed as a whole:

  • Either all of them complete successfully

  • Or none of the them

• Example of transaction:

  • A bank transfer from one account into another (withdrawal + deposit)

  • If either the withdrawal or the deposit fails the whole operation is cancelled

 How it works?

• Transactions guarantee the consistency and the integrity of the database

  • All changes in a transaction are temporary

  • Changes are persisted when COMMIT is executed

  • At any time all changes can be canceled by ROLLBACK

• All of the operations are executed as a whole

  • Either all of them or none of them

 What could go wrong?

• Some actions fail to complete

  • For example, the application software or database server crashes

• Interference from another transaction

  • What will happen if several transfers run for the same account in the same time?

• Some data lost after actions complete

  • Database crashes after withdraw is complete and all other actions are lost

Code First

 Data Models

• Bunch of normal C# classes (POCO)

  • May contain navigation properties

public class PostAnswer
{
    public int PostAnswerId { get; set; } // Primary key
    public string Content { get; set; }

    public int PostId { get; set; } // Foreign key
    public virtual Post Post { get; set; } // Navigation property
}

 Data Models

• Another example of data class (model)

 DbContext

• A class that inherits from DbContext

  • Manages model classes using DbSet type

  • Implements identity tracking, change tracking, and API for CRUD operations

  • Provides LINQ-based data access

• Recommended to be in a separate class library

  • Don't forget to reference the Entity Framework library (using NuGet package manager)

• If you have a lot of models it is recommended to use more than one DbContext

 DbSet

• Collection of single entity type

• Set operations: Add, Attach, Remove, Find

• Use with DbContext to query database 

public DbSet<Post> Posts { get; set; }

public DbSet<PostAnswer> PostAnswers { get; set; }

 DbContext Example

using System.Data.Entity;
using CodeFirst.Models;

public class ForumContext : DbContext
{
    public IDbSet<Post> Posts { get; set; }

    public IDbSet<PostAnswer> PostAnswers { get; set; }

    public IDbSet<Category> Categories { get; set; }

    public IDbSet<Tag> Tags { get; set; }
}

 Data Interaction Example

var db = new ForumContext();

var category = new Category { Parent = null, Name = "Database course" };
db.Categories.Add(category);

var post = new Post();
post.Title = "Срока на домашните";
post.Content = "Моля удължете срока на домашните";
post.Type = PostType.Normal;
post.Category = category;
post.Tags.Add(new Tag { Text = "домашни" });
post.Tags.Add(new Tag { Text = "срок" });

db.Posts.Add(post);
db.SaveChanges();

 How to connect to SQL Server?

• First, create context constructor that calls base constructor with appropriate connection name

public class ForumContext : DbContext
{
    public ForumContext()
        : base("ForumDb")
    { }
    // ...
}

• Then add the connection string in app.config

<connectionStrings>
  <add name="ForumDb" connectionString="Data Source=.;Initial 
    Catalog=ForumDb;Integrated Security=True"
    providerName="System.Data.SqlClient" ></add>
</connectionStrings>

Code First Migrations

 Migrations

 Migrations

 Migration Strategies

 How to use?

Database.SetInitializer(
  new MigrateDatabaseToLatestVersion<ForumContext, Configuration>());

public Configuration()
{
    this.AutomaticMigrationsEnabled = true;
    this.AutomaticMigrationDataLossAllowed = true;
}

 Seed database

protected override void Seed(ForumContext context)
{
    /* This method will be called after migrating to
       the latest version. You can use the
       DbSet.AddOrUpdate() helper extension method 
       to avoid creating duplicate seed data. E.g. */

    context.Tags.AddOrUpdate(new Tag { Text = "срок" });
    context.Tags.AddOrUpdate(new Tag { Text = "форум" });
}

Configure Mappings

 Migrations

 Mappings

 Fluent API

protected override void OnModelCreating(
  DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>().HasKey(x => x.Id);
    modelBuilder.Entity<student>().Property(x => x.Name)
      .IsUnicode(true);
    modelBuilder.Entity<Student>().Property(x => x.Text)
      .HasMaxLength(255);

    base.OnModelCreating(modelBuilder);
}

 Fluent API

 Fluent API