(DEPRECATED documentation)
VITA Features Demo - Code Listing
The following code is part of download zip, demo project. It demonstrates the key features and capabilities of VITA framework:
- Defining entities as .NET interfaces
- Specifying database table keys and indexes using entity attributes or attributes on "companion" types
- Creating database schema objects from entity model defined in c# code
- Creating a data module with entities and custom stored procedures
- Using the module and its entities in application code to create, load, update and delete entity records
- Defining computed properties/columns
- Performing built-in and custom validation on entities before submitting changes to database
- Using direct LINQ queries with entity model
- Using custom stored procedures defined in the model
The data model defines entities for a simplistic Books database, containing books, publishers and authors. Each book has a publisher (one-to-many relation from publisher to books), and zero or more authors (many-to-many relation).
Books entities:
public enum BookType {
Paperback,
Hardcover,
EBook,
}
[Entity, Validate(typeof(BooksModule), "ValidateBook")]
public interface IBook {
[PrimaryKey, Auto]
Guid Id { get; set; }
IPublisher Publisher { get; set; }
string Title { get; set; } //uses default length
[Size(250), Nullable]
string Description { get; set; }
int Quantity { get; set; }
BookType Type { get; set; }
double Price { get; set; }
[ManyToMany(typeof(IBookAuthor))]
IList<IAuthor> Authors { get; }
}
[Entity]
public interface IPublisher {
[PrimaryKey, Auto]
Guid Id { get; set; }
string Name { get; set; }
IList<IBook> Books { get; }
}
[Entity]
public interface IAuthor {
[PrimaryKey, Auto]
Guid Id { get; set; }
[Size(30)]
string FirstName { get; set; }
[Size(30)]
string LastName { get; set; }
[Computed(typeof(BooksModule), "GetFullName")]
string FullName { get; }
[ManyToMany(typeof(IBookAuthor))]
IList<IBook> Books { get; }
}
[Entity]
public interface IBookAuthor {
IBook Book { get; set; }
IAuthor Author { get; set; }
}
Books Keys and Indexes
// This file contains companion types for Books entities.
// Companion types allow you to set some attributes (like DB keys and indexes) on companion types rather than
// on entities themselves. You can put all database artifacts definitions in a separate c# file with companion types
// and put the database admin in charge of this file, so he can fine-tune this stuff without clashing with other
// developers which work with entities in the middle tier.
// You register companion types using the RegisterCompanionTypes method of EntityModelSetup class.
// There are 2 ways to link companion type to entity type
// 1. Inheritance - companion type inherits from entity type; that's what we do here
// 2. Using ForEntity attribute on companion type
[ClusteredIndex("Publisher,Id")]
[Index("Publisher,Title,Type")]
public interface IBookKeys : IBook {
[Index]
new string Title { get; set; }
}
public interface IPublisherKeys : IPublisher {
[Unique]
new string Name { get; set; }
}
public interface IAuthorKeys : IAuthor {
[Index]
new string LastName { get; set; }
}
[PrimaryKey("Book,Author")]
public interface IBookAuthorKeys : IBookAuthor { }
Books module and extensions class
//We could register Book entities directly at program startup. But for better code modularity, we create
// a data module that handles all book-related functionality - including entity registration, stored procedures, etc.
// We also define a small static extension class to add handy entity-creation methods.
public class BooksModule {
string _schema;
DbCommandInfo _getBooksByAuthorCommand, _changePriceCommand, _deleteBooksZeroQuantity;
public BooksModule(EntityModelSetup modelSetup, string schema) {
_schema = schema;
// We need to register only one root type, the rest will be discovered automatically
modelSetup.RegisterEntities(schema, typeof(IBook));
//Register companion types that describe keys and indexes on entities
modelSetup.RegisterCompanionTypes(
typeof(IBookKeys), typeof(IAuthorKeys), typeof(IPublisherKeys), typeof(IBookAuthorKeys));
modelSetup.CompilingCommands += Model_CompilingCommands;
}
//Define and compile stored procedures for custom Select, Update, Delete operations using LINQ syntax.
// This method will be called during model activation: by firing the CompilingCommands event
// the model suggests to custom modules to create stored procedures they will use.
private void Model_CompilingCommands(object sender, CompileCommandsEventArgs e) {
var compiler = e.Database.CommandCompiler;
var books = compiler.EntitySet<IBook>();
var pubs = compiler.EntitySet<IPublisher>();
var authors = compiler.EntitySet<IAuthor>();
var bookAuthors = compiler.EntitySet<IBookAuthor>();
//Let's use 3-table join and find all books of an author identified by name
var queryGetByAuthor = from bk in books
join ba in bookAuthors on bk equals ba.Book
join au in authors on ba.Author equals au
where au.LastName == "?"
select bk;
_getBooksByAuthorCommand = compiler.CompileCommand<IBook>(CommandKind.Select, _schema, "BooksGetByAuthor", queryGetByAuthor, 1);
//Update query must return auto-type containing two kinds of fields: primary key to identify the record,
// and fields to be updated in the record identified by PK
var factor = 1.1d;
var bkType = BookType.Hardcover; //does not matter, will turn into parameter
var changePriceQuery = from p in books
where p.Type == bkType
select new { Id = p.Id, Price = p.Price * factor };
_changePriceCommand = compiler.CompileCommand<IBook>(CommandKind.Update, _schema, "BooksChangePrice", changePriceQuery, 2);
//Delete query must return ID's of books to be deleted
var delQuery = from b in books
where b.Quantity == 0
select new { b.Id };
_deleteBooksZeroQuantity = compiler.CompileCommand<IBook>(CommandKind.Delete, _schema, "BooksDeleteZeroQuantity", delQuery, 1);
}
//Methods executing stored procedures
public IList<IBook> GetBooksByAuthor(IDataSession session, string lastName) {
return session.QueryEngine.ExecuteSelect<IBook>(_getBooksByAuthorCommand, lastName).ToList();
}
public void ChangePrice(IDataSession session, BookType bookType, double changePerc) {
var factor = (100.0 + changePerc) / 100.0d;
session.QueryEngine.ExecuteNonQuery(_changePriceCommand, bookType, factor);
}
public void DeleteBooksWithZeroQuantity(IDataSession session) {
session.QueryEngine.ExecuteNonQuery(_deleteBooksZeroQuantity, 0);
}
// Static method computing FullName computed property for an Author
public static string GetFullName(IAuthor author) {
return author.FirstName + " " + author.LastName;
}
// Static method validating Book entity
public static void ValidateBook(DataValidator validator, IBook book) {
validator.Check(book.Price > 0.01, book, "price", "PriceNegative", "Book price must be greater than 1 cent.");
}
public void DeleteAll(Database database) {
database.DeleteAll<IBookAuthor>();
database.DeleteAll<IAuthor>();
database.DeleteAll<IBook>();
database.DeleteAll<IPublisher>();
}
}//BooksModule
//Helper methods to create entities
public static class BookExtensions {
public static IBook NewBook(this IDataSession session, BookType type, string title, string description,
IPublisher publisher, double price, int quantity) {
var book = session.NewEntity<IBook>() ;
book.Type = type;
book.Title = title;
book.Description = description;
book.Publisher = publisher;
book.Price = price;
book.Quantity = quantity;
return book;
}
public static IPublisher NewPublisher(this IDataSession session, string name) {
var pub = session.NewEntity<IPublisher>();
pub.Name = name;
return pub;
}
public static IAuthor NewAuthor(this IDataSession session, string firstName, string lastName) {
var auth = session.NewEntity<IAuthor>();
auth.FirstName = firstName;
auth.LastName = lastName;
return auth;
}
}//class
Code using the Books module:
Write(" ");
Write(" ");
//Setup model, initialize Books module, create database model, update schema -------------------------------------------------
Write("Constructing Entity model...");
var modelSetup = new EntityModelSetup("Books");
// The following settings are in fact defaults;
// change it to run without stored procs (direct SQL only) or without ref integrity in database;
modelSetup.Settings.Options = ModelOptions.UseStoredProcs | ModelOptions.UseRefIntegrity;
var booksModule = new BooksModule(modelSetup, "books");
var model = modelSetup.CreateModel(); //created Entity Model
var db = model.Connect(connectionString); //created Database object
Write("Updating database schema...");
db.UpdateSchema();
model.CheckActivationLog();
//Delete old records so they do not impact record counts in assertions below
Write("Deleting all old data...");
booksModule.DeleteAll(db);
Write(" ");
//Create some entities in database: publishers, authors, books -----------------------------------------------------
var session = db.OpenSession();
Write("Creating test data in database...");
var msPub = session.NewPublisher("MS Books"); //we are using extension method here
var kidPub = session.NewPublisher("Kids Books");
var john = session.NewAuthor("John", "Sharp");
var jack = session.NewAuthor("Jack", "Pound");
//Books on programming from MS Books
var csBook = session.NewBook(BookType.Paperback, "c# Programming", "Expert programming in c#", msPub, 20.0, 10);
csBook.Authors.Add(john); //this is many-to-many
csBook.Authors.Add(jack);
var vbBook = session.NewBook(BookType.Paperback, "VB Programming", "Expert programming in VB", msPub, 25.0, 10);
vbBook.Authors.Add(jack);
//Folk tale, no authors
var kidBook = session.NewBook(BookType.Hardcover, "Three little pigs", "Folk tale", kidPub, 10.0, 15);
//Let's remember some ID's, we'll use them later
var msPubId = msPub.Id;
var csBookId = csBook.Id;
var vbBookId = vbBook.Id;
var kidBookId = kidBook.Id;
session.SaveChanges(); //Save all
Write(" Done. Created 2 authors, 2 publishers and 3 books.");
Write(" ");
//Load all books and print titles
Write("Reading back all books:");
session = db.OpenSession();
var allBooks = session.GetAll<IBook>().ToList();
AssertTrue(3 == allBooks.Count, "Invalid # of books");
foreach (var bk in allBooks)
Write(" Book: " + bk.Title + " from " + bk.Publisher.Name);
Write(" ");
//Loading entities by primary key --------------------------------------------------------
// Verify that parent objects referenced by the same FK value result in the same object
Write("Loading books by Book Id:");
session = db.OpenSession();
// load book by ID, check the title
csBook = session.GetEntity<IBook>(csBookId);
AssertTrue(csBook != null, "Failed to find the book by Id.");
AssertTrue(csBook.Title == "c# Programming", "Loading book by Id: wrong book loaded, title mismatch.");
vbBook = session.GetEntity<IBook>(vbBookId);
AssertTrue(vbBook != null, "Failed to find the book by Id.");
Write(" Done: Loaded and verified 2 programming books.");
//Compare publishers as objects
object csPubObj = csBook.Publisher;
object vbPubObj = vbBook.Publisher;
AssertTrue(csPubObj != null, "Publisher is null!");
AssertTrue(csPubObj == vbPubObj, "c# and vb books publishers is not the same Publisher instance.");
Write(" Verified: both loaded books reference the same Publisher instance.");
Write(" ");
// Validation ------------------------------------------------------------------------------------
Write("Entity validation: trying to save entities with errors.");
session = db.OpenSession();
var invalidAuthor = session.NewAuthor(null, "VeryLoooooooooooooooooooooooooooongLastName");
var invalidBook = session.NewBook(BookType.EBook, "Not valid book", "Some invalid book", null, -5.0, 10);
//We expect 4 errors: Author's first name should not be null; Author's last name is too long; Publisher cannot be null;
// Price must be > 1 cent. The first 3 errors are found by built-in validation; the last error, price check, is added
// by custom validation method.
try {
session.SaveChanges();
} catch (ValidationException vex) {
AssertTrue(vex.Errors.Count == 4, "Found validation errors.");
foreach (var err in vex.Errors)
Write(" Error: " + err.ToString());
}
Write(" ");
// Entity lists, one-to-many -----------------------------------------------------------------------
// For a publisher, get the books (Publisher.Books)
Write("Entity Lists, one-to-many. ");
Write("Loading a publisher and enumerating its Books property: ");
session = db.OpenSession();
msPub = session.GetEntity<IPublisher>(msPubId);
AssertTrue(2 == msPub.Books.Count, "Invalid # of books from MS Books");
foreach (var bk in msPub.Books)
Write(" Book: " + bk.Title);
Write(" ");
//Entity lists, many-to-many -----------------------------------------------------------------------
Write("Entity Lists, many-to-many... ");
Write("Loading a book (about c#) and enumerating its Authors property: ");
session = db.OpenSession();
csBook = session.GetEntity<IBook>(csBookId);
AssertTrue(2 == csBook.Authors.Count, "Invalid authors count for c# book");
foreach (var a in csBook.Authors)
Console.WriteLine(" Author: " + a.FullName);
Write(" ");
//Direct LINQ query ----------------------------------------------------------------------------------
Write("Direct LINQ query... ");
Write(" Finding books by publisher's name 'MS Books':");
session = db.OpenSession();
var books = session.Linq.EntitySet<IBook>();
//let's find books by publisher's name
var msbooks = from b in books
where b.Publisher.Name == "MS Books"
orderby b.Title
select b;
var msBookList = msbooks.ToList();
foreach (var b in msBookList)
Console.WriteLine(" Book: " + b.Title);
//Records retrieved by LINQ are attached to session, they are updatable.
// Change the price of c# book (from 20 to 10) and save it; read it again and check that the price has changed
Write("Updating a book retrieved by LINQ query: ");
Write(" Changing price of c# book from 20 to 10.");
csBook = msBookList[0]; //we know it's the first book, we sort by title
csBook.Price = 10.00;
session.SaveChanges();
session = db.OpenSession(); //start another session to make sure we load fresh version
csBook = session.GetEntity<IBook>(csBookId);
AssertTrue(Math.Abs(csBook.Price - 10) < 0.01, "Book price did not change to 10.");
Write(" Done, verified. ");
Write(" ");
// Using SELECT custom stored proc ------------------------------------------------------------------
Write("Using SELECT custom stored procedure BooksGetByAuthor.");
// Get books by author name
session = db.OpenSession();
var booksByJack = booksModule.GetBooksByAuthor(session, "Pound");
AssertTrue(2 == booksByJack.Count, "Found wrong # of books by Jack Pound.");
Write(" Done, found 2 books by Jack.");
Write(" ");
// Using UPDATE custom stored proc ------------------------------------------------------------------
Write("Using UPDATE custom stored procedure BooksChangePrice.");
Write(" Reducing price by 20% on all hardcover books.");
//Use stored proc to reduce the price by 20% on all hardcovers
// We have Kid book in Hardcovers, original price 10
booksModule.ChangePrice(session, BookType.Hardcover, -20);
//check that changes actually took place
session = db.OpenSession();
kidBook = session.GetEntity<IBook>(kidBookId);
//Original price 10, now it should be 8
AssertTrue(Math.Abs(8 - kidBook.Price) < 0.01, "Kids book price is not reduced after discount.");
Write(" Done, verified - hardcover book price changed from 10 to 8.");
Write(" ");
// Using DELETE custom stored proc -------------------------------------------------------------------
Write("Using DELETE custom stored procedure BooksDeleteZeroQuantity.");
Write(" Preparation: load a book, change its quantity to 0, update it.");
// Set kids book quantity to zero, then delete all books with zero quantity using stored procedure
session = db.OpenSession();
kidBook = session.GetEntity<IBook>(kidBookId);
kidBook.Quantity = 0;
session.SaveChanges();
Write(" Done.");
Write(" Now delete books with zero quantity using stored proc BooksDeleteZeroQuantity.");
booksModule.DeleteBooksWithZeroQuantity(session);
//check it is deleted
session = db.OpenSession();
kidBook = session.GetEntity<IBook>(kidBookId);
AssertTrue(kidBook == null, "Books with zero quantity were not deleted");
Write(" Done, verified - book deleted.");
Write(" ");
Write("End Demo");
Write("Have a look at the database populated by this demo - tables, indexes, stored procedures, data, etc.");
}//method