Software Engineering I Lecture 9 Reminder of what is left for this - - PowerPoint PPT Presentation

Software Engineering I

Lecture 9

Reminder of what is left for this semester

• Today: Databases
• 11-23: Asynchronous programming
• 11-30: Web services
• 12-07: Middleware, filters, testing
• 12-14: UI
• 12-21: Design patterns with .net
• 01-04: Summary and overview of exam

.NET 5.0 is released!

• C# 9 language version as well
• Entity framework core 5.0 released
• More info: https://docs.microsoft.com/en-

us/dotnet/core/dotnet-five

Socrative – how are we doing?

• Go to Socrative.com
• Student login
• Enter: DOTNET2020
• 5mins;

Overview

• Intro to MSSQL

– Overview – Working with database

• Main classes for working with Database:

– Connection – Command – DataSet, DataTable, and DataAdapter

• Intro to ORM

Why?

• 90% of applications have a need to store data
• Storing to file is inefficient and difficult to use
• Practical assignments: each application should store users info

(login), any specific data (books that are in library, people/cars that are missing, people that are in your social network)

• .NET applications have multiple ways to work with database –

today we’re going to explore it

Intro to MSSQL

• Microsoft SQL Server
• Base: Transact-SQL (TSQL)
• T-SQL expands standard SQL by adding:

– Procedural programming – Local variables – Functions for working with strings – Math functions – Changes to UPDATE and DELETE statements

• This makes Transact-SQL Turing complete

Turing complete

• A Turing Complete system means a system in which a program

can be written that will find an answer (although with no guarantees regarding runtime or memory).

– So, if somebody says "my new thing is Turing Complete" that means in principle (although often not in practice) it could be used to solve any computation problem.

• A Turing Complete system means a system in which a program

can be written that will find an answer.

T-SQL few notes

• from with join:
• Try/catch (starting 2005, before - @@ERROR):

Database creation

• View → Server Explorer
• Data Connections → Add Data Connection
• Data source:

– Microsoft Access Database file – Microsoft ODBC Data source – Microsoft SQL server – Microsoft SQL Server Compact – Microsoft SQL Server Database file (good for tests) – Oracle Database – Other (live demo).

ADO.NET

• ADO.NET is a collection of classes in .NET framework, which

allows to work with databases while using:

– DML commands: insert/update/select/delete. – DDL commands: create tables, functions, etc.

• ADO.NET is considered to be continuation of ActiveX Data

Objects

Connection

• Object of Connection class is used to establish a bridge between

program and database.

• SqlConnection - to connect to SQL Server

(namespace: System.Data.SqlClient).

– Derives System.Data.Common. DBConnection.

Connection

• System.Data.Common.DBConnection

has many methods (MSDN), but main ones:

Method Description Close Closes connection type obejct (disconnect) Open Opens Connection type object (connect)

Connection

Property Description ConnectionString Gets or sets the string used to open a connection to a database. Example: Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword More: http://www.ConnectionStrings.com ConnectionTimeout Gets the time in seconds that the system should wait while establishing a connection to the database before generating an error. Database Gets the name of the database DataSource Gets the name of the database server ServerVersion Gets the server version for the database State Gets a string that represents the state of the connection such as Open

• r Closed

Command

• Is used to execute statements against a database.
• Both – DML and DDL.
• Main class: System.Data.Common.DBCommand
• SQL Server: System.Data.SqlClient.SqlCommand
• Methods:

– ExecuteNonQuery – ExecuteReader – ExecuteScalar – ExecuteXmlReader

Command: ExecuteNonQuery

• ExecuteNonQuery used for commands which does not return

results.

• For example: insert, update, delete:

Command: ExecuteNonQuery

• To call a database procedure:
• Call:

Command: ExecuteNonQuery

Command: ExecuteReader

• ExecuteReader method is designed to select multi-column records from

database (SELECT)

• ExecuteReader returns DBDataReader type object.
• DBDataReader

is a forward-only result collection, which remains connected to database while reader is open.

Command: ExecuteReader

Command: ExecuteScalar

