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

As I wrote in my post the other day about SQL Server 2008, I would present some examples on how to use some of the new data types supported in SQL Server 2008 when developing .NET applications.

I “googled” the subject and found a couple of blogs about it. I wasn’t able to find anything on MSDN, except on the Forums. The information on the forums was somewhat useful, so I started off…

Dataset designer trouble

Unfortunately the use of the Dataset designer and TableAdapters and columns of geometry type aren’t compatible, at least not in any way I found out. You will receive an error when dragging a table with a geometry column to the designer.

I tried a couple of ways to get around this but I failed. As long as you don’t need the value of the geometry in your .NET code there are no worries. If you have the need to use a geometry column, you have to get around the UDT (user-defined type) error by using a stored procedure and execute it “the hard way”. I will explain how to do that later on.

If you really have to use TableAdapters the regular way, add a stored procedure to the database for getting data from the table(s) you want. Let the stored procedure return the geometry column as a WKT formatted string containing the geometry. The WKT string can be used to initialize a .NET geometry object for you to use. To insert or update data in the table you have to add stored procedures for this as well. The stored procedures will have to take WKT string parameters instead of the geometry objects. The WKT string can be used for initializing the geometry objects in your stored procedures.

I will explain how to use TableAdapters and geometry columns in part 2 of this post.

How to

I tried two different ways to use the geometry data type. I will explain one of these ways here, the rough way that uses the “low-level” .NET classes SqlConnection and SqlCommand, while the other uses a TableAdapter and the Dataset designer, explained in part 2.

The rough way

In this section I will explain how to use the .NET classes SqlConnection and SqlCommand to insert, retrieve, update and delete data including geometry objects in a table.

The steps below are summarized here.

Setup the database

First start by adding a table to the database (by the way, I called my database freddes), containing a geomtry column (line 15):

