asked
reading geography data type using sqldatareader in c sharp
All, I have spatial polygon data in sql server 2008. I am trying to bring that data (specifically all the lat and longs for a given polygon) into a c shape application. I am trying to use a sqldatareader. However, I dont see any datatype that I can read my data into. SqlDataReader myDataReader = null; SqlConnection mySqlConnection; SqlGeography geometryfield; string InputFile = "SELECT POSTCODE,geom FROM " + Properties.Settings.Default.InputFile; mySqlConnection = new SqlConnection(Properties.Settings.Default.LocalConn); mySqlConnection.Open(); myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection); bool bMore = false; if (myDataReader.HasRows) { bMore = myDataReader.Read(); while (bMore) { BoundaryClass Record = new BoundaryClass(); Record.PostCode = myDataReader.GetValue(0).ToString(); // How do I read the geom field from the select statement above into a geography data tpye // in the Record object above as I am with the first column of data? I can not use // myDataReader.GetValue(1).ToString(). I get // the error that I can not convert string to geography data type. I am trying to read the // geom column which has my polygon points so that I can loop through the number of points // for the polygon to get the respective lat and longs for each zip code. bMore = myDataReader.Read(); } }
If the question is indeed about georgaphy data type (geometry word is also used making it a bit confusing) then you can do it by simply changing your SQL statement like this: --use this select PostCode, geom.Lat Lat, geom.Long Long from -- etc --instead of this select PostCode, geom from -- etc This way you can easily use existing Sql types to read the data from your reader. Without changing your SQL statement, you can simply cast the reader's column value to your SqlGeography type. Using your variable names: geometry = myDataReader.GetValue(1) as SqlGeography; // now you can set your custom Record's respective property to that geometry // above, which is actually meant to be geography but has unfortunate name :( Oleg P.S. I would like to point out that the code in question looks pretty scary. All 3 of the used types: **SqlConnection**, **SqlDataReader** and **SqlCommand** implement **IDisposable** interface yet none of them is wrapped into a **using** block. This is a very dangerous practice which eventually bites. The code like this can work OK in development, but should never be allowed anywhere near production.
+1 - You hadn't posted your edit when I wrote my answer! :)
@Matt Whitfield I am sorry, I did not see your answer there yet when I was editing mine. I figured that the problem in the code is only because **myDataReader.GetValue(1).ToString();** is erroneously used instead of doing desired **(SqlGeography)myDataReader.GetValue(1)** or the kinder version of it in the form of **myDataReader.GetValue(1) as SqlGeography** which I suggested :(
Don't be sorry - that's the name of the game :) In any case, the code presented isn't complete :)
Add a reference to `Microsoft.SqlServer.Types` in your project, then use SqlDataReader.GetValue() and cast: using (SqlDataReader sdr = sqlCommand.ExecuteQuery()) { while (sdr.Read()) { object o = sdr.GetValue(0); if (o is SqlGeography) { SqlGeography geog = (SqlGeography)o; } } } Edit -> Also - another point. SqlConnection, SqlCommand and SqlDataReader are all IDisposable classes. You should wrap them in using () in order to make sure that Dispose is called on them when they go out of scope. If you can't do that for whatever reason (async or threaded code most likely), then implement IDisposable in the class where you use them, and make sure that when you consume the class you use using () around it.
@Matt Whitfield Reference was already there. The only problem with the code was that it was using equivalent of
SqlGeography geog = o.ToString(); --instead of SqlGeography geog = (SqlGeography)o; -- or someling like what I suggested SqlGeography geog = o as SqlGeography;The latter is a bit safer as if you hard cast
Apple a = (Apple)orange; --you get runtime exception, but if you soft cast Apple a = orange as Apple;then you get **a is null** but no error, that is until you try to use it still avoiding the code blowup with polite if (a != null) a.Color = "Red";
@Oleg - yeah, that's why I included the `if (o is SqlGeography)` check first. I prefer using is around the code block because then the casted object only exists in the scope of the code block where you have verified that it is of the correct type. Basically because it limits my ability to mess up. But, whether I was using `as` or a hard cast, I would always use an `is` check first to limit scope.
@Matt Whitfield Yeah, I see. This makes my comment embarassingly retarded. Sorry about that. In my defence, I seem not to be able to see straight today due to lack of good sleep last night (happens to people who already reached an advanced age of 42 though). I am out to get one more cup of coffee :)
@Oleg - I wouldn't worry about it - I'm having a slow week by all accounts -
http://ask.sqlservercentral.com/questions/18524/converting-gregorian-date-value-to-hijri-date and
http://ask.sqlservercentral.com/questions/18564/elements-and-attributes-when-using-for-xml-explicit are both pretty crap answers. I won't delete them though, because that seems a bit sneaky!
@Matt Whitfield Not really. There is no way to produce a good answer to the first one because of the differences in Kuwaiti and Saudi calendar flavours. This would be like asking to correctly calculate date in Julian calendar still used by the Greek and Russian Orthodoxes (possible but useless exercise). It uses a silly assumption that every year has 356 days except for the leap years which occur once in 4 years when there are 366 days. This is just plain wrong, i.e. Orthodoxes consider year 1900 as a leap year, but surely there was no February 29 1900. Same applies to 2100 (not going to be leap year either), they believe that this one would be a leap year as well (and get behind the real calendar by yet another day on the top of 13 they are already behind). As far as the **xml explicit** is concerned, there is a very good reason why the queries which use it are called **SQL from hell**. I am sure that this thing will slowly die on its own.
Thank you everyone. I can not use geog.lat in my select. Geog field contains polygon data which includes many points. I need to be able to read the column data into a geography type so that I can iterate through the points to pull each lat and long geog.lat(i), geog.long(i). Thank you for the suggestion of wrapping it with using statements. This is just development code. Only using this code to pull the data into an app where I can create the lat/longs from the polygon. This is not going to be used by anyone else and not in production. Will however update to do so. I will try casting it and see how that works. Appreciate all the helpful hints.
