.NET and spatial data (with SQL Server 2008), part 2

The other day I published a post about the spatial data support in SQL Server 2008 and using the new data types in a .NET application. I decided to write about “the rough way” and promised to publish an article about how to use DataSet designer and TableAdapters… Here it is!

I will try to present an example to accomplish the same result as in .

Dataset designer trouble again

As I mentioned in there are some issues with the geometry and geography data types when using the Dataset designer, leaving you with the fact that you have to handle these objects as strings, WKT formatted. The WKT formatted strings can be used both on the application level to initiate the spatial data objects as well as on the database side. I will show how to do this later.

How to

In this part of the post I will explain “the TableAdapter way” to handle geometry objects as WKT formatted strings. “The rough way” explained in uses the .NET classes SqlConnection and SqlCommand.

The TableAdapter way

In this section I will explain how to use a TableAdapter for insertion, updates, deletion and selection of spatial data from a .NET application stored in an SQL Server 2008 database.

The steps are summarized below.

Setup the database

The database to use is set up in the same way as in .

The stored procedures needed are TAGetDataPerson, TAUpdateDataPerson and TAInsertDataPerson. “TA” stands for TableAdapter, just for marking that these methods will be used by a TableAdapter. The deletion of records will use the same procedure as , DeleteDataPerson.

USE [freddes]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--------TAGetDataPerson
CREATE PROCEDURE [dbo].[TAGetDataPerson]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT ID, Name, City, Location.ToString() as LocationWKT FROM Person
END
GO

--------TAUpdateDataPerson
CREATE PROCEDURE [dbo].[TAUpdateDataPerson]
	@ID  int,
	@Name varchar(50),
	@City varchar(50),
	@LocationWKT text
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE Person SET
		Name = @Name,
		City = @City,
		Location=geometry::STPointFromText(@LocationWKT,1)
	WHERE ID = @ID
END
GO

--------TAInsertDataPerson
CREATE PROCEDURE [dbo].[TAInsertDataPerson]
	@Name varchar(50),
	@City varchar(50),
	@LocationWKT text
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO Person
	VALUES(	@Name,
			@City,
			geometry::STPointFromText(@LocationWKT,1))
END

Please note the geometry::STPointFromText on line 32 and 49. The Point objects stored in the database are created from a text parameter.

Now you are set to get to the coding of the .NET application.

Visual Studio coding

Create a .NET application using Visual Studio.

Add a Dataset to the solution and right-click the empty area in design view -> Add -> TableAdapter. A wizard will guide you to create a connection to the database. On step “Choose a Command Type” be sure to choose “Use existing stored procedures“. Choose the stored procedures according to this:

• Select: TAGetDataPerson
• Insert: TAInsertDataPerson
• Update: TAUpdateDataPerson
• Delete: DeleteDataPerson

Note that the Delete procedure is the one created in . Just “Next” all steps after that and then “Finish”. The dataset designer will now contain a datatable and an adapter:

Note that I changed the names of the generated table and adapter.

Now it is time to use the adapter. First you can truncate the table in the database by invoking the following on the database, using SQL Management Studio, if there are records left from the previous part:

truncate table freddes.dbo.Person

Let’s take a look at the code for accomplishing the same result as in , but now use the TAPersonTableAdapter:

        public void TryTheTableAdapter()
        {
            // Create the TableAdapter object
            GeomDataSetTableAdapters.TAPersonTableAdapter ta =
                    new GeomDataSetTableAdapters.TAPersonTableAdapter();

            /////////////////////////
            // Insert data
            /////////////////////////

            // Create a geometry object
            SqlGeometryBuilder gLocationBuilder = new SqlGeometryBuilder();
            gLocationBuilder.SetSrid(1);
            gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
            gLocationBuilder.BeginFigure(488369, 6303707);
            gLocationBuilder.EndFigure();
            gLocationBuilder.EndGeometry();

            // Invoke the insert method on the adapter
            ta.Insert(
                    "Fredde Jonsson",
                    "Vaxjo",
                    gLocationBuilder.ConstructedGeometry.ToString());

            // Create a new geometry object
            gLocationBuilder = new SqlGeometryBuilder();
            gLocationBuilder.SetSrid(1);
            gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
            gLocationBuilder.BeginFigure(674750, 6580400);
            gLocationBuilder.EndFigure();
            gLocationBuilder.EndGeometry();

            // Invoke the insert method on the adapter
            ta.Insert(
                    "The King",
                    "Stockholm",
                    gLocationBuilder.ConstructedGeometry.ToString());

            // Create a new geometry object
            gLocationBuilder = new SqlGeometryBuilder();
            gLocationBuilder.SetSrid(1);
            gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
            gLocationBuilder.BeginFigure(449958, 6404728);
            gLocationBuilder.EndFigure();
            gLocationBuilder.EndGeometry();

            // Invoke the insert method on the adapter
            ta.Insert(
                    "Hockey",
                    "Jonkoping",
                    gLocationBuilder.ConstructedGeometry.ToString());

            /////////////////////////
            // Get data
            /////////////////////////

            // Invoke the select procedure
            GeomDataSet.TAPersonDataTable personTable = ta.GetData();
            foreach (GeomDataSet.TAPersonRow row in personTable)
            {
                System.Console.WriteLine(
                    row.ID + ", " +
                    row.Name + ", " +
                    row.City);

                SqlGeometry location =
                    SqlGeometry.STGeomFromText(
                      new System.Data.SqlTypes.SqlChars(row.LocationWKT),
                      1);

                System.Console.WriteLine("WKT: " + location.ToString());

                System.Console.WriteLine("------------------------------------");
            }

            /////////////////////////
            // Update data
            /////////////////////////

            // Create a new geometry object
            gLocationBuilder = new SqlGeometryBuilder();
            gLocationBuilder.SetSrid(1);
            gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
            gLocationBuilder.BeginFigure(415012, 6583122);
            gLocationBuilder.EndFigure();
            gLocationBuilder.EndGeometry();

            // Invoke the update procedure
            ta.Update(
                    102,
                    "Hockey",
                    "Karlstad",
                    gLocationBuilder.ConstructedGeometry.ToString());

            /////////////////////////
            // Delete data
            /////////////////////////
            ta.Delete(100);
        }

Some notes on the source code:

• Line 23, 37, 51: The SqlGeometryBuilder is used to create the WKT string.
• Line 66-69: An SqlGeometry object is created from the WKT string, but it is converted back to a WKT string on line 71. This is just for “fun”.

As you can see in the code the geometry objects are created using an SqlGeometryBuilder object. The SqlGeometryBuilder and SqlGeometry can be used after adding a reference to Microsoft.SqlServer.Types. This DLL will be in the GAC after installing SQL Server 2008.

When the code has finished executing, the table in the database will have the same data as in .

Summary

The steps to create the above behavior are:

1. Add the database to the SQL Server 2008 instance, if not added in part 1.
2. Create the table.
3. Create the stored procedures that return or take WKT strings as parameters.
4. Create a DataSet and add a TableAdapter using the stored procedures created.
5. Call the methods on the TableAdapter.
6. Look in the database using the SQL Management Studio and be happy about the results you have achieved.

Read more

Look at for links to the MSDN Books Online. There you will find info about the SqlGeometry classes as well as geometry and geography stuff in SQL Server 2008.