inerting/updating a collection of data values into SQL Server db all at once.

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

Assuming that you have the list as a joined string array, you could something like this with the following function I once wrote:

CREATE FUNCTION dbo.Split

(

@.String VARCHAR(200),

@.Delimiter VARCHAR(5)

)

RETURNS @.SplittedValues TABLE

(

OccurenceId SMALLINT IDENTITY(1,1),

SplitValue VARCHAR(200)

)

AS

BEGIN

DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0

BEGIN

SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN

LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues

SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)

END

RETURN

END

So this would evaluate in your case to:

Set @.ListOfIDs = '1, 2, 3, 4, 5'

INSERT INTO SomeTable
(Column)
Select SplitValue From dbo.Split(@.ListOfIDs,',')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I thought that SQL Server 2005 allowed the insertion of VS 2005 DataTables etc. no?|||

MarilynJ wrote:

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

fill the dataset from data from sql server

update on the front end. vs2005 is using twoway binding

so there's not much work to be done

then

call the tableadapter update method

to commit chages to the database as a batch.

this is known as batch update

|||And remember that i you are working completly disconnected from the database that you have to specify your own Commands to update / insert / delete the data. Otherwise you could use the commandbuilder which will get you the appropiate commands if you read the schema from the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.desql