Презентация

DB Apps Introduction - Databases Advanced - Entity Framework - октомври 2016

 

Материали от темата

Съдържание на документа

DB Apps Introduction 
Intro to ADO.NET

SoftUni Team

Technical Trainers

Software University

http://softuni.bg

SQL

Database

Applications


2

	Data Access Models

	ADO.NET Architecture

	Data Providers, DB Interfaces and Classes

	Accessing SQL Server from ADO.NET 
(Connected Model)

	Connecting with SqlConnection

	Using SqlCommand and SqlDataReader

	Parameterized Queries

Table of Contents


3

	SQL Injection

	What is SQL Injection and How to Avoid It?

	Connecting to Other Databases

Connecting to MySQL

Table of Contents (2)


4



sli.do
#Entity

Questions


Data Access Models


6

Connected data access model

Applicable to an environment where the database is constantly available

Connected Model

DB

constantly open

connection



Database

ADO.NET client

DB

ADO.NET


7

Connected data access model (SqlClient)

Benefits:

Concurrency control is easier to maintain

Better chance to work with the most recent version of the data

Drawbacks:

Needs a constant reliable network

Problems when scalability is an issue

Connected Model: Benefits and Drawbacks


Disconnected Model

Disconnected data access model (DataSet)

A subset of the central database is copied locally at the client and he works with the copy

Database synchronization is done offline









Legacy technology (deprecated)



8

DB

temporary (offline)

connection



Database

ADO.NET client

DB

ADO.NET


9

Benefits:

The client connects to DB from time to time

Works with the local copy the rest of the time

Other clients can connect during that time

Has superior scalability

Drawbacks:

The data you work with is not always the latest data in the database

Additional efforts to resolve the conflicts caused by different versions of the data

Disconnected Model: Benefits and Drawbacks


ADO.NET Architecture


11

ADO.NET is a standard .NET class library for accessing databases, processing data and XML

A program model for working with data in .NET

Supports connected, disconnected and ORM data access models

Excellent integration with LINQ

Allows executing SQL in RDBMS systems

DB connections, data readers, DB commands

Allows accessing data in the ORM approach

LINQ-to-SQL and ADO.NET Entity Framework

What Is ADO.NET?


12

Data Providers are collections of classes that provide access to various databases

For different RDBMS systems different Data Providers are available

Each provider uses vendor-specific protocols to talk to the database server

Several common objects are defined:

Connection – to connect to the database

Command – to run an SQL command

DataReader – to retrieve data

Data Providers In ADO.NET


13

Several standard ADO.NET Data Providers come as part of .NET Framework

SqlClient – accessing SQL Server

OleDB – accessing standard OLE DB data sources

Odbc – accessing standard ODBC data sources

Oracle – accessing Oracle database

Third party Data Providers are available for:

MySQL, PostgreSQL, Interbase, DB2, SQLite

Other RDBMS systems and data sources

SQL Azure, Salesforce CRM, Amazon SimpleDB, …

Data Providers in ADO.NET (2)


14

Retrieving data in connected model

Open a connection (SqlConnection)

Execute command (SqlCommand)

Process the result set of the query by
using a reader (SqlDataReader)

Close the reader

Close the connection

SqlClient and ADO.NET Connected Model

SqlConnection

SqlCommand

SqlDataReader







Database

SqlParameter

SqlParameter

SqlParameter








15

Disconnected model: the data is cached in a DataSet

Open a connection (SqlConnection)

Fill a DataSet (using SqlDataAdapter)

Close the connection

Modify the DataSet

Open a connection

Update changes into 
the DB

Close the connection

ADO.NET: Disconnected Model

SqlConnection

SqlDataAdapter

DataSet







Database

Warning:

DataSets / DataAdapters are legacy technology (not in use since .NET 3.5)


16

LINQ-to-SQL is ORM framework for SQL Server

Create object models mapping the database

Open a data context

Retrieve data with LINQ / modify the tables in the data context

Persist the data context changes into the DB

Connection is auto-closed

ADO.NET: LINQ-to-SQL

SqlConnection

DataContext





Database

Table

Table

Table








ORM Model

ORM data access model (Entity Framework)

Maps database tables to classes and objects

Objects can be automatically persisted in the database

Can operate in both connected and disconnected modes

17

ORM

Framework

OO

Programming

Language





Database


18

ORM model benefits

Less code

Use objects with associations instead of tables and SQL

Integrated object query mechanism

ORM model drawbacks:

Less flexibility

SQL is automatically generated

Performance issues (sometimes)

ORM Model – Benefits and Problems


19

Entity Framework is generic ORM framework

Create entity data model mapping the database

Open an object context

