CODEWITHSUNDEEP
sqlsql-serverc#-4.0
paddingtonMike
paddingtonMike

Reputation: 1603

What's the best way to update a List on the database?

If I have the following list in C# that was loaded from the database

List<User> user = GetUsers(foo);

and it was updated and I want to store those changes in the database what's the best way of doing it using SQL? It should insert the records added to that list, updated the modified records and delete the ones that are not present in the collection.

I'm no using the EntityFramework so I need to do this using SQL.

Upvotes: 0

Views: 174

Answers (2)

Andomar
Andomar

Reputation: 238196

Here's an example that adds or inserts a row. It searches for a row with a specific UserID. If the row exists, it uses update to grant the user a point. If the row does not exist, a new row is created with insert.

var connectionString = "Data Source=myServerAddress;" +
    "Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
using (var con = new SqlConnection(connectionString))
{
    con.Open();
    var com = con.CreateCommand();
    com.Parameters.AddWithValue("@UserId", userId);
    com.CommandText = @"
if exists (select * from YourTable where UserId = @UserId)
    update YourTable set TrollPoints = TrollPoints + 1 where UserId = @UserId
else
    insert YourTable (UserId, TrollPoints) values (@UserId, 1)
        ";
    com.ExecuteNonQuery();
}

The use of parameters allows the server to chache the execution plan, and also helps against SQL injection.

Upvotes: 1

DeveloperX
DeveloperX

Reputation: 4683

Copy this list to datatable and set datatable RowStat as (modified,deleted,new) and update datatable using sqldataadapter

Upvotes: 1

Related Questions