Structured types must have at least one field c#

Question

What I actually want to do is I want to send a string array to SQL Server. I am using SQL Server 2008.

This can be marked as duplicate but I am facing a whole other problem when implementing a solution from stactoverflow

LINK: How to pass an array into a SQL Server stored procedure

Here is my C# and stored procedure code

C# code:

string[] str = new string[] {"s" , "a" , "k"};

DataTable dt = new DataTable();
dt.Columns.Add("names");

foreach (string item in str)
{
    dt.Rows.Add(item);
}

foreach (DataRow r in dt.Rows)
{
    Console.WriteLine(r["names"].ToString());
}

DataTable tvp = new DataTable();

SqlConnection conn = new SqlConnection("Data Source=SHZAK;Initial Catalog=synchroniztionTesing;Integrated Security=True");
conn.Open();

using (conn)
{
    SqlCommand cmd = new SqlCommand("strpdPassAnStringArray", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;

    cmd.ExecuteScalar();
}

Before making stored procedure I created a custom type:

CREATE TYPE dbo.arrayOfNames
AS TABLE
(
  name  nvarchar(max)
);

Stored procedure code:

CREATE PROCEDURE [dbo].[strpdPassAnStringArray]
      @List AS dbo.arrayOfNames READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT name FROM @List; 
END

But in application code it is raising this exception:

There are not enough fields in the Structured type. Structured types must have at least one field.

on line

cmd.ExecuteScalar();

Piotrowski

Your DatTable tvp is empty with no data and thus no property, fill it with something... – Soup

@Soup you are right :) Thanks a bunch.. – Piotrowski

Answer 1

ANSWER:

I was so dumb to ask that Question , but at least now i know that what causes this exception

thanks to @Gusman

If your DataTable is empty as it was in my case this exception can be raised

        string[] str = new string[] {"sheraz" , "ahmed" , "khan"};
        DataTable tvp = new DataTable();


        tvp.Columns.Add("names");
        foreach (string item in str)
        {
            tvp.Rows.Add(item);
        }

        foreach (DataRow r in tvp.Rows)
        {
            Console.WriteLine(r["names"].ToString());
        }

        SqlConnection conn = new SqlConnection("Data Source=SHZAK;Initial Catalog=synchroniztionTesing;Integrated Security=True");
        conn.Open();

        using (conn)
        {
            SqlCommand cmd = new SqlCommand("strpdPassAnStringArray", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
            tvparam.SqlDbType = SqlDbType.Structured;
            cmd.ExecuteScalar();
        }

Piotrowski

I would ask @gusman to re-write his comment as an answer and accept it if I was in your place. – Gunderson

@ZoharPeled sure but only if he does.. Until then i think people need to know – Piotrowski