- ADO.NET.
- Архитектура SQL Server.
- Таблицы в реляционных базах данных.
- SQL.
- Типы данных в SQL Server
- SQL запросы. Создание БД, таблицы.
- SQL запросы. CRUD.
- Представление в SQL.
- Последовательности в SQL.
- Этапы проектирования базы данных.
- Нормализация. Нормальные формы.
- Отношения в реляционных базах данных.
- Многие-ко-многим в SQL.
- Один-к-одному в SQL.
- Соединение в SQL.
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Join
- Декартово произведение
- Cross Join
- Подзапросы
- Группировка в SQL
- Агрегатные функции
- Having в SQL
- Connected/disconnected модель
- Провайдеры данных в ADO.NET
- ORM -преимущества и недостатки
- SqlClient Data Provider
- SqlCommand
- SqlDataReader
- Инъекция SQL
- Параметризированные команды
- Entity Framework Core
- Database First Model
- Класс DbContext
- LINQ Query (через методы расширения)
- Code First Model
- CRUD в EF Core
- POCO классы
- Миграции
- Navigation Properties
- Применение Fluent API
- Data Annotation
- Связи через Data Annotation
- Связи через Fluent API
- LINQ Query (через SQL синтаксис)
- IEnumerable и IQueryable в EF Core
- Result Models
- Нативные запросы и хранимые процедуры в EF Core
- Трекинг объектов, настройка трекинга.
- Массовые запросы.
- Виды загрузок данных.
- Каскадное удаление.
- DTO.
- AutoMapper. Конфигурация. Плоские объекты.
ADO.NET - Это стандартная библиотека классов .NET для доступа к базам данных, обработки данных и XML.
- Поддерживает connected/disconnected модели доступа к данным
- Отличная интеграция с LINQ
- Позволяет выполнять SQL в системах СУБД
- Позволяет получить доступ к данным в стиле ORM
- Таблица является фундаментальным блоком любой базы данных
- Каждая строка называется записью
- Столбцы (поля) определяют тип данных, которые они содержат.
- Язык программирования, предназначенный для управления данными в реляционных базах данных.
- Разработан в IBM в начале 70-х годов
- Для связи с Engine мы используем SQL
- Логически разделен на 4 секции:
- Определение данных – описание структуры данных
- Обработка данных – хранение и извлечение данных
- Управление данными – определяет, кто может получить доступ к данным
- Управление транзакциями – связывает операции и разрешает откат
- CREATE
INSERT INTO users VALUES (login="log", password="pas")
- READ
SELECT * FROM users WHERE id=1
- UPDATE
UPDATE users SET pas="123" WHERE id=3
- DELETE
DELETE FROM users WHERE bdate=current_date()
- Определение сущностей
- Определение столбцов таблицы
- Определение первичных ключей для каждой таблицы
- Определение отношений и взаимосвязей
- Определение других ограничений
Отношения «многие ко многим» используют таблицу-посредник для сопоставления
С помощью оператора JOIN мы можем получить данные из двух таблиц одновременно.
JOINы требуют как минимум две таблицы и условия объединения.
SELECT * FROM Employees AS e
INNER JOIN Departments AS d
ON e.DepartmentID = d.DepartmentID
SELECT * FROM Employees AS e
LEFT OUTER JOIN Depatments AS d
ON e.DepartmentID = d.DepartmentID
SELECT * FROM Employees AS e
RIGHT OUTER JOIN Departments AS d
ON e.DepartmentID = d.DepartmentsID
SELECT * FROM Employees AS e
FULL JOIN Departments AS d
ON e.DepartmentID = d.DepartmentID
Декартово произведение двух множеств - множество, элементами которого являются все возможные упорядоченные пары элементов исходных множеств.
Cross Join является Декартовым произведением
SELECT * FROM Employees AS e
CROSS JOIN Departments AS d
Подзапросы - SQL запрос внутри большего запроса.
Может быть вложен в
- SELECT
- INSERT
- UPDATE
- DELETE.
Обычно добавляется в WHERE.
Пример: Вернуть всех сотрудников из отдела бухгалтерии.
SELECT FROM Employees AS e
WHERE e.DepartmentID IN
(
SELECT d.DepartmentID
FROM Departments AS d
WHERE d.Name = ‘Finance’
)
Группировка позволяет «получить» данные в отдельных группах на основе общего атрибута (столбца).
GROUP BY позволяет получить каждую отдельную группу и использовать над ней функцию (например, Average, Min или Max).
Пример: верните сумму заработной платы по отделам.
SELECT e.DepartmentID, SUM(e.Salary) AS TotalSalary
FROM Employees AS e
GROUP BY e.DepartmentID
ORDER BY e.DepartmentID
Агрегатные функции оперируют непустыми группами для анализа данных результата.
Агрегатные функции всегда игнорируют NULL значения.
- COUNT - подсчитывает значения в одном или нескольких сгруппированных столбцах.
- SUM – суммируют значение в столбцах
- MAX – большее значение столбца
- MIN – меньшее значение столбца
- AVG – среднее значение столбца
Определяет условие поиска для группы или статистического выражения.
Предложение HAVING можно использовать только в инструкции SELECT.
HAVING обычно используется с предложением GROUP BY.
Если предложение GROUP BY не используется, используется одна неявная агрегированная группа.
Пример: отфильтруйте отделы, у которых суммарная зарплата больше или равная 15 000.
-
Connected обеспечивает прямой доступ только для чтения к данным в источнике данных и возможность выполнения команд на удаленном источнике данных.
-
Disconnected позволяет управлять данными, полученными из источника данных, а затем согласовывать их с источником данных.
Стоит запомнить:
Извлечение данных в connected model :
- Открываем соединение (SqlConnection)
- Выполняем команду (SqlCommand)
- Обрабатываем результирующий набор данных запроса с помощью объекта чтения (SqlDataReader)
- Закрываем объект чтения
- Закрываем соединение
• Провайдеры данных - это коллекции классов, которые обеспечивают доступ к различным базам данных.
Для различных систем управления реляционными базами данных представлены различные провайдеры данных:
- Провайдеры данных имеют общие объекты
- Connection – для подключения к базе данных
- Command - для выполнения SQL команды
- DataReader – для извлечения данных
Примеры: (не так важно, но можно запомнить)
Несколько провайдеров данных поставляются как часть .NET Framework
- SqlClient - доступ к SQL Server
- OleDB - доступ к стандартным источникам данных OLE DB
- Odbc - доступ к стандартным источникам данных ODBC
- Oracle - доступ к базам данных Oracle
- Сторонние провайдеры данных доступны для:
- MySQL, PostgreSQL, Interbase, DB2, SQLite
- Другие системы СУБД и базы данных
- SQL Azure, Salesforce CRM, Amazon SimpleDB, …
Модель доступа к данным в стиле ORM (Entity Framework Core) :
-
Сопоставляет таблицы базы данных с классами и объектами
-
Объекты могут быть автоматически сохранены в базе данных
-
Может работать как в подключенном (connected model) и в отключенном (disconnected model) режимах
-
Преимущества ORM
- Меньше кода
- Используем объекты вместо таблиц и SQL
- Интегрированный объектный механизм запросов
-
Недостатки ORM:
- Менее гибкое решение
- SQL генерируется автоматически – вопросы к качеству генерации
- Проблемы с производительностью (иногда)
- SqlConnection - используется,чтобы установить соединение с бд
- SqlCommand - выполняет Sql комманды в сервере через соединение
- SqlDataReader - извлекает данные запроса, по сути есть результат запроса.
- Явное открытие и закрытие – методы
Open()
иClose()
- Используется конструкция
using (dbConnection) { // выполняем комманды }
ExecuteScalar()
– возвращает первый элемент первого столбца первой строкиExecuteReader()
– возвращаетSqlDataReader
(вопрос выше), а значит извлекает результат запросаExecuteNonQuery()
- возвращает количество затронутых строк(int)
- Извлекает последовательность записей через курсор, возвращенных в результате Sql- команды
- Данные доступны только для чтения
- Обработка только вперед
- Свойства и методы:
- Read()-перемещает курсор вперед и возвращает false,если следующей записи нет
- Indexer[]-извлекает значение в текущей строке в таблице по имени столбца или индексу
- Close() –закрывает курсор и освобождает ресурсы
SQL инъекция — это один из самых доступных способов взлома сайта. Суть таких инъекций – внедрение в данные (передаваемые через GET, POST запросы или значения Cookie) произвольного SQL кода.
Если сайт уязвим и выполняет такие инъекции, то по сути есть возможность творить с БД (чаще всего это MySQL) что угодно.
Примеры:
-
string id = Request["id"]; $"SELECT * FROM Articles WHERE id={id}"
При нормальной работе - id - число. Однако
Можно передать
id=1+OR+TRUE
, что сломает запрос в видSELECT * FROM Articles WHERE id=1 OR TRUE
то есть выберет все данные, а не только запрошенные.
-
Или можно написать
id=1+;+UPDATE+users+SET+password+=+"+123+"+WHERE+login+=+"+admin+";
Это сделает запрос
SELECT * FROM Articles WHERE id=1;UPDATE users SET password="123" WHERE login="admin";
Что обновит админский пароль и по факту откроет полный доступ к сайту.
Для не допущения этого нужно экранировать переменные, подставляемые в запрос, а лучше использовать параметризованные запросы.
- Sql запросы и процедуры могут иметь входные и выходные параметры
- Доступ осуществляется через свойство Parameters класса SQlCommand
- По сути обычные методы, в которых описывается работа с бд Свойства объекта:
- ParameterName – имя параметра(название метода)
- DbType - тип данных(NVarChar…)(параметры в скобочках)
- Size - размер типа(если можно)
- Direction – input/output параметры (есть ли выходное значение)
Стандартный ORM фреймворк .NET и .NETCore:
- Предоставляет возможность использование LINQ(это язык для интегрированных в среду разработки запросов)
- Автоматическое отслеживает изменения объектов в памяти
- Работает со многими реляционными базами данных
- Открытый исходный код с независимым циклом выпуска
Это модель, при которой у нас есть действующая БД и по ней генерируются классы для работы.
Сгенерировать классы можно с помощью Scaffold-DbContext
в командной строке.
DbContext предоставляет:
- CRUD операции (Create, Read, Update, Delete)
- Простой доступ к табличным связям
- Выполнение LINQ запросов как простых SQL запросов
- Управление созданием/удалением/миграцией баз данных
Это класс, который описывает вашу Бд в самом коде
- Как использовать:
- Создать объект:
(Конструктор уже принимает в себя connection string обычно)
var context = new MyDbContext();
- Создать объект:
- Свойства класса :
- Database – обеспечивает создание и удаление
- ChangeTracker – трекер изменения объектов
- Все классы таблицы реализуются как свойства тип DbSet
public DbSet<Student> Students { get; set; }
- LINQ (Language-Integrated Query) представляет простой и удобный язык запросов к источнику данных.
- В качестве источника данных может выступать объект, реализующий интерфейс IEnumerable (например, стандартные коллекции, массивы), набор данных DataSet, документ XML.
- Но вне зависимости от типа источника LINQ позволяет применить ко всем один и тот же подход для выборки данных.
- Методы расширения позволяют "добавлять" методы в существующие типы без создания нового производного типа, перекомпиляции и иного изменения первоначального типа.
- Методы расширения представляют собой разновидность статического метода, но вызываются так же, как методы экземпляра в расширенном типе.
- Для клиентского кода, написанного на языках C#, F# и Visual Basic, нет видимого различия между вызовом метода расширения и вызовом методов, определенных в типе.
- Самые распространенные методы расширения — стандартные операторы запросов LINQ, которые добавляют функции запросов в существующие типы System.Collections.IEnumerable и System.Collections.Generic.IEnumerable.
- Для использования стандартных операторов запросов их необходимо ввести в область действия с помощью директивы using System.Linq.
- Тогда каждый тип, реализующий тип IEnumerable, будет иметь методы экземпляра, в частности GroupBy, OrderBy, Average и т. д.
- Эти дополнительные методы можно видеть в завершении операторов IntelliSense при вводе точки после экземпляра типа IEnumerable, например List или Array.
При использовании подхода Code-First сначала определяется модель в коде, а затем, на ее основе создается (или модифицируется) база данных.
Например:
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public int Age { get; set; }
public byte[] Photo { get; set; }
// Ссылка на заказы
public virtual List<Order> Orders { get; set; }
}
public class Order
{
public int OrderId { get; set; }
public string ProductName { get; set; }
public string Description { get; set; }
public int Quantity { get; set; }
public DateTime PurchaseDate { get; set; }
// Ссылка на покупателя
public Customer Customer { get; set; }
}
- Этот кусок кода определяет данные заказчика и его покупки.
- Для каждого заказчика указывается имя, email, возраст и фотография профиля.
- Идентификатор используется в качестве первичного ключа таблицы Customer.
- Кроме того, в этом классе есть ссылка на коллекцию покупок.
- Эта ссылка выражена в виде виртуального свойства и имеет тип обобщенной коллекции List.
- Класс Order содержит идентификатор заказа, который позволяет уникальным образом распознать каждый заказ в таблице.
- Кроме того этот класс содержит свойства, описывающие название товара, его количество, описание и дату заказа.
- Также здесь указана ссылка на покупателя в виде свойства Customer.
POCO - Plain Old C# Object
IEnumerable<string> query = from word in words
where word.Length == 3
select word
- Join
- Соединяет таблицы с помощью LINQ /методов расширения
var employees = from e in context.Employees join d in context.Departments on d equals e.Department select new { Employee = e.FirstName, JobTitle = e.JobTitle, Department = d.Name };
- Соединяет таблицы с помощью LINQ /методов расширения
- Группировка
- С помощью LINQ можно группировать любые коллекции, включая в базах данных создавая запросы:
var groupedEmployees = from employee in context.Employees group employee by employee.JobTitle;
- С помощью LINQ можно группировать любые коллекции, включая в базах данных создавая запросы: