DevAndy | Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Query für Rekursives löschen

[crayon-66424469d4672359783504/]

Posted on 29. Januar 2019 by Andreas in SQL

Tags: Rekursiv

Query um eine UI Tabelle darzustellen. Mit Filter pro Spalte, Sortierung pro Spalte, variablen Paging und Anzahl Zeilen ohne Filterung

DECLARE @Page INT = 1
, @PageSize INT = 50	
, @LanguageID INT = 46
, @ObjectID UNIQUEIDENTIFIER = '27461EA1-6EAC-47C5-8500-50A411C440D6'

, @Sort_Key BIT = NULL
, @Sort_Default BIT = NULL
, @Sort_Translation BIT = NULL

, @Filter_Key NVARCHAR(50) = NULL
, @Filter_Default NVARCHAR(255) = NULL
, @Filter_Translation NVARCHAR(MAX) = NULL 

SELECT 

 [KeyStore].[ID] AS [KeyStore_ID]
, [KeyStore].[Key] AS [KeyStore_Key]
, [KeyStore].[Default] AS [KeyStore_Default]
, [KeyStore].[ApplicationID] AS [KeyStore_ApplicationID]

, [Value].[ID] AS [Value_ID]
, [Value].[Value] AS [Trans]
, [Value].[LanguageID] AS [Value_LanguageID]

, [Language].[EnglishName] AS [Language_EnglishName] 
, TotalRows = COUNT(*) OVER() -- Return Total Rows. Without Offset

FROM [Translation].[KeyStore]
LEFT JOIN [Translation].[Value] [Value] ON [Value].[KeyStoreID] = [KeyStore].[ID]
INNER JOIN [Translation].[Language] [Language] ON [Language].[ID] = @LanguageID

WHERE 
([ApplicationID] = @ObjectID AND ([Value].LanguageID IS NULL OR [Value].LanguageID = @LanguageID))  	

-- Filter: Key
AND ( (@Filter_Key IS NULL OR @Filter_Key = '') OR ([KeyStore].[Key] LIKE REPLACE(@Filter_Key, '*', '%') OR (DIFFERENCE([KeyStore].[Key], @Filter_Key) > 3) ) )

-- Filter: Default
AND ( (@Filter_Default IS NULL OR @Filter_Default = '') OR ([KeyStore].[Default] LIKE REPLACE(@Filter_Default, '*', '%') OR (DIFFERENCE([KeyStore].[Default], @Filter_Default) > 3) ) )

-- Filter: Translation
AND ( (@Filter_Translation IS NULL OR @Filter_Translation = '') OR ([Value].[Value] LIKE REPLACE(@Filter_Translation, '*', '%') OR (DIFFERENCE([Value].[Value], @Filter_Translation) > 3) ) )
 
ORDER BY	
	-- Sort: Key		
	CASE @Sort_Key WHEN 0 THEN [KeyStore].[Key] END DESC, CASE @Sort_Key WHEN 1 THEN [KeyStore].[Key] END ASC
	-- Sort: Default
	, CASE @Sort_Default WHEN 0 THEN [KeyStore].[Default] END DESC, CASE @Sort_Default WHEN 1 THEN [KeyStore].[Default] END ASC
	-- Sort: Value
	, CASE @Sort_Translation WHEN 0 THEN [Value].[Value] END DESC, CASE @Sort_Translation WHEN 1 THEN [Value].[Value] END ASC
	-- Default Sort	
	, CASE WHEN @Sort_Key IS NULL AND @Sort_Default IS NULL AND @Sort_Translation IS NOT NULL THEN 	[KeyStore].[Key] END ASC
	-- Offset paging
	OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY

DECLARE @Page INT = 1

, @PageSize INT = 50

, @LanguageID INT = 46

, @ObjectID UNIQUEIDENTIFIER = '27461EA1-6EAC-47C5-8500-50A411C440D6'

, @Sort_Key BIT = NULL

, @Sort_Default BIT = NULL

