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!
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/