Retrieve data with LINQ / modify the tables in the object context

Persist the object context changes into the DB

Connection is auto-closed

ADO.NET: Entity Framework

SqlConnection

ObjectContext

Database

Entity

Entity

Entity

EntityClient

Data Provider
















SQL Client Data Provider


SqlClient Data Provider

SqlConnection

Establish database connection to SQL Server 

SqlCommand

Executes SQL commands on the SQL Server through an established connection

Could accept parameters (SQLParameter)

SqlDataReader

Retrieves data (record set) from SQL Server
as a result of SQL query execution



21


22

SqlConnection establish connection to SQL Server database

Requires a valid connection string

Connection string example:



Connecting to SQL Server:

The SqlConnection Class

Data Source=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=true;

SqlConnection con = new SqlConnection(

  "Server=.\SQLEXPRESS;Database=Northwind;

   Integrated Security=true");

con.Open();


DB Connection String

Database connection string

Defines the parameters needed to establish
the connection to the database

Settings for SQL Server connections:

Provider – name of the DB driver

Data Source / Server – server name / IP address + database instance name

Database / Initial Catalog – database name

User ID / Password – credentials

23


24

Server=server_name\database_instance

"." or "(local)" or "SOME_SERVER"

Database instance is "MSSQL", "SQLEXPRESS" or other SQL Server instance name

Integrated Security – true / false

DB Connection String (2)


Connection Pooling

By default SqlClient Data Provider uses connection pooling for improved performance

Connection pooling works as follows:

When establishing a connection an existing one is taken from the so called "connection pool"

If there is no free connection in the pool, a now connection is established

When closing a connection it is returned to the pool, instead of being closed

25


Working with SqlConnection

Explicitly opening and closing a connection

Open() and Close() methods

Works through the connection pool

DB connections are IDisposable objects

Always use the using construct in C#!

26


SqlConnection – Example

Creating and opening connection to SQL Server (database SoftUni)

27

SqlConnection dbCon = new SqlConnection(

    "Server=.\\SQLEXPRESS; " +

    "Database=SoftUni; " +

    "Integrated Security=true");

dbCon.Open();

using (dbCon)

{

  // Use the connection to execute SQL commands here …

}


28

More important methods

ExecuteScalar()

Returns a single value (the value in the first column of the first row of the result set)

The returned value is System.Object but can be casted to the actual returned data type

ExecuteReader()

Returns a SqlDataReader

It is a cursor over the returned records (result set)

CommandBehavior – assigns some options

The SqlCommand Class (1)


29

More important methods

ExecuteNonQuery()

Used for non-query SQL commands, e.g. INSERT

Returns the number of affected rows (int)

The SqlCommand Class (2)


30

SqlCommand – Example

SqlConnection dbCon = new SqlConnection(

  "Server=.; " +

  "Database=SoftUni; " +

  "Integrated Security=true");

dbCon.Open();

using(dbCon)

{

  SqlCommand command = new SqlCommand(

    "SELECT COUNT(*) FROM Employees", dbCon);

  int employeesCount = (int) command.ExecuteScalar();

  Console.WriteLine("Employees count: {0} ", employeesCount);

}


31

SqlDataReader retrieves a sequence of records (cursor) returned as result of an SQL command

Data is available for reading only (can't be changed)

Forward-only row processing (no move back)

Important properties and methods:

Read() – moves the cursor forward and returns false if there is no next record

Indexer[] – retrieves the value in the current record by given column name or index

Close() – closes the cursor and releases resources

The SqlDataReader Class


32

SqlDataReader – Example

SqlConnection dbCon = new SqlConnection(…);

dbCon.Open();

using(dbCon)

{

  SqlCommand command = new SqlCommand("SELECT * FROM Employees", dbCon);

  SqlDataReader reader = command.ExecuteReader();

  using (reader)

  {

    while (reader.Read())

    {

      string firstName = (string)reader["FirstName"];

      string lastName = (string)reader["LastName"];

      decimal salary = (decimal)reader["Salary"];

      Console.WriteLine("{0} {1} - {2}", firstName, lastName, salary);

    }

  }

}


SQL Injection

What is SQL Injection and How to Prevent It?

SQL Injection

How to prevent it?


34

What is SQL Injection?

bool IsPasswordValid(string username, string password)

{

  string sql = 

    "SELECT COUNT(*) FROM Users " +

    "WHERE UserName = '" + username + "' and " +

    "PasswordHash = '" + CalcSHA1(password) + "'";

  SqlCommand cmd = new SqlCommand(sql, dbConnection);

  int matchedUsersCount = (int) cmd.ExecuteScalar();

  return matchedUsersCount > 0;

}



bool normalLogin = 

  IsPasswordValid("peter", "qwerty123"); // true

bool sqlInjectedLogin = 

  IsPasswordValid(" ' or 1=1 --", "qwerty123"); // true

bool evilHackerCreatesNewUser = IsPasswordValid(

  "' INSERT INTO Users VALUES('hacker','') --", "qwerty123");


35

The following SQL commands are executed:

Usual password check (no SQL injection):



SQL-injected password check:



SQL-injected INSERT command:





How Does SQL Injection Work?

SELECT COUNT(*) FROM Users WHERE UserName = 'peter'
and PasswordHash = 'XOwXWxZePV5iyeE86Ejvb+rIG/8='

SELECT COUNT(*) FROM Users WHERE UserName = ' ' or 1=1
-- ' and PasswordHash = 'XOwXWxZePV5iyeE86Ejvb+rIG/8='

SELECT COUNT(*) FROM Users WHERE UserName = ''
INSERT INTO Users VALUES('hacker','')
--' and PasswordHash = 'XOwXWxZePV5iyeE86Ejvb+rIG/8='


36

Ways to prevent the SQL injection:

SQL-escape all data coming from the user:







Not recommended: use as last resort only!

Preferred approach:

Use parameterized queries

Separate the SQL command from its arguments

Preventing SQL Injection

string escapedUsername = username.Replace("'", "''");

string sql = 

    "SELECT COUNT(*) FROM Users " +

    "WHERE UserName = '" + escapedUsername + "' and " +

    "PasswordHash = '" + CalcSHA1(password) + "'";


The SqlParameter Class

What are SqlParameters?

SQL queries and stored procedures can have input and output parameters

Accessed through the Parameters property of the SqlCommand class

Properties of SqlParameter:

ParameterName – name of the parameter

DbType – SQL type (NVarChar, Timestamp, …)

Size – size of the type (if applicable)

Direction – input / output

37


38

Parameterized Commands – Example

private void InsertProject(string name, string description, DateTime

    startDate, DateTime? endDate)

{

  SqlCommand cmd = new SqlCommand("INSERT INTO Projects " + 

    "(Name, Description, StartDate, EndDate) VALUES " +

    "(@name, @desc, @start, @end)", dbCon);

  cmd.Parameters.AddWithValue("@name", name);

  cmd.Parameters.AddWithValue("@desc", description);

  cmd.Parameters.AddWithValue("@start", startDate);

  SqlParameter sqlParameterEndDate = new SqlParameter("@end", endDate);



  if (endDate == null)

    sqlParameterEndDate.Value = DBNull.Value;



  cmd.Parameters.Add(sqlParameterEndDate);

  cmd.ExecuteNonQuery();

}


Connecting to Non-Microsoft Databases


40

ADO.NET supports accessing various databases via their Data Providers:

OLE DB – supported internally in ADO.NET

Access any OLE DB-compliant data source

E.g. MS Access, MS Excel, MS Project, MS Exchange, Windows Active Directory, text files

Oracle – supported internally in ADO.NET

MySQL – third party extension

Connecting to Non-Microsoft Databases


41

OleDbConnection – establishes a connection to an OLE DB source of data





OleDbCommand – executes an SQL commands through an OLE DB connection to a DB

OleDbParameter – parameter for a command

OleDbDataReader – to retrieve data from a command, executed through OLE DB

OLE DB Data Provider

OleDbConnection dbConn = new OleDbConnection(

  @"Provider=Microsoft.Jet.OLEDB.4.0;Data

  Source=C:\MyDB.mdb;Persist Security Info=False");


Connecting to MySQL

Accessing MySQL from ADO.NET


43

Download and install MySQL Connector/Net

http://dev.mysql.com/downloads/connector/net/

Add reference to MySQL.Data.dll

Available also from NuGet (see http://nuget.org/packages/Mysql.Data/)

Connecting to MySQL:

Connecting to MySQL from C#

MySqlConnection dbConnection = 

  new MySqlConnection("Server=localhost; Port=3306; Database=world; Uid=root; Pwd=root; pooling=true");


DB Apps Introduction

https://softuni.bg/courses/


License

This course (slides, examples, demos, videos, homework, etc.)
is licensed under the "Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International" license

45

Attribution: this work may contain portions from

"Databases" course by Telerik Academy under CC-BY-NC-SA license


Free Trainings @ Software University

Software University Foundation – softuni.org

Software University – High-Quality Education, Profession and Job for Software Developers

softuni.bg 

	Software University @ Facebook

	facebook.com/SoftwareUniversity

	Software University @ YouTube

	youtube.com/SoftwareUniversity

	Software University Forums – forum.softuni.bg