LightSpeed and SQL Server 2008

We are currently busy working through features for LightSpeed 3.0, and one of the ones on my plate is to add in a new provider for SQL Server 2008 which can take advantage of some of the capabilities we have in SQL Server 2008.

We have done a great deal over the recent minor versions to augment our provider capabilities, adding support for stored procedures and views but we would like to go further. So far, here is what we have added in to our SQL Server 2008 provider.

Support for Native Types

There are a number of new native types in SQL Server 2008 such as geography, geometry and the new date and time data types.

We now support these types and translate them accordingly into properties on your LightSpeed entities. Some of these map to existing CLR types such as datetime2 –> DateTime, however some of the other data types (geometry, geography) are actually all new CLR based types living in the Microsoft.SqlServer.Types assembly.

Here is an example of this at work – we have a Cinema entity which has a geography data type storing its physical location as per this schema:

Shot012

Here is the definition of the entity we get generated:

[Serializable]
[System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
public partial class Cinema : Entity<long>
{
  #region Fields
 
  [ValidatePresence]
  [ValidateLength(0, 255)]
  private string _name;
  [ValueField]
  private Address _address;
  [ValueField]
  private SqlGeography _geoLocation;
  private int _seats;
  private long _locationId;
 
  #endregion
 
  // other generated code omitted for brevity..
}

And here is what we can then do with it:

foreach (var cinema in unitOfWork.Cinemas)
{
  Console.WriteLine("{0} is at {1},{2} - {3}", cinema.Name, cinema.GeoLocation.Lat, cinema.GeoLocation.Long, cinema.Address.Street);
}
 
unitOfWork.Cinemas.First().GeoLocation = SqlGeography.Parse("POINT (45 45)");
unitOfWork.SaveChanges();

Support for CLR based UDTs

Along a similar line to our support for SqlGeometry and SqlGeography, we also support custom CLR based UDT’s. These might make sense for you in place of a ValueObject.

Here is an example – given our Cinema entity above, you will notice the Address column is of an Address data type, this is a custom CLR type I have created and deployed.

Here is the class definition for that UDT:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=8000)]
public struct Address : INullable, IBinarySerialize
{
  private bool _isNull;
 
  private string _street;
  private string _city;
  private string _country;
 
  public string Street { get { return _street; } set { _street = value; } }
  public string City { get { return _city; } set { _city = value; } }
  public string Country { get { return _country; } set { _country = value; } }
 
  // other code omitted for brevity
}

And like with the SqlGeography above it is just natively available to make use of:

unitOfWork.Cinemas.Skip(1).First().Address = new Address() { Street = "124 Foo St", City = "Wellington", Country = "New Zealand" };
unitOfWork.SaveChanges();

What else should we add in?

We still have plenty of time before LightSpeed 3.0 is going to go out the door, so I thought I would open the floor to you to suggest some additional SQL specific capabilities that we should be adding in. A QueryNotification hook to refresh the L2 cache? Support for Table Value Parameters in stored proc calls? Some kind of handling for FILESTREAM based columns?

We welcome your feedback :)

Tagged as LightSpeed

5 Responses to “LightSpeed and SQL Server 2008”

  • It’s about time you did some bloody work around here. ;)

    j.

    p.s. looks sexy!

  • Checkmate, my RDBMS ORM mapper will be lightspeed because of this feature.

  • Hi,

    Suppor for the FileStream datatype would be very cool!

    kind regards,
    Erik

  • QueryNotification on the L2 Cache, while potentially dangerous, would be the sexiest add to an already great ORM. Second to that, adding TVPs to stored proc wrappers would be very nice.

  • Do you have any plans to add FileStream support yet? It would be really useful if LightSpeed supported this natively.

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top