• ExecuteScalar() is used when it is known, that result is

a single value (a row).

• Used for aggregate functions like SUM/AVG .

Common rules

• Don't write „select * ...“!!!
• Close() is a must for both - DBDataReader and

Connection.

• Without

Close()

• pen

connections might remain, which might be very painful.

• Try use using:

DEMO – look at SqlDemo.rar

DataSet/DataTable

• DBDataReader: minuses?
• One solution: DataSet and DataTable
• DataTable differs from DBDataReader:

– Not constantly connected to DB. Meaning that it is possible to connect, load data, disconnect, modify, connect, update. – Data can be manipulated in more directions.

• DataSet is a container, containing DataTable objects.

DataAdapter

• DataAdapter is used to:

– Load data to DataSet (meanig to DataTable(s)) – Perform CRUD operations

• SQL sentance can be passed in constructor.
• Even after closing cn, ds.Tables[0] is accessable.

DataTable

• DataSet has property Tables.
• Tables has a collection of DataTable type objects, which were returned by

query.

• DataTable

has Rows propery, which has collection

• f

DataRow type objects. – Rows can be iterated or looked at through index. – Rows has property Count.

• DataAdapter class allows insert/update/delete as well.

DataAdapter: Select

DataAdapter: Insert

DataAdapter: Update

DataAdapter: Delete

DataAdapter

• NewRow() method (in DataTable class object) is used to create new lines.

– New values are added via column names – And then DataTable.Rows.Add() method is used.

• DataAdapter object Update() method call forces ADO.NET to:

– Look at what operations were performed – Call needed commands (in this case - InsertCommand)

DataAdapter

• UpdateCommand and DeleteCommand have different Command type
• bjects, which have different logic behind them.
• DbDataAdapter.Update()

will execute corresponding commands, depending on what happened with DataTable object.

• DbDataAdapter tries to execute the commands without checking if they

make sense.

DataGridView

• Class for displaying table(s) data in UI.
• Allows to run CRUD operations.
• Realization:

– Via code: DataGridView.cs – Visual Studio: MSDN: How to: Display Related Data in a Windows Forms Application

• Capabilities: it is possible to display master / detail tables (e.g. clients and
• rders).

When should you use ADO.NET?

NEVER

Well, actually..

• If you want to execute simple SQL queries, and there is no need

to manipulate data;

• If you want your application to have the best performance (this

can be gained with ORM too, but it is difficult);

• Legacy code support;
• And that is why it is in requirements for third assignement

ORM

• ADO.NET classes allowing manipulating data:

– SqlCommand, SqlDataReader, SqlDataAdapter and DataSet.

• MS recommendation:

– Low (however, not too low) level work with data should be handled via ORM (Object-Relational Mapper).

• ORM – programming technique for converting data between incompatible

type systems using object-oriented programming languages.

– ORM hides work with ADO.NET data providers. – Creates "virtual object database" that can be used from within the programming language.

ORM

• ORM: to use or not to use?
• Example: school database, having:

– Instructor and Course tables – Many-to-Many saved in CourseInstructor table.

• Task: display all instructors and courses that they have.

ORM way

• SchoolContext:

– DBContext type object – Namespace: System.Data.Entity – Works with DB.

Some ORMs

• ADO.NET Entity Framework is main one in NET.
• LINQ to SQL is a bit legacy ORM.
• NHibernate another popular one.
• Some others:

– micro ORM such as Dapper or Massive, – Enterprise Library Data Access Application Block.

ORM advantages

• Less code to be written (code is testable).
• Code is not DB-specific.

– Entity Framework allows to migrate between databases without changing application code (-ish).

• Software is easier to expand and maintain

– However, maybe a bit slower.

• Real world is so complicated, since:

– Migrations – Entities – Relations...

Tip of the day

• LINQPAD - The .NET Programmer’s Playground
• Download link - https://www.linqpad.net/Download.aspx
• Basic functionality is free
• A lot of material on how to use – check next slide

Database first

• Project → Add new item → „ADO.NET Entity Data Model“
• Choose existing DB.
• Class in the code is find via:

