SQL 2008 spatial sqlGeography

dbrillon posted on Wednesday, July 22, 2009

I tried to use a sqlGeography type in my business object but the data portal fail when fetching the data.

Does anyone use this data type in CSLA

sergeyb replied on Wednesday, July 22, 2009

I believe it is binary data, so you should read it into byte[] property.

Sergey Barskiy
Principal Consultant
office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

-----Original Message-----
From: dbrillon [mailto:cslanet@lhotka.net]
Sent: Wednesday, July 22, 2009 5:45 PM
To: Sergey Barskiy
Subject: [CSLA .NET] SQL 2008 spatial sqlGeography

I tried to use a sqlGeography type in my business object but the data portal fail when fetching the data.

Does anyone use this data type in CSLA

dbrillon replied on Wednesday, July 22, 2009

Reading from the database is not my problem. The object is read correctly by using this code:

using (var cm = ctx.Connection.CreateCommand())
{
cm.CommandText = @"SELECT [LocationId],[Name],[Description],[Type],[Distance],[Color],[BackColor],[Shape] FROM [Locations]";
cm.CommandType = System.Data.CommandType.Text;

using (var reader = new SafeDataReader(cm.ExecuteReader()))
{
while (reader.Read())
{
Add(new LocationChildInfo(
reader.GetInt32("LocationId"),
reader.GetString("Name"),
reader.GetString("Description"),
reader.GetByte("Type"),
(float)reader.GetInt32("Distance"),
UIntToColor((uint)reader.GetInt16("Color")),
UIntToColor((uint)reader.GetInt16("BackColor")),
(SqlGeography)reader["Shape"]
));
}
}
}

I defined a property in my object as sqlGeography and this seems to be the problem. What should i use instead of this in my BO.

RockfordLhotka replied on Wednesday, July 22, 2009

Most Sql types are not serializable - which makes sense, as they are unique to SQL Server, and thus to a data access layer. I would assume SqlGeography is not serializable (though I don't know for sure).

Most SQL types need to be converted into .NET types before they can be effectively used. Your code does that - you can see how you convert your other values to a string, int or uint type. You'll almost certainly have to do the same with the geography type as well.

dbrillon replied on Wednesday, July 22, 2009

I guess that's the problem.  My solution is to use a byte[] to store my geography object and reconstruct it when I need it.

public class LocationChildInfo : ReadOnlyBase<LocationChildInfo>
{
#region Business Methods
public int LocationId { get; private set; }
public string Name { get; private set; }
public string Description { get; private set; }
public byte Type { get; private set; }
public float Distance { get; private set; }
public Color Color { get; private set; }
public Color BackColor { get; private set; }
public string Shape { get; private set; }
public byte[] ShapeAsBin { get; private set; }
#endregion

internal LocationChildInfo(int locationId, string name, string description, byte type, float distance, Color color, Color backColor, SqlGeography shape)
{
LocationId = locationId;
Name = name;
Description = description;
Type = type;
Distance = distance;
Color = color;
BackColor = backColor;
Shape = shape.ToString();
ShapeAsBin = shape.STAsBinary().Buffer;
}
}

This will go througth the DataPortal.  When I need to restore my geography object, I use the following code:

LocationInfoList locations = LocationInfoList.GetReadOnlyList();
System.Data.SqlTypes.SqlBytes bytes = new System.Data.SqlTypes.SqlBytes(locations[0].ShapeAsBin);
SqlGeography geo = SqlGeography.STGeomFromWKB(bytes, 4326);

After that I can manipulate my geography object

eg:

double area  = (double)geo.STArea();

 

 

sergeyb replied on Wednesday, July 22, 2009

Check this blog - maybe this is the problem:
http://blogs.conchango.com/stevewright/archive/2009/01/23/how-to-load-spatial-data-into-sql-server-2008-from-net.aspx


Sergey Barskiy
Principal Consultant
office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

-----Original Message-----
From: dbrillon [mailto:cslanet@lhotka.net]
Sent: Wednesday, July 22, 2009 6:56 PM
To: Sergey Barskiy
Subject: Re: [CSLA .NET] RE: SQL 2008 spatial sqlGeography

Reading from the database is not my problem. The object is read correctly by using this code:

using (var cm = ctx.Connection.CreateCommand())
{
cm.CommandText = @"SELECT [LocationId],[Name],[Description],[Type],[Distance],[Color],[BackColor],[Shape] FROM [Locations]";
cm.CommandType = System.Data.CommandType.Text;

using (var reader = new SafeDataReader(cm.ExecuteReader()))
{
while (reader.Read())
{
Add(new LocationChildInfo(
reader.GetInt32("LocationId"),
reader.GetString("Name"),
reader.GetString("Description"),
reader.GetByte("Type"),
(float)reader.GetInt32("Distance"),
UIntToColor((uint)reader.GetInt16("Color")),
UIntToColor((uint)reader.GetInt16("BackColor")),
(SqlGeography)reader["Shape"]
));
}
}
}

I defined a property in my object as sqlGeography and this seems to be the problem. What should i use instead of this in my BO.