c# - Passing multiselect selected items into SQL Server 2008 -

i can't seem find example of online. want pass of values of multiselect listbox sql stored procedure write them table.

i know if want pass 1 selected value sql this:

c#:

using (sqlcommand cmd = new sqlcommand("sp_registeruser", conn)) {     cmd.commandtype = commandtype.storedprocedure;     using (sqldataadapter da = new sqldataadapter(cmd))         cmd.parameters.add("@userpreferences", sqldbtype.char).value = matchprefs.selectedvalue;         cmd.parameters.add("@username", sqldbtype.char).value = username.text;      conn.open();     cmd.executenonquery(); 

sql:

alter procedure [dbo].[sp_registeruser]     @userpreferences varchar(max),     @username varchar(25)  begin  insert tmpusermatch (     userid,     matchfield,     matchvalue      ) values (     (select top 1 userid tmpusers username = @username),     'matchpreferences',     @userpreferences     )  end      

but how if matchprefs multiselect?

edit

i have c# part down, replacing:

cmd.parameters.add("@userpreferences", sqldbtype.char).value = matchprefs.selectedvalue; 

with:

var selectedinterests = matchprefs.items.cast<listitem>().where(item => item.selected).select(item => item.value).tolist(); string strinterests = string.join(",", selectedinterests).trimend(); cmd.parameters.add("@userpreferences", sqldbtype.char).value = strinterests; 

and on sql end have function create table-valued parameter:

select * dbo.csvtotable(@userpreferences) 

just need completing sql side 2 additional fields need include; userid , matchfield.

1. don't prefix procs sp_ (this hurts performance causes sql server in [master] db first)
2. use sqldbtype.varchar
3. specify max length (-1 max, highly doubt need 8000 characters)
4. input param definition @userpreferences should have matching max length (again, no reason use max)
5. don't pass in @username can up. pass in @userid instead. beforehand , store in session or viewstate if use on several pages, else when load initial page.
6. why instantiate dataadapter ?
7. your sql split function doesn't create table-valued parameter. returns result set.

insert schemaname.tmpusermatch (     userid,     matchfield,     matchvalue ) select     @userid,     'matchpreferences',     csv.splitval  dbo.splitcsv(@userpreferences) csv;