Database name is not allowed with a table-valued parameter

Question

I am getting following error when I call the Select function:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 3 ("@SearchTableVar"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid.

C# code

//DTO
public class SP_SearchEntity_Result
{
    public string ID { get; set; }
    public string NAME { get; set; }
}

//Businesslogic
public IQueryable Select(int PageIndex, int PageSize, List<KeyValuePair<string, string>> SearchBy, List<KeyValuePair<string, System.Data.SqlClient.SortOrder>> SortBy)
{
    SqlDatabase obj = (SqlDatabase)DatabaseFactory.CreateDatabase();//System.Configuration.ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString
    return obj.ExecuteSprocAccessor<SP_SearchEntity_Result>("SP_SearchEntity", PageIndex, PageSize, SearchBy.ToDataTable(), SortBy.ToDataTable()).AsQueryable<SP_SearchEntity_Result>();
}

//Extension methods
public static DataTable ToDataTable(this List<KeyValuePair<string, string>> source)
{
    DataTable dataTable = new DataTable("Test");
    dataTable.Columns.Add("KEY",typeof(System.String));
    dataTable.Columns.Add("VALUE", typeof(System.String));

    foreach (KeyValuePair<string, string> data in source)
    {
        var dr = dataTable.NewRow();
        dr["KEY"] = data.Key;
        dr["VALUE"] = data.Value;
        dataTable.Rows.Add(dr);
    }

    return dataTable;
}

public static DataTable ToDataTable(this List<KeyValuePair<string, System.Data.SqlClient.SortOrder>> source)
{
    DataTable dataTable = new DataTable("Test");
    dataTable.Columns.Add("KEY", typeof(System.String));
    dataTable.Columns.Add("VALUE", typeof(System.String));

    foreach (KeyValuePair<string, System.Data.SqlClient.SortOrder> data in source)
    {
        var dr = dataTable.NewRow();
        dr["KEY"] = data.Key;
        dr["VALUE"] = data.Value == System.Data.SqlClient.SortOrder.Ascending ? "ASC" : "DESC";
        dataTable.Rows.Add(dr);
    }

    return dataTable;
}

The stored procedure returns two tables in result

SQL proc definition

CREATE TYPE KeyValueTableVariable AS TABLE
(
    [KEY] NVARCHAR(800),
    [VALUE] NVARCHAR(800)
)
GO
CREATE PROCEDURE SP_SearchEntity
@PageIndex INT=NULL,      
@PageSize INT=NULL,     
@SearchTableVar dbo.KeyValueTableVariable READONLY,
@SortTableVar dbo.KeyValueTableVariable READONLY 
AS
BEGIN
    /*Bla bla bla*/
    SELECT '1' as [ID], 'Nitin' as [NAME]
    SELECT '1' as [COUNT]
END

Answer 1

There are a number of requirements/limitations for passing Table Valued parameters to SQL Server. See e.g. the example under "Passing a Table-Valued Parameter to a Stored Procedure":

The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured.

And note that just using AddWithValue is insufficient - the SqlDbType has to be changed to Structured.

I believe that the ExecuteSprocAccessor method isn't performing this change (or possibly, as in some other examples, where the TypeName has to be set to the name of the table type). I can't chase this all through the enterprise library source code, but since I can't find the word "Structured" anywhere in the solution, that's what leads me to this conclusion.

So, if you want to use TVPs, I think you have to abandon the Enterprise Library and write the data access code yourself using SqlClient types. (Since you're using TVPs, you're already abandoning the possibility of switching to a different RDBMS anyway).