Inserts, Updates and Deletes

Insert:

The following example generates an insert statement for the Person object.
Note: if you are using an autoincrement column for the ID, you must specify that in the mappings. A column that is marked as IsAutoIncrement will not be included in the list of insert values. If you forget to set this mapping for an autoincremented column, you will get an error.
        public void AddPerson(Person person)
        {
                using (var db = CreateDB())
                {
                        db.Insert<Person>(person);                        
                }
        }

Note that in the example above, if a field has been mapped with IsAutoIncrement = true and ReturnValue = true (if using the attribute mappings), or with ".SetAutoIncrement() and '.SetReturnValue()" (if using the FluentMappings), then the identity property will be automatically set during the insert using this overload.
Using the attribute mappings, it would look like this:
...
    [Column("ID", IsPrimaryKey = true, IsAutoIncrement=true, ReturnValue=true)]
    public int ID { get; set; }
...

Using the FluentMappings, it would look like this:
    ...
    .For(p => p.ID)
        .SetPrimaryKey()
        .SetAutoIncrement()
        .SetReturnValue()
    ...

In the sample code above, if ".SetReturnValue()" is called, the ID will be automatically populated during an insert.

Mapping an identity / auto increment field for use with inserts

In order for an insert statement to automatically populate an autoincrementing identity field, and to prevent an insert from trying to create in input parameter for an identity field, you must configure it in your mappings using the "IsAutoIncrement" parameter, and you must also mark the field as a "ReturnValue".

Identity field configuration using attribute mapping:
        // Map the Person ID column as ReturnValue=true
        [Column(IsPrimaryKey = true, IsAutoIncrement=true, ReturnValue=true)]
        public int ID { get; set; }

Identity configuration using fluent mapping:
        builder.BuildColumns<Person>()
                .SetReturnValue("ID")
                .SetPrimaryKey("ID")
                .SetAutoIncrement("ID");


Update:

The update method employs a lambda expression based filtering mechanism for creating the where clause.
The following filters are supported: >, >=, <, <=, ==, !=, Contains, StartsWith, EndsWith. (see examples below)


        public void UpdatePerson(Person person)
        {
                using (var db = CreateDB())
                {
                        db.Update<Person>(person, p => p.ID == person.ID);                        
                }
        }

Delete:

The delete method employs a lambda expression based filtering mechanism for creating the where clause.
The following filters are supported: >, >=, <, <=, ==, !=, Contains, StartsWith, EndsWith. (see examples below)

        public void DeleteByName(string firstName, string lastName)
        {
                using (var db = CreateDB())
                {
                        db.Delete<Person>(p => p.FirstName == firstName && p.LastName == lastName);
                }
        }

Last edited Jan 26, 2015 at 4:45 AM by jmarr, version 1

Comments

No comments yet.