VBA Excel: РАБОТА С БАЗОЙ ДАННЫХ, МИНИ ORM НА VBA

ORM переводится, как object relational mapping (объектно-реляционное отображение). Это означает, что мы работаем с базой не на уровне SQL запросов, а на уровне объектов.

Давайте я покажу пример и как это будет работать в итоге.

Для начала скажу, что у меня есть база «SQL Server 2016» серьезная база для промышленной реализации, но не пугайтесь она так же доступна простым смертным. Где её скачать расскажу позже.

Каждый раз для использования нашей ORM необходимо объявить и инициализировать несколько переменных c типом DataBase, RecordSet, UnitOfWork. И соединить их с помощью метода Init.

Давайте объявим эти переменные.

Dim db As New DataBase
Dim Rs As New ADODB.Recordset
Dim uow As New unitOfWork

uow.Init db, Rs

Давайте посмотрим, как получить данные.

Для этого объявим переменную с типом User. И реализуем цикл с помощью метода GetAll . Посмотрите, как удобно получать данные о пользователях. Всё что нам надо это просто вызвать метод GetAll в свойстве UserRepository. Точно такие же репозитории будут добавляться и для других типов данных, например, некие документы или накладные InvoiceRepository. После чего в самом цикле мы переберем всех пользователи из базы и выведем их в консоль.

В цикле можно перебрать любые данные. Использовать такие методы, как GetAll очень удобно. Мы не заморачиваемся с SQL запросами и получаем объект определенного типа, свойства и методы которого можно получить и увидеть через IntelliSence. Свойства полученного объекта в нашем примере это Id, Email, UserName объект пока не содержит методов только свойства.

Теперь давайте посмотрим, как добавить запись в базу

Фактически всё что нам нужно это создать объект и задать этому объекту новые значения.

Потом добавить объект через метод “Add”, который есть у любых коллекций и зафиксировать изменения в базе через метод “uow.Commit”.

Если мы запустим наш цикл, который выводил данные мы увидим нашу новую запись.

Та же самая запись сохранилась в базе. При этом Id добавился автоматически, т.е. Id это так называемый первичный ключ в базе, который сама база добавляет автоматически.

И в этом примере добавление данных выглядит крайне удобно и вполне естественно. Создаем объект пользователь, задаем ему значения свойств и добавляем в репозиторий. Всё!

Итак, теперь подробнее про саму реализацию ORM.

