Sending a DataTable as a parameter to stored procedure

Question

I'm trying to send a DataTable to a stored procedure using c#, .net 2.0 and SQLServer 2012 Express.

This is roughly what I'm doing:

        //define the DataTable
        var accountIdTable = new DataTable("[dbo].[TypeAccountIdTable]");

        //define the column
        var dataColumn = new DataColumn {ColumnName = "[ID]", DataType = typeof (Guid)};

        //add column to dataTable
        accountIdTable.Columns.Add(dataColumn);

        //feed it with the unique contact ids
        foreach (var uniqueId in uniqueIds)
        {
            accountIdTable.Rows.Add(uniqueId);
        }

        using (var sqlCmd = new SqlCommand())
        {
            //define command details
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "[dbo].[msp_Get_Many_Profiles]";
            sqlCmd.Connection = dbConn; //an open database connection

            //define parameter
            var sqlParam = new SqlParameter();
            sqlParam.ParameterName = "@tvp_account_id_list";
            sqlParam.SqlDbType = SqlDbType.Structured;
            sqlParam.Value = accountIdTable;

            //add parameter to command
            sqlCmd.Parameters.Add(sqlParam);

            //execute procedure
            rResult = sqlCmd.ExecuteReader();

            //print results
            while (rResult.Read())
            {
                PrintRowData(rResult);
            }
        }

But then I get the following error:

ArgumentOutOfRangeException: No mapping exists from SqlDbType Structured to a known DbType.
Parameter name: SqlDbType

Upon investigating further (in MSDN, SO and other places) it appears as if .net 2.0 does not support sending a DataTable to the database (missing things such as SqlParameter.TypeName), but I'm still not sure since I haven't seen anyone explicitly claiming that this feature is not available in .net 2.0

Is this true?

If so, is there another way to send a collection of data to the database?

Thanks in advance!

I believe Table-value parameters were added in SQL Server 2008, so they're probably not available in .NET 2.0. Could you send XML instead and parse it from the stored procedure into a table variable? — lc., Jan 01 '13 at 16:43
Agreed with it being far from optimal, I'm just trying to think outside the box a little. In your case it seems like you are trying to send a bunch of GUIDs and since they're fixed size you might even be able to just line them up end-to-end in a varbinary and then split them back out into a table variable in a loop in the procedure. Not optimal for something more complex but it might work here. — lc., Jan 01 '13 at 17:22

score 1 · Accepted Answer · answered Jan 01 '13 at 16:44

Out of the box, ADO.NET does not suport this with good reason. A DataTable could take just about any number of columns, which may or may not map up to a real table in your database.

If I'm understanding what you want to do - upload the contents of a DataTable quickly to a pre-defined, real table with the same structure, I'd suggest you investigate SQLBulkCopy.

From the documentation:

Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008.

However, you can define Table Value Parameters in SQL Server in later versions, and use that to send a Table (DateTable) in the method you're asking. There's an example at http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/

I've created the type, but i can't seem to be able to send the DataTable from c#. — Asaf Sitner, Jan 01 '13 at 21:47
Well, I managed to be smarter on the DB side and achieve the effect I wanted without sending the DataTable. Apparently there's no way around it with .net 2, which is a shame, but that's life. Thanks for the help anyway! — Asaf Sitner, Jan 01 '13 at 22:31

score 0 · Answer 2 · answered Feb 02 '15 at 18:06

Per my experience, if you're able to compile the code in C# that means the ADO.Net support that type. But if it fails when you execute the code then the target database might not support it. In your case you mention the [Sql Server 2012 Express], so it might not support it. The Table Type was supported from [Sql Server 2005] per my understanding but you had to keep the database compatibility mode to greater than 99 or something. I am 100% positive it will work in 2008 because I have used it and using it extensively to do bulk updates through the stored procedures using [User Defined Table Types] (a.k.a UDTT) as the in-parameter for the stored procedure. Again you must keep the database compatibility greater than 99 to use MERGE command for bulk updates.

And of course you can use SQLBulkCopy but not sure how reliable it is, is depending on the

Sending a DataTable as a parameter to stored procedure

2 Answers2

Linked