Passing multiple columns from c# to user defined table type in sql server

Question

I am trying to pass 2 values from c# to sql server that executes a stored procedure. Going through a user defined table type that looks like this:

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

and the start of my stored procedure looks like this

  CREATE    procedure [dbo].[indicator]

@id [dbo].[Ident] READONLY
  as

i am trying to pass the ids from tvp & tvp2 to sql server table type in the GetValue2 method. However i cant get it to work. I can get it to work when i pass one value but how do i get it to work for two? Any help would be great!

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;
    }

Your TVP is defined as having two columns, but you're only specifying one on your C# code. Further, "Not working" is not a helpful problem statement - be specific (is the result not expected, do you get an exception, etc.). — Ian Kemp - SO dead by AI greed
– Ian Kemp - SO dead by AI greed, Commented Sep 4, 2020 at 12:17
@IanKemp i get this error ''Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s).'' — Ryan Gadsdon
– Ryan Gadsdon, Commented Sep 4, 2020 at 13:23

Mauricio Ortega · Accepted Answer · 2020-09-04 13:51:09Z

Your method GetValue2 already works fine invoking your [dbo].[indicator] stored procedure. You must correct your main method to test it correcltly as follows to bypass the error "Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s)."

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));

}

Collectives™ on Stack Overflow

Passing multiple columns from c# to user defined table type in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related