在SQL Server 2008中处理来自c＃的多个db更新

I like to find a way to handle multiple updates to a sql db (with one singe db roundtrip). I read about table-valued parameters in SQL Server 2008 http://www.codeproject.com/KB/database/TableValueParameters.aspx which seems really useful. But it seems I need to create both a stored procedure and a table type to use it. Is that true? Perhaps due to security? I would like to run a text query simply like this:

我想找到一种方法来处理sql db的多个更新（使用一个单独的db往返）。我在SQL Server 2008 http://www.codeproject.com/KB/database/TableValueParameters.aspx中读到了表值参数，这看起来非常有用。但似乎我需要创建一个存储过程和一个表类型来使用它。真的吗？也许是出于安全考虑？我想像这样运行一个文本查询：

var sql = "INSERT INTO Note (UserId, note) SELECT * FROM @myDataTable";
var myDataTable = ... some System.Data.DataTable ...
var cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
var param = cmd.Parameters.Add("@myDataTable", System.Data.SqlDbType.Structured);
param.Value=myDataTable;
cmd.ExecuteNonQuery();

所以

A) do I have to create both a stored procedure and a table type to use TVP's? and
B) what alternative method is recommended to send multiple updates (and inserts) to SQL Server?

A）我是否必须创建存储过程和表类型才能使用TVP？和B）建议使用哪种替代方法向SQL Server发送多个更新（和插入）？

1 个解决方案

#1

Yes, you need to create the types.

是的，您需要创建类型。

Alternatives are sending a big string sql batch or passing XML to sprocs.

替代方案是发送大型字符串sql批处理或将XML传递给sprocs。

The downside to big sql string batches is it can blow the sql proc cache and might cause sql to recompile - especially if the batch is unique because of input data being part of that large string. By definition each batch would be unique.

大sql字符串批处理的缺点是它可以破坏sql proc缓存并可能导致sql重新编译 - 特别是如果批处理是唯一的，因为输入数据是该大字符串的一部分。根据定义，每个批次都是唯一的。

XML was the main alternative before TVPs. The one downside to XML, for at least awhile, sql azure didn't support it (that might change?) so it limits your options.

在TVP之前，XML是主要的替代方案。 XML的一个缺点，至少有一段时间，sql azure不支持它（可能会改变？）因此它限制了你的选择。

TVPs seem to be the way to do this. Our project just converted to using TVPs.

TVP似乎是这样做的方式。我们的项目刚刚转换为使用TVP。

Hope that helps.

希望有所帮助。

#1