Creating a GIS application in .NET - GIS Succinctly - page 95

}
private static List < string > GetCitysForCounty( string countyName)
{
string sql =
string .Format(
"SELECT t.Name FROM ukcountys c, ukcitys t WHERE name2 = :county AND
ST_Within(t.geometry,ST_Transform(c.the_geom,27700))" );
List < string > results = new List < string >();
using ( NpgsqlConnection conn = new NpgsqlConnection (_connString))
{
conn.Open();
using ( NpgsqlCommand command = new NpgsqlCommand (sql, conn))
{
command.Parameters.Add( new NpgsqlParameter ( "county" , NpgsqlDbType .Varchar));
command.Parameters[0].Value = countyName;
using ( NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
results.Add(dr.GetString(0));
}
}
}
}
return results;
}
Anyone who has done any raw ADO.NET programming should immediately recognize what
we are doing here, and may even quite reasonably ask why we're not using LINQ to SQL, or
Entity Framework, or…the list goes on.
In the first place, the raw Postgres data provider doesn't provide an Entity Framework or
LINQ-to-SQL data model provider. Secondly, since we're using spatial functions, it's far
better to do this in ADO.NET than in a model where the SQL-level syntax is deeply hidden
from view.