Zine.net online

   SQL Server Event Notifications (EN) to właściwość wprowadzona już w wersji 2005 ale niedoceniona przeze mnie wówczas i myślę, że przez wielu entuzjastów SQL-a. Funkcjonalnie działanie opiera się na SQL Server Service Broker i dlatego implementując EN warto wesprzeć się na moim add-in do SSMS czyli Service Broken, którego źródła są tutaj. Wersja tam znajdująca działa z wersją SQL Server 2008 (również Express).

   Dlaczego warto mieć takie narzędzie do wsparcia? Dlatego, że Service Broker bywa bardzo kapryśny w implementowaniu. Kiedy wydaje się, że już wszystko zostało zrobione i następuję moment końcowego odliczania do uruchomienia implementacji nagle okazuje się, że po naciśnięciu klawisza F5 następuje głucha cisza. Wówczas niezbędne okazuje się sprawne przejrzenie właściwości i infrastruktury Service Broker'a aby odkryć możliwą przyczynę braku działania. Szczególnie przydatna w takich momentach jest grupa opcji Show oraz Other options, która w prosty i szybki sposób dostarcza szeregu informacji w postaci okienkowej bez konieczności znajomości wywołań widoków systemowych związanych z Service Broker.

   Z moich doświadczeń wynika, że najlepszym rozwiązaniem jest posiadanie zaimplementowanego jednego bazowego rozwiązania, które tworzyłoby środowisko nasłuchujące zdarzenia generowane przez SQL Server. Taka implementacja powinna mieć dosyć szeroką klasę event'ów do nasłuchiwania. Dosyć szeroką ale nie za szeroką. W moich doświadczeniach skupiłem się głównie na klasie TRC_ERRORS_AND_WARNINGS, która obejmuje głownie sytuacje wyjątkowe oraz informacje o zachowaniu się SQL Server (interesowała mnie głownie ta klasa a nie klasa DDL_EVENTS ponieważ interesowały mnie głównie błędy generowane prze implementację posadowione na SQL Server). Napisałem, że nie powinna być za szeroka ponieważ w doświadczeniach posunąłem się do zastosowania klasy TRC_ALL_EVENTS co pokazało, że tabela do przechowywania komunikatów "pompowanych" przez SQL Server w błyskawicznym tempie zaczęła się zapełniać masą rekordów z komunikatami zdarzeń. Ta implementacja bazowa byłaby wznawiana na czas uruchomień nowych implementacji EN nasłuchując możliwych sytuacji wyjątkowych, w tym związanych z tworzoną właśnie nową implementacją. Poz zakończeniu wdrażania nowej implementacji EN tę bazową należałoby wyłączyć do czasu uruchomienia następnej.

   Nie chcę tu szczegółowo opisywać jak zaimplementować EN. Jest to niezwykle proste. Przykład jest tutaj. Na co jednak warto zwrócić szczególną uwagę?

1. Najważniejsze jest aby baza danych, której dotyczy EN miała włączony Service Broker. Bez tego nie mam mowy o działaniu EN. Tworzenie nowej bazy danych przez GUI z SSMS domyślnie wyłącza Service Broker. Więc jest to punkt krytyczny. Mój add-in pozwala na włączenie i wyłączenie go z zakładki Brokers, która pokazuje wszystkie Service Broker's aktualnie dostępnego servera SQL. Odblokowanie i zablokowanie odbywa się poprzez naciśnięcie kontrolki check box.
   
2. Tworząc subskrypcje zdarzeń przez CREATE EVENT NOTIFICATION należy bezwzględnie pamiętać, że nazwa serwisu jest literałem a nie sysname i do tego jest case sesitive i jest to błąd czasami trudny do zauważenia (dlatego tworząc tę subskrypcję należy do nazwy serwisu zastosować metodę Copy-Paste nazwy serwisu z polecenia CREATE SERVICE).
3. Jeżeli w tworzeniu subskrypcji EN korzystamy ze składni 'current database' informującą, że subskrypcja odnosi się do bieżącej bazy danych to literał ten jest również case sensitive.
4. Poza stosowaniem metody Copy-Paste do nazwy serwisu zdecydowanie odradzam stosowanie tej metody to tworzenia nowej implementacji na podstawie już istniejącej. Wbrew pozorom trzeba dokonać zmian w wielu miejscach i pominięcie choćby jednej zmiany owocuje w braku działania implementacji EN. Schemat tworzenia EN jest prosty i można go szybko opanować: tworzenie kolejki, tworzenie serwisu w oparciu o kontrakt http://schemas.microsoft.com/SQL/Notifications/EventNotification, utworzenie implementacji EN w oparciu o wcześniej utworzony serwis z wyborem określonych klas zdarzeń, utworzenie procedury przetwarzającej komunikaty z kolejki, aktywowanie kolejki z utworzoną wcześniej procedurą. Korzystając z Service Broken do SSMS można to zrobić bez znajomości poleceń T-SQL, korzystając głównie z GUI tego dodatku (poza oczywiście tworzeniem procedury aktywującej, którą trzeba napisać dostosowując do do konkretnej implementacji).
5. EN generuje zdarzenia w postaci dokumentów XML i tworząc tabelę rejestrującą komunikaty warto utworzyć kolumnę, której zawartość będzie pobierana elementu z EVENT_INSTANCE/EventType oraz kolumnę na moment powstania zdarzenia, której zawartość pochodzi od elementu EVENT_INSTANCE/PostTime. Pierwsza informacja pozwala nam szybko zorientować się z jakim zdarzeniem mamy do czynienia a druga pozwala zgrupować szereg rekordów dotyczących tej samej sytuacji wyjątkowej. No i oczywiście kolumna na sam dokumenty XML. Jak może wyglądać jego konkretny wygląd dla poszczególnych zdarzeń można przeczytać ten dokument (jeśli ktoś lubi czytać dokumenty XSD).
6. W przypadku problemów zobaczyć co zwraca zapytanie: SELECT * FROM sys.server_event_notifications. Może się okazać, że w zestawie odpowiedzi nie będzie naszego EN. Wówczas należy przyjrzeć się całej strukturze tworzonej w pkt. 4 czy nie zawiera błędów.

