Comment on page
Data manipulation
There are three data manipulation statements in SQL92:
INSERT
, UPDATE
and DELETE
. UPDATE
is not supported in Starcounter SQL, DELETE
is available through Db.SQL
, and INSERT
is available with reload. Objects are otherwise created and updated in the programming code.All modifications have to be wrapped in a transaction. These modifications are visible to other transaction after the changes have been commited.
Database objects are created with the native program code operator
new
:new Person()
{
FirstName = "John",
LastName = "Doe"
};
A database object can be updated using the native program code assign operator
=
.For example, instead of instantiating an object like in the example above, it's possible to create the object and then update its properties:
var person = new Person();
person.FirstName = "John";
person.LastName = "Doe";
To update the
LastName
of all the Person
objects in the database, they would be looped through and updated, like so:var people = Db.SQL<Person>("SELECT p FROM Person p");
foreach (var person in people)
{
person.LastName = person.LastName.ToUpper();
}
There are two ways to delete database objects:
- 1.Using the
Delete
method on an object - 2.Using
DELETE FROM
Delete
is used for single objects and DELETE FROM
is used for many objects.They look like this:
var john = new Person();
john.Delete();
Db.SQL("DELETE FROM Person");
Db.SQL("DELETE FROM Person WHERE Name = ?", "John");
person.Delete()
will just delete john
while DELETE FROM Person
will delete all objects of the Person
class.To delete database objects that are bound to the view-model, the view-model object should be deleted before the database object is deleted.
Deleting many records with
DELETE FROM
might breach the size limit for a single transaction which will cause Starcounter to throw ScErrMemoryLimitReachedAbort
. This can be fixed by using Delete
and splitting the deletion in smaller transactions: var people = Db.SQL($"SELECT p FROM {typeof(Person)} p");
foreach (var person in people)
{
Db.Transact(() => person.Delete());
}
Last modified 5yr ago