由于没有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

1制作调用存储过程中的sqlserver从C＃code，然后做在存储过程中的所有处理工作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＃中循环，一次从C＃一个记录传递数据sqlparams到一个存储过程中插入每个记录。

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（在myarray的INT键）...插入一条记录

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

我能做到这一点code在我的睡眠，但如何将能够回滚如果事情在处理过程中发生了什么？而且我应该做的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屋！