Podsłuchowywując :) SQL Server, szczególnie poprzez TRC_ALL_EVENTS można si wiele dowiedzieć o tym jak funkcjonuje SQL Server. Widać jak ważna jest baza master oraz jak często używany jest SHOWPLAN_XML.

 W SSMS wpisując polecenie:

RAISERROR('Uuuuuuuuuus!", 16, 1)

dowiadujemy się, że idzie za tym zdarzenie USER_ERROR_MESSAGE. Natomiast polecenie

DELETE FROM [blablabla]

generuje najpierw zdarzenie EXCEPTION (w SSMS, na zakładce Messages jest pierwsza linia komunikatu zawierająca treść Msg 208, Level 16, State 1, Line 1), natomiast drugim zdarzeniem jest USER_ERROR_MESSAGE i odpowiada to drugiej linii komunikatu na zakładce Messages: Invalid object name 'blablabla'.

Jak to z podsłuchowywaniem bywa można się dowiedzieć rzeczy niekoniecznie fajnych. Pisałem już o tym, że postawienie bazy w tryb offline powoduje dziwne komunikaty przy generowaniu skryptów z bazy danych. Okazuje się, że ten wyjątek jest generowany stale w EN przy każdej sytuacji gdy server używa używa do jakichś celów listy baz podpiętych do server'a. Błąd jest chyba w metodzie ConnectionInfo.

EN są świetnym narzędziem, które pozwala na lekką implementacje systemu wczesnego ostrzegania o sytuacjach wyjątkowych dziejących się na bazie danych obsługiwanych przez nasza aplikację. EN nie generują dodatkowego narzutu na działanie server'a ponieważ są one generowane stale niezależnie czy mamy zaimplementowane EN czy nie. Jeżeli log zdarzeń postawimy na odrębnym wolumenie niż baza produkcyjna to może to być rozwiązanie bardzo optymalne.

Dla zainteresowanych podaję linki do najbardziej interesujących przypadków śledzenia sytuacji wyjątkowych przy pomocy EN:

1. Alertowanie zdarzenia DEADLOCK_GRAPH poprzez e-mail opisane na blogu ReSQueL.
2. Alertowanie zdarzenia BLOCKED_PROCESS_REPORT opisane na blogu Tony Rogerson'a.

Hmm, udało sie zrobić notkę na blogu bez obrazków :)

Ostatnio na forum wss.pl było sporo wątków na temat niejawnych konwersji dokonywanych przez SQL Server i zagrożeniach z tego płynących. Problemów związanych z niejawną konwersją dotyczyło również moje zgłoszenie na microsoft.connect.com dotyczące wyrażeń zawierających wartości typu datetime i datetime2. Ale robiąc wpis na moim blogu dotyczącym przeniesienia danych tabelarycznych ze strony HTML do tabeli SQL Server natknąłem się na problem jawnej konwersji (kolumna z poprzednim miejsce w rankingu była liczbą ujętą w nawiasy lub skrótem 'N.Rkd' gdy drużyna nie była dotąd notowana).
Na początek zasadnicze pytanie: co to jest?

(1)

Czy to jest wyrażenie numeryczne? Tak mi się wydawało.

Ale:

select CAST('(1)' as numeric)

zwraca zaskakujący wynik:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

W takim razie spróbujmy zastosować funkcję ISNUMERIC do określenia czy według SQL Server jest to wyrażenie numeryczne. W BOL dla tej funkcji jest wyraźnie napisane:

Determines whether an expression is a valid numeric type

Kiedyś przeanalizowałem cały zestaw znaków szukając tych dla których funkcja ta zwróci wartość 1. Więc wiem, że niekoniecznie musi być to liczba. Ale:

select ISNUMERIC('(1)')

zwraca zero. Czyli po prostu to nie jest wyrażenie numeryczne według SQL Server. Dziwne.

Właściwie to powinien być kolejny wpis do mojego dziennika pokładowego MSSQL 2008 ale ostatecznie uznałem, że dziennik pokładowy będzie pokazywał raczej dziwne przypadki użycia ;-)

Wersja 2008 ma bardzo fajną nową funkcjonalność w postaci generatora listy dodawanych wierszy poprzez INSERT. Więcej na ten temat można znaleźć tutaj.

Chciałem zastosować tę funkcjonalność do przetworzenia przecinkowej listy w tablicę zawierającą poszczególne elementy tej listy. Elementy tej listy miałby być wyrażeniami dowolnych typów.

