C# SqlCommandBuilder , CommandUpdate - how to write correct update based on select with outer join tables

Question

I want is to update 2 fields: p.FlagaWaznosci and p.Notatka

My select looks like:

Select  DISTINCT p.id,p.Model_Number,p.Product_Name,p.Website_Link,p.Entry_date,p.LastUpdate_date,p.PrzydzialRozmiarow_ID,p.FlagaWaznosci,p.Notatka,pr.NazwaRozmiarowki,wd.LINK_StockX 
from Products p with(nolock)
left outer  join Widok_Model_Sklep_Stockx_Linki wd with(nolock) on wd.Product_ID = p.id 
left outer join PrzydzialRozmiarow pr with(nolock) on pr.id = p.PrzydzialRozmiarow_ID 
inner join Shops s with(nolock) on s.ID = p.Shop_ID 

There is just outer joins to get correct data that I need to be displayed in gridview. And now when values p.FlagaWaznosci or p.Notatka is changed I want to save update in my database.

I try to use

//loads dataand fill to gridview
 DataTable WszystkieProduktyDlaDanegoSklepu;
 SqlDataAdapter sda555123 = new SqlDataAdapter("here is my select", conn123);
 sda555123.Fill(WszystkieProduktyDlaDanegoSklepu);
 
 //later update table Prooducts and save changed on p.Notatka and p.FlagaWaznosci
 cmdbl = new SqlCommandBuilder(sda555123);
 cmdbl.ConflictOption = ConflictOption.OverwriteChanges;
 sda555123.Update(WszystkieProduktyDlaDanegoSklepu);

But this way I have error

So I searched a lot and found: I have to write own CommandUpdate.

So ... sda555123.UpdateCommand and I don't have idea how can I write own update for it in update command.

The update in SQL Server should looks like:

Update Products
set FlagaWaznosci = @Flagawaznosci from my sda555123,
Notatka = @Notatka from my sda555123 
where id = @ p.ID from my sda555123 

How my command update should looks like here?

EDIT 1 :

i try added : WszystkieProduktyDlaDanegoSklepu.PrimaryKey = new DataColumn[] { WszystkieProduktyDlaDanegoSklepu.Columns["id"] } but nothing . Still this error.

Answer 1

I would solve the problem by changing the approach instead of mutating the update command of the SqlDataAdapter.

Given that Products.id in your query is unique within the result set:

1- Create a temporary table (local or global), having its columns same as the result of the query with id as primary key.

2- Insert data into the temporary table using your select statement.

3- DataAdatper.selectQuery.commandText is set to "select * from TempTable"

4- The update command is now based on a simple select statement, consequently any change in the datagridview/datatable can be updated to the temptable using dataadapter.update(datatable)

5- As for the final database update, you could use the below statement

Update Prd
set Prd.FlagaWaznosci = TempTable.FlagaWaznosci ,Prd.Notatka = TempTable.Notatka  etc.. all the fields that need to be updated  
from my Products as Prd 
Inner Join TempTable on TempTable.id = Prd.id

Note that the update in (5) will affect all rows, even unchanged ones. To address this issue you can proceed as below

1- Save changed ids in a list.

List<string> lst = new List<string>();
foreach(DataRow dr in datatable.GetChanges(DataRowState.Modified))
{
 lst.add(dr["id"].ToString());
}

2- Convert your list to a string value to be concatenated with the query in (5)

 String strchange = String.Join(",",lst); //will give you id1,id2,...
 //The update query becomes
 Update Prd
 set Prd.FlagaWaznosci = TempTable.FlagaWaznosci ,Prd.Notatka = 
 TempTable.Notatka  etc.. all the fields that need to be updated  
 from my Products as Prd 
 Inner Join TempTable on TempTable.id = Prd.id
 Where Prd.id In ( strchange )