List<int> to database

[Jeff]

hi

asp.net 3.5

In my code I have this variable:
List<int> values = new List<int>();

I wonder how to trasfer this value to the database. That generic list
contain Id's I want to insert into a table, Each item in the generic list
represent a row in the database... so if generic list contains 3 items, then
3 rows will be created...

But I'm not sure how to transfer this generic list over to the database, so
far I've thought about these solutions:
- have a loop in my code which calls the stored procedure for each item in
the generic list,
- conver the generic list into a string, then pass the string.. but in the
stored procedure I would have to process the string again to get the Id
(with process the string I mean that the string contains for example
value like "2, 3, 4", I my stored procecdure code need to get the indivdual
id inside this string,)


any ideas

 

[Cowboy \(Gregory A. Beamer\)]

What I hear you asking:

Should I loop in my code or loop in SQL Server?

If you send in a string, to SQL Server, you will end up having to loop
there, so which do you feel more comfortable with. I guess you could use
sp_execute as a different option, but it is not optimized in SQL Server, so
it better be small loads you are working with. But, if you are working
small, there is no issue with looping.

Then again, you could circumvent the sproc altogether and assemble your own
SQL with the inserts. This would fare better on the SQL Server side than
sp_execute, as far as optimization. You could also batch the entire thing
this way.

If you want a "one shot" process, you could create an XML doc, as it can be
ripped, but you need to be on at least SQL 2005 to have any sort of
optimization around this process.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

 

[Cowboy \(Gregory A. Beamer\)]

What I hear you asking:

Should I loop in my code or loop in SQL Server?

If you send in a string, to SQL Server, you will end up having to loop
there, so which do you feel more comfortable with. I guess you could use
sp_execute as a different option, but it is not optimized in SQL Server, so
it better be small loads you are working with. But, if you are working
small, there is no issue with looping.

Then again, you could circumvent the sproc altogether and assemble your own
SQL with the inserts. This would fare better on the SQL Server side than
sp_execute, as far as optimization. You could also batch the entire thing
this way.

If you want a "one shot" process, you could create an XML doc, as it can be
ripped, but you need to be on at least SQL 2005 to have any sort of
optimization around this process.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

 

[sloan]

///////

I would certainly go with looping. With web apps, many small actions are
(almost) always better than one big action...
////////

Sometimes it depends. If you're inserting 100 rows or something, a bulk
insert can help performance.
Specifically, if there are several indexes on that table. If you do them 1
by 1, then you have pay the index penalty 100 times.
If you bulk insert, you pay the index penalty 1 time.

.............

http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html
Xml Shredding in Sql Server 2005.

Sql Server 2008 DataTables
http://blog.benhall.me.uk/2007/07/sql-server-2008-table-value-parameters.html

Those are some ways to send down N number of values to Sql Server for
processing.

 

[sloan]

///////

I would certainly go with looping. With web apps, many small actions are
(almost) always better than one big action...
////////

Sometimes it depends. If you're inserting 100 rows or something, a bulk
insert can help performance.
Specifically, if there are several indexes on that table. If you do them 1
by 1, then you have pay the index penalty 100 times.
If you bulk insert, you pay the index penalty 1 time.

.............

http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html
Xml Shredding in Sql Server 2005.

Sql Server 2008 DataTables
http://blog.benhall.me.uk/2007/07/sql-server-2008-table-value-parameters.html

Those are some ways to send down N number of values to Sql Server for
processing.