Skroiłem więc procedurę składowaną, która będzie przekształcała tę listę w postać dogodną do użycia przez tę funkcjonalność oraz która będzie ostatecznie wstawiała elementy tej listy do tabeli wynikowej (zakładam, że na innym poziome będzie się odbywała kontrola czy lista nie zawiera przypadkiem SQL Injection):

CREATE PROCEDURE List2Table (     @list nvarchar(max) ) AS BEGIN CREATE TABLE #t(value SQL_VARIANT) SET @list = '('+REPLACE(@list, ',','),(')+')' DECLARE @sql nvarchar(max) = N'INSERT INTO #t VALUES '+@list EXECUTE sp_executesql @stmt = @sql SElECT value FROM #t DROP TABLE #t END

Ale uruchomienie procedury dla przykładowych danych testowych:

DECLARE @list nvarchar(max) = N'3,getdate(),234.56,newid(),''text''' EXECUTE List2Table @list = @list

kończy sie komunikatem błędu:

Msg 206, Level 16, State 2, Line 1 Operand type clash: uniqueidentifier is incompatible with datetime (0 row(s) affected)

Co ma piernik do wiatraka? Przecież utworzyłem tabelę z kolumną typu SQL_VARIANT, która powinna “łyknąć” wszystko jak leci.

W takiej sytuacji trzeba zajrzeć do planu wykonania. Ale do tego celu musimy wykorzystać przykład bez dynamicznego SQL ponieważ plan zapytania procedury z dynamicznym SQL nie daje satysfakcjonującej odpowiedzi na przyczynę takiego zachowania. Czyli na przykład dla takiego zapytania:

declare @t table (x sql_variant) insert into @t values (2),( 'abc'), (GETDATE()), (2.345)

Robiąc  różne kombinacje z typami na liście doszedłem do wniosku, że działa to w ten sposób:

• parser analizuje listę wyszukując w niej wyrażenie o typie z najwyższym priorytetem,
• następnie tworzy rzutowania wszystkich elementów listy na ten typ,
• a na zakończenie rzutuje uzyskane powyżej wyniki poszczególnych wartości z listy na docelowy typ kolumny w tabeli.

przy takim podejściu to rzeczywiście będą błędy. Moim zdaniem powinno być tak, że generator planu zapytania buduje rzutowania poszczególnych wartości z listy ale na typ SQL_VARIANT a dopiero potem te wartości próbuje zapisać do tabeli. SQL_VARIANT ma najwyższy priorytet w związku z tym  błąd może wystąpić na próbie zapisu do tabeli a nie na etapie przygotowania wartości do zapisu ale to już zupełnie inna sytuacja.

W wyniku tego jeżeli wszystkie elementy listy będą miały jednakowy typ (jakikolwiek) i będą zgodne z typem tabeli docelowej lub mające implikowany operator rzutowania wszystko działałoby “po staremu” a jednocześnie można byłoby mieć bardziej “otwartego” INSERT’a możliwością wykorzystania nowej funkcjonalności generatora rekordów.

Na wss.pl pojawił się wątek dotyczący działań na typach datetime.
Jeden z uczestników dyskusji stwierdził, że operacja odejmowania dwóch wartości datetime daje w wyniku czas.
Jak pokazałem, zapytanie

SELECT GETDATE() - GETDATE()

daje w wyniku 1900-01-01 00:00:00.000 czyli nadal typ datetime (należało się tego spodziewać) a dopiero po jawnym zrzutowaniu na float widzimy numeryczną reprezentację z różnicy dat (dlaczego nie na time, o tym mała uwaga na końcu notki). Ale pomyślałem sobie, że w pewnych warunkach wynik może być dodatni lub ujemny i to niedeterministycznie. Postanowiłem sprawdzić jak jest naprawdę.

Zrobiłem sobie funkcję:

ALTER FUNCTION retard ( ) RETURNS datetime AS BEGIN DECLARE @i int = 0 WHILE (@i < 10000000) set @i = @i+1 RETURN '1900-01-01 00:00:00.000' END

i zrobiłem zapytanie:

select GETDATE() select dbo.retard() select GETDATE()

Na moim komputerze opóźnienie było kilkusekundowe. Wtedy zrobiłem kolejne zapytanie:

SELECT GETDATE() - dbo.retard() - GETDATE()

Ale tym razem wynik był zerowy (czyli 1900-01-01 00:00:00.000).

Hmm, dziwne. Po obejrzeniu planu zapytania widać, że dwa odwołania do funkcji getdate() generują ConstExpr1001 i ConstExpr1002 ale są one wartościowane w bliskim sobie czasie i server taktuje je jako stałe znane mu w czasie wykonania i w związku z tym wartościowane przed wartościowaniem innym składników zapytania.

Skoro tak to opakujmy funkcję getdate() o tak:

CREATE FUNCTION myGetdate ( ) RETURNS datetime AS BEGIN RETURN getdate() END

i zróbmy zapytanie:

select dbo.mygetdate() - dbo.retard() - dbo.mygetdate()

Oooo i teraz jest efekt!
I widać z tego, że przy prostych operatorach arytmetycznych wartościowanie jest w kolejności wystąpienia (bo wynik jest mniejszy od 1900-01-01 00:00:00.000) . Hmm, skoro tak to zróbmy tak:

select dbo.mygetdate() - cast((dbo.retard() + dbo.mygetdate() ) as int)

