将多列从c#传递到sql server中的用户定义的表类型。

Question

我正在尝试从c#向执行存储过程的sql server传递2个值。查看用户定义的表类型，如下所示：

CREATE TYPE [dbo].[Ident] AS TABLE(
    [Id] [uniqueidentifier] NOT NULL,
    [Id2] [uniqueidentifier] NOT NULL
)
GO

我的存储过程的开始如下所示

  CREATE    procedure [dbo].[indicator]

@id [dbo].[Ident] READONLY
  as

我正在尝试将来自tvp & tvp2的in传递给GetValue2方法中的sql server表类型。然而，我不能让它工作。当我传递一个值时，我可以让它工作，但是如何让它对两个值起作用呢？任何帮助都是最好的！

static void Main(string[] args)
        {
            String connectionString = "........";           

        List<Guid> tempguid = new List<Guid>();
        tempguid.Add(Guid.Parse("guid...."));
        DataTable tvp = new DataTable();
        tvp.Columns.Add(new DataColumn("Id", typeof(Guid)));
        //populate DataTable from your List here
        foreach (var id in tempguid)
            tvp.Rows.Add(id);

        List<Guid> tempguidid2 = new List<Guid>();
        tempguid.Add(Guid.Parse("guid....."));
        DataTable tvp2 = new DataTable();
        tvp2.Columns.Add(new DataColumn("Id", typeof(Guid)));
        //populate DataTable from your List here
        foreach (var id in tempguid)
            tvp2.Rows.Add(id);

        Console.WriteLine(GetValue2(ref tvp, connectionString));




    }

    public static List<Guid> GetValue2(ref DataTable tvp, ref DataTable tvp2, String connectionString)
    {

        List<Guid> items = new List<Guid>();

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("[dbo].[indicator]", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter tvpParameter = new SqlParameter();
            tvpParameter.ParameterName = "@id";
            tvpParameter.SqlDbType = System.Data.SqlDbType.Structured;
            tvpParameter.Value = tvp;
            tvpParameter.TypeName = "[dbo].[Ident]";
            cmd.Parameters.Add(tvpParameter);

            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    Console.WriteLine((int)rdr["type"]);
                }
                Console.ReadLine();
            }
        }
        return items;
    }

Answer 1

在调用dbo.indicator存储过程时，GetValue2方法已经可以很好地工作了。您必须更正main方法以正确测试它，如下所示，以绕过错误“尝试传递具有1列的表值参数，其中相应的用户定义表类型需要2列”。

static void Main(string[] args) {
    String connectionString = "........"; //Replace with your specific connection string

    DataTable tvp = new DataTable(); //Creates the two columns Id,Id2 required to map to the database table type [dbo].[Ident]
    tvp.Columns.Add(new DataColumn("Id", typeof(Guid)));
    tvp.Columns.Add(new DataColumn("Id2", typeof(Guid)));

    //Just adding a row to datatable tvp
    var newRow = tvp.NewRow();
    newRow["Id"] = new Guid();  //or Guid.Parse("guid....") using a valid GUID string
    newRow["Id2"] = new Guid(); //or Guid.Parse("guid....") using a valid GUID string
    tvp.Rows.Add(newRow);

    DataTable tvp2 = new DataTable(); 
    // - tvp2 is declared just to call GetValue2 
    // - GetValue2 has a parameter tv2 that is not used
    Console.WriteLine(GetValue2(ref tvp, ref tvp2, csb.ConnectionString));

}