Tworzenie projektu bazodanowego SQL Server w Visual Studio

Dziś powiemy sobie w jaki sposób stworzyć projekt bazodanowy w Visual Studio. W obecnych czasach standardem stało się tworzenie rozwiązań bazodanowych w oparciu o SQL Server przy wykorzystaniu Visual Studio i dodatku Data Tools. Narzędzie to w połączeniu z systemami kontroli wersji takimi jak GIT daje nam nie tylko stabilne ale również efektywne środowisko pracy.

Zacznijmy od krótkiego wstępu gdzie powiemy sobie z czym mamy do czynienia. Przy pomocy Visual Studio możemy stworzyć następujące projekty powiązane z SQL Server:

• Database Engine,
• Integration Services,
• Analysis Services,
• Reporting Services.

Projekt bazodanowy jest wbudowany w Visual studio 2019, a pozostałe komponenty są dostępne w ramach opcjonalnego rozszerzenia. Rozszerzenie to możemy doinstalować do pełnoprawnego Visual Studio bądź też  wybrać odchudzoną wersje Shell posiadająca tylko i wyłącznie wspomniane wyżej typy projektów. Dzięki wykorzystaniu Visual Studio dostajemy cały szereg użytecznych funkcjonalności wbudowanych w to narzędzie jak :

• kolorowanie składni i Intellisense,
• debugging kodu,
• możliwość wykonania standardowych operacji Build i Rebuild pozwalających na sprawdzenie powstałego kodu i utworzenie pliku wynikowego,
• łatwy deployment na serwer docelowy,
• automatyczne generowanie plików deploymentu do wdrażania przyrostowego i pełnego,
• możliwość podpięcia pod system kontroli wersji,
• możliwość używania zmiennych środowiskowych oraz pre i post deployment skryptów,
• możliwość porównania schematu pomiędzy projektami, bazami danych itp (pisałem kiedyś o tej funkcjonalności tutaj)

Te i wiele innych zalet powoduje, że to właśnie Visual Studio jest najlepszym miejscem do tworzenia i utrzymywania naszych projektów związanych z SQL Server i usług pokrewnych. Dzisiejszy artykuł ma na celu wprowadzić w świat projektów bazodanowych i w jego ramach pokażemy sobie jak taki projekt w ogóle stworzyć.

W mojej demonstracji będę używał Visual Studio 2019 ale sposób tworzenia tego typu projektów nie różni się znacząco pomiędzy współczesnymi wersjami tego narzędzia. Pierwszym krokiem po otwarciu narzędzia będzie oczywiście wybranie opcji File -> New ->Project tak jak zostało to zaprezentowane poniżej:

W otwartym oknie musimy wyszukać projekt SQL Server Database Project i klikamy Next:

W kolejnym oknie nadajemy nazwę naszemu projektowi oraz solucji w której będzie się on znajdował. Dodatkowo możemy wskazać ścieżkę na dysku gdzie będą przechowywane pliki w ramach solucji. Jeśli wszystko mamy gotowe możemy kliknąć Create:

W rezultacie naszych działań powinniśmy otrzymać pusty projekt bazodanowy. Jego obecne “struktury” możemy podejrzeć w oknie Solution Explorer:

W tym momencie możemy w ramach projektu stworzyć każdy obiekt ręcznie w odpowiedniej strukturze folderów. Aby tego dokonać wystarczy kliknąć prawym przyciskiem myszy na projekt i z menu kontekstowego wybrać Add, a następnie typ obiektu, który chcemy stworzyć:

W ramach niniejszego artykułu wybierzemy jednak nieco inną drogę. Zaimportujemy sobie struktury z istniejącej bazy danych wrzuconej na serwer. Zrobimy to klikając prawym przyciskiem myszy na projekt i wybierając Import -> Database. Jak możecie zauważyć poniżej baza danych nie jest jedynym wyjściem – możemy również importować dane ze skryptu .sql lub dacpac:

Definiowanie  importu struktury bazy danych jest raczej intuicyjne i nie powinno nikomu przysporzyć problemów. Okno tej operacji możecie zobaczyć poniżej:

Pierwszym krokiem jest zdefiniowanie połączenia do bazy danych, którą chcemy zaimportować – w moim przypadku podłączę się do lokalnej instancji SQL Server i znajdującej się tam bazy Adventure Works. Sekcję Target project pozostawiamy pustą ponieważ chcemy dodać struktury do istniejącej solucji, a w ramach niej mamy tylko jeden projekt.