Tym razem wynik jest większy od 1900-01-01 00:00:00.000 czyli drugi człon odejmowania jest wartościowany wcześniej. Ale nie ustając w poszukiwaniach zróbmy jeszcze takie zapytanie:

select dbo.mygetdate() - cast((dbo.retard() + dbo.mygetdate() ) as datetime)

Ale tym razem mimo, że spodziewalibyśmy się że drugi człon odejmowania będzie wartościowany wcześniej to wynik odejmowania jest mniejszy od 1900-01-01 00:00:00.000.

Zaglądając do planu zapytania widzimy, że mimo zastosowaliśmy jawne rzutowanie to parser zignorował je ponieważ wiedział, że nie ma takiej potrzeby skoro wszystkie składniki są typu datetime.

Jak widać po tych rozważaniach wynik zapytania będącego treścią tego wpisu jest trudny do przewidzenia. A przy okazji widać, że XML plan prawdę Ci powie ;-)

Rzutowanie różnicy dat na typ time.

Typ time ma dziedzinę wartości (dla maksymalnego rozmiaru time(7)) od 00:00:00.0000000 do 23:59:59.9999999 wobec tego nie istnieje coś takiego jak ujemny czas. Rzutowanie typu datetime na time zwraca z rezultatu datetime jedynie część informującą o czasie.Wobec tego zapytanie

SELECT cast(getdate() - (getdate()+1) AS time(7))

Zwróci wynik 00:00:00.0000000, podobnie jak zapytanie

SELECT cast((getdate()+1) - getdate() AS time(7))

Ale zapytanie

SELECT cast(getdate() - (getdate()+0.3333333333333) AS time(7))

zwróci wynik 16:00:00.0030000 podczas gdy zapytanie

SELECT cast((getdate()+0.3333333333333) - getdate() AS time(7))

zwróci wynik 07:59:59.9970000.

Dlatego operując na interwałach daty i czasu należy jednak polegać na funkcji DATEDIFF. Aby się przekonać co do następstwa dat (czyli wiedzieć czy tyle czasu upłynęło czy tyle czasu upłynie) wystarczyłoby wiedzieć jaki jest znak wyniku tej funkcji dla największej granulacji skali czasowej, czyli dla mikrosekund.

Czyli możemy zbudować zapytanie:

DECLARE @interval float = -0.000001 DECLARE @d1 datetime = getdate() DECLARE @d2 datetime = getdate()+@interval DECLARE @diff int = DATEDIFF(MICROSECOND, @d1, @d2) IF (@diff > 0) print 'Trzeba czekać...' ELSE print 'Minęło...'

I wszystko było pięknie dopóki nie ustawimy absolutnej wartości zmiennej @interwal na większą od 0.02485517 (tak wyszło z moich doświadczeń chociaż teoretycznie powinno to być więcej niż 0.0248551348032). Wówczas cała zabawa kończy się komunikatem

Msg 535, Level 16, State 0, Line 12 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Co jest oczywiste ze względu na fakt, że funkcja DATEDIFF jest typ int a zwracany wynik jest w mikrosekundach (dzień zawiera 86400000000 mikrosekund co jest poza zakresem tego typu).

Mając przed sobą bazę liczącą setki tabel oraz setki procedur składowanych postanowiłem zobaczyć ile zajmie pełny jej skrypt. Uruchomiłem więc opcję Task->Generate Scripts… z menu kontekstowego bazy danych. uruchomiłem więc tę opcję i po pewnym czasie na ekranie ukazał się komunikat:



Po kliknięciu na link Exception type… pokazał się kolejny dramatyczny komunikat:

Pomyślałem sobie, niemożliwe, że taki błąd przydarzył się tylko mi. Googlownica po chwili zwróciła mi kilka linków, które opisują podobny problem, z których wybrałem dwa:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24845124.html

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=378085

Jak widać drugi link jest zgłoszeniem problemu do Microsoft.

Nie zrażając się tym problemem wziąłem na tapetę tym razem dużo mniejszą bazę. Zaznaczyłem wszystkie obiekty do generowania. Uruchomiłem, wszystko poszło OK. Uruchomiłem wygenerowany skrypt na innym serwerze i… totalna klapa. Brakowało kilku tabel! Ponowiłem więc jeszcze raz generowanie skryptu dla wszystkich obiektów (tym razem sprawdzając wszystko dwa razy) ale nic to nie zmieniło: tabel nadal brakowało. Zastanawiałem się czym się te tabele od innych różnią stwierdziłem, że zawierają triggers’y i constraints’y. Ale co to może przeszkadzać? Wygenerowałem więc małą bazę z z dwiema tabelkami “2x2” :) dodałem constrains i trigger, uruchomiłem generate scripts na tej bazie i tu kolejne zdziwienie.

Dlaczego error? kliknąłem w link Failed to retrieve… i nie mogłem uwierzyć własnym oczom:

MSSQL tworząc skrypt dla bazy test domagał się dostępu do innej bazy (która przez przypadek była offline, ale to już chyba raczej błąd ConnectionInfo).

Trzy takie przypadki z jednej opcji to trochę za dużo. Gdy sobie przypomnę prostotę generowania skryptów w wersji 2000 to trudno sie oprzeć refleksji, że “lepsze jest wrogiem dobrego”. Do czasu pojawienia się jawnie określonej poprawki do tej opcji stanowczo odradzam korzystania z niej.