, @Sort_Translation BIT = NULL

, @Filter_Key NVARCHAR(50) = NULL

, @Filter_Default NVARCHAR(255) = NULL

, @Filter_Translation NVARCHAR(MAX) = NULL

SELECT

[KeyStore].[ID] AS [KeyStore_ID]

, [KeyStore].[Key] AS [KeyStore_Key]

, [KeyStore].[Default] AS [KeyStore_Default]

, [KeyStore].[ApplicationID] AS [KeyStore_ApplicationID]

, [Value].[ID] AS [Value_ID]

, [Value].[Value] AS [Trans]

, [Value].[LanguageID] AS [Value_LanguageID]

, [Language].[EnglishName] AS [Language_EnglishName]

, TotalRows = COUNT(*) OVER() -- Return Total Rows. Without Offset

FROM [Translation].[KeyStore]

LEFT JOIN [Translation].[Value] [Value] ON [Value].[KeyStoreID] = [KeyStore].[ID]

INNER JOIN [Translation].[Language] [Language] ON [Language].[ID] = @LanguageID

WHERE

([ApplicationID] = @ObjectID AND ([Value].LanguageID IS NULL OR [Value].LanguageID = @LanguageID))

-- Filter: Key

AND ( (@Filter_Key IS NULL OR @Filter_Key = '') OR ([KeyStore].[Key] LIKE REPLACE(@Filter_Key, '*', '%') OR (DIFFERENCE([KeyStore].[Key], @Filter_Key) > 3) ) )

-- Filter: Default

AND ( (@Filter_Default IS NULL OR @Filter_Default = '') OR ([KeyStore].[Default] LIKE REPLACE(@Filter_Default, '*', '%') OR (DIFFERENCE([KeyStore].[Default], @Filter_Default) > 3) ) )

-- Filter: Translation

AND ( (@Filter_Translation IS NULL OR @Filter_Translation = '') OR ([Value].[Value] LIKE REPLACE(@Filter_Translation, '*', '%') OR (DIFFERENCE([Value].[Value], @Filter_Translation) > 3) ) )

ORDER BY

-- Sort: Key

CASE @Sort_Key WHEN 0 THEN [KeyStore].[Key] END DESC, CASE @Sort_Key WHEN 1 THEN [KeyStore].[Key] END ASC

-- Sort: Default

, CASE @Sort_Default WHEN 0 THEN [KeyStore].[Default] END DESC, CASE @Sort_Default WHEN 1 THEN [KeyStore].[Default] END ASC

-- Sort: Value

, CASE @Sort_Translation WHEN 0 THEN [Value].[Value] END DESC, CASE @Sort_Translation WHEN 1 THEN [Value].[Value] END ASC

-- Default Sort

, CASE WHEN @Sort_Key IS NULL AND @Sort_Default IS NULL AND @Sort_Translation IS NOT NULL THEN [KeyStore].[Key] END ASC

-- Offset paging

OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY

Posted on 5. November 2018 by Andreas in SQL

Spalteninformationen zu Prozeduren anzeigen

Der Profiler ist schon eine tolle Sache. Wenn man im SQL Management Studio eine Prozedur ausklappt, dann macht das Management Studio folgende Abfrage um die Informationen auszulesen:

SELECT
param.name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' + '/Param[@Name=' + quotename(param.name,'''') + ']' AS [Urn],
usrt.name AS [DataType],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
param.default_value AS [DefaultValue],
param.is_output AS [IsOutputParameter],
param.is_cursor_ref AS [IsCursorParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
 case 
    when sp.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = sp.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N'microsoft_database_tools_support') 
        is not null then 1
    else 0
end          
             AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))

SELECT

param.name AS [Name],

'Server[@Name=' + quotename(CAST(

serverproperty(N'Servername')

AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' + '/Param[@Name=' + quotename(param.name,'''') + ']' AS [Urn],

usrt.name AS [DataType],

ISNULL(baset.name, N'') AS [SystemType],

CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],

