发送一个DataTable作为参数传递给存储过程 [英] Sending a DataTable as a parameter to stored procedure

问题描述

我想用C＃.NET 2.0和SQLServer 2012特快一个DataTable发送到存储过程

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

在进一步调查（在MSDN，所以和其他地方），它看起来好像.NET 2.0不支持发送一个DataTable到数据库中（缺少的东西，如 SqlParameter.TypeName ），但我仍然不知道，因为我没有见过任何人明确声称，此功能不是在.NET 2.0

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

这是真的吗？

如果这样，有另一种方式来发送数据的集合到数据库？

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

在此先感谢！

推荐答案

开箱，ADO.NET不会有很好的理由询问服务这一点。的DataTable可能只是采取任何数量的列，这可能会或可能不映射到数据库中的一个真正的表。

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.

如果我理解你想要什么千万 - 快速上传一个DataTable的内容具有相同的结构预先定义的，真正的表，我建议你调查的 SqlBulkCopy的

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.

从文档：

Microsoft SQL Server的包括一个名为
BCP从一个表移动数据到另一个流行的命令提示符工具，无论是单一的
服务器上或服务器之间。 SqlBulkCopy类让你写
托管代码的解决方案，提供了类似的功能。有
等方式将数据加载到SQL Server表（INSERT语句，
为例），但SqlBulkCopy的对他们提供了一个显著的性能
的优势。

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.

SqlBulkCopy类可以用来只写到SQL Server
表中的数据。但是，数据源并不局限于SQL Server的;任何
数据源可以使用，只要该数据可以被加载到
DataTable实例或具有IDataReader的实例读

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将失败当批量装载类型
SQLDATETIME的数据表列到其类型是在SQL Server 2008中增加了
日期/时间类型之一的SQL服务器列。

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.

不过，您可以在更高版本的SQL Server定义表值参数，并用它来在你问的方法来发送表（DateTable）。有在的 http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/

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/

这篇关于发送一个DataTable作为参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！