Transcript gets updated to surname `Good`

COMPSCI 280 S2 2015
Enterprise Software Development
Further exploring database operations from C#/.NET
Jim Warren, [email protected]
Today’s learning objectives


To be able to use the LINQ (language-integrate query)
approach for database queries in C#/.NET
To be able to update the database from C#/.NET


2
To conceptualise an application in terms of CRUD (create, read,
update, delete) operations
To consider the state of an object instance in relation to the
database context
COMPSCI 280
Last lecture…

We learned to connect to a database, set up a model of its
tables and create queries like:
var x2 = db.Database.SqlQuery<Employee>("select * from employee");

This is OK, but it has some disadvantages

We don’t get support from the IDE for the query itself



3
It’s an arbitrary string – no type checking, no intellisense
It’s a bit of a pain to always need to figure out the type of the
returned instances
It’s also a potential security hole…
COMPSCI 280
SQL injection
4
COMPSCI 280
The LINQ approach

Makes the query actually a statement in the C# language
using (EmployeesContext db = new EmployeesContext())
{
var emps = from e in db.Employees
where e.DateOfBirth.Year < 1975
select e;
string surname1=emps.First().surname
We get access to
}

SQL’s select-from-where is turned on its head a bit


all C#’s
operators, too!
Makes the intellisense work better to use from-where-select order
In this case the return value is a collection of objects of a class
we’ve already defined in our Model

Because we had said:
public DbSet<Employee> Employees { get; set; }
and had defined the Employee class with property names exactly aligned to
the columns of the DBMS table
5
COMPSCI 280
LINQ Join

We could, of course, have sent join queries to MySQL as a string,
Notice we said ‘equals’ as a keyword (not the
but LINQ brings Join into C#
= sign); this gives system most flexibility to
var innerJoinQuery =
exploit indices or other optimise the join
from category in db.categories
join prod in db.products on category.ID equals
prod.CategoryID
select new { ProductName = prod.Name, Category =
category.Name };

Notice the ‘select’



Even if we included in the model definitions of the ‘categories’ and ‘products’
tables, we’d need a new class to hold the combination of a product name and
a category name
The ‘new’ statement is constructing an anonymous type with properties
ProductName and Category
innerJoinQuery will be implicitly typed to a generic collection of instances
with the generic’s type parameter being this new 2-property anonymous type

6
We can still do a foreach statement on innerJoinQuery to loop through the results
COMPSCI 280
More LINQ – group by

Get a count of how many employees with each surname
var cnts = from emp in db.Employees group emp by emp.Surname
into g select new {s=g.Key, cnt=g.Count()};
foreach (var c in cnts)
Console.WriteLine("Key: " + c.s + " Count: " + c.cnt);



7
Use ‘group’… ‘by’ keyword pair
Note use of ‘into’ and naming a second index variable (‘g’ in this
case)
Note the special property Key is the thing we grouped by
COMPSCI 280
Or method based GroupBy

Use GroupBy method allowing a lambda to specify just what
we want the grouping Key to be
Count of employees by first letter of surname
foreach (var c in
db.Employees.GroupBy(a=>a.Surname.Substring(0,1)))
Console.WriteLine("Key2: " + c.Key+ " Count2: " + c.Count());
foreach (var c in db.Employees.GroupBy(a =>
Math.Floor(a.DateOfBirth.Year/10f)))
Console.WriteLine("Key2: " + c.Key + "0's Count2: " + c.Count());
Count of employees by decade of birth

And you can apply one method onto the result of another
foreach (var c in
db.Employees.Where(a=>a.Surname.CompareTo("F")>=0).GroupBy(a=>a.Su
rname.Substring(0,1)))
Console.WriteLine("Key2: " + c.Key+ " Count2: " + c.Count());
8
COMPSCI 280
What about the rest of the CRUD?!


CRUD: Create, Read, Update, Delete
A CRUD matrix can be a useful specification for the scope of
programming tasks

E.g. to describe the ‘life cycle’ of each entity in a system; e.g. a hotel
reservation



9
On some screen (e.g. ‘booking’) it is created (SQL INSERT, not CREATE
like making a new table)
There may be a screen to UPDATE it (e.g. ‘change booking’) which probably
also reads the current value (‘R’ of CRUD, SQL SELECT)
And there will be one or more ways to DELETE it (e.g. from a cancellation
action on the change booking screen or a dedicated cancellation screen,
and also once the person has checked in) – then again, you might not
actually delete in a SQL sense, but UPDATE it to cancelled or utilised
status
COMPSCI 280
CRUD matrix (well, for one entity)
Program (or ‘screen’)
Entity=reservation
Make reservation
C
Check reservation
R
Review/change reservation
RU
Review/cancel reservation
RD*
Client arrives (takes up reserved room)
D**
* Or update status to cancelled
** Or update status to taken

