CODEWITHSUNDEEP
c#sql-serverspatial
webdad3
webdad3

Reputation: 9090

passing in Geography Type and SQL converts to Geography type?

I'm figuring out how to take Geography data (Lat/Lon) and add it to a SQL database using a Geography column. I'm referencing Microsoft.SqlServer.Types to get the SqlGeographyBuilder

I should also add that this is on SQL2008r2

My C# project is also using .Net 4.0

This is just a snippet of code that I'm using in my C# code to do this:

SqlParameter param21 = new SqlParameter();

param21.ParameterName = "@Shape";
param21.UdtTypeName = "Geography";
param21.SqlDbType = System.Data.SqlDbType.Udt;
param21.Size = 1;

SqlGeographyBuilder sqlGeogBuild = new SqlGeographyBuilder();
SqlGeography sqlGeog = null;

sqlGeogBuild.SetSrid(4267);

if (inputReader[parmLatColumnName].ToString().Length > 0 && inputReader[parmLonColumnName].ToString().Length > 0)
{
    sqlGeogBuild.BeginGeography(OpenGisGeographyType.Point);
    sqlGeogBuild.BeginFigure(float.Parse(inputReader[parmLatColumnName].ToString()), float.Parse(inputReader[parmLonColumnName].ToString()));
    sqlGeogBuild.EndFigure();
    sqlGeogBuild.EndGeography();
    sqlGeog = sqlGeogBuild.ConstructedGeography;
    param21.Value = sqlGeog;
}
else
{
    param21.Value = DBNull.Value;
}

sqlComm.Parameters.Add(param21);

When I used the profiler I found that my query looked like this:

declare @p21 sys.geography
set @p21=convert(sys.geography,0xE6100000010C00000080B0374040000000806BD057C0)

exec insPoints @stCty=N'31256',@InstrumentType=N'''ogVal''',@FileDate='2008-01-03 00:00:00',@EntryNumber=N'''500''',@Vol=N'NULL',@Page=N'NULL',@Lessor=N'''name''',@Lessee=N'''company name''',@InstrumentDate='2000-01-12 00:00:00',@Term=3,@TermType=N'''Years''',@Expiration='2010-07-12 00:00:00',@Royalty=0.000100000,@GrossAcres=1.000000000,@AgencyId=N'NULL',@Agency=N'''Fee''',@EffDate=NULL,@Survey=N'''A500,A06,A700,A400,A900''',@Longitude=-91.000000000,@Latitude=30.000000000,@Shape=@p21

Why does it convert it to a Geography type when I'm passing it in as a Geography type?

Upvotes: 3

Views: 3081

Answers (2)

Ashutosh Singh
Ashutosh Singh

Reputation: 662

First of all please have a look at this documentation page @MSDN Manipulating UDT Data

So from c# your query will contain a UDT data which first need to be converted into that UDT type by SQL(the default behaviour). Then it will be processed as a native type. A agree with @Stoleg that this conversion will not appear on your profiler when there is a value defined in enum SqlDbType.

But Today we have .NET 4.6 released but yet this is not added in that enum.

Upvotes: 0

Stoleg
Stoleg

Reputation: 9320

SQL Server allows to define your own data types. This is called User Defined Data type (UDT).

You are using a User Defined Type to define @Shape. As you do not have a separate user defined data type Georgaphy defined in your database, it converts value to system datatype geography. Case is not important, it is importtant that

Using a different property of SqlPArameter class - DbType instead to reference db data type directly would have solved this. However, there is no geography member in SqlDbType enumerator.

It looks like a gap between .Net and SQL server. Try setting DbType = "geography", but it is unlikely to work.

Upvotes: 1

Related Questions