Rama do tworzenia wtyczek do SSMS została opublikowana pod adresem http://nextssmsaddin.codeplex.com/. Zapraszam do odwiedzania, korzystania, pobierania, komentowania, krytykowania, poprawiania, ulepszania (chętni do współtworzenia mogą się do mnie zgłaszać przez kontakt na tej stronie w celu dopisania do zespołu).

Wtyczka została napisana w Visual Studio 2005. Ale działa pod SSMS 2005 i 2008 (również w wersji Express).

Podsumowanie wszystkich wpisów w kolejności (bez inwokacji :) )

1.	Start
2.	EventWatcher
3.	ObjectExplorer, ConnectionString, Command
4.	Menu
5.	Exec
6.	Window
7.	The end
8.	http://nextssmsaddin.codeplex.com/

W tym odcinku zaprezentuję schemat dodawania własnych okien (jako obiektów UserControl) do wtyczki SSMS. Wykorzystam do tego celu okna, które zastosowałem w innej wtyczce do SSMS, która zarządzała obiektami Service Broker. Zawartość wyświetlana przez okna będzie zmieniała się dynamicznie przy zmianie dwóch kontekstów:

• kontekst serwera – okno pokazujące obiekty Service Broker aktywne dla danego serwera, w kontekście którego aktualnie się znajdujemy w SSMS (z możliwością zmiany statusu danej instancji Service Broker),
• kontekst bazy danych – okno pokazujące infrastrukturę obiektów Service Broker dla kontekstu aktualnie wybranej bazy danych w Object Explorer SSMS.

I. Window

Tak jak pisałem powyżej, nasze okno będzie komponentem UserControl z implementacją prostego interfejsu zapewniającego odświeżenie jego zawartości:

using System; using System.Collections.Generic; using System.Text; namespace ToolWindows {     public interface IRefreshData     {         void RefreshData();     } }

Kontrolka prezentująca obiekty Service Broker jest bardzo prosta. Składa się z kontrolki Menu oraz kontrolki DataGridView.

 

Chociaż okienko odświeża się automatycznie opcja menu Refersh została dodana jedynie dla przykładu możliwości wstawiania własnego menu do okienka wtyczki. Metoda RefreshData dla tego okna jest bardzo prosta.

