Update Command to update multiple rows in database from ComboBox Selected Value

Question

I know there is a lot of information out there regarding using SQL adapter to update multiple rows in the database from a Datagrid but I can't seem to find a solution after trying many different approaches.

I have a SelectionChange event that populates a comboxBox with the current row selected in the DataGrid and when the dropdown is expanded it shows the remaining items.

I also have a button_click event where I am trying to have the user select all of the necessary rows from the DataGrid and click a button to update those rows with the SelectedValue from the Combobox

Here is my ButtonClick event where I have been able to update the selected row but have not been able to achieve updating multiple rows. I can provide the code for my SelectionChange event and DataGrid if it's needed:

    private void butn_Assign_Click(object sender, RoutedEventArgs e)
    {

        try
        {
            SqlConnection connection = new SqlConnection("Data Source=WINDOWS-B1AT5HC\\SQLEXPRESS;Initial Catalog=CustomerRelations;Integrated Security=True;");
            connection.Open();

                int x;

                // Set the UPDATE command and parameters.
                sAdapter.UpdateCommand = new SqlCommand("UPDATE [hb_Disputes] SET ASSGNTO=@ASSGNTO WHERE DSP_ID=@DSP_ID;", connection);
                sAdapter.UpdateCommand.Parameters.Add("@DSP_ID", SqlDbType.Int, 500).Value = txt_ID.Text;
                sAdapter.UpdateCommand.Parameters.Add("@ASSGNTO", SqlDbType.NVarChar, 10).Value = cmb_AnalystName.SelectedValue;
                sAdapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.Both;

                // Execute the update.
                x = sAdapter.UpdateCommand.ExecuteNonQuery();

                if (x >= 1)
                {
                    MessageBox.Show("Dispute has been assigned");
                }


                connection.Close();

                //Update User's High Bill Dispute Count
                Window parentWindow = Window.GetWindow(this);
                ((MainWindow)parentWindow).HBD_Count();


                // Clear Search Fields
                cmb_AnalystName.SelectedIndex = -1;


                //Refresh DataGrid
                AssignList();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

Answer 1

Create new stored procedure for this and call this procedure from your code

Pass inputs like this @DSP_ID_CommaSeperated='1,2,4,5,6,8' , @ASSGNTO='20'

-- First way using Split Function

create proc Proc_Name
@DSP_ID_CommaSeperated varchar(max),
@ASSGNTO varchar(20)
as
begin


   UPDATE [hb_Disputes] SET ASSGNTO=@ASSGNTO WHERE DSP_ID in
 (
    select items from dbo.split(@DSP_ID_CommaSeperated )
  )

end

-- Second way using dynamic query

create proc Proc_Name
@DSP_ID_CommaSeperated varchar(max),
@ASSGNTO varchar(20)
as
begin


declare @Query nvarchar(max)='UPDATE [hb_Disputes] SET ASSGNTO=@ASSGNTO WHERE DSP_ID in('+@DSP_ID_CommaSeperated+')'

exec @Query

end