A Chain Tutorial

A Chain Tutorial

What is Chain?

Chain is a different way of thinking about ORMs. The basic idea is that anything you want to do should be expressible in a single statement.

Connecting to a Database

To get started, you need to create a data source. Just one, you don’t need to keep creating and disposing them like you would with a ADO.NET connection or EF context.

var dataSource = SqlServerDataSource.CreateFromConfig("SqlServerTestDatabase");
dataSource.Test()

In this case, we decided to read the connection string from app.config. But if you are storing your configuration somewhere else, you can pass in the connection string explicitly:

var dataSource = new SqlServerDataSource("Server=localhost;Database=SqlServerTestDatabase;Trusted_Connection=True;");
dataSource.Test()

Inserting Data

Before we get started, create a couple of tables in your database. The examples here are for SQL Server.

CREATE TABLE Owner
(
	OwnerKey INT NOT NULL IDENTITY PRIMARY KEY,
	DriversLicense VARCHAR(20) NOT NULL UNIQUE,
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	AddressLine1 NVARCHAR(200) NULL,
	AddressLine2 NVARCHAR(200) NULL,
	City NVARCHAR(200) NULL,
	State CHAR(2) NULL,
	Zip smallint NULL
)
CREATE TABLE Vehicle
(
	VehicleKey INT NOT NULL IDENTITY PRIMARY KEY,
	VehicleID varChar(17) NOT NULL,
	Make varChar(50) NOT NULL,
	Model varChar(50) NOT NULL,
	Year smallint NOT NULL,
	OwnerKey INT NULL REFERENCES Owner(OwnerKey)
)

Now let’s create an owner and his car:

var ownerKey = dataSource.Insert("Owner", new { DriversLicense = "B8572496", FirstName = "Elvis", LastName = "Presley" }).ToInt32().Execute();
var vehicleKey = dataSource.Insert("Vehicle", new { VehicleID = "65476XC54E", Make = "Cadillac", Model = "Fleetwood Series 60", Year = 1955 }).ToInt32().Execute();

From left to right:

1. We have a data source
2. We want to perform an insert
3. Our tables are named “Owner” and “Vehicle”
4. We have some information to save, which we’re putting in an anonymous object.
5. We want the primary key back as an integer
6. Make it happen

Chain is going to look at your tables, your data, and generate the right SQL to perform your insert.

Updating Data

Oops, we forgot to note that Elvis owns that Cadillac.

dataSource.Update("Vehicle", new { VehicleKey = vehicleKey, OwnerKey = ownerKey }).Execute();

Again from left to right:

1. We have a data source
2. We want to perform an update
3. Our tables is named “Vehicle”
4. We have some information to save, which we’re putting in an anonymous object.
5. Make it happen

So how does Chain know that VehicleKey is the primary key for the Vehicle table?

Well it asks. The first time you touch a table, Chain will download the schema. Then when you want to do things like perform updates, it has everything it needs to generate the right SQL.

Reading Simple Data

Now what was that VIN number? Let’s ask the database:

var vin = dataSource.From("Vehicle", new { OwnerKey = ownerKey }).ToStringList("VehicleID").Execute();

Again, we simply read this left to right:

1. We have a data source
2. We want something from the data source
3. Our table is named “Vehicle”
4. We have some information to what we’re looking for, which we’re putting in an anonymous object.
5. We want a list of strings
6. Those strings should contain “VehicleID” values
7. Make it happen

Populating Objects

We wouldn’t be an ORM if we couldn’t populate objects. So before we begin, lets define some objects:

        public class Owner
        {
            public int OwnerKey { get; set; }
            public string DriversLicense { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string AddressLine1 { get; set; }
            public string AddressLine2 { get; set; }
            public string City { get; set; }
            public string State { get; set; }
            public short Zip { get; set; }

        }

        public class Vehicle
        {
            public int VehicleKey { get; set; }
            public string VehicleID { get; set; }
            public string Make { get; set; }
            public string Model { get; set; }
            public short Year { get; set; }
            public int OwnerKey { get; set; }
        }

To get the owner, we use a shortcut:

var owner = dataSource.GetByKey("Owner", ownerKey).ToObject<Owner>().Execute();

1. We have a data source
2. We want something from the data source
3. Our table is named “Owner”
4. We have the key.
5. We want an Owner object
6. Make it happen

Since Elvis has multiple cars, we’ll need to use the long form.

var cars = dataSource.From("Vehicle", owner).ToCollection<Vehicle>().Execute();

1. We have a data source
2. We want something from the data source
3. Our table is named “Vehicle”
4. We have the owner.
5. We want some Vehicle objects
6. Make it happen

Wait what? How did it use an Owner object to search the Vehicle table?

Well let’s take a look at the SQL:

SELECT [VehicleKey], [VehicleID], [Make], [Model], [Year], [OwnerKey] FROM [dbo].[Vehicle] WHERE [OwnerKey] = @OwnerKey; 

Chain knows the only property on the owner object that matches the Vehicle table is OwnerKey. So when it builds the WHERE clause, that’s the one it uses.

That’s a little magical, so most of the time we’re really going to use something like this:

//make it clear what we want to filter on
cars = dataSource.From("Vehicle", new { OwnerKey = owner.OwnerKey}).ToCollection<Vehicle>().Execute();
            
//or make the WHERE clause explicit
cars = dataSource.From("Vehicle", "OwnerKey = @OwnerKey", owner).ToCollection<Vehicle>().Execute(); 

Deleting Objects

Let’s clean up:

dataSource.Delete("Vehicle", new { VehicleKey = vehicleKey }).Execute();
dataSource.Delete("Owner", new { OwnerKey = ownerKey }).Execute();

Chain can do a lot more than this. Take a look through the wiki to see some more tricks.