public void RefreshData() {     string connectionString = UtilitySqlTools.Current.ConnectionString();     using (SqlConnection connection = new SqlConnection(connectionString))     {         try         {             StringBuilder sb = new StringBuilder();             sb.AppendLine("SELECT   name AS [Database Name],");             sb.AppendLine("         Upper(service_broker_guid) AS GUID,");             sb.AppendLine("         is_broker_enabled [Is broker enabled?]");             sb.AppendLine("FROM sys.databases");             SqlDataAdapter da = new SqlDataAdapter();             da.SelectCommand = new SqlCommand();             da.SelectCommand.CommandText = sb.ToString();             da.SelectCommand.CommandTimeout = 0;             da.SelectCommand.CommandType = CommandType.Text;             da.SelectCommand.Connection = connection;             DataSet ds = new DataSet();             da.Fill(ds);             dgBrokers.DataSource = ds.Tables[0];             dgBrokers.Columns["Is broker enabled?"].ReadOnly = false;         }         catch (Exception exception)         {             // Logger         }         connection.Close();     } }

W powyższym kodzie najbardziej istotne jest uzyskanie connection string do aktualnie wybranego w SSMS serwera. Sposób jego odzyskania został opisany wcześniej tutaj.

Z punktu widzenia automatyki odświeżania zawartości tego okna przy zmianie kontekstu serwera to wszystko. Mając connection string jesteśmy w stanie w naszym okienku robić praktycznie wszystko “czego nie zabraniają pozostałe paragrafy” cytując za bohaterem książki “Paragraf 22”. W szczególności nasze okno może być bardzo rozbudowane (tak jak okno do infrastruktury obiektów Service Broker), którego nie będę tu analizować a jedynie pokażę go w działaniu na końcu tego odcinka.

II. Jak dodać okno do SSMS?

Mechanizm dodawania okien do SSMS jest bardzo prosty. Ogólna metoda tworzenia nowego ona na zakładce Tool Windows w SSMS jest następująca:

private Window CreateToolWindow(    string typeName,                                            string assemblyLocation,                                            Guid uiTypeGuid,                                            DTE applicationObject,                                            AddIn addinInstance,                                            string caption)        {            Windows2 win2 = applicationObject.Windows as Windows2;            if (win2 != null)            {                object controlObject = null;                Window toolWindow = win2.CreateToolWindow2( addinInstance,                                                            assemblyLocation,                                                            typeName,                                                            caption,                                                            "{" + uiTypeGuid.ToString() + "}",                                                            ref controlObject);                toolWindow.Visible = true;                return toolWindow;            }            return null;        }

Parametrami tej metody są:

• typeName – nazwy typu obiektu okna,
• assemblyLocation – ścieżka do assembly,
• uiTypeGuid – identyfikator obiektu,
• applicationObject – instancja obiektu SSMS,
• addinInstance – instancja obiektu wtyczki,
• caption – caption naszego okienka na zakładce Tool Windows.

Metoda CreateToolWindow2 tworzy nawą zakładkę na obszarze Tool Windows SSMS a jednocześnie w parametrze controlObject zwraca instancję obiektu typu typeName. Również właściwość toolWindow.Object zwraca referencję to tego obiektu co jest szczególnie istotne w naszych zastosowania do automatyzacji odświeżania zawartości okienek.

Szczegółowa metoda dodawania naszego przykładowego okna z zawartością Service Brokers dla domyślnego serwera z SSMS jest niemniej prosta:

public void CreateBrokersWindow(DTE application, AddIn addinInstance)         {             if (toolBrokersWindow == null)             {                 Assembly asm = Assembly.Load("ToolWindows");                 Guid id = Guid.NewGuid();                 toolBrokersWindow = CreateToolWindow(   "ToolWindows.Brokers",                                                         asm.Location,                                                         id,                                                         application,                                                         addinInstance,                                                         brokersWindowCaption);                 toolBrokersWindow.Height = 400;                 toolBrokersWindow.IsFloating = false;                 toolBrokersWindow.SetTabPicture(ToolsResource.Windows.GetHbitmap());                 application.MainWindow.LinkedWindows.Add(toolBrokersWindow);                 application.MainWindow.LinkedWindows.Item(brokersWindowCaption).AutoHides = true;             }         }

Nasze okienko znajduje się w assembly ToolWindows i jest klasy ToolWindows.Brokers. Dodatkowo ustawiamy jego wysokość, żądamy aby nie było oknem pływającym (czyli dokowanym), Dodajemy ikonkę przy pomocy metody SetTabPicture (dodawałem obraz bitmap i metoda, którą tu zastosowałem jest skuteczna, inne metody okazały sie zawodne). Dodajemy nasze okienko do kolekcji LinkedWindows obiektu MainWindow w SSMS i żądamy aby ukrywało sie automatycznie.

Teraz modyfikujemy metodę initializeAddin opisaną w poprzednich odcinkach dodając do niej kod:

private void initializeAddIn() {     try     {         if (Controller.Current.BrokersWindow == null)             Controller.Current.CreateBrokersWindow(applicationObject, addInInstance);     }     catch (Exception exception)     {         // Logger     }

który tworzy i dodaje nasze okienko do SSMS.

I to wszystko jeżeli chodzi o dodanie okienka do SSMS. Mechanizm ten można oczywiście zastosować z powodzeniem do dodawania okienek poprzez menu (tak jest w przypadku okienka prezentującego infrastrukturę obiektów Service Broker).

III. Jak automatycznie odświeżać zawartość naszych okienek?

Czytelnicy tej serii zapewne pamiętają, że w różnych miejscach kodu naszej wtyczki pojawiały się “zwinięte” regiony kodu o nazwach “Refresh database related windows” oraz “Refresh server related windows”. W te miejsca wstawiamy odpowiednio metody:

• RefershBrowsers – do regionu “Refresh server related windows”,
• RefreshInfrastructure – do regionu “Refresh database related windows”.

Wspomniane wyżej metody wyglądają następująco:

/// <summary> /// Refresh Service Brokers window /// </summary> private void RefreshBrokers() {     if (Controller.Current.BrokersWindow != null)     {         ToolWindows.Brokers brokersWin =                 (ToolWindows.Brokers)Controller.Current.BrokersWindow.Object;         brokersWin.ConnectionString = dbContext.ConnectionString;         brokersWin.RefreshData();     } } private void RefreshInfrastructure() {     if (Controller.Current.InfrastructureWindow != null)     {         ToolWindows.Infrastructure infraWin =                 (ToolWindows.Infrastructure)Controller.Current.InfrastructureWindow.Object;         infraWin.CurrentDatabase = dbContext.Database;         infraWin.RefreshData();     } }

Jak to wygląda w działaniu? Do ObjectExplorer podłączamy dwa serewry przełączając sie między nimi zauważymy, że okno Browsers dopasowuje się dynamicznie do zmienionej sytuacji.

W naszej wtyczce dodałem jeszcze jeden element, Show infrastructure,

który wyzwala polecenie pokazania infrastruktury obiektów Service Broker dla aktualnie wybranej bazy danych.

Uruchomienie go gdy w Object Explorer nie została wybrana domyślna baza danych kończy się komunikatem:

ponieważ w metodzie obsługi tego polecenia ustawiliśmy atrybut, że wymaga ono kontekstu domyślnej bazy danych. Gdy kontekst taki określimy wybierając w Object Explorer konkretną baz danych to zobaczymy:

Klikając na zakładkę Service Broker infrastructure zobaczymy:

Zmieniając w Object Explorer domyślną bazę danych możemy zaobserwować dopasowywanie się tego okna do zmieniającej się struktury obiektów Service Broker w tej bazie. I o to nam chodziło :).

Linki:

do następnego odcinka serii o tworzeniu wtyczki do SSMS

do poprzedniego odcinka serii o tworzeniu wtyczki do SSMS

 

Po serii artykułów dotyczących internals pomocnych przy tworzeniu wtyczki do SSMS przejdziemy do tego co jest najbardziej widoczne i niezbędne do jej działania: menu.

W artykule tym opiszę schemat tworzenia menu:

1. Umiejscowienie menu naszej wtyczki w menu głównym SSMS.

2. Dodanie elementu menu oraz jednego poziomu podmenu.

3. Metod resetowania menu wtyczki oraz usuwania go z menu SSMS.

 

I. Inicjacja menu.

Inicjacja menu powinna się odbyć w momencie gdy UI do SSMS jest w pełni gotowe do tego aby zaakceptować nowe elementy. Tym miejscem jest obsługa zdarzenia OnStartupComplete. Umieszczanie kodu inicjującego w metodzie OnConnect zakończy się niepowodzeniem ponieważ UI do SSMS jeszcze na tym etapie nie istnieje. Zainicjowane prawidłowo menu wtyczki pozostaje trwałym elementem UI.

Dla elegancji kodowania wprowadzimy kilka stałych:

#region Const

private const string VS_MENUBAR_COMMANDBAR_NAME = "MenuBar";  // nazwa obiektu menu głównego SSMS
private const string VS_TOOLS_COMMANDBAR_NAME = "Tools";  // nazwa obiektu reprezentującego element “Tools” z menu SSMS

private const string MAIN_COMMAND_NAME = "MyAddinMenu";
private const string MAIN_COMMAND_CAPTION = "My addin";
private const string MAIN_COMMAND_TOOLTIP = "My own SSMS addin tooltip";

private string REMOVE_NAME = "RemoveMyAddin";
private const string REMOVE_CAPTION = "Reset menu";
private const string REMOVE_TOOLTIP = "Reset menu tooltip";

private string UNINSTALL_NAME = "UninstallMyAddin";
private const string UNINSTALL_CAPTION = "Uninstall menu";
private const string UNINSTALL_TOOLTIP = "Uninstall menu tooltip";

private const string MAIN_CREATE_MENU_NAME = "ServiceBrokerCreate";
private const string MAIN_CREATE_MENU_CAPTION = "Create";
private const string MAIN_CREATE_MENU_TOOLTIP = "Create objects for Service Broker tooltip";

private string CREATE_MESSAGE_TYPE_NAME = "CreateMessageType";
private const string CREATE_MESSAGE_TYPE_CAPTION = "Message type";
private const string CREATE_MESSAGE_TYPE_TOOLTIP = "Crete message type for Service Broker tooltip";

private string OTHER_OPTIONS_NAME = "OtherOptions";
private const string OTHER_OPTIONS_CAPTION = "Other options";
private const string OTHER_OPTIONS_TOOLTIP = "Other options tooltip";

#endregion

Każdy element menu wtyczki ma trzy opisujące go właściwości:

- identyfikującą go nazwę,

- caption będące jego wizualną prezentacją w menu,

- tooltip pozwalający na dodatkowy opis elementu menu.

Tworząc identyfikator menu należy pamiętać aby nie zawierał kropek. W przeciwnym wypadku tworzenie menu wygeneruje wyjątek o niewiele mówiącej treści:

…i szukaj wiatru w polu.

Elegancko tworzony kod wymaga stosowania prawidłowej obsługi wyjątków, ale w przypadku wtyczki SSMS ma to jeszcze dodatkowe znaczenie. Wtyczka, która nie obsłuży wyjątku nie wejdzie przy ponownym starcie do metody OnConnect co może spowodować utratę części funkcjonalności lub przyczynę błędnego jej dalszego działania. W takim przypadku wtyczkę należy odinstalować i zainstalować ponownie oraz zmodyfikować kod, tak aby poprawnie obsługiwał wyjątek. Każde zagrożone potencjalnym wyjątkiem miejsce otaczamy nawiasami try-catch.

Czas odkrycie metody inicjującej menu naszej wtyczki:

 

 

II. Tworzenie menu

 

Obiekt CommandBars jest indeksowaną kolekcją elementów menu. Instrukcja:

toolsCommandBar = commandBars[VS_TOOLS_COMMANDBAR_NAME];

odzyskuje obiekt reprezentujący menu “Tools”. Chcemy aby menu naszej wtyczki znalazło sie tuż za tym menu. Instrukcja:

int position = ((CommandBarControl)toolsCommandBar.Parent).Index + 1;

przypisuje indeks pozycji naszego menu w menu SSMS. Instrukcja:

menuCommandBar = commandBars[VS_MENUBAR_COMMANDBAR_NAME];

odzyskuje obiekt będący menu SSMS.

Nasza wtyczka ma mieć menu pod własną kontrolą niezależną od mechanizmów wtyczek do SSMS. W tym celu potrzebna jest metoda stwierdzająca, czy nasze menu jest już włączone do menu SSMS:

private bool isActive(CommandBar menuCommandBar)
{
            foreach (CommandBarControl control in menuCommandBar.Controls)
            {
                if (control.Caption == MAIN_COMMAND_CAPTION)
                    return true;
            }

            return false;
        }

Metoda ta przegląda wszystkie kontrolki podłączone do menu SSMS i sprawdza, czy caption od jakiejś kontrolki jest równy nazwie menu naszej wtyczki. Jeżeli jest to znaczy, że nasze menu jest już zainstalowane.

Przystąpimy teraz do budowania naszego menu. Metoda makeMenu przedstawia szkielet tworzenia menu zawierającego:

• dodanie do menu bar SSMS elementu menu o identyfikatorze reprezentowanym przez stała MAIN_COMMAND_NAME, mającego caption reprezentowane przez stałą MAIN_COMMAND_CAPTION oraz tooltip reprezentowane przez stałą MAIN_COMMAND_TOOLTIP i występującego na pozycji określonej przez parametr position (powyżej określiliśmy, że będzie to pozycja tuż za elementem “Tools” w menu SSMS), ten element menu jest reprezentowany prze obiekt MainSbMenu,
• dodanie do elementu głównego naszego menu dwóch menu rozwijanych (reprezentowanych przez obiekty CreateMenu oraz ToolWindowsMenu)
• do obiektu CreateMenu zostanie dodany jeden element popup menu reprezentowany przez obiekt CreateMenuPopup,
• do obiektu ToolWindowsMenu zostaną dodane dwa obiekty będące również elementami popup menu realizującymi dwa polecenia realizowane przez SSMS (zresetowanie menu wtyczki oraz usunięcie menu wtyczki).

 

 

Jak widać z powyższego kodu, tworzenie elementów menu czy będących pniami dla gałęzi menu czy liści menu zawierającymi named commands to obie metody CreatemainMenu oraz CreateSubMenu mają taką samą charakterystykę parametrów:

• parent – jest nadrzędnym obiektem menu, do którego dodawany jest element tworzonego menu,
• name – jest identyfikatorem tworzonego menu,
• caption – jest wizualną reprezentacją tworzonego menu,
• group – true oznacza, że bieżący element tworzonego menu będzie otwierał grupę menu oddzieloną separatorem, false oznacza, że separtor menu nie będzie występował,
• position – oznacza pozycję na liście menu dodawanej do nadrzędnego menu. Stąd w przypadku dodawania do ToolWindowsMenu kolejne pozycje dodawanych do niego elementów menu są indeksowane poprzez ToolMenuPopup.Controls.Count + 1.

Metoda CreateMainMenu nie tworzy wykonywalnego named command  dla SSMS a jedynie pień dla gałęzi menu. Metoda ta jest prosta i nie wymaga szerszego wyjaśniania.

Metoda CreateSubMenu ma jedno miejsce, które wymaga szczególnego wyjaśnienia.

Ten kod to

try
{
        command = applicationObject.Commands.Item(addInInstance.ProgID + "." + name, -1);
}
catch
{
}

Kod ten jest próbą pobrania obiektu naszego menu. Jeżeli menu to nie istnieje to generowany jest wyjątek (chociaż lepiej byłoby gdyby zamiast tego zwracał po prostu null). Jeżeli wystąpi wyjątek to zmienna command będzie miała wartość null  i znaczyć to będzie, że takiego elementu menu nie ma w SSMS i należy utworzyć go oraz powiązane z nim named command. Warto zwrócić uwagę na dwa szczególne fakty:

• tworzony named command  ma ustawione vsCommandStatusSupported  oraz          vsCommandStatusEnabled czyniąc tym samym pozwolenie na jego wykonanie.
• tworząc element menu nadajemy mu identyfikator określony parametrem name ale SSMS zabezpiecza się przed możliwością zdublowania takich samych poleceń z różnych wtyczek lub pokrycia się ich z poleceniami SSMS i wobec tego nasze polecenie widziane jest przez SSMS jako:

addInInstance.ProgID + "." + name

gdzie addInInstance.ProgID to nic innego jak konstrukcja <namespace>.<class> co w przypadku naszej wtyczki i menu odinstalowania wtyczki oznacza, że SSMS pełny identyfikator dla tego menu będzie następujący: NextAddin.Connect.UninstallMyAddin. Konstrukcja ta być może jest również wyjaśnieniem dlaczego dodanie kropki w podawanej przez nas nazwie menu powoduje wyjątek. Być może SSMS przeszukuje ostatniej kropki w pełnym identyfikatorze menu i wszystko co jest na lewo od kropki traktowane jest jako <namespace>.<class>, których SSMS w tym wypadku nie jest w stanie zlokalizować.

A tak wygląda menu naszej wtyczki dodane do SSMS:

III. Resetowanie i odinstalowanie menu wtyczki

Rozwijając wtyczkę będziemy stawać przed problemem jak zmodyfikować menu naszej wtyczki. Należałoby zbudować metodę, która usunie wszystkie command rozpoczynające się od konstrukcji addInInstance.ProgID oraz element główny naszego menu z SSMS. Zastosowanie tej metody wraz z metodą inicjacji menu dawałoby nam efekt zresetowania menu a tym samym ujawnienia nowych oraz usunięcia starych opcji. Odinstalowanie menu wtyczki wymagałoby wywołania jedynie metody usuwania. Odinstalowanie menu powinno się wykonywać przed odinstalowaniem wtyczki, w przeciwnym wypadku elementy menu pozostaną w SSMS i próby ich wywołania będą się kończyć komunikatem błędu. Resetowanie wtyczek, które jest możliwe w Visual Studio z poziomu linii poleceń uruchamiających go nie jest możliwe w przypadku SSMS (nie ma takiej opcji w linii poleceń SSMS).

Metoda odinstalowania wygląda następująco:

Metoda resetowania wygląda następująco:

Jak spowodować aby metody ResetMyAddin i UninstallAddin wykonały się z menu naszej wtyczki będzie przedmiotem następnego odcinka serii (Exec).

Linki:

do poprzedniego odcinka serii o tworzeniu wtyczki do SSMS

do następnego odcinka serii o tworzeniu wtyczki do SSMS