T-SQL Deep Dive: Except & Intersect

Dies ist vierte Teil einer Serie in der ich nützliche T-SQL Befehle vorstellen möchte, welche wenig bekannt sind oder nur selten eingesetzt werden.

Eine Übersicht über alle Artikel ist hier zu finden.

Als Entwickler steht man häufig vor der Aufgabe mehrere Tabellen oder Abfragen miteinander zu vergleich um zu ermitteln welche Daten identisch sind und welche nicht. Im SQL-Server werden hierzu meist so genannte "Set Operatoren" verwendet. Am bekanntesten ist sicherlich der UNION Operator. Weniger bekannt sind EXCEPT und INTERSECT die ich in dem heutigen Artikel vorstellen möchte.

Beide Operatoren wurden bereits mit SQL Server 2005 eingeführt. Für die Verwendung dieser Operatoren gelten die gleichen Bedingungen wie für den UNION Operator:

  1. Die Anzahl und die Reihefolge der Felder in beiden Abfragen muss identisch sein.
  2. Die Datentypen der Felder müssen miteinander kompatible sein.

EXCEPT gibt alle Werte aus der ersten Abfrage zurück, welche nicht in der zweiten enthalten sind.

INTERSECT gibt alle Werte aus zwei Abfragen zurück, die sowohl in der ersten als auch in der zweiten Abfrage enthalten sind.

Eine Grafik sagt hier mehr als tausend Worte:

Except & Intersect

Für die Beispiele erstellen wir zwei Tabellenvariablen mit Zahlen. Die erste Tabelle enthält alle ungeraden natürlichen Zahlen von eins bis neun, die zweite alle Primzahlen in diesem Bereich.

DECLARE @UngeradeZahlen AS TABLE (
Zahl int
)
DECLARE @Primzahlen AS TABLE (
Zahl int
)

INSERT INTO @UngeradeZahlen VALUES (1),(3),(5),(7),(9)

INSERT INTO @Primzahlen VALUES (2),(3),(5),(7)

UNION liefert eine sortierte und um Duplicate bereinigte Liste aller ungeraden und aller Primzahlen:

SELECT * FROM @UngeradeZahlen
UNION
SELECT * FROM @Primzahlen



Zahl
-----------
1
2
3
5
7
9

Mit EXECEPT können wir alle ungeraden Zahlen anzeigen lassen, welche keine Primzahl sind:

SELECT * FROM @UngeradeZahlen
EXCEPT
SELECT * FROM @Primzahlen



Zahl
-----------
1
9

Wenn wir die Reihenfolge umkehren bekommen wir alle geraden Primzahlen:

SELECT * FROM @Primzahlen
EXCEPT
SELECT * FROM @UngeradeZahlen

Zahl
-----------
2

INTERSECT liefert eine Liste aller ungeraden Primzahlen, dabei ist die Reihenfolge der Abfragen unerheblich:

SELECT * FROM @Primzahlen
INTERSECT
SELECT * FROM @UngeradeZahlen

Bzw.

SELECT * FROM @UngeradeZahlen
INTERSECT
SELECT * FROM @Primzahlen

Zahl
-----------
3
5
7

Fazit

EXCEPT und INTERSECT sind ungemein hilfreiche Befehle zur Datenanalyse. Mit ihnen ist es möglich mit einer gut lesbaren Abfrage die Unterschiede oder Gemeinsamkeiten zwischen zwei Sets zu ermitteln.

T-SQL Deep Dive: Common Table Expressions

Dies ist Teil einer Serie über nützliche jedoch wenig bekannte SQL Server Funktionen.

Eine Übersicht über bisherige Artikel ist hier zu finden.

Im dritten Teil möchte ich den allgemeinen Tabellenausdruck (Common Table Expressions kurz CTE) vorstellen. Eine CTE ist ein temporäres benamtes Ergebnisset welche nur für die Dauer der Abfrage existiert. Man kann sich eine CTE als eine temporäre View vorstellen. Mit CTE können komplexe Abfragen in kleinere, logische Komponentenblöcke zerlegt werden. Dies ermöglicht übersichtlicheren und somit wartbareren Code. Darüber hinaus sind mit CTE rekursive Abfragen mit einer einfachen und deklarativen Syntax möglich. CTE wurden bereits mit SQL Server 2005 eingeführt und sind Bestandteil des ANSI SQL99-Standards.

Alle definierten CTE werden zur Ausführung vom Query Optimizer in die Abfrage eingeflochten als wären Sie normale Unterabfragen. Es entsteht also kein Overhead wie bei temporären Tabellen. Der erstellte Ausführungsplan kann sich aber von Ausführungsplan mit Unterabfragen unterscheiden. Bei kritischen Abfragen sollten also Performancetest durchgeführt werden.

Syntax:

WITH CTE_Name (optionale Spaltennamen) AS
(
SELECT Statement
)

Eine CTE startet immer mit "WITH" gefolgt vom Alias der CTE und einer Liste an Spalten. Die Liste der Spaltennamen ist optional, wenn eindeutige Namen für alle Spalten der Abfragedefinition definiert wurden.

Für CTE gibt es eine ganze Reihe von Limitierungen. Folgende Befehle sind beispielsweise in einer nicht rekursiven CTE Statement nicht erlaubt:

  • ORDER BY (ausser es wurde ein SELECT TOP definiert)

  • INTO

  • OPTION mit Queryhints

  • FOR BROWSE

Eine Abfrage kann mehr als eine CTE enthalten, mehrere CTE können mit Komma getrennt definiert werden. Eine CTE kann eine andere CTE referenzieren und eine CTE kann auch sich selbst referenzieren (dazu später mehr).

Die CTE muss im unmittelbar nächsten Statement konsumiert werden, sonst gibt es einen Fehler:

WITH BeispielCTE AS (
SELECT [LoginID], [OrganizationLevel], [JobTitle]
FROM [HumanResources].[Employee]
)
SELECT 'Andere Abfrage'
SELECT * FROM BeispielCTE


 

Msg 422, Level 16, State 4, Line 8

Common table expression defined but not used.

Beispiele:

Für die Beispiele wird die AdventureWorks2012 Datenbank verwendet. Als Erstes wollen wir eine Liste aller Mitarbeiter erstellen deren Vorgesetzter den Titel "Engineering Manager" trägt:

SELECT p.FirstName, p.LastName, e.JobTitle
FROM HumanResources.Employee AS e INNER JOIN
Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
WHERE (e.OrganizationNode.GetAncestor(1) =
(SELECT OrganizationNode
FROM HumanResources.Employee
WHERE (JobTitle = 'Engineering Manager')))
 

Mit einer CTE können wir die Unterabfrage auslagern:

WITH BeispielCTE AS (
SELECT OrganizationNode
FROM HumanResources.Employee
WHERE (JobTitle = 'Engineering Manager')
)
SELECT p.FirstName, p.LastName, e.JobTitle
FROM HumanResources.Employee AS e INNER JOIN
Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID

INNER JOIN BeispielCTE ON e.OrganizationNode.GetAncestor(1) = BeispielCTE.OrganizationNode

image

Zugegeben, bei diesem überschaubaren Beispiel ist der Übersichtlichkeitsgewinn nicht gerade Beeindruckend. Bei komplexen Abfragen mit mehreren Unterabfragen helfen CTE allerdings enorm die Lesbarkeit zu erhöhen. Gerade auch durch die Möglichkeit eine CTE in einer CTE zu referenzieren (Unterabfrage in einer Unterabfrage).

Rekursion

Wie bereits mehrfach erwähnt kann eine CTE auch sich selbst referenzieren. Damit ist es möglich Werte einer vorhergehende Ebene abzufragen und somit eine Rekursion zu erzeugen. Funktionen wie ROW_NUMBER werden dabei immer auf der aktuellen Rekursionsebene ausgeführt und nicht auf alle Daten.

Syntax:

WITH BeispielCTE AS (
SELECT Anker
UNION ALL
SELECT NachfolgendeKnoten
FROM BeispielCTE
WHERE RekursionsBedingung
)
 
Die Definition einer rekursiven CTE besteht aus zwei Teilen, zuerst wird der so genannte Anker definiert. Dies ist der Basisknoten der Abfrage. Es folgt die Definition der eigentlichen Rekursion mit Verweis auf sich selbst.
 
Eine rekursive CTE kann man sich als eine endlose Aneinanderreihung von UNION ALL Abfragen vorstellen. Unser Beispiel würde dann so aussehen:
SELECT Anker
UNION ALL
SELECT NachfolgendeKnoten1
FROM Anker Query
UNION ALL
SELECT NachfolgendeKnoten2
FROM NachfolgendeKnoten1 Query
UNION ALL
SELECT NachfolgendeKnoten3
FROM NachfolgendeKnoten2 Query
...
 
Folgende Befehle sind in rekursiven CTE nicht erlaubt:
  • SELECT DISTINCT

  • GROUP BY

  • PIVOT (In Versionen kleiner 2012)

  • HAVING

  • TOP

  • LEFT, RIGHT & OUTER JOIN

Beispiel:

Als Beispiel lösen wir das FizzBuzz Problem mit einer einfachen SQL Abfrage:

WITH RekursiveCTE AS (
SELECT 1 AS Nummer
UNION ALL
SELECT Nummer + 1
FROM RekursiveCTE
WHERE Nummer < 100
)
SELECT
CASE
WHEN Nummer % 3 = 0 AND Nummer % 5 = 0 THEN 'FizzBuzz'
WHEN Nummer % 3 = 0 THEN 'Fizz'
WHEN Nummer % 5 = 0 THEN 'Buzz'
ELSE CONVERT(VARCHAR(3), Nummer)
END
FROM RekursiveCTE
ORDER BY Nummer

image

Bei Rekursion besteht immer auch die Gefahr einer unendlichen Schleife. Um dies zu verhindern kann mit dem Query Hint MaxRecursion die maximal Anzahl an Rekursionen definiert werden. Standardmäßig ist dieser auf 100 Rekursionen eingestellt. MaxRecursion 0 entspricht unendlich vielen Rekursionen. Wird der definierte Wert überschritten gibt es einen Fehler:

WITH RekursiveCTE AS (
SELECT 1 AS Nummer
UNION ALL
SELECT Nummer + 1
FROM RekursiveCTE
WHERE Nummer < 100
)
SELECT
CASE
WHEN Nummer % 3 = 0 AND Nummer % 5 = 0 THEN 'FizzBuzz'
WHEN Nummer % 3 = 0 THEN 'Fizz'
WHEN Nummer % 5 = 0 THEN 'Buzz'
ELSE CONVERT(VARCHAR(3), Nummer)
END
FROM RekursiveCTE
ORDER BY Nummer
OPTION (MAXRECURSION 25)
 
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 25 has been exhausted before statement completion.

Fazit

Common Table Expressions helfen beim Zerlegen komplexer Abfragen in mehrere kleinere Abfragen. Sie ermöglichen es wesentlich übersichtlichere und damit wartbarere  Abfragen zu schreiben. Als weiteres Feature lassen sich mit CTE rekursive Abfragen in T-SQL auch ohne Verwendung eines Cursors einfach umzusetzen.

T-SQL Deep Dive: PIVOT & UNPIVOT

Dies ist der zweite Teil einer Serie in der ich nützliche T-SQL Befehle vorstellen möchte, welche wenig bekannt sind oder nur selten eingesetzt werden.

Eine Übersicht über alle Artikel ist hier zu finden.

Diesmal widmen wir uns den Funktionen PIVOT und UNPIVOT. Beide sind seit SQL-Server 2005 verfügbar und transformiert Daten innerhalb einer Abfrage in eine normalisiertere oder denormalisierte Form.

PIVOT

Der PIVOT-Befehl ermöglicht es Daten einer Tabelle zu denormalisieren. Dabei werden Zeilenwerte anhand eines Kriteriums aggregiert und auf mehrere Spalte aufgeteilt. Mit anderen Worten die Tabelle wird gedreht (pivotiert) indem Zeilen zu Spalten werden.

Syntax:
SELECT NichtPivotierteSpalte, ErstePivotierteSpalte, ZweitePivotierteSpalte
FROM
(SELECT Quelle)
AS query
PIVOT
(
aggregationsfunktion(Spalte die aggregiert werden soll)
FOR
Spalte mit Werten welche Spalten werden sollen
IN (ErstePivotierteSpalte, ZweitePivotierteSpalte)
) AS alias

Beispiel:

Wir haben eine Tabelle mit Daten aus verschiedenen Liefersystemen, welche mit Stichtagsdaten gefüllt wird. Wir benötigen eine Übersicht wie viele Daten pro Stichtag pro Liefersystem gekommen sind.

/* Tabelle erstellen */
CREATE TABLE #Staging
(
Liefersystem char(1),
Stichtag date,
Anzahl int
)

/* Tabelle mit Daten befüllen */
INSERT INTO #Staging VALUES
('A', '2014-01-20', 200),
('B', '2014-01-20', 100),
('C', '2014-01-20', 300),
('A', '2014-01-21', 400),
('B', '2014-01-21', 500),
('C', '2014-01-21', 600),
('A', '2014-01-22', 700),
('B', '2014-01-22', 800),
('C', '2014-01-22', 900),
('A', '2014-01-23', 100),
('B', '2014-01-23', 200),
('C', '2014-01-23', 300),
('A', '2014-01-24', 400),
('B', '2014-01-24', 500),
('C', '2014-01-24', 600)

/* Daten umwandeln */
SELECT Stichtag, A, B, C
FROM #Staging
PIVOT (SUM(Anzahl) for Liefersystem in ([A], [B], [C])) as pvt
ORDER BY Stichtag DESC

Und so wandeln wir die wenig übersichtlichen Orginaldaten

image

In eine wesentlich übersichtlichere Form:

image

UNPIVOT

Der UNPIVOT-Befehl ist, wie der Name schon vermuten lässt, das Gegenteil des PIVOT Befehls und normalisiert Daten. Es werden also Spalten in Zeilen umgewandelt.

Syntax:
SELECT NichtPivotierteSpalte, SpaltenWert, SpaltenName
FROM Quelle
UNPIVOT
( SpaltenWert FOR SpaltenName in ([Spalte1], [Spalte2], [Spalte3])) up

Beispiel:

Wir haben eine Tabelle mit Personendaten in einer Form wie man sie häufig in alten Access Anwendungen vorfindet. Diese sollen in eine normalisierte Form überführt werden.

/* Tabelle erstellen */
CREATE Table #Person
(
Name varchar(100) NOT NULL,
Mobil varchar(30),
Arbeit varchar(30),
Privat varchar(30)
)

/* Beispieldaten einfügen */
INSERT INTO #Person VALUES
('Karl Klammer', '0151 111 111 111', '069 111 111 111', NULL),
('Graf Zahl', '0152 222 222 222', NULL, '0661 222 222 222'),
('Max Muster', NULL, '069 333 333 333', '0661 333 333 333')

/* Daten umwandeln */
SELECT *
FROM #Person
UNPIVOT
( Nummer FOR Typ in ([Mobil], [Arbeit], [Privat])) up

Hier als Ergebnis die normalisierten Daten:

image

Dynamische Abfragen

Ein Problem ist dem geneigten Leser evtl. bereits aufgefallen, die Spaltennamen bzw. Suchworte sind fester Bestandteil der Abfrage. Doch was wäre wenn in unserem ersten Beispiel ein neues Liefersystem "D" hinzu kommt?

Nun auch dafür gibt es eine Lösung (wenn auch keine elegante):

DECLARE @query nvarchar(max),
@PivotList varchar(max)

SELECT @PivotList = COALESCE(@PivotList + ', ', N'') + N'[' + Liefersystem + N']'
FROM (SELECT DISTINCT Liefersystem
FROM #Staging
) AS List

SET @query = 'SELECT Stichtag, ' + @PivotList + ' FROM
(
SELECT Stichtag, Anzahl, Liefersystem
FROM #Staging
) x
PIVOT (SUM(Anzahl) for Liefersystem in ('
+ @PivotList + ')) as pvt
ORDER BY Stichtag DESC'



EXEC sp_executesql @query

Für UNPIVOT wird die benötigte Abfrage noch komplexer, da wir die Metadaten der Tabelle abfragen müssen:

DECLARE @sql AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

SELECT @sql = STUFF((select ',' + QUOTENAME(name)
FROM tempdb.sys.columns
WHERE OBJECT_ID = OBJECT_ID('tempdb..#Person')
AND name <> 'Name'
FOR XML PATH('')), 1, 1, '')

SET @query = 'SELECT *
FROM #Person
UNPIVOT ( Nummer FOR Typ in ('
+ @sql + ')) up'

EXEC sp_executesql @query

Fazit

PIVOT und UNPIVOT sind sehr nützliche Funktionen welche das Arbeitsleben erleichtern können. Mit PIVOT können Daten sehr gut für Berichte aufbereitet werden währen UNPIVOT beim Normalisieren von Daten unterstützt. Einziger Wehrmutstropfen ist die fehlende Flexibilität der Abfragen. Zwar kann hierfür dynamisches SQL verwendet werden, aber gerade bei UNPIVOT werden diese sehr schnell komplex und aufwendig…

T-SQL Deep Dive: Temporäre Tabellen vs. Tabellenvariablen

Im Projektalltag habe ich festgestellt, dass oftmals nur ein kleiner Teil der Features des SQL-Servers tatsächlich genutzt wird. Mit dieser Serie möchte ich die Bekanntheit nützlicher Funktionen erhöhen, und zeigen was im SQL-Server jenseits von einfachem SQL möglich ist.

Hier die Übersicht der geplanten und bereits erschienenen Artikel:

        • Temporäre Tabellen vs. Tabellenvariablen
        • PIVOT & UNPIVOT
        • Common Table Expressions
        • Except & Intersect
        • (Weitere Beiträge in Vorbereitung, ich werde diese Liste sukzessive erweitern, sobald sie verfügbar sind.)

Im ersten Artikel geht es um eine Alternative zu temporären Tabellen: die so geannten Tabellenvariablen. Im Folgenden werden diese kurz näher erläutert und die Unterschiede zwischen lokalen temporären Tabellen (#temp), globalen temporären Tabellen (##temp) und Tabellenvariablen (@temp) aufgezeigt.

Lokale temporäre Tabellen werden mit CREATE erstellt und sind während der Session gültig in der sie erzeugt wurden. In dieser Zeit können sie von anderen Prozeduren verwendet werden. Nach Beendigung der Session werden sie automatisch gelöscht.

Globale temporäre Tabellen sind übergreifend für alle Sessions und Benutzer verfügbar und werden automatisch gelöscht sobald keine Session mehr die Tabelle referenziert.

Tabellenvariablen sind, wie der Name bereits andeutet, Variablen und werden mit dem DECLARE-Befehl erstellt. Wie alle Variablen existiert diese nur innerhalb des Ausführungsscopes (z.B: Stored Procedure, BEGIN/END-Block, etc.). Dies bedeutet, nach Verlassen des Scopes sind sämtliche in der Variablen gehaltenen Daten automatisch gelöscht. Entgegen der landläufigen Meinung, werden sowohl temporäre Tabellen als auch Tabellenvariablen in der TempDB gespeichert.

Hier die wesentlichen Unterschiede:

  • Da temporäre Tabellen ganz normale Tabellen sind, können Indizes und Statistiken angelegt werden. Auf Tabellenvariablen dagegen können nur Clustered Indizes und keine Statistiken erstellt werden (für SQL Server 2014 ist die Möglichkeit von Non Clustered Indizes angekündigt).
  • Tabellenvariablen nehmen nicht an Transaktionen teil. Sie erfordern deswegen weniger logging und locking und bringen weniger Overhead mit sich. Temporäre Tabellen dagegen nehmen (mit reduzierten Logging) an Transaktionen teil. Sie sind dadurch potentiell weniger perfomant aber profitieren dafür auch von deren Vorteilen.
  • Auf temporären Tabellen könne sowohl DDL Operationen (Alter, Drop) als auch DML Operationen (SELECT, INSERT, UPDATE, DELETE) vorgenommen werden.
  • Temporäre Tabellen führen oft dazu, das Stored Procedures neu kompiliert werden müssen. Tabellenvariablen haben dieses Problem nicht. (KB 243586)
  • Es ist nicht möglich temporäre Tabellen in Functions zu verwenden. Tabellenvariablen hingegen können verwendet werden. Es ist auch möglich, Tabellenvariablen als Rückgabetyp einer Function zu definieren.
  • Temporäre Tabellen können mit SELECT INTO erstellt werden, ohne vorher die Struktur definieren zu müssen. Tabellenvariablen müssen immer zuerst definiert werden.

Je nach Version des SQL Servers gibt es weitere Einschränkungen bei der Verwendung von Tabellenvariablen:

  • SQL Server 2005
    Ab dieser Version ist es möglich Tabellenvariablen mit INSERT EXEC zu befüllen:
    INSERT @tabelle EXEC usp_beispiel

  • SQL Server 2008

    Tabellenvariablen können nun Eingangs- oder Ausgangsparameter von Stored Procedures sein. Dazu müssen diese aber zuvor als Datentyp im SQL Server definiert sein.

    CREATE TYPE tt_beispiel AS TABLE (spid int, cmd varchar(50))
    GO

    CREATE PROCEDURE usp_beispiel @spids tt_beispiel READONLY AS SELECT * FROM @spids
    GO

    DECLARE @spids tt_beispiel INSERT INTO @spids
    SELECT TOP 10 spid, cmd FROM sys.sysprocesses

    EXEC usp_beispiel @spids=@spids

  • SQL SERVER 2014

    In der neusten Version ist es möglich non clustered Indizes auf Tabellenvariablen zu definieren.

    DECLARE @Beispiel TABLE (

    Spalte1 INT INDEX IX_Spalte1 CLUSTERED,

    Spalte2 INT INDEX IX_Spalte2 NONCLUSTERED,

    INDEX IX_Composite NONCLUSTERED(Spalte1, Spalte2)

    );

Fazit

Wann sollte nun welche Variante verwendet werden? Die Antwort auf diese Frage lautet wie so oft "kommt drauf an…". Bei kleinen Datenmengen sind Tabellenvariablen durch den geringeren Overhead schneller und damit die erste Wahl. Für größere Datenmengen empfiehlt sich eine temporäre Tabelle, um dem Query Optimizer mit Statistiken zu helfen. In Functions und Stored Procedures sollten nach Möglichkeit Tabellenvariablen verwendet werden, außer es handelt sich um verschachtelte Stored Procedures welche Ergebnisse zwischenspeichern. Im Zweifel sollten Performance Tests durchgeführt werden, um die beste Methode zu ermitteln.

FetchXml Berichte für Microsoft CRM 2011 erstellen

Einleitung

Microsoft Dynamics CRM 2011 ist eine vielseitig erweiterbare Plattform welche viele Möglichkeiten zur Anpassung bietet. In dem heutigen Artikel widmen wir uns der Erstellung eines Berichtes für Microsoft Dynamics CRM 2011. Grundsätzlich sind Berichte in Microsoft Dynamics CRM 2011 ganz normale SQL Server Reporting Servies (SSRS) Berichte. Etwas ungewohnt ist der Zugriff auf die CRM Entitäten. Hierfür gibt es zwei Möglichkeiten:

  • SQL basierend
    Diese Berichte benutzen SQL-Abfragen um Daten zu lesen. Alle Standard Berichte von CRM 2011 und CRM Online basieren auf diesen SQL Abfragen. Aus sicherheitstechnischen Gründen können keine eigenen SQL basierenden Berichte in CRM Online deployed werden.
  • FetchXml basierend
    Diese Berichte wurden mit CRM 2011 eingeführt und verwenden FetchXml für Abfragen. FetchXml ist eine proprietäre Abfragesprache auf Basis von Xml und ist zentriert um Entitäten. Die Sprache ermöglicht ebenso Verknüpfungen und Aggregationen. Alle mit dem Wizard erstellen Berichte basieren auf FetchXml.

In diesem Artikel werden benutzerdefinierte Berichte basierend auf FetchXml näher beleuchtet.

Vorbereitungen

Zum Entwickeln von benutzerdefinierten Berichten basierend auf FetchXml müssen zuerst die Microsoft Dynamics CRM 2011 Report Authoring Extensions installiert werden. Diese sind hier zu finden: http://www.microsoft.com/en-us/download/details.aspx?id=27823

Zum jetzigen Zeitpunkt unterstützen die Report Authoring Extensions allerdings nur Business Intelligence Development Studio 2008 (BIDS). Nach der Installation steht uns in BIDS einen neue Datenquelle vom Typ Microsoft Dynamics CRM Fetch zur Verfügung.

Solution anlegen

Zuerst erstellen wir eine neue Solution in Visual Studio 2008:

image

Und wählen Report Server Project:

clip_image002

Bevor wir nun eine DataSource für unseren Bericht erstellen folgt zuerst ein kleiner Exkurs zu DataSources und CRM:

Beim Erstellen einer neuen Organisation werden standardmäßig immer zwei DatasSources im SharedReports-Folder des SSRS erzeugt:

  • MSCRM_DataSource
    Für SQL Abfragen
  • MSCRM_FetchDataSource
    Für FetchXml Abfragen

Diese können in unserem Bericht eingebunden werden. Da die Datenquellen in jeder Organisation vorhanden sind, wird nach dem Deployment eines Berichtes, auch immer die korrekte Datenquelle verwendet und können so den gleichen Report ohne Anpassungen auf verschiedenen Systemen deployen.

Um diese zu verwenden legen wir in unserem Projekt eine Shared Data Source mit dem Namen MSCRM_FetchDataSource an.

clip_image003

Als Typ wählen wir Microsoft Dynamics CRM Fetch und für den Connection string geben wir die URL unserer CRM Instanz an.

clip_image004

Credentials werden von uns nicht verändert und können in den Standardeinstellungen gelassen werden.

image

FetchXml Abfrage erstellen

Als nächstes benötigen wir eine Abfrage für unseren Bericht. Microsoft CRM bietet glücklicherweise eine recht komfortable Möglichkeit auch ohne größere Kenntnisse von FetchXml Abfragen zu erstellen. Hierzu wählen wir im Microsoft CRM die Erweiterte Suche.

clip_image005

Hier können wir uns unsere Abfrage mit einem Wizard erstellen und später das FetchXml anzeigen lassen. Als Entität für die Suche wählen wir Sicherheitsrollen (diese Entität sollte in jeder CRM Instanz zu finden sein) und definieren den Filter wie folgt:

image

Zum Anzeigen des FetchXml klicken wir den Button FetchXml herunterladen in der oberen rechten Ecke.

clip_image007

Die so erzeugte Datei speichern wir an einem beliebigen Ort in unseren Dateisystem und öffnen diese mit NotePad.
Wir ergänzen die Abfrage noch um das Attribut fullname der Entität systemuser, damit wir auch den Namen der Benutzer angezeigt bekommen.

clip_image009

Zum besseren Verständnis von FetchXml hier ein Screenshoot welch Teile der Abfrage welchem SQL Befehl entsprechen.

clip_image010

Bericht erstellen

Nun ist alles bereit, um unseren Bericht zu erstellen. Wir wechseln hierzu wieder nach Visual Studio und erstellen einen neuen Bericht:

clip_image011

Als Datenquelle wählen wir die Shared data source MSCRM_FetchDataSource:

clip_image012

Im nächsten Schritt kopieren wir die vorbereitete Abfrage in das Feld Query String

clip_image013

Als Felder wählen wir nur name und fullname.

clip_image014

Wir geben dem Bericht noch einen Namen und sind fertig.

clip_image015

Hier das Ergebnis:

image

Bericht in CRM System veröffentlichen

Um unseren Bericht anderen Benutzern zur Verfügung zu stellen, muss dieser im CRM System veröffentlicht werden.
Dazu gehen wir zu Berichte

clip_image001

Und wählen Neu

clip_image002[1]

Im folgenden Menu wählen wir für Berichtstyp Vorhandene Datei, geben Name und Beschreibung ein und wählen den Dateispeicherort der von uns erstellten RDL Berichtsdatei aus.

clip_image003[1]

Unter Verwaltung ändern wir bei Bedarf die Sichtbarkeit von Individuell auf Organisation (nur so kann er von anderen Benutzern gesehen werden).

clip_image004[1]

Sind alle Daten eingetragen klicken wir auf Speichern und schließen. Der Bericht ist nun im CRM System veröffentlicht.

Fazit

Berichte für Microsoft CRM 2011 unterscheiden sich nur in in der Art der Abfrage von normalen SSRS Berichten. Mit den hier gezeigten Grundlagen können nun auch komplexere Berichte für Microsoft CRM 2011 erstellt werden.
In einem Folgeartikel werde ich näher auf FetchXml als Abfragesprache und das Verwenden von Parametern eingehen.

Datenzugriff in SharePoint: Fazit

Dieser Artikel ist Teil einer Serie, die sich mit der Frage beschäftigt auf welche Arten Konfigurationen in Applikationen unter SharePoint gespeichert werden können.

Eine vollständige Übersicht über bisherige Artikel ist hier zu finden.

Im letzten Teil unserer Serie werden die Vor- und Nachteile der verschiedenen Methoden gegenübergestellt.

Beurteilt wurden die Methoden in folgenden Kategorien:

  • Implementierung
    Wie einfach ist eine Lösung umzusetzen
  • Sicherheit
    Wie gut sind die Werte geschützt und wie granular lassen sich Rechte einstellen
  • Scope
    Wie flexibel ist die Lösung in ihrer Anwendung
  • Wartbarkeit
    Müssen zusätzliche Komponenten installiert werden und gibt es evtl. Seiteneffekte?
  • Administration
    Wie einfach können die Werte verwaltet werden?

Ein Plus bedeutet die Methode hat in dieser Kategorie Stärken, ein Minus die Methode hat hier Schwächen.

901

Wie man sehen kann bietet jede Methode ihre ganz speziellen Vor- und Nachteile. Es kann daher auch keine konkrete Handlungsempfehlung gegeben werden. Je nach Aufgabenstellung und Hintergrund muss die für das Szenario passende Lösung gewählt werden. Die hier aufgezeigten Beispiele sollten hierbei eine gute Hilfestellung liefern.

Datenzugriff in SharePoint: SharePoint Listen

Dies ist der achte Teil einer Serie, die sich mit der Frage beschäftigt auf welche Arten Konfigurationen in Applikationen unter SharePoint gespeichert werden können. Es wird zuerst immer kurz die Methode vorgestellt, gefolgt von einem kurzen Beispiel und den Vor- und Nachteilen.

Eine vollständige Übersicht über bisherige und noch folgende Artikel ist hier zu finden.

In diesem Teil werden SharePoint Listen vorgestellt. Diese sind DIE zentrale Funktion innerhalb von SharePoint und bieten vielfältige Möglichkeiten zum Pflegen von Einstellungen an. Diese Methode ist hervorragend geeignet wenn Benutzer die Einstellungen selber pflegen können sollen.

Erstellen der Liste

Eine neue Liste ist sehr einfach zu erstellen. Hierzu gehen wir auf „Site Actions“ und wählen „More Options“:

801

Hier wählen wir „List“ -> „Custom List“ und geben der Liste einen Namen:

802

SharePoint legt für uns jetzt eine einfache Liste ohne besondere Spalten an. Wir fügen nun zwei neue Spalten mit „Create Column“ hinzu:

803

Die erste Spalte nennen wir „Wert“, die zweite „Kategorie“. Unsere Liste ist nun fertig und kann mit Beispielen gefüllt werden.

Zugriff auf Liste

Als Nächstes erstellen wir ein neues WebPart um in diesem die Werte auszulesen und anzuzeigen:

[ToolboxItemAttribute(false)]

public class WebPartMitListe : WebPart

{

protected override void CreateChildControls()

{

var konfigItems = new List<String>();

// Liste auslesen

var listItems = SPContext.Current.Web.Lists["Beispiel Liste"].Items;

// Items aus der Liste auf Datenobjekt mappen

foreach (SPListItem item in listItems)

{

konfigItems.Add(

string.Format("Name: {0}, Wert: {1}",item.Title, item["Wert"]));

}

 

// Gridview erzeugen und hinzufügen

var gridView = new GridView

{

ID = "GridViewKonfig",

AutoGenerateColumns = true,

DataSource = konfigItems

};

 

gridView.DataBind();

Controls.Add(gridView);

}

}

Der benötigte Code sehr überschaubar. Wichtig ist auch zu beachten, dass eine Liste auf verschiedenen Ebenen in der SharePoint Hierarchie abgelegt sein kann:

Ebene

Ort

SPFarm

Liste in Central Administration.

SPSite

Liste im root Web der Site Collection.

SPWeb

Liste in SPWeb

Listen auf External Content Types

Soll die Konfiguration nicht in SharePoint abgelegt werden oder eine bestehende Konfigurationsdatenbank verwendet werden, können wir auch eine Liste auf die im letzten Artikel vorgestellten External Content Types (ECT) aufsetzen. Dies geht sehr komfortabel über den SharePoint Designer. Dazu öffnen wir mit dem Designer wieder die Site, navigieren zu den External Content Types und wählen den im letzten Artikel erstellen ECT.

Im Ribbon wählen wir den Punkt „Create Lists & Form“.

804

Hier geben wir der Liste einen Namen und drücken „Ok“:

805

Der Designer legt nun für uns eine Liste in SharePoint an. Diese funktioniert wie eine normale Liste in SharePoint, allerdings mit ein paar Einschränkungen z.B. ist keine Versionierung mehr verfügbar.

Fazit

Wie immer zum Abschluss noch einmal die Vor- und Nachteile der Methode:

Vorteile:

  • Administration

    Zum Erstellen und Bearbeiten von Einträgen kann die gewohnte SharePoint Oberfläche verwendet werden. Zusätzlich können Einträge von SharePoint Versioniert werden.

  • Sicherheit

    Rechte können sehr granular in SharePoint verwaltet werden.

  • Scope

    Listen können auf verschiedenen Ebenen angelegt werden.

  • Flexibel

    Über External Content Types können beliebige Datenquellen angebunden werden. SharePoint kümmert sich hierbei um den Datenzugriff

Nachteile:

  • Implementierung

    Umsetzung ist aufwändiger im Vergleich zu anderen Methoden.

  • Wartbarkeit

    Listen müssen separat erstellt und deployed werden.

Datenzugriff in SharePoint: Business Data Connectivity Services

Dies ist der siebte Teil einer Serie, die sich mit der Frage beschäftigt auf welche Arten Konfigurationen in Applikationen unter SharePoint gespeichert werden können. Es wird zuerst immer kurz die Methode vorgestellt, gefolgt von einem kurzen Beispiel und den Vor- und Nachteilen.

Eine vollständige Übersicht über bisherige und noch folgende Artikel ist hier zu finden.

Im heutigen Teil wird eine Möglichkeit vorgestellt auf Daten zuzugreifen ohne in der Applikation Kenntnisse über das Quellsystem zu besitzen.

Zu den vielfältigen Services, welche SharePoint anbietet, gehört der Nachfolger des Business Data Catalog (BDC) die Business Data Connectivity Services (BCS). Diese dienen dazu beliebige Fremdsysteme an SharePoint anzubinden und ermöglichen Daten aus Datenbanken, Web Services oder auch SAP in SharePoint verfügbar zu machen. Die BCS sind also weniger dazu geeignet z.B. einen ConnectionString zu speichern. Dafür aber umso besser um Daten direkt aus Fremdsystemen auszulesen.

Im Rahmen dieser Serie werden die Business Data Connectivity Services als einfacher Zugriff auf eine Konfigurationsdatenbank verwendet. Das bedeutet, wir legen eine für uns passende Entität in SharePoint an und konfigurieren diese für den Zugriff auf unsere Datenbank. Eine Applikation muss sich dann nicht mehr um die Verbindung kümmern. Es wird „nur“ die Entität angefragt und aus Sicht der Applikation stammen die Daten immer aus SharePoint.

Das hier aufgeführte Beispiel kann aber natürlich auch verwendet werden um unserer Applikation Daten aus einem beliebigen Fremdsystem zur Verfügung zu stellen.

BDC Model erstellen

Das Bindeglied zwischen SharePoint und Fremdsystemen sind die so genannten External Content Types. Es gibt zwei Möglichkeiten diese zu erstellen:

  • Anlegen des Modells in Visual Studio.
  • Modell mit SharePoint Designer erstellen.

Da die zweite Variante einfacher ist, gehen wir für unser Beispiel diesen Weg.

Zuerst erstellen wir eine Tabelle, auf welche wir dann später zugreifen wollen:

CREATE TABLE [dbo].[Konfiguration](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Kategorie] [varchar](50) NOT NULL,

[Name] [varchar](50) NOT NULL,

[Wert] [varchar](50) NULL

) ON [PRIMARY]

Als nächstes starten wir den SharePoint Designer und öffnen unsere Site:

701

Hier wählen wir „External Content Types“ (ECT) und klicken auf “New”:

702

Wir geben dem ECT einen Namen und wählen „External System“ um die Datenquelle zu definieren:

703

Im folgenden Dialog fügen wir eine neue Verbindung mit „Add Connection“ hinzu:

704

In dem aufgehendem Dialog wählen wir den passenden SQL-Server und konfigurieren die Verbindung zu unserer Datenbank. Die Verbindung kann auch über dem im letzten Teil vorgestellten Secure Store erfolgen. Dazu muss im Dialog nur der Name des Secure Stores angegeben werden. Dieser benötigt natürlich das Recht den Secure Store auszulesen:

705

Nach Klick auf “Ok”, legt der SharePoint Designer die Verbindung im Metadata Store an. Wir wählen nun unsere Konfigurationstabelle mit einem Rechtsklick aus, und wählen „Create All Operations“:

706

Dies startet einen Wizard in dem wir auf „Next“ klicken:

707

Im nächsten Fenster müssen wir eine eindeutige Id Spalte benennen. Ohne diese kann SharePoint nicht mit den Daten arbeiten:

708

Im letzten Dialogfenster können wir noch einen Filter definieren. Dieser ist für unser Beispiel jedoch unerheblich. Wir beenden also den Wizard mit Klick auf „Finish“:

709

Der SharePoint Designer erstellt nun alle CRUD Operationen:

710

Zum Abschluss wird der External Content Type gespeichert und die Definition im Metadata Store abgelegt. In der Central Administration sind diese unter “Application Management” -> “Manage service applications” -> “Business Data Connectivity Service” zu finden.

711

Zugriff auf BCS

Zum Arbeiten mit den BCS benötigen wir zuerst einmal ein einfaches Datenobjekt:

public class Konfiguration

{

public int Id { get; set; }

public string Name { get; set; }

public string Kategorie { get; set; }

public string Wert { get; set; }

}

Als nächstes benötigen wir ein Repository, mit dem Einträge aus der Tabelle ausgelesen und geändert werden können. Dieses kommuniziert mit den BCS:

[DataObject]

public class KonfigurationRepository

{

private const string LobInstanceName = "Datenbank Beispiel";

private const string EntityName = "BeispielECT";

private const string EntityNameSpace = "http://spdev/personal/markus";

 

[DataObjectMethod(DataObjectMethodType.Select, true)]

public List<Konfiguration> Select()

{

var list = new List<Konfiguration>();

using (new SPServiceContextScope(SPServiceContext.GetContext(SPContext.Current.Site)))

{

// BCS Service ansprechen

var service = SPFarm.Local.Services.GetValue<BdcService>();

// Metadata Store für BCS Service ermitteln

var catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);

// External Content Type definieren

var entity = catalog.GetEntity(EntityNameSpace, EntityName);

// LOB System laden

var instances = entity.GetLobSystem().GetLobSystemInstances();

var lobSysteminstance = instances[LobInstanceName];

// Read Methode definieren

var method = entity.GetMethodInstance("Read List", MethodInstanceType.Finder);

// Items abfragen

var items = entity.FindFiltered(method.GetFilters(), lobSysteminstance);

while (items.MoveNext())

{

if (items.Current != null)

{

// BCS Objekt auf Datenobjekt mappen

list.Add(new Konfiguration()

{

Id = (int)items.Current["Id"],

Name = items.Current["Name"].ToString(),

Kategorie = items.Current["Kategorie"].ToString(),

Wert = items.Current["Wert"].ToString()

});

}

}

}

return list;

}

 

[DataObjectMethod(DataObjectMethodType.Update, true)]

public void Update(Konfiguration neueKonfig)

{

using (new SPServiceContextScope(SPServiceContext.GetContext(SPContext.Current.Site)))

{

// BCS Service ansprechen

var service = SPFarm.Local.Services.GetValue<BdcService>();

// Metadata Store für BCS Service ermitteln

var catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);

// External Content Type definieren

var entity = catalog.GetEntity(EntityNameSpace, EntityName);

// LOB System laden

var instances = entity.GetLobSystem().GetLobSystemInstances();

var lobSysteminstance = instances[LobInstanceName];

// Id für Abfrage definieren 

var identity = new Identity(neueKonfig.Id);

// Objekt laden, Werte setzen und updaten.

var instance = entity.FindSpecific(identity, lobSysteminstance);

instance["Name"] = neueKonfig.Name;

instance["Wert"] = neueKonfig.Wert;

instance.Update();

}

}

}

Als letztes erstellen wir ein WebPart mit einem GridView und einer ObjectDataSource um die Daten anzeigen und bearbeiten zu können:

public class WebPartMitBCS : WebPart

{

protected override void CreateChildControls()

{

var dataSource = new ObjectDataSource

{

ID = "Source",

UpdateMethod = "Update",

SelectMethod = "Select",

TypeName = typeof(KonfigurationRepository).AssemblyQualifiedName,

DataObjectTypeName = typeof(Konfiguration).AssemblyQualifiedName

};

 

 

var gridView = new GridView

{

ID = "GridViewKonfig",

AutoGenerateEditButton = true,

AutoGenerateColumns = true,

DataSourceID = dataSource.ID

};

 

Controls.Add(dataSource);

Controls.Add(gridView);

 

// DataBind wird sonst mehrfach aufgerufen

if (!Page.IsPostBack)

{

gridView.DataBind();

}

}

}

Fazit

Zum Abschluss wieder die Vor- und Nachteile der Business Data Connectivity Services:

Vorteile:

  • Sicherheit

    Rechte können sehr granular in SharePoint verwaltet werden.

  • Scope

    Es können beliebige Datenquellen angebunden werden. SharePoint kümmert sich hierbei um den Datenzugriff

Nachteile:

  • Administration

    Es gibt keine mitgelieferte Oberfläche zum Verwalten der Werte.

  • Implementierung

    Umsetzung ist sehr aufwendig im Vergleich zu anderen Methoden.

  • Wartbarkeit

    External Content Types müssen separat erstellt und verteilt werden

Datenzugriff in SharePoint: Secure Store Services

Dies ist der sechste Teil einer Serie, die sich mit der Frage beschäftigt auf welche Arten Konfigurationen in Applikationen unter SharePoint gespeichert werden können. Es wird zuerst immer kurz die Methode vorgestellt, gefolgt von einem kurzen Beispiel und den Vor- und Nachteilen.

Eine vollständige Übersicht über bisherige und noch folgende Artikel ist hier zu finden.

Im heutigen Teil widmen wir uns den Secure Store Services.

Der Secure Store Service ist ein Dienst innerhalb SharePoint zum Speichern von Anmeldeinformationen für den Zugriff auf externe Datenquellen. Er wird für eine Vielzahl von Diensten wie Excel- oder Visio-Services verwendet. Die Anmeldeinformationen werden hierbei sicher im SharePoint abgelegt und können über den Service wieder ausgelesen werden. Es ist nicht dazu gedacht Einstellungen programmatisch zu ändern, da der Zugriff rein lesend ist.

Auf einem angelegten SecureStore lassen sich Zugriffsberechtigungen defnieren, welche Benutzer oder Gruppen diesen auslesen dürfen.

Erstellen des SecureStore

Der SecureStore Service wird über die SharePoint Central Administration aufgerufen. Er ist zu finden unter “Application Management > Manage service applications > Secure Store Service”.

Falls noch kein Key für den Secure Store Service generiert wurde, muss dieses vorab durchgeführt werden.

Für unser Beispiel legen wir nun einen neuen SecureStore an:

601

Im folgenden Dialog definieren wir eine “Application ID” und können über „Target Application Type“ die Art des Zugriffs regeln:

  • Group: für Berechtigung von Gruppen
  • Individual: wenn einzelne Benutzer berechtigt werden sollen.

602

Im nächsten Schritt werden die Felder des SecureStore definiert:

603

Um das Beispiel einfach zu halten, wird der komplette ConnectionString in ein einzelnes Feld gespeichert. Es ist aber ohne Probleme möglich seperate Felder für Benutzername, Passwort, Server, etc. hinzuzufügen. Zu beachten ist auch, das die hier vorgenommenen Einstellungen später nicht mehr geändert werden können!

Im letzten Schritt beim Anlegen des Secure Store werden die Personen oder Gruppen definiert, welche den SecureStore administrieren können:

604

Als Nächstes müssen wir den SecureStore mit Werte füllen. Hierzu wählen wir den neuen SecureStore aus und klicken auf „Set Credentials“.

605

Nun tragen wir den ConnectionString und die Credential Owner ein. Die Credential Owner sind alle Personen und Gruppen, welche den Secure Store auslesen dürfen.

606

Nun drücken wir Ok und der Secure Store kann verwendet werden.

Wer eine Secure Store Applikation lieber mit Hilfe von PowerShell anzulegen möchte, findet hier eine Anleitung dazu: https://www.sdx-ag.de/2012/05/sharepoint-2010-deployment-teil-3.html

Zugriff auf SecureStore

Bevor wir die Secure Store Services ansprechen können, müssen wir noch zwei Dlls in das Visual Studio Projekt einbinden:

  • Microsoft.BusinessData.dll
    C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPIMicrosoft.BusinessData.dll
  • Microsoft.Office.SecureStoreService.dll
    C:WindowsassemblyGAC_MSILMicrosoft.Office.SecureStoreService14.0.0.0__71e9bce111e9429cMicrosoft.Office.SecureStoreService.dll

Nun können wir ein neues WebPart mit zwei Controls anlegen:

[ToolboxItemAttribute(false)]

public class WebPartMitSecureStore :  WebPart

{

private TextBox _txt;

private Button _buttonSave;

const string ApplicationId = "SecureStore_Beispiel";

 

protected override void CreateChildControls()

{

_txt = new TextBox() {ID = "TextBoxConnectionString"};

_buttonSave = new Button

{

Text = "Load ConnectionString!",

ID = "ButtonLoad"

};

_buttonSave.Click += LoadConnectionString;

 

Controls.Add(_buttonSave);

Controls.Add(_txt);

}

 

void LoadConnectionString(object sender, EventArgs e)

{    }

}

Das Auslesen von Daten aus dem Secure Store Service geht mit wenigen Codezeilen:

private void LoadConnectionString(object sender, EventArgs e)

{

var provider = new SecureStoreProvider { Context = SPServiceContext.Current };

 

using (var credentialCollection = provider.GetCredentials(ApplicationId))

{

// Der Connectionstring wurde als Generic CredentialType angelegt

var secureStoreCredential = (from c in credentialCollection

where c.CredentialType == SecureStoreCredentialType.Generic

select c).Single();

 

_txt.Text = GetDecryptedCredentialString(secureStoreCredential.Credential);

}

}

 

/// <summary>

/// Wandelt den Pointer in einen String um

/// </summary>

private static string GetDecryptedCredentialString(SecureString secureString)

{

var p = Marshal.SecureStringToBSTR(secureString);

 

try

{

return Marshal.PtrToStringUni(p);

}

finally

{

if (p != IntPtr.Zero)

{

Marshal.FreeBSTR(p);

}

}

}

Fazit

Als Fazit wieder die Vor- und Nachteile der hier vorgestellten Methode:

Vorteile:

  • Administration

    Konfiguration kann über die Central Administration gepflegt werden.
  • Sicherheit

    Rechte können sehr granular in SharePoint verwaltet werden. Berechtigungen für Administration können separat vergeben werden.

  • Implementierung

    Einfach realisierbar. Konfiguration kann mit wenigen Codezeilen ausgelesen werden.

  • Wartbarkeit

    Die Secure Store Services sind eine Standardfunktion von SharePoint, müssen aber separat aktiviert werden.

Nachteile:

  • Scope

    Werte können nur global gespeichert werden und es ist nur ein rein lesender Zugriff möglich!

Datenzugriff in SharePoint: web.config

Dies ist der fünfte Teil einer Serie, die sich mit der Frage beschäftigt auf welche Arten Konfigurationen in Applikationen unter SharePoint gespeichert werden können. Es wird zuerst immer kurz die Methode vorgestellt, gefolgt von einem kurzen Beispiel und den Vor- und Nachteilen.

Eine vollständige Übersicht über bisherige und noch folgende Artikel ist hier zu finden.

In heutigen Teil widmen wir uns dem Speichern von Konfiguration über eine web.config Datei. Die Verwendung einer web.config in SharePoint ist komplizierter als man vermuten sollte. Das Auslesen von Konfigurationseinträgen ist hierbei noch kein Problem und funktioniert wie gewohnt bei nicht SharePoint Anwendungen. Die Schwierigkeiten tauchen erst auf wenn versucht wird Änderungen an der Konfiguration in der Farm zu verteilen. Features verfügen nicht über eine eigene Konfigurationsdatei, sondern verwenden zusammen mit allen Applikationen und Services die globale web.config der IIS-Web-Site.

Das Problem besteht also darin, die benötigten Einträge in die bestehende web.config zu integrieren ohne diese dabei kaputt zu machen. Glücklicherweise bietet SharePoint genau hierfür einen einfachen Mechanismus: die Klasse SPWebConfigModification. Mit dieser lassen sich Einträge problemlos hinzufügen und auch wieder entfernen.

Die Klasse SPWebConfigModification definiert einen Konfigurationseintrag über folgende Eigenschaften:

  • Path – XPath Ausdruck, welcher die Position innerhalb der web.config definiert
  • Type – Hier gibt es drei verschiedene Typen:
    • EnsureAttribute – Die Modifikation ändert den Wert eines Attributes.
    • EnsureChildNode – Die Modifikation fügt einen Knoten hinzu oder entfernt ihn
    • EnsureSection – Modifikation ändert eine komplette Sektion
  • Name – Identifiziert den Knoten zusammen mit der Path Eigenschaft eindeutig. Hier ist also auch ein XPath Ausdruck notwendig.
  • Sequence – Ein Index für den Fall wenn mehrere Modifikationen mit dem gleichen Typ und Namen hinzugefügt werden sollen. Sollte Standardmäßig immer “0” sein.
  • Owner – Identifiziert den “Besitzer” der Modifikation. Kann verwendet werden um alle Modifikationen welche zu einer Applikation gehören zu identifizieren z.B. wenn bei der Deinstallation eines Features alle dazugehörigen Konfigurationseinträge wieder entfernt werden. Es sollte daher unbedingt eindeutig sein.
  • Value – XML Knoten oder Attribut, welches hinzugefügt werden soll.

Beispiel

Der geeignete Zeitpunkt um benötigte Einträge in der web.config anzulegen, ist beim Aktivieren des Features. Hierfür benötigen das Feature einen so genannten Event Receiver. Diesen erstellen wir in Visual Studio mit einem Rechtsklick auf das entsprechende Feature:

501

Visual Studio legt innerhalb des Features eine neue Klasse mit vier auskommentierten Methoden an, welche virtuelle Methoden der Basisklasse überschreiben. Im Folgenden ist es dann notwendig, diese virtuellen Methoden weiter auszuformulieren:

public class BeispielFeatureEventReceiver : SPFeatureReceiver

{

// Uncomment the method below to handle the event raised after a feature has been activated.

 

//public override void FeatureActivated(SPFeatureReceiverProperties properties)

//{

//}

 

 

// Uncomment the method below to handle the event raised before a feature is deactivated.

 

//public override void FeatureDeactivating(SPFeatureReceiverProperties properties)

//{

//}

 

 

// Uncomment the method below to handle the event raised after a feature has been installed.

 

//public override void FeatureInstalled(SPFeatureReceiverProperties properties)

//{

//}

 

 

// Uncomment the method below to handle the event raised before a feature is uninstalled.

 

//public override void FeatureUninstalling(SPFeatureReceiverProperties properties)

//{

//}

 

// Uncomment the method below to handle the event raised when a feature is upgrading.

 

//public override void FeatureUpgrading(SPFeatureReceiverProperties properties, string upgradeActionName, System.Collections.Generic.IDictionary<string, string> parameters)

//{

//}

}

Zuerst passen wir die Methode „FeatureActivated“ an, und fügen folgenden Code hinzu:

public override void FeatureActivated(SPFeatureReceiverProperties properties)

{

// Site öffnen in welche unser Feature installiert wurde

using (var site = ((SPSite)properties.Feature.Parent))

{

site.WebApplication.WebConfigModifications.Clear();

site.WebApplication.Update();

site.WebApplication.WebService.ApplyWebConfigModifications();

 

// Knoten "connectionStrings" erstellen, falls noch nicht vorhanden

var connectionNode = new SPWebConfigModification

{

Path = "configuration",

Name = "connectionStrings",

Sequence = 0,

Owner = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name,

Type = SPWebConfigModification.SPWebConfigModificationType.EnsureSection,

Value = "<connectionStrings />"

};

 

// Connectionstring erstellen

var rootNode = new SPWebConfigModification

{

Path = "configuration/connectionStrings",

Name = "add[@name="" + ConfigName + ""]",

Sequence = 0,

Owner = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name,

Type = SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode,

Value = "<add name="" + ConfigName + "" "

+

"connectionString="metadata=res://*/ModelDemo.csdl|res://*/ModelDemo.ssdl|res://*/ModelDemo.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=Uptime;Integrated Security=True;MultipleActiveResultSets=True&quot;" "

+ "providerName="System.Data.EntityClient" />"

 

};

 

// Neue Knoten hinzufügen

site.WebApplication.WebConfigModifications.Add(connectionNode);

site.WebApplication.WebConfigModifications.Add(rootNode);

 

// Änderungen speichern und Anpassungen verteilen

site.WebApplication.Update();

site.WebApplication.WebService.ApplyWebConfigModifications();

}

}

Wenn das Feature deaktiviert wird, müssen die Einträge natürlich auch wieder entfernt werden. Dies geschieht in der Methode FeatureDeactivating, welche wir als nächstes auskommentieren:

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)

{

// Site öffnen in welche unser Feature installiert wird

using (var site = ((SPSite)properties.Feature.Parent))

{

var webApp = site.WebApplication;

// "Unsere" Modifikationen ermitteln

var mods = (from m in webApp.WebConfigModifications

where m.Owner == System.Reflection.Assembly.GetExecutingAssembly().GetName().Name

select m).ToList();

 

// Modifikationen löschen

mods.ForEach(m => webApp.WebConfigModifications.Remove(m));

 

// Änderungen speichern und Anpassungen verteilen

webApp.Update();

webApp.WebService.ApplyWebConfigModifications();

}

}

Fazit

Zum Abschluss wieder die Vor- und Nachteile der hier vorgestellten Methode:

Vorteile:

  • Implementierung

    Datenzugriff funktioniert wie gewohnt von nicht SharePoint Anwendungen. Zum Verteilen der Konfiguration sind ein paar zusätzliche Zeilen Code erforderlich, aber alles in allem einfach umzusetzen.

  • Wartbarkeit

    Die Klasse SPWebConfigModification class kümmert sich um die Verteilung innerhalb der Farm.

Nachteile:

  • Administration

    Es gibt keine mitgelieferte Oberfläche zum Verwalten der Werte.

  • Wartbarkeit

    Änderungen an der web.config können von Service Packs oder Updates überschrieben werden. Dies kann zu Problemen führen.

    Darüber werden Konfigurationseinträge beim Deployment nicht von SharePoint validiert. Es kann also sehr schnell passieren, dass die web.config nach einem Deployment ungültige Einträge enthält und somit der komplette SharePoint nicht mehr zu erreichen ist!

  • Scope

    Einstellungen gelten global. Es ist also sehr schwierig Einstellungen pro Benutzer zu speichern.