SqlGeometry is a spatial data type introduced in SQL Server 2008 (R2). It can be found in the Microsoft.SqlServer.Types namespace. This is a special data type and not one of the standard SqlDbTypes (enum) datatypes. So – if you have to write (save) this particular value (a SqlGeometry value) along with a set of other database values, what specific SqlParameter should you use?

In other words, if you are constructing a list of SqlParameters (to pass into your SqlCommand), how can you tag along a SqlGeometry data type without offending the database? You can do this in two steps”:

  1. Set the SqlDbType to UDT (User Defined Type)
  2. Set the UdtTypeName to geometry

This is illustrated in the code snippet below:

 SqlCommand sqlCmd = cmd as SqlCommand;
  for (int i = 0; i < parameterValues.Count; i++)
  {   
   SqlDbType dbType = GetDbType(parameterValue.DataType); // This needs to return SqlDbType.UDT for your Geometry data type
   SqlParameter sqlPrm = sqlCmd.Parameters.Add(paramName, dbType);//Step 1:This dbType should be SqlDbType.UDT
             
        // handle the geometry type here
         if (dbType == SqlDbType.Udt)
         {
               sqlPrm.UdtTypeName = "geometry"; // Step 2: Set this to "geometry" - now we can treat this 
                                                like any other SqlParameter even though it's datatype is SqlGeometry
              }
              SqlParameter sqlPrm = sqlCmd.Parameters.Add(paramName, dbType);
           }

Summary

Even though SqlGeometry represents a non-standard SqlDbType, one can simply treat it as a User Defined Type (UDT) with its UdtTypeName set to geometry. This allows us to treat the resulting parameter as a regular SqlParameter.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin