Skip to content

Instantly share code, notes, and snippets.

@gistlyn
Last active Nov 10, 2020
Embed
What would you like to do?
Rich Create Table Example
using System;
using System.Collections.Generic;
using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using ServiceStack.DataAnnotations;
var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.Open(); // Open ADO.NET DB Connection
public class Player
{
public int Id { get; set; } // 'Id' is PrimaryKey by convention
[Required]
public string FirstName { get; set; } // Creates NOT NULL Column
[Alias("Surname")] // Maps to [Surname] RDBMS column
public string LastName { get; set; }
[Index(Unique = true)] // Creates Unique Index
public string Email { get; set; }
public List<Phone> PhoneNumbers { get; set; } // Complex Types blobbed by default
[Reference]
public List<GameItem> GameItems { get; set; } // 1:M Reference Type saved separately
[Reference]
public Profile Profile { get; set; } // 1:1 Reference Type saved separately
public int ProfileId { get; set; } // 1:1 Self Ref Id on Parent Table
[ForeignKey(typeof(Level), OnDelete="CASCADE")] // Creates ON DELETE CASCADE Constraint
public Guid SavedLevelId { get; set; } // Creates Foreign Key Reference
public ulong RowVersion { get; set; } // Optimistic Concurrency Updates
}
public class Phone // Blobbed Type only
{
public PhoneKind Kind { get; set; }
public string Number { get; set; }
public string Ext { get; set; }
}
public enum PhoneKind
{
Home,
Mobile,
Work,
}
[Alias("PlayerProfile")] // Maps to [PlayerProfile] RDBMS Table
[CompositeIndex(nameof(Username), nameof(Region))] // Creates Composite Index
public class Profile
{
[AutoIncrement] // Auto Insert Id assigned by RDBMS
public int Id { get; set; }
public PlayerRole Role { get; set; } // Native support for Enums
public Region Region { get; set; }
public string Username { get; set; }
public long HighScore { get; set; }
[Default(1)] // Created in RDBMS with DEFAULT (1)
public long GamesPlayed { get; set; }
[CheckConstraint("Energy BETWEEN 0 AND 100")] // Creates RDBMS Check Constraint
public short Energy { get; set; }
public string ProfileUrl { get; set; }
public Dictionary<string, string> Meta { get; set; }
}
public enum PlayerRole // Enums saved as strings by default
{
Leader,
Player,
NonPlayer,
}
[EnumAsInt] // Enum Saved as int
public enum Region
{
Africa = 1,
Americas = 2,
Asia = 3,
Australasia = 4,
Europe = 5,
}
public class GameItem
{
[PrimaryKey] // Specify field to use as Primary Key
[StringLength(50)] // Creates VARCHAR COLUMN
public string Name { get; set; }
public int PlayerId { get; set; } // Foreign Table Reference Id
[StringLength(StringLengthAttribute.MaxText)] // Creates "TEXT" RDBMS Column
public string Description { get; set; }
[Default(OrmLiteVariables.SystemUtc)] // Populated with UTC Date by RDBMS
public DateTime DateAdded { get; set; }
}
public class Level
{
public Guid Id { get; set; } // Unique Identifer/GUID Primary Key
public byte[] Data { get; set; } // Saved as BLOB/Binary where possible
}
// Delete and Recreate above schema
if (db.TableExists<Level>())
db.DeleteAll<Level>(); // Delete ForeignKey data if exists
//DROP and CREATE ForeignKey Tables in dependent order
db.DropTable<Player>();
db.DropTable<Level>();
db.CreateTable<Level>();
db.CreateTable<Player>();
//DROP and CREATE tables without Foreign Keys in any order
db.DropAndCreateTable<Profile>();
db.DropAndCreateTable<GameItem>();
var savedLevel = new Level
{
Id = Guid.NewGuid(),
Data = new byte[]{ 1, 2, 3, 4, 5 },
};
db.Insert(savedLevel);
var player = new Player
{
Id = 1,
FirstName = "North",
LastName = "West",
Email = "north@west.com",
PhoneNumbers = new List<Phone>
{
new Phone { Kind = PhoneKind.Mobile, Number = "123-555-5555"},
new Phone { Kind = PhoneKind.Home, Number = "555-555-5555", Ext = "123"},
},
GameItems = new List<GameItem>
{
new GameItem { Name = "WAND", Description = "Golden Wand of Odyssey"},
new GameItem { Name = "STAFF", Description = "Staff of the Magi"},
},
Profile = new Profile
{
Username = "north",
Role = PlayerRole.Leader,
Region = Region.Australasia,
HighScore = 100,
GamesPlayed = 10,
ProfileUrl = "https://www.gravatar.com/avatar/205e460b479e2e5b48aec07710c08d50.jpg",
Meta = new Dictionary<string, string>
{
{"Quote", "I am gamer"}
},
},
SavedLevelId = savedLevel.Id,
};
db.Save(player, references: true);
// Load Player Record and its Referenced Data
var dbPlayer = db.LoadSingleById<Player>(player.Id);
dbPlayer.PrintDump();
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="System.Memory" version="4.5.4" targetFramework="net45" />
<package id="ServiceStack.Text" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.Client" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.Common" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.Interfaces" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.OrmLite" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.OrmLite.Sqlite.Windows" version="5.10.0" targetFramework="net45" />
</packages>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment