Datenbank Projekte in Visual Studio 2005 (auch 2003)

1. Datenbank Projekte in Visual Studio 2005 (auch 2003) Jan A. Staecker, bei .Net Developer-Group Ulm

2. Jan A. Staecker • Studium Physik an TU München • 5 Jahre bei M.A.X. GmbH in München (Microsoft Solution Provider) • Seit 1998 freiberuflich mit Projekten • große (Branchenlösung), • mittlere (Mitarbeit) und • kleine (etwa vba) • .NET und SQL Server • Access Frontend / Excel

3. Ziele • Synchronisierung der Entwicklung von SQL Server Datenbankobjekten. Etwa eine Development, ein Test und ein Live System. • Versionsvergleich von Datenbankobjekten per Visual Source Safe. • Manuelles Einstellen in unterschiedliche Systeme führt oft zu Problemen, wenn man doch mal ein Statement vergisst. • DBObjekt enthält mehr als nur TSQL

4. Was ist ein Datenbankprojekt ? • Eine Menge von, in Verzeichnissen strukturierbaren Dateien, die jeweils T-SQL Code enthalten. • Zuordnung zu SQL Server Datenbank, wobei eine immer aktuell verwendet wird.

5. Was kann man damit machen? • Den T-SQL Teil einer Datei gegen den aktuellen Server ausführen. • Markierten Teil einer Datei ausführen • Alle markierten Dateien ausführen (Reihenfolge ist alphabetisch) • Vorteil: Versionierung mit Source Safe

6. Was kann in einer Datei stehen? • Meistens wohl DDL Code: • SQL zerfällt ja in • DQL Data Query Language – SELECT • DML Date Manipulation Language INSERT, UPDATE, DELETE • DDL Data Definition Language CREATE, ALTER, DROP • DCL Data Control LanguageGRANT, DENY, REVOKE

7. Anlegen eines Datenbankprojektes in Visual Studio

8. Hier etwa wähle ich einen Verweis aus, den ich aus dem C# oder VB Projekt her angelegt habe. Wahl eines Verweises.

9. Datenbank Projektmappe • Name war: TSQL Idee Es gibt schon: • Vorgabeverzeichnisse • Datenbank Referenzen • Das Schloss symbolisiert den Zustand „eingecheckt“ in Visual Source Safe.

10. Beispiel für Arbeit mit VSS • Anlegen einer Tabelle mit Tabellenskript • Etwa 2 Felder, einchecken • Weiter Editieren und Vergleichen

11. Datenbankabfrage • Daten aus dem Nichts, per dbo.sysobjects • Erstellen mit Hilfe der Datenbankabfrage (Zu erkennen an der Endung .dtq) • Ändern in eine Erstellungsabfrage • Einchecken und Ändern • Nachteil: Man kann diese nicht mehr vergleichen

12. Beheben durch Skripten • Wir fügen stattdessen einfach eine normale Skriptdatei (.sql) hinzu. • In dieser können wir einfaches DQL als auch DML entwerfen. • Wir fügen nochmal dbo.sysobjects hinzu. • Und vergleichen:

13. Beispiel: Ideen Datenbank Was machen wir nicht? Rollen werden von Personen und Stellvertretern repräsentiert. Man könnte dieses Beispiel als Workflow abbilden. • Wir sammeln einerseits Ideen und bewerten diese. • Im Kern werden unterschiedliche Rollen/Teilnehmer die Idee absegnen.

14. Erstellen einer Tabelle für Ideen • Wir erzeugen eine Tabelle, etwa für Ideen. • Eine Idee kann erstellt, angenommen und realisiert sein. • Jeder Schritt bekommt einen Zeitstempel, also ein Feld in dem Datensatz. • In der Realität werden Ideen auch verworfen oder nicht realisiert oder getestet.

15. Beispiel

16. Anlage des Skripts • Wir erstellen die Tabelle per T-SQL auswendig oder nehmen bei SQL 2000 den „Enterprise Manager“ oder bei SQL 2005 das „SQL Management Studio“ zur Hilfe. • Wir erstellen ein Skript für das Neuanlegen (Create) • als auch für Delete, falls wir die Definition verändern wollen. • Dies ergibt schon unser erstes Skript.

17. Erstellen 2. Tabelle „Unterschriften“ • Eine Zweite Tabelle enthält alle „Unterzeichner“ in verschiedenen Rollen zu der Idee, also ggf. Projektleiter, Firmenchefs, evtl. auch Stellvertreter. Insbesondere natürlich eine Bemerkung und ein Datum, an dem die Idee unterzeichnet wurde. • Liegen komplett alle Unterzeichnungen vor, so gilt die Idee als angenommen. • Wir nehmen an, das es einen Prozess gibt, der vorher die Unterzeichner festlegt. • Eine weitere Unterschrift der oder des Realisierer(s) kennzeichnet die Idee als realisiert.

18. Anlage des Skripts /****** Objekt: Table [dbo].[Unterschrift] Skriptdatum: 07/16/2007 22:19:27 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Unterschrift]') AND type in (N'U')) DROP TABLE [dbo].[Unterschrift] GO CREATE TABLE [dbo].[Unterschrift]( [UnterschriftID] [int] IDENTITY(1,1) NOT NULL, [IdeeID] [int] NOT NULL, [Signierer] [int] NOT NULL, [Bemerkung] [nvarchar](max) COLLATE Latin1_General_CI_AS NOT NULL, [Datum] [datetime] NULL, CONSTRAINT [PK_Unterschrift] PRIMARY KEY CLUSTERED ( [UnterschriftID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

19. Anlage der Relation Eine Relation ist ein wichtiges Element in der Datenbank • Dieses gewährleistet, dass es für jede Unterschrift immer eine Idee gibt. • Die 1:n Relation erlaubt mehrere Unterschriften zu einer Idee zu verwenden. • Es gibt keine 2 Ideen mit dem gleichen Schlüssel.

20. Erstellen des Skripts • Entweder als neue Datei oder aber in einer der vorhandenen Dateien. • Will man die Skripte in einer bestimmten Reihenfolge aufrufen, so sollte man diese auch entsprechend bezeichnen. Vorteil einer weiteren Datei liegt darin, dass man erst alle Tabellen in beliebiger Reihenfolge erstellen kann • und danach die die Relationen (Dennoch muss man die Relationen ggf. wieder vorher entfernen) Nachteil einer weiteren Datei ist ein zusätzliches Element in der Projekthierarchie.

21. Automatisches Änderungsskript

22. Relation entfernen • IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Unterschrift_Idee]') AND parent_object_id = OBJECT_ID(N'[dbo].[Unterschrift]')) • ALTER TABLE [dbo].[Unterschrift] DROP CONSTRAINT [FK_Unterschrift_Idee] • GO Will man alles in einem Rutsch laufen lassen, muss man dieses Skript zuerst ausführen. Danach die beiden Tabellen Zum Schluss die Relation setzen.

23. Beispiel • Aufruf aller 4 Skripte am Stück.Wir werden durch fehlerlose Ausführung belohnt.

24. Ausführen auf Live Datenbank • Keiner sagt Live-Datenbank, sondern verwendet den Namen des Produkts. • Anwenden auf eine andere Datenbank. • Wir haben bisher entwickelt und vielleicht auch getestet. • Nun verteilen wir das Design Skript auf die Livedatenbank.

25. Test durch Datensätze • Wir fügen ein Stück SQL ein, welches uns Datensätze generiert. • Dazu benötigen wir den Index der gerade angelegten Idee. • Auf dem SQL Server (meist) kein Problem, da wir per @@Identity den Zugriff auf die letzte angelegte ID bekommen.

26. Packen in eine Transaktion

27. Läuft dies? • Nein, denn wir haben vergessen, dass die Bemerkung keinen Standardwert besitzt oder Null sein darf. • Wie ändern wir dies auf Dev System und Live-Server ? • Etwa per neuem Skript oder Änderung unserer Design-Skripte.

28. Weiterer Test Abzeichnen(Diesmal mit festen ID-Werten)

29. Teil 2: Alle Zeichnen ab • UPDATE dbo.Unterschrift • SET Bemerkung = N'ich bin dabei', Datum = GETDATE() • WHERE (UnterschriftID = @UnterschriftID) • UPDATE dbo.Unterschrift • SET Bemerkung = N'ich bin auch dabei', Datum = GETDATE() • WHERE (UnterschriftID = 200) • SELECT UnterschriftID, IdeeID, Signierer, Bemerkung, Datum • FROM Unterschrift Was ist nun? Wie zeichnen wir unsere Idee ab? Wir müssen den Datensatz der Idee ändern!

30. Kennzeichnen als Abgezeichnet • Die einfachste Idee ist sicher, dies manuell zu setzen. • Was machen wir aber, wenn es unterschiedliche Frontends zu diesem Prozess gibt oder auch ein administratives Frontend existiert. Vielleicht auch mal einen Datensatz von Hand setzen und dennoch alles stimmig sein soll. • Eventuell werden (später in einer Prozessänderung) ja auch Unterschriften zurückgezogen, während oder nachdem wir eigentlich alles schon als Abgezeichnet erachtet haben.

31. Lösung durch Trigger • Damit die Datenbank immer einen gültigen Status besitzt schreiben wir einen Trigger, der alle unterzeichneten Datensätze durchgeht und das Datum setzt. • Hierfür wäre eine Funktion nett, die uns für jeden beim Aktualisieren gesetzte Unterschrift den Status der Idee angibt.

32. Funktion dbo.isAngenommen() • Funktionen spricht man immer mit dem Namensraum an, hier also samt „dbo“.

33. Test der Funktion Entspricht die Funktion unserer Aufgabe? Print dbo.isAngenommen(@IdeeID) UPDATE dbo.Unterschrift SET Bemerkung = N'ich bin dabei', Datum = GETDATE() WHERE (UnterschriftID = @UnterschriftID) Print dbo.isAngenommen(@IdeeID) UPDATE dbo.Unterschrift SET Bemerkung = N'ich bin auch dabei', Datum = GETDATE() WHERE (UnterschriftID = 200) Print dbo.isAngenommen(@IdeeID)

34. Schreiben des Triggers • Tipp: erst mal so tun, als ob wir eine Tabelle Unterschrift (mit Alias) hätten, die die geänderten Daten-sätze enthält. Zuletzt die Tabelle in „Inserted“umbenennen.

35. Code des Triggers CREATE Trigger dbo.UpdateIdeeAbgezeichnet ON Unterschrift FOR UPDATE as UPDATE Idee SET Angenommen = null FROM Inserted AS Unterschrift_1 INNER JOIN Idee ON Unterschrift_1.IdeeID = Idee.IdeeID WHERE (dbo.isAngenommen(Idee.IdeeID) = 0) UPDATE Idee SET Angenommen = GETDATE() FROM Inserted AS Unterschrift_1 INNER JOIN Idee ON Unterschrift_1.IdeeID = Idee.IdeeID WHERE (dbo.isAngenommen(Idee.IdeeID) <> 0)

36. Test des Triggers • SELECT UnterschriftID, IdeeID, Signierer, Bemerkung, Datum • FROM Unterschrift • SELECT IdeeID, Kurz, Beschreibung, Erstellt, Angenommen, Umgesetzt • FROM Idee • UPDATE dbo.Unterschrift • SET Bemerkung = N'bin wieder unschlüssig', Datum = NULL • WHERE (UnterschriftID = 200) • SELECT IdeeID, Kurz, Beschreibung, Erstellt, Angenommen, Umgesetzt • FROM Idee

37. Warum nicht als berechnetes Feld? • Das ist auch eine schöne Möglichkeit. • Allerdings können berechnete Felder deutlich längere Laufzeiten nach sich ziehen. • Berechnete Felder lassen sich nicht indizieren, somit sind Abfragen auf „Angenommen is Null“ nicht besonders performant. • Beachte auch Auswirkungen in Frontends

38. Datenbankprojekt imSQL Server Management Studio • Dies ist sicher auch ein gangbarer Weg! • Allerdings finde ich es von Visual Studio aus besser gelöst • Auf den folgenden Folien werde ich zwischen VS (Visual Studio) und MS (Management Studio) Projekten unterscheiden.

39. Verbindungen im MS-Projekt • Je SQL Server und User kann man nur eine Verbindung erstellen. Also nicht per Datenbank wie im VS Projekt. • Tipp: außer man nutzt die XML-Projektdatei. Dann wird aber nicht der zugewiesene Name, sondern der SQL Server angezeigt.Beide Verbindungen zeigen hier auf unterschiedliche Datenbanken

40. Variable Ausführung der Abfragen • Abfragen (TSQL Skripte) werden nun einer Verbindung fest zugeordnet. Ein Wechsel ist im UI des MS nicht möglich. • Abfragen kann man nicht gruppieren, also in Unterverzeichnisse werfen. • Mehrere Skriptdateien sind nicht zusammen ausführbar. Problem: Etwa 0 von 20 Skripten, die ich im VS in einem Verzeichnis unterbringe, gegen die Datenbank B statt A auf dem gleichen Server laufen zu lassen ist nicht so möglich.

41. Vorteile im MS • Abfragen lassen sich sofort analysieren und somit besser bewerten.

42. XML Projektdatei (des MS) • Mit etwas Manipulation der XML-Datei kann man die Ablage in unterschiedliche Verzeichnisse erzwingen. • Deren Namen werden aber immer wieder zum Standardnamen Abfragen zurückgeändert.

43. Resümee • Wenn man kein Visual Studio beim Kunden auf dem Entwicklungsrechner hat, ist das Projekt im Management Studio sicher eine überlegenswerte Alternative. • Ggf. verwendet man eben mehrere Projekte für unterschiedliche Aufgaben • Bei gezielter Optimierung von Abfragen kann ich mir vorstellen, mit dem Management Studio die besseren Karten hat, da hier die Anbindung bereits vorliegt. • Für komplette Datenbankprojekte hingegen, mit der Teilung in Prod/Test und Dev-System, würde ich lieber unter Visual Studio durchführen.

44. Umschließen des DB Objekts • Im Datenbankprojekt kann man zu jedem Datenbankobjekt weiteren Code generieren: • Etwa zum Registrieren des Objektes • manchmal auch um eine vorherige Version zu löschen (Bevorzugt ALTER verwenden) • Weiteren Code, der zum Objekt passt.

45. Beispiel: Fehler Protokoll • Wir erzeugen je eine Tabelle für unsere StoredProcedures und eine Tabelle für die auftretenden Fehler. • Da wir viele StoredProcedures nutzen wollen, sollen diese gleich automatisch registriert werden. • Jede SP liefert im Fehlerfall den Fehler mit einer eigenen ID.

46. Anlegen der StoredProcedure 1/3 • ALTER PROCEDURE [dbo].[myDevide] • @a int, @b int • AS • BEGIN • DECLARE @RetValint • BEGIN TRY • SET @RetVal = @a/@b • END TRY

47. Abfangen möglicher Fehler 2/3 • BEGIN CATCH • INSERT INTO dbo.SP_Errors (SP_UID, SPErr_Text,SPErr_Location) • values ('{FD04AE68-5F64-4903-B08E-3CB626E74AF6}', • ERROR_MESSAGE(), ERROR_LINE()) • END CATCH • END • Go

48. Registrierung und Testcode 3/3 • if not exists(SELECT SP_UID FROM dbo.StoredProcedures • WHERE SP_UID = '{FD04AE68-5F64-4903-B08E-3CB626E74AF6}') BEGIN • INSERT INTO dbo.StoredProcedures (SP_UID, SP_Name,SP_Bemerkung) • values ('{FD04AE68-5F64-4903-B08E-3CB626E74AF6}‚ ,'dbo.myDevide', 'Teilung zweier Zahlen') • -- TestCode • EXECUTE dbo.[myDevide] 0,0 • DECLARE @RETVAL int • execute @RetVAl = dbo.[mydevide] 1,2 • print @RetVAl • END

49. Was ist nun das Besondere? • Pur auf dem SQL Server hätten wir das nicht so schön machen können. • Neue SPs werden nicht automatisch angemeldet! • In der Praxis wird man dies noch ausformulieren und in eigene StoredProcedures stecken. • Faszinierend ist es nach ½ Jahr im stabilen Betrieb sich die tatsächlichen Fehler anzusehen und zu analysieren.

50. Verschlüsseln DerDatenbankrezepte • Auf dem SQL Server kann man den TSQL Code von Trigger , Views, Stored Procedures und Functions normalerweise sehen und analysieren. • Die Quelltexte finden sich in der Sys-Query:Sys.syscomments