SQL Server - Update rows based on a list

I'm working with Windows Forms - VB.NET.

Here's what I have:

• A ListView with checkboxes set to True
• A Button (triggers the update)
• A database table with similar fields as the ListView

What I want to happen:

• when the user clicks the Button, all items on the ListView with checkbox checked will be updated.

My progress:

• I've already collected the ID of the checked items and stored them in an array. I'll be using this to update the database table.

The problem:

• I don't know how to put them in the SqlCommand.Parameters
• Also, I don't know the updat开发者_JAVA百科e command for such scenario (where in/exist (@parameters))

Thanks in advance!

If you're using SQL Server 2008 or later, you can use table-valued parameters. These let you continue to deal with the separate IDs on separate rows, perform SQL joins, etc. There are plenty of examples on the page I've linked to, e.g.:

Using connection
  ' Create a DataTable with the modified rows.
  Dim addedCategories As DataTable = _
    CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Define the INSERT-SELECT statement.
  Dim sqlInsert As String = _
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _
  & " SELECT nc.CategoryID, nc.CategoryName" _
  & " FROM @tvpNewCategories AS nc;"

  ' Configure the command and parameter.
  Dim insertCommand As New SqlCommand(sqlInsert, connection)
  Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
    "@tvpNewCategories", addedCategories)
  tvpParam.SqlDbType = SqlDbType.Structured
  tvpParam.TypeName = "dbo.CategoryTableType"

  ' Execute the query
  insertCommand.ExecuteNonQuery()
End Using

You could easily replace that INSERT with an UPDATE, as shown earlier on the page:

UPDATE dbo.Categories
SET Categories.CategoryName = ec.CategoryName
FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
ON dbo.Categories.CategoryID = ec.CategoryID;

And adjust parameter names accordingly.

It seems you want to use a single call to the database, for that you would need to build a string with the command, I don't think you can pass a list as parameter to a command / sp.

The update command:

UPDATE [table] SET [field1] = value1, [field2] = value2
WHERE [ID] IN (id1, id2, ..., idN)