Warto zwrócić uwagę również na ustawienie Folder structure gdzie mamy do dyspozycji opcje None, Schema, Object Type, Schema/Object Type. Polecam wybranie tej ostatniej dzięki czemu powstała w ramach projektu struktura folderów będzie grupowała obiekty najpierw po schemacie, a później po typie obiektu. Jak już będziemy gotowi aby rozpocząć import możemy kliknąć Start i wtedy VS wykona za nas całą robotę. Po zakończeniu powinniśmy zobaczyć okno z podsumowaniem:

Okno Solution Explorer odzwierciedla w tym momencie strukturę bazy Adventure Works zaimportowaną z serwera ładnie i przejrzyście pogrupowaną w foldery:

Mając do dyspozycji obiekty możemy je oczywiście w dowolnym momencie edytować i dostosowywać do własnych potrzeb. Dla przykładu możemy spróbować typ danych kolumny, jak widać możemy to zrobić zarówno po stronie edytora graficznego jak i kodu. Dodatkowo błędy składni są sygnalizowane natychmiast i linie które zmodyfikowaliśmy oznaczone są kolorem (domyślnie żółty oznacza zmodyfikowany ale nie zapisany, zielony oznacza zmodyfikowany i zapisany):

Całkiem dużo funkcjonalności przy prostej definicji tabeli prawda? A to tylko początek całej gamy możliwości jakie daje nam projekt tego typu. Za chwilę spróbujemy stworzyć sobie tzw. Publish Script ale zanim tego dokonamy musimy dokonać pewnych zmian – w moim przypadku wykonałem następujące modyfikacje:

• Zwiększyłem rozmiar pola AddressLine1 w tabeli Person.Address z NVARCHAR(60) na NVARCHAR(160),
• Dodałem tabele TEST zawierającą kolumnę ID typu BIGINT,
• Usunąłem widok [Person].[vAdditionalContactInfo].

Pierwsza zmiana została przedstawiona powyżej jednak pokażę jak dodać tabelę. Aby dodać jakikolwiek inny obiekt musimy zlokalizować folder w solucji, który będzie nas interesował (zgodnie z przyjętą nomenklaturą będzie to Schemat/Typ Folderu) i kliknąć prawym przyciskiem myszy i wybrać Add -> Table:

W otwartym oknie możemy jeszcze wybrać inny typ obiektu, który by nas interesował – podajemy również pełną nazwę obiektu:

Efektem naszych działań jest znane już nam okno definiowania tabeli gdzie możemy dostosować strukturę tabeli, zapisać i zamknąć:

Usuwanie obiektów jest raczej proste i sprowadza się do kliknięcia prawym przyciskiem na wybrany obiekt i wybranie opcji Delete. Mając już wszystkie zmiany możemy kliknąć prawym przyciskiem myszy na projekt i wybrać Rebuild aby zbudować projekt i sprawdzić ewentualne błędy. Jeżeli takowych nie ma jeszcze raz klikamy prawym przyciskiem myszy na projekt i tam z menu kontekstowego wybieramy Publish:

W oknie publikacji przede wszystkim definiujemy połączenie do docelowego serwera i bazy danych:

Następnie klikamy Advanced aby przejść do kilku opcji, które powinniśmy ustawić:

Najważniejsze z nich to:

• Deploy database properties – zaznaczamy jeśli chcemy wrzucać właściwości zdefiniowane w projekcie na serwer, osobiście z tej opcji rzadko korzystam.
• Always re-create database – jeśli zaznaczymy to istniejąca baza danych będzie usunięta i stworzona na podstawie solucji – do wykorzystania w zależności od sytuacji ale należy jak zawsze uważać.
• Block incremental deployment if data loss might occur – jeśli robimy deployment przyrostowy (czyli przeciwieństwo drop – create) to w 99% przypadków nie chcemy naruszyć danych w bazie docelowej i właśnie ta opcja powoduje to, że deployment zakończy się błędem jeśli Visual Studio wykryje, że jakakolwiek zmiana może spowodować utratę danych.
• Execute deployment  script in single user mode – baza docelowa będzie przełączona w tryb single user na czas deploymentu.
• Backup database before deployment – przed rozpoczęciem deploymentu wykonana zostanie kopia zapasowa docelowej bazy danych.

Reszty opcji nie będę tłumaczył jednakże warto się z nimi zapoznać aby mieć świadomość co one takiego zmieniają. Gdy już jesteśmy gotowi klikamy OK i wracamy do głównego okna.  Aby nie musieć definiować wszystkich tych opcji za każdym razem możemy wszystkie nasze ustawienia zapisać jako profil:

Dzięki temu plik publish.xml zostanie zapisany w ramach solucji i w momencie gdy będziemy chcieli wrzucić nasze zmiany możemy użyć tego właśnie pliku. Dodatkowo warto wspomnieć, że możemy mieć wiele różnych plików z różnymi konfiguracjami co rozszerza nieco nasze możliwości. Na ten moment spróbujmy wygenerować plik klikając widoczny  na powyższym zrzucie przycisk Generate Script – w wyniku tego działania możemy podejrzeć co Visual Studio chce zrobić na serwerze wynikowym – cały powstały skrypt możecie zobaczyć poniżej. Zawarte są w nim wszystkie zmiany jakie należy wykonać aby baza na serwerze docelowym miała takie struktury jak ta zdefiniowana w naszej solucji:

/*
Deployment script for AdventureWorks2017

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "AdventureWorks2017"
:setvar DefaultFilePrefix "AdventureWorks2017"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'Dropping [Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]...';


GO
DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
    ON [Person].[Address];


GO
PRINT N'Altering [Person].[Address]...';


GO
ALTER TABLE [Person].[Address] ALTER COLUMN [AddressLine1] NVARCHAR (160) NOT NULL;


GO
PRINT N'Creating [Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
    ON [Person].[Address]([AddressLine1] ASC, [AddressLine2] ASC, [City] ASC, [StateProvinceID] ASC, [PostalCode] ASC);


GO
PRINT N'Creating [dbo].[Test]...';


GO
CREATE TABLE [dbo].[Test] (
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Refreshing [HumanResources].[vEmployee]...';


GO
EXECUTE sp_refreshsqlmodule N'[HumanResources].[vEmployee]';


GO
PRINT N'Refreshing [Purchasing].[vVendorWithAddresses]...';


GO
EXECUTE sp_refreshsqlmodule N'[Purchasing].[vVendorWithAddresses]';


GO
PRINT N'Refreshing [Sales].[vIndividualCustomer]...';


GO
EXECUTE sp_refreshsqlmodule N'[Sales].[vIndividualCustomer]';


GO
PRINT N'Refreshing [Sales].[vSalesPerson]...';


GO
EXECUTE sp_refreshsqlmodule N'[Sales].[vSalesPerson]';


GO
PRINT N'Refreshing [Sales].[vStoreWithAddresses]...';


GO
EXECUTE sp_refreshsqlmodule N'[Sales].[vStoreWithAddresses]';


GO
PRINT N'Creating [Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Nonclustered index.', @level0type = N'SCHEMA', @level0name = N'Person', @level1type = N'TABLE', @level1name = N'Address', @level2type = N'INDEX', @level2name = N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode';


GO
PRINT N'Update complete.';


GO

Po prześledzeniu skryptu widzimy, że odzwierciedlone zostały prawie wszystkie nasze zmiany. Jak słusznie zauważyła Moni w komentarzu brakuje komendy usuwającej widok mimo, że wykonałem taką operację. Stało się tak dlatego, że domyślnie usunięte obiekty z projektu nie są usuwane z docelowej bazy danych. Jeśli chcielibyśmy zmienić to zachowanie musimy wejść we wspomniane wcześniej zaawansowane ustawienia w publish skrypcie i tam w sekcji Drop możemy ustawić co ma być usuwane (jeśli w ogóle) przy gdy czegoś nie ma w projekcie, a jest w docelowej bazie danych:

Przechodząc dalej przez skrypt możemy dostrzec również, że skrypt musi działać w trybie SQLCMD o którym z całą pewnością napiszę parę słów w ramach osobnego artykułu na ten moment o nim tylko wspominając. W tego typu skrypcie warto znaleźć wszelkie komunikaty “data loss may occur” jeśli tylko występują, dzięki czemu będziemy wiedzieć, że akurat taka a nie inna operacja spowoduje utratę danych i Visual Studio dokonuje sprawdzenia czy np. dana tabela zawiera wiersze. Jest to użyteczna wskazówka szczególnie na początku przygody z Data Tools w późniejszym czasie wejdzie nam to w krew na tyle, że automatycznie sami rozpoznamy sytuacje tego typu.

Po wygenerowaniu pliku możemy go uruchomić korzystając z przycisku dostępnego w górnej części okna:

Oczywiście nie musimy generować pliku i dopiero go uruchamiać – możemy to zrobić bezpośrednio z poziomu profilu:

Oczywiście jest wiele kwestii o których nie powiedziałem jak np. zmienne, referencje, pre i post deployment scripts i cała masa innych aspektów związanych z developmentem baz danych i automatyzacją. Na te i wiele innych kwestii przyjdzie jeszcze czas w ramach kolejnych wpisów na ten moment mam nadzieję, że ten artykuł będzie dobrym początkiem – pozdrawiam.