USE [freddes]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Person](
	[ID] [int] IDENTITY(100,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[Location] [geometry] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (
   PAD_INDEX  = OFF,
   STATISTICS_NORECOMPUTE  = OFF,
   IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS  = ON,
   ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

This will add a table to the database:

Now add a stored procedure for getting all data from the table:

USE [freddes]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetDataPerson]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT ID, Name, City, Location FROM Person
END

Now you’re set to get data out of the table and use it in your .NET application. To be a little bit ahead of things you can add the insert, update and delete procedures. The SQL script for these methods looks like this:

--------UpdateDataPerson
USE [freddes]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateDataPerson]
	@ID  int,
	@Name varchar(50),
	@City varchar(50),
	@Location geometry
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE Person SET Name = @Name, City = @City, Location=@Location WHERE ID = @ID
END
GO

--------InsertDataPerson
CREATE PROCEDURE [dbo].[InsertDataPerson]
	@Name varchar(50),
	@City varchar(50),
	@Location geometry
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO Person VALUES(@Name, @City, @Location)
END
GO

--------DeleteDataPerson
CREATE PROCEDURE [dbo].[DeleteDataPerson]
	@ID int
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM Person WHERE ID = @ID
END

Visual Studio coding

Now create a .NET application, create your SqlConnection and get going!

The code below demonstrates how to use the stored procedures. It will insert a couple of records, update some of them, delete others and display the result.

The connection string, freddesConnectionString, is on the same format as before, located in App.config’s connectionString section:

“Data Source=(local)\SQLEXPRESS2008;Initial Catalog=freddes;Integrated Security=True”

Let’s add the C# methods:

        public void InsertData()
        {
            // Create the connection object
            SqlConnection connection =
             new SqlConnection(
              ConfigurationManager.ConnectionStrings["freddesConnectionString"].ConnectionString);

            try
            {
                ////////////////////////////////////////////////////
                // Insert data
                ////////////////////////////////////////////////////
                // Create and initialize the command object with the connection object
                SqlCommand command = new SqlCommand("InsertDataPerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Create the "standard" paramaters
                SqlParameter pName = new SqlParameter("@Name", "Fredde Jonsson");
                SqlParameter pCity = new SqlParameter("@City", "Vaxjo");

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

                // Create the geometry parameter
                SqlParameter pLocation =
                   new SqlParameter("@Location", gLocationBuilder.ConstructedGeometry);
                pLocation.UdtTypeName = "geometry";

                // Add all parameters
                command.Parameters.Add(pName);
                command.Parameters.Add(pCity);
                command.Parameters.Add(pLocation);

                // Open the connection and execute the stored procedure
                connection.Open();
                command.ExecuteNonQuery();

                // Clear the parameters and do it one more time to add another guy
                command.Parameters.Clear();

                pName = new SqlParameter("@Name", "The King");
                pCity = new SqlParameter("@City", "Stockholm");

                gLocationBuilder = new SqlGeometryBuilder();
                gLocationBuilder.SetSrid(1);
                gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
                gLocationBuilder.BeginFigure(674750, 6580400);
                gLocationBuilder.EndFigure();
                gLocationBuilder.EndGeometry();

                pLocation =
                   new SqlParameter("@Location", gLocationBuilder.ConstructedGeometry);
                pLocation.UdtTypeName = "geometry";

                command.Parameters.Add(pName);
                command.Parameters.Add(pCity);
                command.Parameters.Add(pLocation);

                command.ExecuteNonQuery();

                // Clear the parameters and do it one more time to add another guy
                command.Parameters.Clear();

                pName = new SqlParameter("@Name", "Hockey");
                pCity = new SqlParameter("@City", "Jonkoping");

                gLocationBuilder = new SqlGeometryBuilder();
                gLocationBuilder.SetSrid(1);
                gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
                gLocationBuilder.BeginFigure(449958, 6404728);
                gLocationBuilder.EndFigure();
                gLocationBuilder.EndGeometry();

                pLocation =
                   new SqlParameter("@Location", gLocationBuilder.ConstructedGeometry);
                pLocation.UdtTypeName = "geometry";

                command.Parameters.Add(pName);
                command.Parameters.Add(pCity);
                command.Parameters.Add(pLocation);

                command.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                connection.Close();
            }
        }

Let’s look at the data

        public void GetData()
        {
            // Create the connection object
            SqlConnection connection =
             new SqlConnection(
              ConfigurationManager.ConnectionStrings["freddesConnectionString"].ConnectionString);

            try
            {
                ////////////////////////////////////////////////////
                // Get data
                ////////////////////////////////////////////////////
                SqlCommand command = new SqlCommand("GetDataPerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    System.Console.WriteLine(
                        reader["ID"].ToString() + ", " +
                        reader["Name"].ToString() + ", " +
                        reader["City"].ToString());

                    SqlGeometry location = (SqlGeometry)reader["Location"];
                    System.Console.WriteLine("WKT: " + location.ToString());

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

                reader.Close();
            }
            finally
            {
                // Close the connection
                connection.Close();
            }
        }

Let’s update some data

        public void UpdateData()
        {
            // Create the connection object
            SqlConnection connection =
             new SqlConnection(
              ConfigurationManager.ConnectionStrings["freddesConnectionString"].ConnectionString);

            try
            {
                ////////////////////////////////////////////////////
                // Update data
                ////////////////////////////////////////////////////
                SqlCommand command = new SqlCommand("UpdateDataPerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                // Let's move Hockey to Karlstad
                SqlParameter pID = new SqlParameter("@ID", 102);
                SqlParameter pName = new SqlParameter("@Name", "Hockey");
                SqlParameter pCity = new SqlParameter("@City", "Karlstad");

                SqlGeometryBuilder gLocationBuilder = new SqlGeometryBuilder();
                gLocationBuilder.SetSrid(1);
                gLocationBuilder.BeginGeometry(OpenGisGeometryType.Point);
                gLocationBuilder.BeginFigure(415012, 6583122);
                gLocationBuilder.EndFigure();
                gLocationBuilder.EndGeometry();

                SqlParameter pLocation =
                   new SqlParameter("@Location", gLocationBuilder.ConstructedGeometry);
                pLocation.UdtTypeName = "geometry";

                command.Parameters.Add(pID);
                command.Parameters.Add(pName);
                command.Parameters.Add(pCity);
                command.Parameters.Add(pLocation);

                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                connection.Close();
            }
        }

Let’s delete some data

        public void DeleteData()
        {
            // Create the connection object
            SqlConnection connection =
             new SqlConnection(
              ConfigurationManager.ConnectionStrings["freddesConnectionString"].ConnectionString);

            try
            {
                ////////////////////////////////////////////////////
                // Delete data
                ////////////////////////////////////////////////////
                SqlCommand command = new SqlCommand("DeleteDataPerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter pID = new SqlParameter("@ID", 100);

                command.Parameters.Add(pID);

                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                connection.Close();
            }
        }

The following method calls the other methods added above.

        public void TryTheGeometryStuff()
        {
            InsertData();
            GetData();
            UpdateData();
            DeleteData();
        }

After the methods have executed the Person table will contain two records, one with The King in Stockholm and one with Hockey in Karlstad.

Debug the code above and check the database table for changes, using the SQL Management Studio, after each call to the stored procedures.

The steps performed above are summarized below.

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.

The classes used for handling the spatial data are described more in detail on the MSDN Library web site. Look in the SQL Server 2008 Books Online Section, for the geometry and geography types.

Summary

The steps to create the above behavior are:

1. Add a database to the SQL Server 2008 instance.
2. Create a table containing one or more spatial data columns.
3. Create one or more stored procedures that takes geometry objects as parameters.
4. Call the stored procedures from your code, using the SqlConnection and SqlCommand. Pass the geometry objects, created by the SqlGeometryBuilder class, as parameters.
5. Look in the database using the SQL Management Studio and be happy about the results you have achieved.

More

In part 2 I will explain how to use TableAdapters in the DataSet designer to accomplish the same result as above. This post will be published shortly.