0

C# I am currently working on a project that relies on downloading a table roughly 100 entries. I first download the table and store it in a local variable, then link the variable to a DataGridView where the user can edit values. Once done the user pushes save and it must update the table in the SQL DB with the changed information.

I am asking for a best practice here, is it advisable to delete the rows you have changed and bulk upload the changes or update based or even multiple parameters?

I know when working with SQL exclusively, you can use commands like UPDATE FROM and use tables as the source but I do not know how this would work using C#.

Thanks for help in advance.

public DataTable GetSingleTable(string sTableName, string sGetConnString)  
{  
    DataTable dtTabletoReturn = new DataTable();  
    string sCommand = "SELECT * FROM " + sTableName+ " WHERE 
    BranchID = '"+ sBranchID +"'";  
    SqlConnection sqlConnection = new SqlConnection(sGetConnString);  
    sqlConnection.Open();  
    SqlDataAdapter sqlOilAdapter = new SqlDataAdapter(sCommand, sqlConnection);          
    sqlOilAdapter.Fill(dtTabletoReturn);  
    sqlConnection.Close();  
    return dtTabletoReturn;  
}
Spawn
  • 935
  • 1
  • 13
  • 32
Stef
  • 17
  • 3
  • Please show the code used to "download" the table. If you use an SqlDataAdapter you have all what is needed to Update the changes on your table back to the server – Steve Aug 17 '21 at 09:44
  • If it were me I would write a MERGE stored procedure and pass in your datagridview, that's if your data has a primary key – d0little Aug 17 '21 at 09:53
  • You need to save the reference to the sqlDataAdapter and when you need to save your changes call the SqlDataAdapter.Update method. For this to work you should be sure that you datatable has a primary key defined and you need to call the SqlCommandBuilder to add the required commands to the SqlDataAdapter. See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters There is also an example in the second answer here https://stackoverflow.com/questions/8171647/c-sharp-using-dataadapter-to-update-sql-table-from-a-datatable-sql-table-no – Steve Aug 17 '21 at 10:05
  • Use **EntityFramework** to communicate with db and easily do CRUD. https://www.entityframeworktutorial.net/efcore/entity-framework-core.aspx – mohabbati Aug 17 '21 at 10:09
  • Side note: **Do not** inject things into your query, use proper parameterization. Admittedly that's difficult with a dynamic table name, but we don't know why you have that anyway. – Charlieface Aug 17 '21 at 10:10

3 Answers3

0

Entity Framework MVC will be the best practice for you. You can start with the basics from here: https://www.entityframeworktutorial.net/what-is-entityframework.aspx

Arunava
  • 11
  • 2
0

As others have mentioned, if this is not impossible in Your project, try EF core or Dapper - both should simplify your struggles (not without adding some other later in some peculiar scenarios).

If going with EF core, take a look at connected / disconnected scenarios. In any case, when getting data by using EF in, lets say for simplicity, connected scenario, the EF core context tracks entities (Your data).

It will detect changes made to those entities, so in the end, just calling a SaveChanges() method on EF core DbContext will save and transfer just the modified data.

Mind that this very basic explanation, You will have to read about it by Yourself if You choose to go that way.

quain
  • 861
  • 5
  • 18
0

So after fiddling around and I rate the best procedure would be to use the DataAdapter Update command, I was looking for best practices here. Unfortunately the Entity Framework, as far as I can tell, works best when building an application from scratch. https://www.youtube.com/watch?v=Y-aGjF6_Ptc&t=166s <- this was the best so far.

Stef
  • 17
  • 3