数据表值不匹配使用SqlGeography和c#
本文关键字:SqlGeography 不匹配 数据表 | 更新日期: 2023-09-27 18:16:23
所以,下面的代码给了我一个相当令人困惑的异常。
public void BuildTable()
{
using (SqlConnection connection = new SqlConnection("Data Source=ylkx1ic1so.database.windows.net;Initial Catalog=hackathon;Persist Security Info=True;User ID=REDACTED;Password=REDACTED"))
{
SqlGeographyBuilder builder = createTestPoint();
SqlGeography myGeography = builder.ConstructedGeography;
connection.Open();
DataTable myTable = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM myTable", connection))
{
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
adapter.Fill(myTable);
myTable.Rows.Add(6, "Test", myGeography);
adapter.Update(myTable);
}
}
}
private SqlGeographyBuilder createTestPoint()
{
SqlGeographyBuilder builder = new SqlGeographyBuilder();
builder.SetSrid(4326);
builder.BeginGeography(OpenGisGeographyType.Point);
builder.BeginFigure(31, -85);
builder.EndFigure();
builder.EndGeography();
return builder;
}
行myTable.Rows。Add(6, "Test", myGeography);是我得到以下异常的地方:
Type of value has a mismatch with column typeCouldn't store <POINT (-85 31)> in placemark Column. Expected type is SqlGeography.
我不明白的是为什么这会失败。在调试时,我尝试了这个:
for (int i = 0; i < myTable.Columns.Count; i++)
{
Debug.WriteLine(myTable.Columns[i].DataType);
}
Debug.WriteLine(myGeography.GetType());
我的输出是:
System.Int32
System.String
Microsoft.SqlServer.Types.SqlGeography
Microsoft.SqlServer.Types.SqlGeography
因此,我显然试图将SqlGeography对象放在接受SqlGeography对象的列中
try with direct insert
string sqlCommandText = "insert into myTable(col1,col2,col3) Values(@col1,@col2,@col3)";
SqlCommand sqlCommand = new SqlCommand(sqlCommandText, connection);
sqlCommand.Parameters.AddWithValue("@col1", 6);
sqlCommand.Parameters.AddWithValue("@col2", "Test");
sqlCommand.Parameters.Add(new SqlParameter("@col3", myGeography) { UdtTypeName = "Geography" });
sqlCommand.ExecuteNonQuery();
这个问题很老了,但是这个答案可以帮助未来的用户:
我发现不匹配是由于不同的SqlGeography类版本。使用正确版本的Microsoft.SqlServer.Types.dll程序集可以解决此问题。
我将dll更改为2009.100版本(适用于SQL Server 2008R2 SDK)用于。net 4的应用程序,但我正在连接到SQL Server 2014。