DAAB and Table Valued Parameters -- supported??

Topics: Data Access Application Block
Feb 12, 2009 at 4:18 PM
Is it possible to call a stored proc using the DAAB and pass in a table valued param to the proc?  When setting a DbType in the AddInParameter method, I can't seem to find one that works.

db.AddInParameter(dbCommand, "@myTableVar", DBType.Object, myDataTable);

Does not seem to work.

Feb 13, 2009 at 4:46 AM

Is there any exception being thrown? would you mind posting your stored procedure?

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
Feb 13, 2009 at 12:05 PM

I don't believe the sproc code is relevant... and it is quite large... I will try to reproduce again with a simple sproc.

I believe the issue is with the datatype... if I use the DAAB, I have to pass in DbType.Object and the underlying SqlDbType is set to Variant string.... instead of SqlDbType.Structured.

Feb 13, 2009 at 12:09 PM
Yes. that's what i meant to say. can you provide a simple repro for this.

Feb 15, 2009 at 11:45 PM
            var dataTable = new DataTable();
            dataTable.Columns.Add("MyCol", typeof(System.Int32));
            DbCommand cmd = database.GetStoredProcCommand("MySp", dataTable);
            using (IDataReader reader = database.ExecuteReader(cmd))

The SP works with SqlConnection and SqlCommand but with DAAB I have the exception:
 The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 1 ("@MyTable"), 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."

Valiant, do you have working example for DAAB?

Feb 16, 2009 at 12:44 AM
The quick fix is to cast the Database to an SqlDatabase object.  The SqlDatabase AddInParameter method has overloads that accept SqlDbType instead of DbType and this allows you to pass the necessary SqlDbType.Structured.

I believe the problem is that Table Valued Parameters are not database agnostic and are not compatible with the Database class.

Feb 16, 2009 at 9:27 AM
leftyfarell, can you send me your sample solution?
Feb 17, 2009 at 6:40 PM
I mean doing something like this:

SqlDatabase database = (SqlDatabase)DatabaseFactory.CreateDatabase();  //cast to SqlDatabase object instead of default Database object

DataTable dt = GetPopulatedDataTable(); //input data to sproc

DbCommand dbCommand = database .GetStoredProcCommand("SaveUsers");
database .AddInParameter(dbCommand, "@userDataTable", SqlDbType.Structured, dt);  //here you can use SqlDbType enum because you've casted the database to a SqlDatabase

DataSet ds = database .ExecuteDataSet(dbCommand);

This of course ties you to a SQL Server database implementation instead of a more database agnostic design.
Feb 17, 2009 at 9:31 PM
Thank you leftyfarrell,

Of course it ties me to to a SQL Server database. I doubt about the migration to Oracle (heared that there are table-valued params in this db too) :-) as well as to any other DB. But even for Oracle you can implement such clutch theoretically :-)
Anyway DAAB code works with your work around:

            SqlDatabase db = (SqlDatabase) database;
            DbCommand cmd = database.GetStoredProcCommand(myCommandName);
            db.AddInParameter(cmd, "@TableParam", SqlDbType.Structured, dataTable);
            using (IDataReader reader = database.ExecuteReader(cmd))

Aug 4, 2009 at 8:25 AM

This only works if you are creating DbCommand from the GetStoredProcCommand method.

If you use the GetSqlStringCommand method, you need to provide the SqlParameter.TypeName.  However, EntLib 4.1 is not updated to support the new Sql Server 2008 datatypes.  The AddInParameter+AddParameter overloads do not allow you to pass in the TypeName.  It would be nice if there was an AddParameter overload that accepts just SqlParameter.

(I'm using GetSqlStringCommand because I am calling a scalar UDF.  The workaround is to wrap the UDF inside a SP.)

My guess is as to why you don't need to specify the SqlParameter.TypeName when using the GetStoredProcCommand is during the SP compilation, it resolves all the types used.  Meanwhile, GetSqlStringCommand causes an ad-hoc compilation and it needs help to determine what is the specific table type (as there can be more than one).


Aug 4, 2009 at 8:29 AM

As of changeset 56399, this has not addressed yet.

Feb 24, 2010 at 7:12 PM

I sound pretty confusing.

Basically, what I wanted to say is if you want to execute an ad hoc sql with a table valued parameter you need to specify the table type name using the SqlParameter.Type.

// Define the INSERT-SELECT statement.
string sqlInsert = 
    "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
    + " SELECT nc.CategoryID, nc.CategoryName"
    + " FROM @tvpNewCategories AS nc;"

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
    sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

http://msdn.microsoft.com/en-us/library/bb675163.aspx  (The last example.) 

 I had thought because of EntLib, this property (TypeName) is not accessible.  My bad.  The simplest and straight solution is to use the Command.Parameters collection to gain access the SqlParameter.  In order to access it thru EntLib, you need to inherit from Database and override the ConfigureParameter virtual method.