Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
287 views
in Technique[技术] by (71.8m points)

c# - Since there is no Sqlserver array parameter, what's the best way to proceed?

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.

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

Am I better off

Option 1.

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?

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.

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

Or

Option 2.

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.

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...