Biggle's Blog

Web- und Software Development

by Mario Binder

MSSQL Shuffle Funktion zum anonymisieren von Daten

Ich wollte einige Datensätze für die Testumgebung anonymisieren und heraus kam eine kleine Funktion, die ein zufälligen String generiert. Als Parameter wird der Funktion eine beliebige Zeichenfolge und die Angabe, wie lang der zufällige Wert sein soll, mitgegeben. Der erste Buchstabe im Resultat wird als Großbuchstabe zurückgegeben.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE VIEW Random
    AS SELECT RAND() AS RAND
GO
 
CREATE FUNCTION Shuffle(@string AS nvarchar(MAX), @LENGTH AS INT)
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @RESULT NVARCHAR(MAX)
    DECLARE @counter INT
 
    SET @RESULT = ''
    SET @counter = 0
 
    WHILE @counter < @LENGTH
    BEGIN
 
        SELECT @RESULT = @RESULT + SUBSTRING(@string, (SELECT CONVERT(INT, (RAND * LEN(@string) + 1)) FROM Random), 1)
        SET @counter = @counter + 1
    END
 
    SELECT @RESULT = UPPER(LEFT(@RESULT,1)) + SUBSTRING(@RESULT,2,LEN(@RESULT))
    RETURN @RESULT
END
GO

Aufzurufen wird das Ganze dann wie folgt:

1
2
UPDATE dbo.Persons
SET Lastname = dbo.Shuffle('abcdefghijklmnoprstuvwz', 8)

Alternativ könnte man anstelle der Zeichen, den alten Wert aus der Spalte und die Länge mitgeben,

1
2
UPDATE dbo.Persons
SET Lastname = dbo.Shuffle(Lastname, LEN(Lastname))

Viel Spaß beim entwickeln : )
Cool wäre jetzt noch, wenn man den String lesbar macht, hat einer eine Idee?

by Mario Binder

Connectionstring ermitteln – The simplest way

Der wohl einfachste Weg einen Connectionstring zu ermitteln und dabei noch den String verifizieren, kann man durch folgende Verfahrensweise. (Voraussetzung Visual Studio & SQL Server Management Studio (SSMS)

Man legt sich an einem Ort seiner Wahl ein File mit der Extension *.udl an z.B. conn.udl und klickt dieses File doppelt. Hier kann man nun den Server und die Datenbank auswählen, die man im Connectionstring benötigt. Unter Provider noch den richtigen DB-Provider festlegen und zum Schluss mit OK bestätigen.

Voila, öffnent man sein *.udl file nun mit dem Editor, findet man dort seinen Connnectionstring : )

1
2
3
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=tempdb;Data Source=SX-PC\sqlexpress

by Mario Binder

Stored Procedure mit skalaren Rückgabewert im Entity Framework

Will man Stored Procedures über das Entity Framework aufrufen, kann man das – wenn man denn ein EntitySet erwartet – recht einfach. Erwartet man aber eine skalare Variable (Int32, String, Guid…) weiter als Response, gibt es leider ein bekanntes Problem, dass dafür kein Code generiert wird.

Um dennoch eine solche Procedure verwenden zu können, müssen einige Einträge in dem Model von Hand getätigt und im Code muss eine eigene EntityConnection implementiert werden, die für den StoredProcedure-Aufruf eine temporäre Verbindung aufbaut. Um das zu verdeutlichen, hier meine Herangehensweise in einem Testprojekt.

In der Datenbank erstelle ich mir vorerst eine Stored Procedure namens GetPersonenId. Diese Procedure erwartet zwei Parameter, zum einen eine Id, und zum anderen einen boolschen Wert. Was genau in der SP passieren soll, ist für dieses Beispiel nicht relevant.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE [TestDB2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetPersonenId]
	@Root uniqueidentifier ,
	@Rekursiv bit
AS
BEGIN
	SET NOCOUNT ON;
 
	If @Rekursiv = 0
	BEGIN
		select PersonenId
		from Personen
		where ChildId = @Root
	End
 
	Else
	Begin
		-- rekursive abfrage...
	End
END

Nun füge ich meinem Projekt ein ADO.NET Entity Data Model hinzu und wähle aus meine Datenbankverbindung meine Datenbank aus.

Im kommenden Dialog wähle ich hier unter den “Gespeicherten Prozeduren” meine SP aus.

Das nun erstellte Model schliesse ich und öffne das auch gleich wieder mit den integrierten XML-Editor, dazu ein Rechtsklick auf das Model im Projektmappen-Explorer und auf “Öffnen mit…” Hier wähle ich dann den XML Editor zum öffnen des Modells.

Hier findet man den generierten Code kommentiert mit den jeweiligen Bereichen (SSDL, CSDL, C-S mapping). Im Knoten edmx:StorageModels (SSDL content)  sollte bereits, eine vom EF definierte Funktion existieren, die den Namen der Procedure trägt.

1
2
3
4
<Function Name="GetPersonenId" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="Root" Type="uniqueidentifier" Mode="In" />
  <Parameter Name="Rekursiv" Type="bit" Mode="In" />
</Function>

Die kann so belassen werden. Im Knoten edmx:ConceptualModels (CSDL content) fügen wir ein FunktionImport Knoten hinzu, der den Rückgabetypen und die Parameter wie diese in der StoredProcedure definiert wurden. Wenn der Knoten EntityContainer kein schliessendes Tag hat sondern /> endet, muss dieser so geändert werden.

1
2
3
4
<FunctionImport Name="GetPersonenId" ReturnType="Collection(Guid)">
  <Parameter Name="Root" Type="Guid" Mode="In" />
  <Parameter Name="Rekursiv" Type="Boolean" Mode="In" />
</FunctionImport>

Im Knoten edmx:Mappings (C-S mapping content) ergänzen wir im Knoten EntityContainerMapping den Knoten FunctionImportMapping. Der kommende FunctionName im Code  ist der im Schema definierte Namespace plus der Name von der SP. Auch hier wichtig, wenn der Knoten EntityContainerMapping kein schliessendes Tag hat sondern /> endet, muss dieser so geändert werden.

1
<FunctionImportMapping FunctionImportName="GetPersonenId" FunctionName="PersonenModel.Store.GetPersonenId" />

Das edmx-File kann nun gespeichert und geschlossen werden und wir wechseln in die Klasse wo wir die Methode bauen möchten, die die Prozedur  aufruft. Jetzt verweisen wir noch auf System.Configuration um den ConfigurationManager für die Übergabe des ConnectionStrings aus der App.Config. Der Connectionstring kann aber auch direkt in die Variable connection geschrieben werden…

Hier nun der Code, in den Kommentare werden die einzelnen Zeilen kommentiert.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
public static IEnumerable<Guid> ReadPersonenIdStruktur(Guid root, bool recursive)
{
    //Liste instanziieren
    var personen = new List<Guid>();
 
    //connectionstrring aus der app.config holen
    string connection = ConfigurationManager.ConnectionStrings["PersonenEntities"].ConnectionString;
 
    //context verwenden
    using (var conn = new EntityConnection(connection))
    {
        try
        {
            //connection oeffnen
            conn.Open();
 
            //EntityCommand instanzieren
            EntityCommand cmd = conn.CreateCommand();
 
            /* der comandtext setzt sich aus den entities-namen der
             * bei der Erstellung des Models festgelegt wurde
             * plus den Namen der Stored Procedur                     *
             */
            cmd.CommandText = "PersonenEntities.GetPersonenId";
 
            //commandtype festlegen
            cmd.CommandType = CommandType.StoredProcedure;
 
            //die parameter die erwartet werden von der sp dem Kommando hinzufuegen
            cmd.Parameters.AddWithValue("Root", root);
            cmd.Parameters.AddWithValue("Rekursiv", recursive);
 
            //ausfuehren
            var result = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
 
            //ergebnisse in die liste schreiben
            while (result.Read())
            {
                personen.Add((Guid)result.GetValue(0));
            }
        }
        catch (Exception ex)
        {
            //Fehlerbehandlung
        }
        finally
        {
            //connection schliessen und wegschmeissen
            conn.Close();
            conn.Dispose();
        }
    }
    return personen;
}

Wenn noch Fragen offen sind, versuche ich zu helfen. Ansonsten wie immer,

viel Spass beim entwickeln : )


by Mario Binder

Der ReportingService (SSRS) unter C#

Das eine oder andere HowTo um den SQL Server ReportingService anzusprechen funktionierten bei mir immer nur zur Hälfte. Wem es genau so geht, hier eine kleine Anleitung, wie man den ReportingService unter VS2008 via WSDL konsumiert.

Zieldefinition: Über eine C# Applikation wird es ermöglich, ein  Bericht über die SQL Server Reporting WSDL Schnittstelle  als PDF zu rendern und zu speichern. Der Bericht muss dazu auf dem ReportServer vorhanden sein. Möchte man einen Bericht erstellen, kann er z.B.  den ReportBuilder 3.0 dazu verwenden.

Okay lets go.

Man fügt dem Projekt zu Beginn einen Dienstverweis hinzu, folgende URL (Server anpassen : ) zum ReportService  wird dazu verwendet.

1
http://<SERVER>/reportserver/reportservice2005.asmx?wsdl

Der Securityknoten in der automatisch angelegten app.conf wird durch folgende Einträge angepasst

1
2
3
4
5
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm" proxyCredentialType="Windows"
        realm="">
    </transport>
</security>

Die Using Direktive für den Client findet man in der eben angelegten ServiceReference, in meinem Fall lautet diese

1
using SQLReportingServiceTest.ReportingService;

Für die Render Methode benötigen wir unter anderen noch einige Variablen und den entsprechenden Clientaufruf.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
//Das Format, in dem der Bericht gerendert werden soll. Dieses Argument ist einer Renderingerweiterung zugeordnet. Zu den unterstützten Formaten gehören Microsoft Office Excel, PDF und Image.
string format = "PDF";
//[out] Die beim Rendern des Berichtsinhalts verwendete Codierung.
string encoding;
 //[out] Der MIME-Typ des gerenderten Berichts.
string mimeType;
 //[out] Die für die Ausgabedatei verwendete Dateinamenerweiterung.
string extension;
// [out] Ein Array von Warning-Objekten zur Beschreibung der Warnungen, die während der Berichtsverarbeitung ausgegeben wurden.
Microsoft.Reporting.WinForms.Warning[] warnings = null; string[] streamIDs = null;
 
using (var rs = new ReportingService2005SoapClient())
{
    //Festlegen der zulässigen Identitätswechselebene (Server/Client)
    rs.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
 
    //Instanz des ReportViewer-Steuerelements
    var rv = new ReportViewer();
 
    //Festlegen des Verarbeitungsmodus des ReportViewer-Steuerelements.
    rv.ProcessingMode = ProcessingMode.Remote;
 
    //Berichtsserver festlegen
    rv.ServerReport.ReportServerUrl = new Uri(@"http://<SERVER>/reportserver");
 
    //Festlegen des Pfades zum Bericht
    rv.ServerReport.ReportPath = @"/MeinBericht";
 
   //Verarbeitet den Bericht und rendert ihn im angegebenen Format und speichert diesen in ein byteArray
    byte[] bytes = rv.ServerReport.Render(format, null, out mimeType, out encoding, out extension, out streamIDs, out warnings);
 
    //Schreibt das Resultat in ein pdf
    FileStream fs = File.Create("result.pdf", bytes.Length);
    fs.Write(bytes,0,bytes.Length);
    fs.Close();
}

Nach Ausführen dieses Codes, steht im bin Ordner nun eine result.pdf zur Verfügung.


Weiterführende Links:


Viel Spass beim entwickeln : )

by Mario Binder

Mehrfache SQL Statements absetzen | C# Quicky

Will man mehrere SQL Statements abschicken, wirft man ein Auge auf “Transaction”. Um über ein SQL Command solche Transactions zu starten, kann man folgenden Codeansatz dazu verwenden

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Guid _testOEguid = Guid.NewGuid();
 
var createDummyAgentur = @"
INSERT INTO [dbo].[TABLE]([COLUMN], [row1], [row2])
SELECT N'{0}', 36485, 1
";
 
var createDummyOE = @"
INSERT INTO [dbo].[TABLE]([COLUMN], [row1], [row2])
SELECT N'{0}', N'Bank AG', N'Muster'
";
 
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestPersonEntities"].ConnectionString))
{
    con.Open();
    SqlTransaction transaction = con.BeginTransaction();
    try
    {
        SqlCommand command = con.CreateCommand();
        command.CommandType = CommandType.Text;
        command.Transaction = transaction;
 
        command.CommandText = String.Format(createDummyOE, _testOEguid);
        command.ExecuteNonQuery();
 
        command.CommandText = String.Format(createDummyAgentur, _testOEguid);
        command.ExecuteNonQuery();
 
        transaction.Commit();
    }
    catch (Exception)
    {
        transaction.Rollback();
    }
    finally
    {
        con.Close();
    }
}

Mit dieser Methodik kann man beliebig viele Statements abschicken, einfach oder ?!

Viel Spass beim entwickeln : )

by Mario Binder

Verteilte Datenbankabfragen

Wenn man ein Ergebniss von mehreren Tabellen aus verschiedenen Datenbanken selektieren möchte, kann man das über ein UNION bewerkstelligen.

Die Anzahl der zu selektierenden Spalten muss immer gleich sein. Man kann auch verschiedene Spalten abfragen, diese müssen aber immmer vom selben Datentyp sein. Gegebenfalls muss man diese dann in den entsprechenden Typen casten.

Example


SELECT lastname, firstname FROM db1.contactTable
UNION
SELECT lastname, firstname FROM db2.otherTable