How to Pass User Defined Table Type as Stored Procedured Parameter in C#

How to pass User Defined Table Type as Stored Procedured parameter in C#

You need to see this example on CodeProject.

SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt); 

where dt is a DataTable,
and the @fileDetails parameter is a table type in SQL:

create type FileDetailsType as table
(
    FileName        varchar(50),
    CreatedDate        varchar(50),
    Size       decimal(18,0)
)

Edit:
This MSDN Developer's Guide article also would help.

Create a user defined table type in c# to use in sql server stored procedure

Simplest option is to create a DataTable in C# code and pass it as a parameter to your procedure. Assuming that you have created a User Defined Table Type as:

CREATE TYPE [dbo].[userdefinedtabletype] AS TABLE(
    [ID] [varchar](255) NULL,
    [Name] [varchar](255) NULL
)

then in your C# code you would do:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof (string));
dt.Columns.Add("Name", typeof (string));
//populate your Datatable

SqlParameter param = new SqlParameter("@userdefinedtabletypeparameter", SqlDbType.Structured)
{
    TypeName = "dbo.userdefinedtabletype",
    Value = dt
};
sqlComm.Parameters.Add(param);

Remember to specify SqlDbType.Structured as the type of parameter and specify the name you have used in creating your UDT.

EF Core execute procedure with many User-Defined Table Type

You are passing the parameters as a List<SqlParameter>, whereas FromSqlRaw expects a SqlParameter[] array (or variable length params). So you need to convert it to an array.

Also, you have three parameters and are only passing one, and ideally you should pass by name

FromSqlRaw(@"
EXEC dbo.GetData
  @id_zmiennej = {0},
  @lata = {1},
  @okresy = {2};
", parameters.ToArray<object>())

Another issue: you should set Direction on the TVP parameters

        SqlParameter parameter = new SqlParameter("@lata", dt)
        {
            SqlDbType = SqlDbType.Structured,
            TypeName = "[dbo].[TabIntType]",
            Direction = ParameterDirection.Input,
        };

Pass table value type to SQL Server stored procedure via Entity Framework

Lets say you want to send a table with a single column of GUIDs.

First we need to create a structure using SqlMetaData which represents the schema of the table (columns).

The below code demonstrates one column named "Id" of the GUID is the SQL stored procedure parameter table type

var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

Next you create a list of records that match the schema using SqlDataRecord.

The below code demonstrates how to add the items inside a list using the above created schema. Create a new SqlDataRecord for each of the items in the list. Replace SetGuid with the corresponding type and Replace Guid.NewGuid() as the corresponding value.
Repeat new SqlDataRecord for each item and add them to a List

var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

Then create the SqlParameter:

var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds"; //@UserIds is the stored procedure parameter name
parameter.TypeName = "{Your stored procedure type name}"
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

Then simply call the stored procedure by using the Database.SqlQuery.

IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers @UserIds", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):

CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):

CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

Call stored procedure with user-defined table type as parameter; error: column name or number of supplied values does not match table definition

Here is a simplified version of your code (changed so it can run stand alone):

        string ConnectionString = "cs"; // set your connection string here

        var dt = new DataTable();
        dt.Columns.Add(new DataColumn("DataColumnName", typeof(string)));

        var dataRow = dt.NewRow();
        dataRow["DataColumnName"] = "col1";
        dt.Rows.Add(dataRow);

        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("[dbo].[GetQuoteSearchData]", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter cob = cmd.Parameters.AddWithValue("@cob", "aa");
            cob.SqlDbType = SqlDbType.VarChar;
            cob.Direction = ParameterDirection.Input;

            SqlParameter function = cmd.Parameters.AddWithValue("@function", "bb");
            function.SqlDbType = SqlDbType.VarChar;
            function.Direction = ParameterDirection.Input;

            SqlParameter searchColumnName = cmd.Parameters.AddWithValue("@searchColumnName", "cc");
            searchColumnName.SqlDbType = SqlDbType.VarChar;
            searchColumnName.Direction = ParameterDirection.Input;

            SqlParameter controlValues = cmd.Parameters.AddWithValue("@controlValues", dt);
            controlValues.Direction = ParameterDirection.Input;
            controlValues.SqlDbType = SqlDbType.Structured;
            controlValues.TypeName = "[dbo].[ControlValuesTable]";

            conn.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            conn.Close();
        }

I also added a line to the body of your stored proc, as the stored proc you posted will give an error like:

Incorrect syntax near 'END'.

without it.

drop proc [dbo].[GetQuoteSearchData]
GO
drop type dbo.ControlValuesTable
GO
CREATE TYPE dbo.ControlValuesTable AS 
    TABLE
    (
        DataColumnName varchar(100) 
    );
GO

CREATE PROCEDURE [dbo].[GetQuoteSearchData]
    @cob nvarchar(50),
    @function nvarchar(50),
    @searchColumnName nvarchar(50),
    @controlValues as [dbo].[ControlValuesTable] READONLY
AS
BEGIN
 select * from @controlValues
END
GO

This ran ok on my system.
If you still can't get it to work, perform an sql profiler trace to capture the raw sql request being sent to Sqlserver, and try replaying the request in SSMS.

e.g. the request trace should look something like:

declare @p4 dbo.ControlValuesTable
insert into @p4 values(N'col1')

exec [dbo].[GetQuoteSearchData] @cob='aa',@function='bb',@searchColumnName='cc',@controlValues=@p4

which when replayed in SSMS returns a row.

As a side note, I think you should also be able to remove this line:

controlValues.TypeName = "[dbo].[ControlValuesTable]";  

as it's not required when CommandType = CommandType.StoredProcedure.

SQL/C#: DataTable to stored procedure (INSERT from user-defined table type) - Converting error

To pass a null value, you need to set the column value as DBNull.Value. You can set DBNull like this

dt.Rows[dtRow][dtCol] = currentRow.Cell(tl_Col + dtCol).Value ?? (object)DBNull.Value;

You must also set SqlDBType TypeName and Direction properties

cmd.Parameters.Add(
    new SqlParameter("@trips", SqlDBType.Structured)
    {
        TypeName = "dbo.udtTripsPerMonth",
        Direction = ParameterDirection.Input,
        Value = dt
    });

using user defined tables in SQL Stored Procedure - invalid data type

In the context of T-SQL your code is valid. The type can be created and the procedure, too.

Then, in the C# code use the following:

DataTable dt = new DataTable();
dt.Columns.Add("Product Item", typeof (string));
//add other columns here

SqlParameter param = new SqlParameter("@uploadedTable", SqlDbType.Structured)
{
    TypeName = "dbo.userdefinedtabletype",
    Value = dt
};
sqlComm.Parameters.Add(param);

---

Related Topics