Convert nvarchar to geography data type in MSSQL

问题:

I am getting location data from the browser in ASP.NET and trying to insert it into SQL as a geography data type, is there a way to convert this in TSQL so I can just pass it as NVarchar?

回答1:

Pass a Well-Know Text,(WKT) from your code and then you can use STGeomFromText method in SQL Server like:

SELECT geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)

In C# you can do:

string WKTRepresentation = "LINESTRING (100 100, 20 180, 180 180)";
using (SqlConnection conn = new SqlConnection("connectionString"))
using (SqlCommand cmd = new SqlCommand("SELECT geometry::STGeomFromText(@WKT, 0)", conn))
{
    conn.Open();
    cmd.Parameters.Add(new SqlParameter("@WKT", SqlDbType.NVarChar) { Value = WKTRepresentation });
    var reader = cmd.ExecuteReader();
}