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 Gets or sets the string used to open a connection to a database. Example: ConnectionString Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword More: http://www.ConnectionStrings.com Gets the time in seconds that the system should wait while establishing a ConnectionTimeout connection to the database before generating an error. Gets the name of the database Database Gets the name of the database server DataSource Gets the server version for the database ServerVersion Gets a string that represents the state of the connection such as Open State or 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() open 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 of 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 objects, 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 orders).

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 [ fileName DataContext]