CAST(param.precision AS int) AS [NumericPrecision],

CAST(param.scale AS int) AS [NumericScale],

param.default_value AS [DefaultValue],

param.is_output AS [IsOutputParameter],

param.is_cursor_ref AS [IsCursorParameter],

sp.object_id AS [IDText],

db_name() AS [DatabaseName],

param.name AS [ParamName],

CAST(

case

when sp.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = sp.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit) AS [ParentSysObj],

1 AS [Number]

FROM

sys.all_objects AS sp

INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id

LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))

Posted on 4. November 2018 by Andreas in SQL

Pokemon Tower Defense Tricks

// Letzte Trades

http://www.ptdtrading.com/latestTrades.php?whichProfile=1

JQuery hinzufügen dynamisch

var jq = document.createElement('script');
jq.src = "https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js";
document.getElementsByTagName('head')[0].appendChild(jq);
// ... give time for script to load, then type (or see below for non wait option)
jQuery.noConflict();

var jq = document.createElement('script');

jq.src = "https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js";

document.getElementsByTagName('head')[0].appendChild(jq);

// ... give time for script to load, then type (or see below for non wait option)

jQuery.noConflict();

Blendet alle Regulären Pokemon aus

$('.block:not(.shiny):not(.shadow)').remove()

1	$('.block:not(.shiny):not(.shadow)').remove()

Blendet Pidgeot aus

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/18_1.png"]').closest('.pokemon_compact').remove()

1	$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/18_1.png"]').closest('.pokemon_compact').remove()

// Chrome Extension: ScriptRunner
//folgendes kopieren:

$('.block:not(.shiny):not(.shadow)').remove()

  //Pidgeot
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/18_1.png"]').closest('.pokemon_compact').remove()
//Pidgey
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/16_1.png"]').closest('.pokemon_compact').remove()

//Tentacool
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/72_1.png"]').closest('.pokemon_compact').remove()
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/72_0.png"]').closest('.pokemon_compact').remove()
//Staryu
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/120_1.png"]').closest('.pokemon_compact').remove()
//Onix
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/95_0.png"]').closest('.pokemon_compact').remove()
//Geodude
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/74_1.png"]').closest('.pokemon_compact').remove()
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/75_0.png"]').closest('.pokemon_compact').remove()
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/63_1.png"]').closest('.pokemon_compact').remove()
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/121_1.png"]').closest('.pokemon_compact').remove()
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/75_1.png"]').closest('.pokemon_compact').remove()  
$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/95_1.png"]').closest('.pokemon_compact').remove()  
$('.block.pokemon_compact').find('b:contains((Hacked Version)').closest('.pokemon_compact').remove()

// Chrome Extension: ScriptRunner

//folgendes kopieren:

$('.block:not(.shiny):not(.shadow)').remove()

//Pidgeot

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/18_1.png"]').closest('.pokemon_compact').remove()

//Pidgey

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/16_1.png"]').closest('.pokemon_compact').remove()

//Tentacool

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/72_1.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/72_0.png"]').closest('.pokemon_compact').remove()

//Staryu

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/120_1.png"]').closest('.pokemon_compact').remove()

//Onix

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/95_0.png"]').closest('.pokemon_compact').remove()

//Geodude

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/74_1.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/75_0.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/63_1.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/121_1.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/75_1.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('img[src="http://www.ptdtrading.com/games/ptd/small/95_1.png"]').closest('.pokemon_compact').remove()

$('.block.pokemon_compact').find('b:contains((Hacked Version)').closest('.pokemon_compact').remove()

Posted on 18. September 2018 by Andreas in Allgemein

Alle Country Codes als SQL Query

Einfach eine Konsolenanwendung erstellen und folgendes Snippet reinkopieren:

Posted on 4. September 2018 by Andreas in C#, SQL

Eintrag in AppSettings zur Laufzeit ändern

Zunächst benötigt man eine Referenz auf “System.Configuration”. Diese ist per Standard nicht in einem Projekt eingebunden

Man muss eine AppSettings.config erstellen, die in etwa so aussieht:

&lt;?xml version="1.0" encoding="utf-8" ?&gt;
&lt;configuration&gt;
  &lt;appSettings&gt;
    &lt;add key="User" value="Andreas"/&gt;
  &lt;/appSettings&gt;
 
  &lt;startup&gt; 
        &lt;supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /&gt;
    &lt;/startup&gt;
&lt;/configuration&gt;

<?xml version="1.0" encoding="utf-8" ?>

</appSettings>

</startup>

</configuration>

Anschließend noch eine Hilfsfunktion:

private void ChangeInAppSetting(string key, string value) {
    System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    config.AppSettings.Settings[key].Value = value;
    config.Save(ConfigurationSaveMode.Modified);
    ConfigurationManager.RefreshSection("appSettings");
}

private void ChangeInAppSetting(string key, string value) {

System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

config.AppSettings.Settings[key].Value = value;

config.Save(ConfigurationSaveMode.Modified);

ConfigurationManager.RefreshSection("appSettings");

}

Die man ausführen kann:

ChangeInAppSetting("User", "Martin");

1

ChangeInAppSetting("User", "Martin");
Auslesen kann man die AppSettings ganz einfach mit

ConfigurationManager.AppSettings["User"];

1

ConfigurationManager.AppSettings["User"];

Posted on 22. Mai 2018 by Andreas in C#

Tags: appSettings, Configuration, ConfigurationManager

SQL Backup erzeugen

-- database name  
DECLARE @name VARCHAR(50) = 'MyDatabase';
-- specify filedate format (yyyy-mm-dd)
DECLARE @fileDate VARCHAR(20) = REPLACE(CONVERT(VARCHAR(20),GETDATE(),111),'/','-') 
-- path for backup files  (Muss mit \ enden)
DECLARE @path VARCHAR(256) = 'MyBackupDirectory\'  
DECLARE @fileName VARCHAR(256) = CONCAT(@path, @name, '_', @fileDate, '.bak');


BACKUP DATABASE @name TO DISK = @fileName

-- database name

DECLARE @name VARCHAR(50) = 'MyDatabase';

-- specify filedate format (yyyy-mm-dd)

DECLARE @fileDate VARCHAR(20) = REPLACE(CONVERT(VARCHAR(20),GETDATE(),111),'/','-')

-- path for backup files (Muss mit \ enden)

DECLARE @path VARCHAR(256) = 'MyBackupDirectory\'

DECLARE @fileName VARCHAR(256) = CONCAT(@path, @name, '_', @fileDate, '.bak');

BACKUP DATABASE @name TO DISK = @fileName

Kurz und knackiger Skript um ein Backup zu erzeugen.

Ergebnis: MyDatabase_2018-04-30.bak

Andrere Datumsformate auch hier: https://www.techonthenet.com/sql_server/functions/convert.php

Posted on 30. April 2018 by Andreas in SQL

Insert and retrieve autogenerated ID

Möchte man einen Insert durchführen und daraufhin die generierte ID erhalten, so geht es beim Identifiert (Int) sehr einfach über den OUTPUT Parameter:

CREATE PROCEDURE dbo.MyTable_Insert
	 @ID Int OUTPUT
	, @ColumnA nvarchar(10)
	, @ColumnB nvarchar(10)

INSERT INTO dbo.MyTable (
					ColumnA
					, ColumnB
				)
				VALUES (
					@ColumnA
					, @ColumnB
				)
			
			SELECT @ID = @@IDENTITY

CREATE PROCEDURE dbo.MyTable_Insert

@ID Int OUTPUT

, @ColumnA nvarchar(10)

, @ColumnB nvarchar(10)

INSERT INTO dbo.MyTable (

ColumnA

, ColumnB

)

VALUES (

@ColumnA

, @ColumnB

)

SELECT @ID = @@IDENTITY

Beim Uniqueidentifier (GUID) ist das Selektieren eines @@Identity nicht möglich.

