既然没有 Sqlserver 数组参数，那么最好的方法是什么? [英] Since there is no Sqlserver array parameter, what's the best way to proceed?

问题描述

我需要在 sqlserver 中创建多条记录，每条记录在 A 列中具有相同的值，但在 B 列中具有唯一值.我在数组中有 B 列的值.

I need to create multiple records in sqlserver, each with the same value in column A, but with a unique value in column B. I have the values for column B in an array.

我使用的是 VS2008、aspnet、c# 3.5、sqlserver 2005.

I am using VS2008, aspnet, c# 3.5, sqlserver 2005.

我过得更好吗

选项 1.

从c#代码对sqlserver中的存储过程进行1次调用，然后在tsql中完成存储过程中的所有处理工作?

Making 1 call to a stored procedure in sqlserver from c# code, and then doing all the processing work in the stored procedure in tsql?

这将涉及将 c# 数组中的所有值组合成一个逗号分隔的字符串，并将该字符串作为参数传递给 tsql，然后循环并将字符串拆分为单独的值并为每个值插入一条记录，所有这些都在一个存储过程.

This would involve combining all the values in the c# array into one comma delimited string and passing the string to tsql as a parameter, then looping and breaking the string apart into individual values and inserting a record for each one, all within a stored procedure.

据我所知，这将涉及在必要时轻松回滚，但在 tsql 中处理非常笨拙的字符串.

From what I can see, this would involve easy rollback if necessary, but very clumsy string processing in tsql.

或者

选项 2.

在 c# 中进行循环并将数据作为 sqlparams 从 c# 一次一条记录传递到存储过程以插入每条记录.

Doing the looping in c# and passing the data as sqlparams from c# one record at a time to a stored proc to insert each record.

即，foreach (int key in myarray) ...插入一条记录

Ie, foreach ( int key in myarray) … insert a record

我可以在睡眠中执行此代码，但是如果在处理过程中发生某些事情，我将如何回滚?我应该在单个 connection.open 和 connection.close 中进行循环吗?

I could do this code in my sleep, but how would I be able to rollback if something happened in the middle of processing? And should I do the looping within in a singe connection.open and connection.close?

有没有其他人可以这样做?

Anyone have any other options for doing this?

推荐答案

此处广泛涵盖了此主题:SQL 2005 中的数组和列表

this topic is extensively covered here: Arrays and lists in SQL 2005

这篇关于既然没有 Sqlserver 数组参数，那么最好的方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！