Для работы с этим примером нам потребуется несколько программ. Первая программа — это SQL Server 2016. Вы сможете найти её по адресу (https://www.microsoft.com/en-us/sql-server/application-development). Также для работы нам потребуется Management studio (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms). Выберите и нажмите ссылку в зависимости от вашего языка.

В ООП есть специальные шаблоны проектирования, чтобы другие программисты, увидев их понимали, что мы написали и как работает наша программа.

В данном случае у нас есть шаблон проектирования – репозиторий его суть — это удобное переключение баз данных, получение данных в объектном стиле. В моем примере мы работаем с SQL Server 2016, но с шаблоном проектирования репозиторий мы можем поменять базу, например, на MySql или MS Access довольно просто. При этом наша основная логика не пострадает.

Теперь, давайте посмотрим пример поподробнее. Для того, чтобы реализовать шаблон нам надо несколько классов сам репозиторий. В моем примере это – UserRepository. Класс, который объединяет репозитории это класс UnitOfWork. И классы, которые соответствуют таблицам в базе данных такие классы называют модели данных. Они же в идеале должны содержать большую часть логики (которые обычно реализуются через методы), так называемая, богатая модель. Если модель не содержит логики т.е. методов, то её принято называть ананимичная модель т.е. бедная. В моем простом примере нет пока логики, поэтому модель «User» пока можно отнести к ананимичной модели данных.

И так давайте посмотрим на модель User. В классе добавлено три псевдосвойства Id, Email, UserName. У меня есть отдельное видео рассказывающее про эти свойства. Те же самые поля у меня определенны в базе данных. В Management Studio я создал простую базу TestDb и добавил таблицу User. В исходниках у Вас будет SQL файл с запросом на создание этой таблицы. Просто запустите его в своей Management Studio.

Либо в Management Studio добавьте таблицу, нажав правую кнопку -> New -> Table. И добавьте поля Id, Email, UserName. Для Id добавьте тип данных bigint, для Email nvarchar(50), для UserName nvarchar(50).

Потом нажмите правую кнопку мышки по полю Id и выберите “Set primarity key” (добавить). Это тот самый первичный ключ, который автоматически увеличивается при каждом добавлении данных в базу.

Поэтому в свойствах поля проставьте инкремент полю Id, чтобы при каждом добавление записей в таблицу поле Id автоматически увеличивалось на единицу.

UnitOfWork

Давайте теперь посмотрим класс UnitOfWork

Данный класс в основном должен содержать только репозитории и метод Commit для сохранения данных в базе. И может содержать несколько вспомогательных членов.

В моем примере я добавил метод Init, чтобы через этот метод можно было внедрить объекты типа DataBase и Recordset. Т.к. в стандартном конструкторе на VBA нет возможности передать параметры. Такой механизм передачи объектов извне класса называется внедрение зависимостей.

Private pUserRepository As UserRepository

Public Function Init(DataBase As DataBase, Recordset As ADODB.Recordset)
    Set pUserRepository = New UserRepository
    Set pUserRepository.DataBase = DataBase
    Set pUserRepository.Recordset = Recordset
End Function

Public Connection As New ADODB.Connection
Private pStr As String


Private Sub Class_Initialize()
    pStr = "Driver={SQL Server Native Client 11.0};Server=localhost;Database=TestDb;Trusted_Connection=yes;"
    Connection.ConnectionString = pStr
    If Connection.State = 0 Then
        Connection.Open
    End If
End Sub

Друзья я еще не рассказывал, как работать с базами через библиотеку ADODB. Если вы хотите более подробный урок по этой теме просто напишите мне или пролайкайет видео.

Репозиторий (UserRepository)

Сам репозиторий довольно простой для начала я добавил три публичных свойства, чтобы был доступ к самой базе SQL Server через объект типа DataBase. Добавил свойство RecordSet для работы с ним внутри метода GetAll. В принципе сам класс UserRepositroy можно запускать независимо, но для реализации шаблона я инициализирую репозиторий в классе UnitOfWork.

Public LocalUsers As New Collection
Public DataBase As DataBase
Public Recordset As ADODB.Recordset

И далее два основных метода. Метод GetAll и метод Add. Метод GetAll просто получает все записи из базы данных и переводит их в объект коллекцию типа User c помощью встроенного объекта RecordSet. Внутри метода я запускаю SQL запрос на выборку нужных мне полей. А далее через объект RecorSet получаю их.

Public LocalUsers As New Collection
Public DataBase As DataBase
Public Recordset As ADODB.Recordset


Public Function GetAll() As Collection
    Dim selectAllQuery As String:
    selectAllQuery = "Select Id, Email, UserName from [User]"
    
    If DataBase Is Nothing Then MsgBox "DataBase not found set it"
    If Recordset Is Nothing Then MsgBox "RecordSet not found set it"
    Recordset.Open selectAllQuery, DataBase.Connection
    
    Dim userCollection As New Collection
    Dim user As user
    With Recordset
        While (Not .EOF)
            Set user = New user
            
            user.Id = .Fields(0).Value
            user.Email = .Fields(1).Value
            user.UserName = .Fields(2).Value
            
            userCollection.Add user
            
            .MoveNext
        Wend
    End With
    
    Recordset.Close
    Set GetAll = userCollection
End Function

Public Function Add(user As user)
    LocalUsers.Add user
End Function