Daher muss eine temporäre Tabelle hinhalten:

CREATE PROCEDURE dbo.MyTable_Insert
	 @ID Uniqueidentifier OUTPUT -- Ebenfalls den OUTPUT Parameter
	, @ColumnA nvarchar(10)
	, @ColumnB nvarchar(10)

CREATE TABLE #tmp(ID uniqueidentifier)	-- Temporäre Tabelle erzeugen
	
INSERT INTO dbo.MyTable (
					ColumnA
					, ColumnB
				)
				OUTPUT inserted.ID INTO #tmp -- Die ID in die temp. Tabelle schreiben
				VALUES (
					@ColumnA
					, @ColumnB
				)
			
			SELECT @ID = (SELECT TOP 1 ID FROM #tmp) -- Die ID wieder selektieren

CREATE PROCEDURE dbo.MyTable_Insert

@ID Uniqueidentifier OUTPUT -- Ebenfalls den OUTPUT Parameter

, @ColumnA nvarchar(10)

, @ColumnB nvarchar(10)

CREATE TABLE #tmp(ID uniqueidentifier) -- Temporäre Tabelle erzeugen

INSERT INTO dbo.MyTable (

ColumnA

, ColumnB

)

OUTPUT inserted.ID INTO #tmp -- Die ID in die temp. Tabelle schreiben

VALUES (

@ColumnA

, @ColumnB

)

SELECT @ID = (SELECT TOP 1 ID FROM #tmp) -- Die ID wieder selektieren

Posted on 12. April 2018 by Andreas in SQL

Vivaldi Browser

Auf der Suche nach einem Browser, der Tabs stacken/gruppieren kann, bin ich bei Vivaldi gelandet. Dieser hat mich in vielerlei hinsicht überzeugt:

Die Engine ist die Gleiche wie bei Chromium.
Das Entwicklungsmenü ist zu Chrome gleich.
Man kann Tabs gruppieren und wirklich alles ändern.

Anbei der Link zu Vivaldi: https://vivaldi.com

Ich habe noch die style.css anpassen müssen, da die Schriftart zu smoothie aussieht und mir die Schriftgröße für die Tabs zu klein war.

Diese befindet sich bei Windows hier:

C:\Users\Seibel\AppData\Local\Vivaldi\Application\1.14.1077.60\resources\vivaldi\style\common.css

Dazu folgende Änderungen einfügen: Im Kopf einfügen

 * {
	 font-smooth: never !important;
	-webkit-font-smoothing : none !important;
 }

* {

font-smooth: never !important;

-webkit-font-smoothing : none !important;

}

Nach .tab-position .tab { suchen und folgendes hinzufügen

font-size: 9pt !important;

1	font-size: 9pt !important;

Dann im kommpletten Stylesheet nach smooth suchen und bis auf den angefügten Kopfeintrag alle smooth Einträge entfernen

Als Theme habe ich das Dark Theme genommen und die Farben angepasst:

Posted on 5. April 2018 by Andreas in Allgemein

Snippet um Zip Dateien im Memory zu erstellen

Wenn Foldername gefüllt ist, wird ein Verzeichnis erstellt, wo die Dateien reinkommen.

/// <summary>
        /// Das Dictionary wird iteriert und die Dateien darin gezippt. Der Key Value des Dictionarys entspricht der neuen Dateienbezeichnung
        /// Foldername ist das Verzeichnis, wo alle Dateien enthalten sind. Leeres Verzeichnis -> Dateien liegen lose im Zip Archiv
        /// </summary>
        /// <returns>Zip File</returns>

/// <summary>

/// Das Dictionary wird iteriert und die Dateien darin gezippt. Der Key Value des Dictionarys entspricht der neuen Dateienbezeichnung

/// Foldername ist das Verzeichnis, wo alle Dateien enthalten sind. Leeres Verzeichnis -> Dateien liegen lose im Zip Archiv

/// </summary>

/// <returns>Zip File</returns>

public static MemoryStream ZipFiles(IDictionary&lt;string, FileInfo&gt; files, string zipName, string Foldername = "") {
    if (!zipName.EndsWith(".zip")) {
        zipName += ".zip";
    }            
    var stream = new MemoryStream();
    var zip = new ZipArchive(stream, ZipArchiveMode.Create, true);
    // Verzeichnis ins Archiv erstellen, wo darauf folgende Dateien rein kommen
    if (Foldername != string.Empty) {
        zip.CreateEntry(Foldername + "/");
    }
    // Dateien, dem Archiv hinzufügen
    foreach (KeyValuePair&lt;string, FileInfo&gt; file in files) {
        // Zip Eintrag erzeugen
        ZipArchiveEntry entry = zip.CreateEntry(Foldername + "/" + file.Key, CompressionLevel.Optimal);
        // Zip Eintrag ins Memory schreiben
        using (var zipEntry = entry.Open())
        using (var fileStream = new FileStream(file.Value.FullName, FileMode.Open, FileAccess.Read)) {
            fileStream.CopyTo(zipEntry);
        }
    }
    zip.Dispose();
    stream.Seek(0, 0);
    return (stream.Length &gt; 0) ? stream : null;
}

public static MemoryStream ZipFiles(IDictionary<string, FileInfo> files, string zipName, string Foldername = "") {

if (!zipName.EndsWith(".zip")) {

zipName += ".zip";

}

var stream = new MemoryStream();

var zip = new ZipArchive(stream, ZipArchiveMode.Create, true);

// Verzeichnis ins Archiv erstellen, wo darauf folgende Dateien rein kommen

if (Foldername != string.Empty) {

zip.CreateEntry(Foldername + "/");

}

// Dateien, dem Archiv hinzufügen

foreach (KeyValuePair<string, FileInfo> file in files) {

// Zip Eintrag erzeugen

ZipArchiveEntry entry = zip.CreateEntry(Foldername + "/" + file.Key, CompressionLevel.Optimal);

// Zip Eintrag ins Memory schreiben

using (var zipEntry = entry.Open())

using (var fileStream = new FileStream(file.Value.FullName, FileMode.Open, FileAccess.Read)) {

fileStream.CopyTo(zipEntry);

}

zip.Dispose();

stream.Seek(0, 0);

return (stream.Length > 0) ? stream : null;

}

In Verbindung mit MVC kann der Stream nun Chunk-weise ausgegeben werden:

 System.IO.MemoryStream zipFile = getStream();

                        //Alternativ: result = File(zipFile, "application/zip", filename);
                        Response.ClearHeaders();
                        Response.ClearContent();

                        Response.ContentType = "application/zip";
                        Response.CacheControl = "No-cache";
                        Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
                        Response.AddHeader("Content-Length", zipFile.Length.ToString());
                        int chunkSize = 8192;
                        byte[] buffer = new byte[chunkSize];
                        int offset = 0;
                        int read = 0;
                        using (var fs = zipFile) {
                            while ((read = fs.Read(buffer, offset, chunkSize)) > 0) {
                                if (!Response.IsClientConnected)
                                    break;
                                Response.OutputStream.Write(buffer, 0, read);
                                Response.Flush();
                            }
                        }

System.IO.MemoryStream zipFile = getStream();

//Alternativ: result = File(zipFile, "application/zip", filename);

Response.ClearHeaders();

Response.ClearContent();

Response.ContentType = "application/zip";

Response.CacheControl = "No-cache";

Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);

Response.AddHeader("Content-Length", zipFile.Length.ToString());

int chunkSize = 8192;

byte[] buffer = new byte[chunkSize];

int offset = 0;

int read = 0;

using (var fs = zipFile) {

while ((read = fs.Read(buffer, offset, chunkSize)) > 0) {

if (!Response.IsClientConnected)

break;

Response.OutputStream.Write(buffer, 0, read);

Response.Flush();

}

Posted on 4. Januar 2018 by Andreas in C#

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!