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

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?

CodePudding user response：

Kindly update your tables separately because in join you just seen two or more than two tables into one table form . but you cant do any crud operation on

CodePudding user response：

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 )