来自C＃的表值参数 [英] Table Value Parameter from c#

问题描述

我已经阅读了许多文章，它们看起来都如下。当我从SSMS执行时，一切都很好。从c＃执行时，没有异常。当我检查表时，没有插入C＃。

I have read a number of articles and they all look like the following. When I execute from SSMS everything is fine. When I execute from c#, I get no exceptions. When I check the table, the c# did not insert.

CREATE TABLE dbo.Rates
    (Id BIGINT PRIMARY KEY IDENTITY, 
    LocationName NVARCHAR(50) NOT NULL, 
    CostRate INT NOT NULL);
GO

/* Create a table type. */
CREATE TYPE dbo.RatesType AS TABLE 
    ( LocationName NVARCHAR(50)
    , CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo.usp_InsertLocationRates
    @TVP RatesType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO Rates
            (LocationName
            , CostRate)
        SELECT LocationName, CostRate
        FROM  @TVP;
GO

INSERT INTO dbo.Rates
        (LocationName
        , CostRate)
VALUES ( N'Costa Rica', 38)

DECLARE @T as dbo.RatesType
INSERT INTO @T(LocationName, CostRate)
VALUES('Equador', 24)
EXEC usp_InsertLocationRates @T;

SELECT * FROM DBO.Rates

这里是不产生任何结果的c＃错误，没有结果。

Here is the c# that produces no error and no results.

try
{
    DataTable table = new DataTable("Items");
    table.Columns.Add("LocationName", typeof(string));
    table.Columns.Add("CostRate", typeof(int));    
    table.Rows.Add("Bali, Indonesia", 43);        

    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDB"].ConnectionString))
    {
        connection.Open();

        var insertCommand = new SqlCommand("usp_InsertLocationRates", connection);
        var tableValueParameter = insertCommand.Parameters.AddWithValue("@TVP", table);
        tableValueParameter.SqlDbType = SqlDbType.Structured;
        tableValueParameter.TypeName = "RatesType";

        insertCommand.ExecuteNonQuery();
        connection.Close();
    }
}
catch (Exception e)
{
    // No error exists!
}

先运行SQL然后再运行c＃之后的结果：

The results after running the SQL and then the c#:

Id  | LocationName  | CostRate
1   | Costa Rica    | 38
2   | Equador       | 24

推荐答案

默认 CommandType 是文本；您需要指定该命令用于存储过程：

The default CommandType is Text; you need to specify that the command is for a stored procedure:

insertCommand.CommandType = CommandType.StoredProcedure;

添加此代码后，您的代码对我有用。

After adding this your code works for me.

这篇关于来自C＃的表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！