A first look at SQL 2008 spatial data types round trip with sql 2008. I was surprised to find that the Entity Framework, LINQ to SQL and Dataset Designer do not currently support Spatial data types.  Given this was the case and wanting to have a play with the spatial data type the following is how I used the sqlgeometry spatial data type to store and retrieve a list both lines and points consisting of X,Y co-ordinates.

Lets use a bottom up approach create a table using the GEOMETRY data type:

CREATE TABLE [dbo].[MyData](
	[ID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newsequentialid()) ,
	[Data] [GEOMETRY] NULL);
GO

Create a stored procedure to insert data into the MyData table:

CREATE PROCEDURE [dbo].[InsertMyData]
            @Lines GEOMETRY 
AS	
	INSERT INTO [dbo].[MyData] (Data)
             VALUES (@Lines);
RETURN 0
GO

And create a stored procedure to retrieve a the data:

CREATE PROCEDURE [dbo].[GetMyData]
	@ID uniqueIdentifier	
AS
	SELECT *
	FROM [dbo].[MyData]
	WHERE ID = @ID	
	
RETURN 0
GO

Code to insert and retrieve data:

1. Add a reference to C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll which contains SqlGeometryBuilder

2.                      using System;
                        using System.Collections.Generic;
                        using System.Data;
                        using System.Data.SqlClient;
                        using System.Threading;
                        using System.Threading.Tasks;
                        using Microsoft.SqlServer.Types;
   

This is where it gets back to the !good old days without generation of your DAL. Add a method to call the InsertMyData stored procedure:

        public bool InsertMyData(Guid id, List> lines)
        {
            int result = 0;
            if (lines.Count > 0)
            {
                using (SqlConnection conn = new SqlConnection(connString))
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "InsertMyData";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@ID", id));
                    cmd.Parameters.Add(SpatialDataHelper.GetGeometrySqlParameter("@Data", lines, true));
                    conn.Open();
                    result = cmd.ExecuteNonQuery();
                }
            }

            return (result > 0);
        }

Now you may be wondering what is SpatialDataHelper.GetGeometrySqlParameter. Well that too is something that needs to be implemented. Even though the SQL geometry type is a system type the type must be treated as user defined type. Take note of the sqlParam.UdtTypeName = “geometry”;

 public static class SpatialDataHelper
    {
        public static SqlParameter GetGeometrySqlParameter(string paramName, List> lines, bool makeValid)
        {
            SqlParameter sqlParam = new SqlParameter();
            sqlParam.ParameterName = paramName;
            sqlParam.UdtTypeName = "geometry";

            SqlGeometryBuilder geomBuilder = new SqlGeometryBuilder();

            // SqlGeometryBuilder.SetSrid Must be called at the beginning of each geometry sequence
            geomBuilder.SetSrid(0); 
            geomBuilder.BeginGeometry(OpenGisGeometryType.GeometryCollection);
            
            //break out each line
            foreach (List lp in lines)
            {
                if (lp.Count > 0)
                {
                    if (lp.Count == 1) //check if its a point or a line and start a geometry for the point or line
                        geomBuilder.BeginGeometry(OpenGisGeometryType.Point);
                    else
                        geomBuilder.BeginGeometry(OpenGisGeometryType.LineString);

                    int count = 0;
                    foreach (Point p in lp) //add all points
                    {
                        if (count == 0)
                            geomBuilder.BeginFigure(p.X, p.Y);
                        else
                            geomBuilder.AddLine(p.X, p.Y);

                        count++;
                    }

                    geomBuilder.EndFigure();
                    geomBuilder.EndGeometry();
                }
            }
            geomBuilder.EndGeometry();

            SqlGeometry constructed = geomBuilder.ConstructedGeometry;
            if (makeValid)
            {
                //Note required to convert into a geometry instance with a valid Open Geospatial Consortium (OGC) type. 
                // this can cause the points to shift - use with caution...
                constructed = constructed.MakeValid();
            }
            sqlParam.Value = constructed;

            return sqlParam;
        }        
    }

Finally to get your data back out its necessary to call the previously defined GetMyData stored procedure and then crunch out the points into their original form.

        public List> GetMyData(Guid id)
        {
            ConcurrentBag> bag = new ConcurrentBag>();

            using (SqlConnection conn = new SqlConnection(connString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "GetMyData";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", id));
                conn.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        startLinesID = reader.GetGuid(reader.GetOrdinal("ID"));
                        SqlGeometry gLines = (SqlGeometry)reader["Data"];
                        // using Parallel.For to speed up crunch.  Note: STGeometryN starts with 1 based index therefore start with i of 1
                        Parallel.For(1, gLines.STNumGeometries().Value + 1, delegate(int i)
                        {
                            SqlGeometry geom = gLines.STGeometryN(i);
                            List line = new List();
                            switch (geom.STGeometryType().ToString())
                            {
                                case "LineString":    //a LineString is a line within the SQLGeometry                               
                                    for (int j = 1; j <= geom.STNumPoints(); j++) //get all points forming the line
                                    {
                                        line.Add(new Point(geom.STPointN(j).STX.ToSqlInt32().Value, geom.STPointN(j).STY.ToSqlInt32().Value));
                                    }
                                    break;
                                case "Point": //an individual point                                   
                                    line.Add(new Point(geom.STPointN(1).STX.ToSqlInt32().Value, geom.STPointN(1).STY.ToSqlInt32().Value)); //1 is the first index not 0 based
                                    break;
                            }
                            bag.Add(line);
                        });
                    }
                }
            }

            return bag.ToList();
        }  

This seems to do the job although I would be interested to hear of better ways to do this.