For a more involved system you may have many entities
represented as further columns

10
Each cell marked with a subset of the letters in ‘CRUD’
COMPSCI 280
The UPDATE
Native SQL version


Given that I have a reference to an object emp of class Employee
with updated values:
Re-establish the database connection
using (EmployeesContext db = new EmployeesContext()) {
string sql=String.Format(
"UPDATE employee SET Surname='{0}',GivenNames='{1}',"+
"DateOfBirth='{2:yyyy-MM-dd}' WHERE id={3}",
emp.Surname,emp.GivenNames,emp.DateOfBirth,emp.id);
db.Database.ExecuteSqlCommand(sql);
...
}
Then we just .ExecuteSqlCommand (as
compared to doing . SqlQuery) on the
database context to tell MySQL to have
at it
11
COMPSCI 280
Here we’re just building the text
of a SQL command with the help
of String.Format to plug in the
parameters from our C# code at
the curly braces (note the 3rd
parameter - #2 counting from 0!
– is formatted so MySQL
recognises the date literal; also
note carefully the double quotes
interpreted by C# and the signle
quotes that are for MySQL
Native INSERT
Similar to the UPDATE, once we have a database context
established we can do INSERT (or DELETE) SQL

string sql_ins = String.Format("INSERT INTO employee
(GivenNames,SurName,DateOfBirth) "+
"VALUES ('{0}','{1}','{2:yyyy-MM-dd}')","Bob",
"Barker",DateTime.Now);
db.Database.ExecuteSqlCommand(sql_ins);
string sql_del = "delete from employee where
GivenNames='Bob'";
db.Database.ExecuteSqlCommand(sql_del);
12
COMPSCI 280
The UPDATE v2
Entity Framework version


If you’ve gotten an object from the database, the system tracks that
that object is “attached” to the database context
using (EmployeesContext db = new EmployeesContext()){
var updEmp = from emp in db.Employees where emp.Surname ==
"Barker" select emp;
foreach (Employee e3 in updEmp) e3.Surname = "Good";
db.SaveChanges();
...
}

If you want to update a record



13
Every employee with surname ‘Barker’
gets updated to surname ‘Good’
Retrieve it
Change properties of the object as you wish
Save the changes
COMPSCI 280
INSERT by adding
If you simply create a ‘new’ object of the class of a record, it
isn’t attached to the database context by default



However, you use the appropriate DbSet’s Add method to attach it
Use the database context’s SaveChanges method to make the
insertion stick
using (EmployeesContext db = new EmployeesContext()) {
...
var e1 = new Employee{GivenNames="Tommy", Surname="Tank"};
db.Employees.Add(e1);
db.SaveChanges();
Note curly-brace “object initializer”
syntax. Invokes constructor and then
assigns the specified values to the
named properties of the new object
14
Adds a new employee with name “Tommy
Tank”, letting the ‘id’ be supplied automatically
and leaving DateOfBirth unspecified (probably
will be set to 1 Jan 1 AD !)
COMPSCI 280
Delete
To delete



Get a handle on the relevant object(s) (e.g. with a query, like we did
for update)
Invoke the Remove method of the DbSet with the object to delete
as a parameter
using (EmployeesContext db = new EmployeesContext()) {
...
var deletedEmp = from emp in db.Employees where emp.Surname
== "Tank" select emp;
foreach (Employee emp in deletedEmp)
db.Employees.Remove(emp);
db.SaveChanges();

15
Note: if deleting (or in some cases updating) where there are foreign key
constraints you may need to think carefully about the ‘cascade’ of effects
with respect to referential integrity (see https://technet.microsoft.com/enus/library/aa902684(v=sql.80).aspx)
COMPSCI 280
Where we’re up to



We’ve learned some basics of .NET and C#
We’ve seen that we can interact with our DBMS by sending it
native SQL or using C#/.NET language-embedded syntax
(LINQ and Entity Framework)
Now…



Work the second labsheet (if you haven’t already)
Get seriously into Assignment 2
See you other side of the break!

16
FYI, I’m on leave first week of the break, but will be at work and answering
questions second week of the break
COMPSCI 280