在ASP.NET中对几何图形SQL命令C#进行参数化，无法正常工作 [英] Parametrizing geometry sql command c# in ASP.NET, not working

问题描述

我已经使用了该字符串并通过字符串连接对其进行了测试.但是，正如您所知，使用此字符串格式化sql命令并不安全.

I have used this string and tested it with string concatenation.But as you know it is not safe to use this to format an sql command.

 SqlCommand param = new SqlCommand();
        param.CommandText = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (geometry::STGeomFromText('POINT(@center_lat @center_lng)',0),geometry::STGeomFromText('POLYGON((@polygon))',0));";
        param.Parameters.Add(new SqlParameter("@center_lat", center_lat));
        param.Parameters.Add(new SqlParameter("@center_lng", center_lng));
        param.Parameters.Add(new SqlParameter("@polygon", polygon));

我去参数化字符串并得到以下错误:

I go to parametrize the string and get the following error:

System.Data.SqlClient.SqlException(0x80131904):一个.NET Framework在执行用户定义的例程或聚合过程中发生错误"geometry":System.FormatException:24141:预期有一个数字输入的位置17.输入的内容为@center_lat.

System.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24141: A number is expected at position 17 of the input. The input has @center_lat.

好像没有将值放入字符串中.但是当我单步执行代码时，它的确包含了值.

Looks like it hasn't put the value into the string. but when I step through the code it does indeed hold the value.

可能是什么问题?

谢谢

推荐答案

感谢 Me.Name .我必须将正确的程序集添加到ASP中.net项目，这使我能够正确设置UDT类型.更新的代码如下.

Thanks to Me.Name. I had to add the correct assemblies to the ASP.net project, which enabled me to set the UDT type correctly. Updated Code is below.

SqlCommand param = new SqlCommand();
        SqlGeometry point = SqlGeometry.Point(center_lat,center_lng,0);
        SqlGeometry poly = SqlGeometry.STPolyFromText(new SqlChars(new SqlString(polygon)),0);
        param.CommandText = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (@point,@poly);";
        param.Parameters.Add(new SqlParameter("@point", SqlDbType.Udt));
        param.Parameters.Add(new SqlParameter("@poly", SqlDbType.Udt));
        param.Parameters["@point"].UdtTypeName = "geometry";
        param.Parameters["@poly"].UdtTypeName = "geometry";
        param.Parameters["@point"].Value = point;
        param.Parameters["@poly"].Value = poly;

这篇关于在ASP.NET中对几何图形SQL命令C#进行参数化，无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！