Spruce

Set of extension methods representing a lightweight orm and a migration framework, for use with Dapper. This is a .net 4.5 library designed to work with MS SQL Server 2008+. This library can be installed via nuget.

Conventions

• Ids are generated from SQL. This project supports int, long, and guid ids. All objects are expected to have an Id field - composite ids are not supported.
• This project includes a basic data migration framework. Migrations are one way (no rolling back) and are handled all in code. Running the migrations is up to the user.

Example usage

• Real world, example usage can be found in the MvcKickstart project.

Advanced configuration options

How do I set audit fields before an item gets saved or clear cache after an item is saved?

Spruce has a couple events that you can hook. In particular, you'll be interested in the Saving and Saved events. Saving is fired before the item is inserted/updated, while Saved is fired after the db command has completed.

// Somewhere in your application configuration
SpruceSettings.Saving += (item, e) =>
	{
		var entityType = item.GetType();
		int? userId = null;
		UserPrincipal currentUser;
		if (HttpContext.Current != null)
		{
			currentUser = HttpContext.Current.User as UserPrincipal;
		}
		else
		{
			currentUser = Thread.CurrentPrincipal as UserPrincipal;
		}
		if (currentUser != null && currentUser.UserObject != null && currentUser.UserObject.Id != default(int))
			userId = currentUser.UserObject.Id;
		if (userId.HasValue)
		{
			var createdBy = entityType.GetProperty("CreatedBy");
			if (createdBy != null && (int) createdBy.GetValue(item) == default(int))
				createdBy.SetValue(item, userId.Value);
			var modifiedBy = entityType.GetProperty("ModifiedBy");
			if (modifiedBy != null)
				modifiedBy.SetValue(item, userId.Value);
		}
		var createdOn = entityType.GetProperty("CreatedOn");
		if (createdOn != null && (createdOn.GetValue(item) == null || (DateTime) createdOn.GetValue(item) == default(DateTime)))
			createdOn.SetValue(item, DateTime.UtcNow);
		var modifiedOn = entityType.GetProperty("ModifiedOn");
		if (modifiedOn != null)
			modifiedOn.SetValue(item, DateTime.UtcNow);
	};
// Clear cache for an object after it is saved
SpruceSettings.Saved += (item, e) =>
	{
		Cache.ClearFor(item);
	};

How do I change a sql data type used when creating tables/columns?

SQL types are defined in SpruceSettings.cs. Look near the bottom for the SqlSchemaTypeMap field. You may add or update the map as needed:

// Somewhere in your application configuration
SpruceSettings.SqlSchemaTypeMap[typeof(TimeSpan)] = "long";

Please keep in mind that this only affects schema information. You may need to adjust Dapper's type mapping for querying.

You may also explicitly set the sql type on a per field basis. For example:

public class TestTable {
  [AutoIncrement]
  public int Id { get; set; }
  [SqlType("decimal(9,6)")]
  public public decimal Latitude { get; set; }
  [SqlType("decimal(9,6)")]
  public public decimal Longitude { get; set; }
}

### I would like my enum to map to an nvarchar field instead of an int. How do I do this?

> You can have any field map to nvarchar by decorating the property with the [StringLength(xx)] attribute
> For example:
```csharp
public class TestTable {
  [AutoIncrement]
  public int Id { get; set; }
  [StringLength(50)]
  public MyEnumType MyEnumProperty { get; set; }
}

How do I specify a primary key?

// Auto incrementing primary key Id. The [PrimaryKey] attribute is inferred when using [AutoIncrement]
public class TestTable {
  [AutoIncrement]
  public int Id { get; set; }
}

// Guid primary key
public class TestTable {
  [PrimaryKey]
  [Default("newSequentialId()")]
  public Guid Id { get; set; }
}

How do I specify a foreign key relationship?

public class TestTable {
  [AutoIncrement]
  public int Id { get; set; }
}
public class OtherTestTable {
  [AutoIncrement]
  public int Id { get; set; }
  [References(typeof(TestTable))]
  public int TestTableId { get; set; }
}

How do I control the name of the table?

// By default, Spruce will use plural table names, based on the name of the class. 
// You can override this by using the table attribute from the System.ComponentModel.DataAnnotations.Schema namespace
[Table("TestTable")]
public class TestTable {
  [AutoIncrement]
  public int Id { get; set; }
}