Updating Data : Simple.Data Help

Simple.Data

Simple.Data defines a number of commands for the modification of data in a data store. Update and UpdateAll are used to modify existing data.

Update

The Update and UpdateAll methods allows the updating of data already in your database.

The return value of an Update method is the number of rows that were just updated.

You can Update using two forms, Named parameters and by object. The object can be a POCO or a dynamic (i.e.ExpandoObject).

Note: There are differences in the generated SQL when using Named Parameters or object, where the object includes all properties including on the object, but the Named Parameters only includes those you specify

Update (Named Parameters)

    _db.Users.UpdateById(Id: 1, Name: "Steve", Age: 50);         

Generates the following SQL

    @p0 = 1
    @p1 = 'Steve'
    @p2 = 50
    update [dbo].[Users] set [Name] = @p1, [Age] = @p2 where [dbo].[Users].[Id] = @p0

Update (object)

    dynamic record = new SimpleRecord();
    record.Id = 1;
    record.Name = "Steve";
    record.Age = 50;
    _db.Users.UpdateById(record);             

Generates the following SQL

    @p0 = 1
    @p1 = 'Steve'
    @p2 = 50
    update [dbo].[Users] set [Name] = @p1, [Age] = @p2 where [dbo].[Users].[Id] = @p0

This is also equivalent to the following with a POCO

var user = new User
            {
               Id = 1,
               Name = "Steve",
               Age = 50
            };
_db.Users.UpdateById(user);             

Generates the following SQL

    @p0 = 1
    @p1 = 'Steve'
    @p2 = 50
    update [dbo].[Users] set [Name] = @p1, [Age] = @p2 where [dbo].[Users].[Id] = @p0

Update by primary key

You can use the Update without specifying a condition if your table has a primary key:

Insert (object)

dynamic record = new SimpleRecord();
record.Id = 1;
record.Name = "Steve";
record.Age = 50;
_db.Users.Update(record);             

Generates the following SQL

@p0 = 1
@p1 = 'Steve'
@p2 = 50
update [dbo].[Users] set [Name] = @p1, [Age] = @p2 where [dbo].[Users].[Id] = @p0

This is also equivalent to the following with a POCO

var user = new User
            {
               Id = 1,
               Name = "Steve",
               Age = 50
            };
_db.Users.Update(user);             

Generates the following SQL

@p0 = 1
@p1 = 'Steve'
@p2 = 50
update [dbo].[Users] set [Name] = @p1, [Age] = @p2 where [dbo].[Users].[Id] = @p0

UpdateAll

The UpdateAll method allows a user more control over the WHERE clause on your update statement. You may omit the WHERE clause entirely (thus causing an update on a whole table) or you may specify criteria to update a set within your table. There are two forms of specifying a criteria, by named arguments or by passing in an expression.

Update (with no Where clause)

        _db.Users.UpdateAll(Name: "Steve");          

Generates the following SQL

    @p0 = 'Steve'
    update [dbo].[Users] set [Name] = @p0

Update (with criteria, named argument form)

_db.Users.UpdateAll(Name: "Steve", Condition: _db.Users.Age > 30);        

Generates the following SQL

@p0 = 'Steve'
@p1 = 30
update [dbo].[Users] set [Name] = @p0 where [dbo].[Users].[Age] > @p1

Update (with criteria, expression form)

_db.Users.UpdateAll(_db.Users.Age > 30, Name: "Steve");       

Generates the following SQL

@p0 = 'Steve'
@p1 = 30
update [dbo].[Users] set [Name] = @p0 where [dbo].[Users].[Age] > @p1