– If file with name [fileName].dbml – Then DBContext will be [fileNameDataContext]

Database first

• Minuses:

– Code is generated from DB, which sometimes is not OK. – If we create DB by ourselves as well, we do double job. – POCO (plain old CLD objects) classes can be changed only in partial classes or T4 files.

• Plusses:

– Very OK, if DB is created by DBA or similar. – EDM (entity data model) wizard takes care about hierarchies and relationships – less error prone. – DB is open for changes – model can be updated afterwards.

T4 files

• File [name].edmx has [name].tt file.

– Text Transformation Template Toolkit – Called "T4 template".

• Auto-generated: a mix of text and control blocks, which allows to generate

entity-related code.

• Has a collection of entities, as separate files.

Context: HashSet class

• Same as List, only...

– Unique values – Insert returns false, if it failed. – Inner structure allows faster search (unique) – Set operations possible:

• Union/Intersection/IsSubsetOf ....

– Insert order not saved – To select value one iterates or uses Contains()

Model first

• Project → Add new item → „ADO.NET Entity Data Model“
• Choose „Empty Model“.
• API window used to create

– On your own:

• Entity Framework → Get Started → Model First
• After all that: „Generate Database from Model“.

Model first

• Visual creation (might be useful for DBA and similar)
• Similar to DB-first
• Plusses:

– Everything is aimed at point'n'click.

• Minuses:

– Hard to control both – entity and DB code (updates are extra complicated).

• That's why it's used rarely, mostly for small projects (with accurate design).

– DB changes are not welcomed, because DB is updated according to model. – POCO classes changed through partial classes or T4 files only.

Code first

• Everything happens through the classes:
• "virtual" keyword is used to mark navigational properties and to enable

Lazy Loading.

• Allows working in OOP, without looking at DB.
• Focus to app and it's development, rather than DB.

Code first

• Plusses

– Most flexible – No magic which is hard to control (model and DB first) – OK to use with having DB as well – Powerfull migration mechanism

• Minus:

– No manual changes in DB.

• More: in the literature

DEMO - Look at EntityFramework.rar

Instructions for using code first with entity framework core

• Install these nugget packages:

– Microsoft.EntityFrameworkCore – Microsoft.EntityFrameworkCore.Relational – Microsoft.EntityFrameworkCore.SqlServer – Microsoft.EntityFrameworkCore.Tools

• Define your database context

– Must inherit from DbContext – Must have constructor without parameters, if you are using OnConfigruing method to configure database (no DI)

Instructions for using code first with entity framework core

• Define your DBSets that will represent the tables
• Override OnModelCreating method, to use fluent api, so that you could define how to

create your tables

• For console app, override OnConfiguring and provide connection string
• Set the project that your DBContext is defined in to be a startup project
• Add migration: open package manger console, select your project where db context is

defined as default one and type command add-migraiton YourMigrationName

• If you're happy with generated migration (you can find it in created Migrations folder),

then run update-database in package manager console

• You have a database and table created, you can go and check it using your IDE

Lazy vs Eager

• 4 SQL (lazy) vs one (eager)

When ORM speed is not enough

• Manual SQL queries are created and executed. Example:

– A table that has many rows. – Update command needed for a single column (all values in it) – EF would do that for each line, SQL would have the option doing it at once.

When ORM speed is not enough

• Changing EF execution configuration logic. Example:

– EF determines, which fields have changed in the program (meaning which ones have to be updated in DB), when silently checking them with original ones (!!!)

• Imagine having iteration-type environment where that happens!
• Solution: disable tracking in that cycle:

– property AutoDetectChangesEnabled in DbContext.Configuration

Literature

• MCSD toolkit:

– Chapter 9: Working with ADO.NET (DataSet/ DataTable/ DataAdapter)

• MSDN:

– How to: Bind Data to the Windows Forms DataGridView Control – ASP.NET Data Access Options – Extra: How to: Display Related Data in a Windows Forms Application

• Web:

– http://www.ConnectionStrings.com

Next time

• Asynchrounous programming

– Thread – Task – Async/await – Real examples

Questions