SQL Server Types

Demis Bellot edited this page Feb 26, 2018 · 7 revisions

OrmLite can be extended to support new Types using SQL Server Special Type Converters which currently adds support for the SQL Server-specific SqlGeography, SqlGeometry and SqlHierarchyId Types.

Since these Types require an external dependency to the Microsoft.SqlServer.Types NuGet package they're contained in a separate NuGet package that can be installed with:

PM> Install-Package ServiceStack.OrmLite.SqlServer.Converters

Once installed, all available SQL Server Types can be registered on your SQL Server Provider with:

SqlServerConverters.Configure(SqlServer2012Dialect.Provider);

SqlServer 2012 Connection String

In addition to using SqlServer2012Dialect.Provider you'll also need to specify you're using MSSQL 2012 on the connection string by adding the ;Type System Version=SQL Server 2012; suffix, e.g:

var dbFactory = new OrmLiteConnectionFactory(
  "Server=host;Database=db;User Id=sa;Password=test;Type System Version=SQL Server 2012",
  SqlServer2012Dialect.Provider);

var db = dbFactory.OpenDbConnection();

Example Usage

After the Converters are registered they can treated like a normal .NET Type, e.g:

SqlHierarchyId Example:

public class Node {
    [AutoIncrement]
    public long Id { get; set; }
    public SqlHierarchyId TreeId { get; set; }
}

db.DropAndCreateTable<Node>();

var treeId = SqlHierarchyId.Parse("/1/1/3/"); // 0x5ADE is hex
db.Insert(new Node { TreeId = treeId });

var parent = db.Scalar<SqlHierarchyId>(
    db.From<Node>().Select("TreeId.GetAncestor(1)"));
parent.ToString().Print(); //= /1/1/

SqlGeography and SqlGeometry Example:

public class GeoTest {
    public long Id { get; set; }
    public SqlGeography Location { get; set; }
    public SqlGeometry Shape { get; set; }
}

db.DropAndCreateTable<GeoTest>();

var geo = SqlGeography.Point(40.6898329,-74.0452177, 4326); // Statue of Liberty

// A simple line from (0,0) to (4,4)  Length = SQRT(2 * 4^2)
var wkt = new System.Data.SqlTypes.SqlChars("LINESTRING(0 0,4 4)".ToCharArray());
var shape = SqlGeometry.STLineFromText(wkt, 0);

db.Insert(new GeoTestTable { Id = 1, Location = geo, Shape = shape });
var dbShape = db.SingleById<GeoTest>(1).Shape;

new { dbShape.STEndPoint().STX, dbShape.STEndPoint().STY }.PrintDump();

Output:

{
    STX: 4,
    STY: 4
}

Memory Optimized Tables

OrmLite allows access to many other advanced SQL Server features including Memory-Optimized Tables where you can tell SQL Server to maintain specific tables in Memory using the [SqlServerMemoryOptimized] attribute, e.g:

[SqlServerMemoryOptimized(SqlServerDurability.SchemaOnly)]
public class SqlServerMemoryOptimizedCacheEntry : ICacheEntry
{
    [PrimaryKey]
    [StringLength(StringLengthAttribute.MaxText)]
    [SqlServerBucketCount(10000000)]
    public string Id { get; set; }
    [StringLength(StringLengthAttribute.MaxText)]
    public string Data { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime? ExpiryDate { get; set; }
    public DateTime ModifiedDate { get; set; }
}

The [SqlServerBucketCount] attribute can be used to configure the bucket count for a hash index whilst the new [SqlServerCollate] attribute can be used to specify an SQL Server collation.

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.