具有C#抛出错误的CLR存储过程

具有C#抛出错误的CLR存储过程

问题描述:

我正在使用C#制作CLR存储过程,为此我将通过示例进行学习.

Hi I am working on making a CLR stored procedure using C#, for which I am learning through examples.

下面是我现在正在尝试的

Below is what I am trying now

public static void GetProductsByPrice(int price)
{
    SqlConnection connection = new SqlConnection("context connection=true");
    connection.Open();

    string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();

    SqlCommand command = new SqlCommand(commandText, connection);
    SqlDataReader reader = command.ExecuteReader();

    // Create the record and specify the metadata for the columns.
    SqlDataRecord record = new SqlDataRecord(
        new SqlMetaData("col1", SqlDbType.NVarChar, 100),
        new SqlMetaData("col2", SqlDbType.NVarChar, 100));

    // Mark the begining of the result-set.
    SqlContext.Pipe.SendResultsStart(record);

    // Send 10 rows back to the client. 
    while (reader.Read())
    {
        // Set values for each column in the row.
        record.SetString(0, reader[1].ToString()); //productName
        record.SetString(1, reader[2].ToString()); // productDescription
        // Send the row back to the client.
        SqlContext.Pipe.SendResultsRow(record);
    }

    // Mark the end of the result-set.
    SqlContext.Pipe.SendResultsEnd();
}

但是当我尝试运行它时,出现以下错误

But when I try to run it I get the below errors

消息6549,级别16,状态1,过程GetProductsByPrice,第0行
在执行用户定义的例程或聚合'GetProductsByPrice'期间发生.NET Framework错误:
System.Data.SqlClient.SqlException:SQL Server不支持区域设置标识符(LCID)16393.
System.Data.SqlClient.SqlException:
在Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(布尔值ignoreNonFatalMessages)
在Microsoft.SqlServer.Server.SqlPipe.SendResultsStart(SqlDataRecord记录)
在StoredProcedures.GetProductsByPrice(Int32价格)
用户交易(如果有的话)将被回滚.

我指的是这篇 msdn文章获取代码.

I am referring to this msdn article for the code.

请帮助我.

异常指出: SQL不支持语言环境标识符(LCID)16393

SqlMetaData.LocaleId 属性包含区域设置ID的列或参数,其中默认值是当前线程的当前语言环境.

The SqlMetaData.LocaleId property contains the locale ID of the column or parameter where the default value is the current locale of the current thread.

在这种情况下,默认值为 16393 ,它是英语-印度语言环境(

The default value in this your case 16393 which is the English - India locale (see table) but it seems your SQL server was installed with a different locale English - United States

因此,您有三个选择:

  1. 配置/重新安装SQL Server以使用语言环境英语-印度
  2. 将当前线程的语言环境更改为SQL Server支持的本地语言
  3. 在创建 SqlMetaData 时手动指定语言环境:

  1. Configure/ReInstall your SQL server to use the locale English - India
  2. Change the locale of your current thread to a local which is supported by SQL server
  3. Specify the locale manually when creating the SqlMetaData:

 SqlDataRecord record = new SqlDataRecord(
  new SqlMetaData("col1", SqlDbType.NVarChar, 1033, SqlCompareOptions.None),
  new SqlMetaData("col2", SqlDbType.NVarChar, 1033, SqlCompareOptions.None));

其中1033是英语-美国