Is there a way to create a new table type and pass a data-table without a procedure?

Question

I'm using C# and SQL Server in Windows environment with Visual Studio 2017. I'm trying to pass a datatable (called @profiles) to the SQL script.

In order to do so, I first must create a type of a table that matches the data table passed.

Problem is that in every way I tried to populate a new table with the datatable passed I'm getting one of two exceptions:

1. "Column, parameter, or variable @profiles. : Cannot find data type ProfileIdTableType."

2. "The table type parameter '@profiles' must have a valid type name."

From what I searched I could find that a datatable with new table type is generally used with a procedure, but no matter - I still get the above exceptions.

I tried to declare a new table type and use the @profiles with it with no success.

When I declare the SqlParameter I'm using to pass it, I generally encounter the first exception (can't find the type)

I should mention that I can't find the created type in the "Programmability" section of SQL Server (but my type is temp and so it should be)

These are 2 ways I'm using to pass the datatable to the script from C#:

SqlParameter @profiles = new SqlParameter("@profiles", profileIds.Tables[0]);
profiles.TypeName = "ProfileIdTableType";

or:

DbParameter @profiles = new SqlParameter("@profiles", profileIds.Tables[0]);

and then use it:

updatedProfiles = (int)DbAdminOps.ExecuteNonQueryCommand(updateProfileSettingsCommand, CommandType.Text, new DbParameter[] { @profiles, @updatedTemplate }, null);

This is the SQL script i used last (but tried many variations not presented here)

    -- create a table type of profile Ids passed by user
    CREATE TYPE ProfileIdTableType AS TABLE (ID INT)
    go

    DECLARE @PRFL ProfileIdTableType
    GO

    CREATE PROCEDURE PopulateTable
        @profiles ProfileIdTableType READONLY
    AS 
        INSERT INTO @PRFL(ID) 
            SELECT [ID] FROM @profiles
    GO

    @profiles ProfileIdTableType
    EXEC PopulateTable @profiles
    go

I expected @profiles be recognized as a table so i can use it in my script but all i get really is exception. I put a lot of effort into it but just couldn't.

Went through all the stack overflow questions, youtubes, microsoft documentation and web.

If there's any information I left out and is important - let me know.

Would really appreciate some advice.

Cheers!

Answer 1

The key point is to specify SqlDbType as Structured plus to define TypeName as shown in the following snippet.

    comm.Parameters.AddWithValue("@tvpEmails", dt);
// EMAIL.TVP_Emails should exist on your SQL instance under UDDT types
     comm.Parameters[comm.Parameters.Count - 1].TypeName = "EMAIL.TVP_Emails";
     comm.Parameters[comm.Parameters.Count - 1].SqlDbType = SqlDbType.Structured;

See the complete code down below. Please let me know if you have any difficulties.

using System.Data;
using System.Data.SqlClient;
using System.Net.Mail;

namespace ConsoleApp10
{
    class Program
    {
        static void Main(string[] args)
        {
            var mm = new MailMessage();
            using (var conn = new SqlConnection("your connection string"))
            {
                using (var comm = new SqlCommand())
                {

                    comm.Connection = conn;
                    conn.Open();


                    comm.CommandText =
                        @"INSERT INTO [EMail].[MailAttachments] (fileName,fileSize,attachment)
                                             SELECT fileName, fileSize, attachment FROM @tvpEmails";


                    var dt = CreateTable();
                    foreach (var eml in mm.Attachments)
                    {
                        var newRow = dt.NewRow();
                        newRow["FileName"] = eml.Name;
                        newRow["FileSize"] = eml.ContentStream.Length;
                        var allBytes = new byte[eml.ContentStream.Length];
                        newRow["Attachment"] = allBytes;
                        eml.ContentStream.Position = 0;
                        dt.Rows.Add(newRow);
                    }

                    comm.Parameters.AddWithValue("@tvpEmails", dt);
                    comm.Parameters[comm.Parameters.Count - 1].TypeName = "EMAIL.TVP_Emails";
                    comm.Parameters[comm.Parameters.Count - 1].SqlDbType = SqlDbType.Structured;
                    comm.ExecuteNonQuery();
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }

            }

        }
        private static DataTable CreateTable()
        {
            var dt = new DataTable();
            dt.Columns.Add("FileName", typeof(string));
            dt.Columns.Add("FileSize", typeof(long));
            dt.Columns.Add("Attachment", typeof(byte[]));
            return dt;
        }

    }
}