Sending a DataTable as a parameter to a stored procedure

Question

I am trying to send a DataTable to a stored procedure using C #,. Net 2.0 and SQLServer 2012 Express.

This is roughly what I am 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 further study (in MSDN, SO and other places) it looks as if .net 2.0 does not support sending DataTable to the database (such things are missing like SqlParameter.TypeName ), but I'm still not sure, since I have not seen so that anyone explicitly states that this feature is not available in .net 2.0

It's true?

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

Thanks in advance!

Answer 1

From the outside, ADO.NET does not support this for a good reason. A DataTable can accept any number of columns that may or may not be mapped to a real table in your database.

If I understand what you want to do - quickly load the contents of the DataTable into a predefined real table with the same structure, I suggest you examine SQLBulkCopy .

From the documentation:

Microsoft SQL Server includes a popular command line utility called bcp to move data from one table to another, whether on the same server or between servers. The SqlBulkCopy class allows you to write which 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 if the data can be loaded into a DataTable instance or read with an IDataReader instance.

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

However, you can define table value parameters in SQL Server in later versions and use this to send a table (DateTable) in the requested method. Example: http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/

Answer 2

In my experience, if you can compile the code in C #, it means that the type of support is ADO.Net. But if this fails when executing the code, then the target database may not support it. In your case, you mention [Sql Server 2012 Express], so it may not support it. The table type was supported with [Sql Server 2005] in my understanding, but you should have supported database compatibility mode greater than 99 or something else. I am 100% sure that it will work in 2008, because I used it and widely used it for mass updates via stored procedures, using [ User Defined Table Types ] (aka UDTT) as a parameter for the stored procedure. Again, you must maintain database compatibility greater than 99 in order to use the MERGE command for bulk updates.

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