sql server filestream

1.) Filestream TSQL

check filestream status:

SELECT * FROM sys.configurations WHERE name = 'filestream access level'

A) Pepare SQL Server

1) configuration Manager / SQL-Server Db Service Propteries / Filestream Tab: Check all
2) EXEC sp_configure filestream_access_level, 2  RECONFIGURE
3) restart SQL Server Service

B) Prepare DATABASE

use testFileStream;


alter database testFileStream ADD FILEGROUP fsg CONTAINS FILESTREAM;


ALTER DATABASE testFileStream ADD FILE ( NAME = N'fsf', FILENAME = N'D:\sqlData\filesteamtest\' ) TO FILEGROUP fsg;

C) Prepare TABLE

its necesarry to have a rowguidcol:

CREATE TABLE dbo.BLOB2 
(
  ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL default newid() UNIQUE ,
  BLOB VARBINARY(MAX) FILESTREAM NULL
)
otherwise you get: A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property

alter table blobs add RowID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL default newid() UNIQUE 

alter table blobs add  [Bytes2] varbinary(max) filestream null;

alter existing Columns doesn't work:

alter table blobs alter column ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL default newid() UNIQUE 

alter table blobs alter column [Bytes] varbinary(max) filestream null;

D) Insert Files

　
INSERT INTO dbo.blob2 (blob)


SELECT * FROM
OPENROWSET(BULK N'd:\9bat\test.sql', SINGLE_BLOB) AS Import


or
INSERT INTO [dbo].[FileTableTb] ([name],[file_stream])SELECT'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB) AS FileData
or

INSERT INTO [dbo].FileTableTb ([name],[file_stream]) SELECT 'test1.txt', CONVERT(VARBINARY(MAX),'TestFileText') AS FileData

or Update Files:
update [dbo].FileTableTb set [file_stream] =(SELECT * FROM OPENROWSET(BULK N'd:\temp\test2.txt', SINGLE_BLOB) AS FileData) WHERE id=12

E) Check Result

select *, blob.PathName() from blob2;

in D:\sqlData\filesteamtest\
there are dirs named with guids (2-levels)

allowing a url

netsh http add urlacl url= http://+:51915/Server.wcfService/ user=domian\username

win 10 november update langsam / slow

build 10240 läuft auf meinem lenovo thinkstation mit core i5 recht flott, build 10586 extrem langsam - besonders visual studio

windows build nummer

HKCU\Control Panel\Desktop den DWORD-Wert (32-Bit) mit dem Namen PaintDesktopVersion hinzufügen und auf 1


https://windowsblog.at/2015/07/31/howto-build-nummer-wieder-am-desktop-anzeigen/

raspberry pi 3 windows 10 iot

windows iot download mit windows iot dashboard installieren (rasperry pi3: insider preview, custom)

ipadresse:8080 webinerface

powershell admin:
start-service winrm
set-item wsman:\localhost\client\trustedhosts -value * -force
enter-pssession -ComputerName 169.254.63.249 -Credential "169.254.63.249\Administrator"

https://www.hackster.io/
Remote "Desktop":
Windows Iot Remote Clint Universal App Insallieren, im Webinterface am Raspi links unten Remote aktivieren

citadel

list usb devices:
sudo lsusb

list devices
sudo blkid

list partitions:
sudo fdsik -l

list disks by ...:
ls -al /dev/disk/by-uuid/
ls /dev/disk/by-label/

ls /dev/sd*

apt-get install:

ntfs-3g

exfat

hdparm


list power mode of hd:
sudo hdparm -C /dev/sda

dmesg

raspberry pi fernzugriff mittels VNC

vnc server:

sudo apt-get update
sudo apt-get install x11vnc
x11vnc -usepw -forever -rfbport 12345 -display :0

startet server mit port 12345

W10: UWP Packages auf Windows 10 Handy (Mobile) laden deployen

Visual Studio:
rechte Maustaste auf App, Store, Create Package (No Store)


Handy:
Wlan ein
Einstellungen / Update und Sicherheit / Für Entwickler / Entwicklermodus,
Gerätesuche ein
Geräteportal ein
unter Geräteportal steht Verbindung herstellen über:
WiFi
https://x.y.z.a (IpAdresse)

mit Browser auf Handy Ip (steht unter Geräteportal siehe oben) verbinden und koppeln - wichtig: Handy und PC müssen im selben Subnetz sein

in App Manager App Uploaden: ....AppxBundle

rasperry pi

LED am GPIO Pin 12 ein und ausschalten:

import Rpi.GPIO as IO

import time

OI.setmode(IO.BOARD)

IO.setp(12,IO.OUT)

state=True

while True:

  IO.output(12,state)

  state= not state

  time.sleep(1)

falls GPIO nicht vorhanden, mit apt-get installieren

Temperatursensor 

Dallas-18820 

1607C4+23AA

oracle can connect with sqlplus but not with sql developper / kann mit sqlplus verbinden aber nicht mit sqldeveloper

sqlplus braucht für Verbindungen zum lokalen host unter Angabe der Sid scheinbar keinen listener:
sqlplus user/pwd@sid

für tns jedoch schon:

splplus user/pwd@tnsName

SQL developper braucht auch einen listener zum verbidnen, wenn also sqlplus funktioniert, dann ist wahrscheinlihc der listener falsch konfiguriert (Hostanem ...)

oracle tablespace verkleinern / resize smaller

get minimum size of files / minimalgroesse der Datei:

select f.file_name, (t.block_size*max(e.block_id)/1024/1024) MB
from dba_tablespaces t, dba_data_files f, dba_extents e
where e.tablespace_name = 'SYSAUX'
and   e.tablespace_name = f.tablespace_name
and e.tablespace_name = t.tablespace_name
group by f.file_name, t.block_size;

(from http://www.informatik-aktuell.de/betrieb/datenbanken/der-oracle-sysaux-tablespace-reorganisation-und-verkleinerung.html)

alter database datafile '' resize  ;

durch reorganisieren des frgamentierten Files kann die minimalgroesse herabgesetzt werden - the minimal size can be reduced by reorganicing the fragmented tablespace:

1. get objects at end of file (first rows):
   set pagesize 3000
   set linesize 3000
   select e.file_id, max(e.block_id),
   e.owner, e.segment_name, segment_type, partition_name
   from dba_extents e
   where e.tablespace_name = 'SYSAUX'
   group by e.file_id, e.owner, e.segment_name, segment_type, partition_name
   order by 1 desc , 2 desc;
2. reorganize object blocking end of file:
   alter table owner.tablename move;
   alter Index rebuild;
    
3. get minimum size of file shrink:
   select f.file_name, (t.block_size*max(e.block_id)/1024/1024) MB
   from dba_tablespaces t, dba_data_files f, dba_extents e
   where e.tablespace_name = 'SYSAUX'
   and e.tablespace_name = f.tablespace_name
   and e.tablespace_name = t.tablespace_name
   group by f.file_name, t.block_size;
4. shrink file:
   alter database datafile 'C:\ORA_DAT\myfile.DBF' resize 4500M;

Entity Framework AddOrUpdate

funktioniert gut, solange das Objekt nicht im lokjalen Speicher vorhanden ist - dann macht die Methode nämlich gar nichts

https://blog.oneunicorn.com/2012/05/03/the-key-to-addorupdate/

Außerdem ist es möglich, dass die DbSet Listen Einträge mit doppeltem Key enthalten - der wird dann erst beim Schreiben in die Datenbank geprüft und man erhält dann eine Key Violation. Daher besser zunächst zu suchen und dann zu adden.

ssl zertifikat erstellen und iis https (ssl) einrichten

1) SLL Zertifikat erwerben und installieren am IIS:

1a) Zertifikats Anforderung erstellen:

Im IIS unter server \ serverzertifikate mit rechter Maustasete eine Zertifikatsanforderung erstellen und speichern => man erhält ein Textfile das in etwa so aussieht:
oder auf bestehendes Zertifikat renew klicken

-----BEGIN NEW CERTIFICATE REQUEST-----
blablabla
-----END NEW CERTIFICATE REQUEST-----

1b) Zertifikat von Zertifizierungsstelle austellen lassen:

diesen Text dann in die Zwischen ablage kopieren und beim Zertifikatsanbieter einfügen.
kostenlose Webzertifikate gibt z.b. startssl.com - allerdings seit ende 2016 werden diese von vielen Browsern nicht mehr als sicher erachtet.

1) email adresse angeben => erhalte code, mit diesem wird dann ein zertifiakt ausgestellet, das man in den Webbrowser importieren muß (neu starten des Webbrowsers) dann wieder startssl.com ansurfen, einloggen
2) hostname validieren
3) zertifikat erstellen mittels wizard => zip file enthält iis.zip, dieses enthält ein CRT File

sslmarket.at
1) konto erstellen
2) zertifikat beantragen => brauche Emailadresse für die ssl domain

1c) Zertifikat importieren in IIS:

Intermediate Zert sollte vorher in die Zwischenzertifizierungsstellen importiert werden (Zert Manager)

das File von der Zertifizierungsstelle dann im IIS unter server\serverzertifikate mit rechter Maustaset Zertifikatsanforderung abschließen importieren (*.* bei txt auswählen)
bei Problemen zunächst als Perönliches Certt abschließen, exportieren (pfx), löschen und neu importieren als Webhosting

2) Webinhalt für https freigeben

2a) Website ssl Bindung hinzufügen

Website auswählen, bei Bindungen https hinzufügen und zuvor importiertes Zertifikat auswählen, es sollten dann z.b.  eine Bindung für Port 80 und eine für 443 existieren,

2b) Firewall Port 443 freigeben

2c) Web Servicekonfigurieren

http://blog.adnanmasood.com/2008/07/16/https-with-basichttpbinding-note-to-self/

The modified basicHttpBindinging to allow security mode = Transport

<bindings>

            <basicHttpBinding>
                <binding name="defaultBasicHttpBinding">
                    <security mode="Transport">                        <transport clientCredentialType="None"/>
                    </security>
                </binding>
            </basicHttpBinding>
        </bindings>
        <services>
            <service behaviorConfiguration="MyServiceBehavior"
            name="MyServiceName">   
                <endpoint address="https://AdnanMasood.com/MyService.svc"
                            binding="basicHttpBinding"
                            bindingConfiguration="defaultBasicHttpBinding"
                            contract="Axis.IServiceContract" />
            <serviceBehaviors>           
                <behavior name="MyServiceBehavior">
                    <serviceMetadata httpsGetEnabled="true"/>
                    <serviceDebug includeExceptionDetailInFaults="false"/>
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <add key="CustomIISServiceHostEndPoint" value=https://AdnanMasood.com/MyService.svc"/>
    </appSettings>

which corresponds to your end point.

<system.serviceModel>        

and the httpsGetEnabled

<behaviors>

and last but not least, if hosting in IIS, here is the key for custom factory. Details about how to do this part can be found on the MSDN article "Deploying an Internet Information Services-Hosted WCF Service" referenced below.

    <appSettings>        

The relationship between the two objects cannot be defined because they are attached to different ObjectContext objects.

When using more then one Context, Objects have to be detached before added to another one

Adding Wcf Service Logging

you don't need to change your source code just add to your service web.config:

<system.diagnostics>
<sources>
<source name="System.ServiceModel"
                    switchValue="Information, ActivityTracing"
                    propagateActivity="true">
<listeners>
<add name="traceListener"
type="System.Diagnostics.XmlWriterTraceListener"
initializeData= "C:\Log\wcf.svclog" />
</listeners>
</source>
</sources>
</system.diagnostics>

visual studio or iis: HTTP Error 403.14 - Forbidden The Web server is configured to not list the contents of this directory.

HTTP Error 403.14 - Forbidden

The Web server is configured to not list the contents of this directory.

solution: add  <directoryBrowse enabled="true" /> for root path:

  <location path=".">
    <system.webServer>
      <directoryBrowse enabled="true" />
    </system.webServer>
  </location>

Disk / Drive / Partition Image software

Norton Ghost:
takes long to install, installer often opens command prompt and interrupts you, complicated gui, can' t read Linux partitions

DriveImageXML: can`t read Linux partitions

Acronis2016: can read Linux partitions, very comfortable gui, but I tried to use test version to make backup didn't work

Partition Image: is a dos tool, doesn't work under windows 10 (first attemp, no time for second try)

compare andronoid - windows 10 phone

I had a Samsung S3 neo and switched to Lumia 640.

summary: windows phone 10 has fewer apps than andronoid, but those apps are more stable and faster than andronoid

Writing is faster and reliabler on Windows 10 phone than on the andronoid system.

Navigation: on Lumia there is the here navigation installed, which has offline maps and speed warning, but I didn't find the traffic jam visualisation like google maps.

Energy: the Lumia Akku endures longer then the Samsung, I think windows phone 10 is more power saving

Continous Integration Build with visualstudio online: No queue was specified in the build request.

if you get error        

No queue was specified in the build request.

set

General Tab -> Default agent queue = Hosted


to set up new CI build:
Go to Build Menu of your project
create new Build definition by pressing +
General Tab -> Default agent queue = Hosted

Update Wpf StatusLabel from other thread - Status Label auf richtigem Thread updaten

        ///

updates StatusLabel on Gui Thread

        /// StatusText to set
        private void UpdateStatus(string sStatus)
        {
            Trace.WriteLine(sStatus);
            if (!LblStatus.CheckAccess()) //Check if correct Thread
            {
                LblStatus.Dispatcher.BeginInvoke(DispatcherPriority.Send, //Wrong Thread
                    new Action(UpdateStatus), sStatus); //start again on correct thread
                return; //and leave
            }
            LblStatus.Content = sStatus; //only on correct thread the label is set
        }


call like this:
1) start longrunning Task:

Task.Factory.StartNew(MyLong,TaskCreationOptions.LongRunning);


2) call Update Status from Longrunning Task
        private void MyLong()       
{
            UpdateStatus("do some Long time ...");       

}

get Backup sets of a Backup file including more than one backup

declare @backupFile varchar(max) = 'C:\SqlBackup\test.BAK';
-- THIS IS SPECIFIC TO SQL SERVER 2012 and 2014
--
declare @headers table
(
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),       
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),       
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),       
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),       
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),       
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),       
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),       
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),       
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),       
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),       
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),       
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),       
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),       
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),       
    Containment varchar(256),
    --
    -- This field added to retain order by
    --
    Seq int NOT NULL identity(1,1)
);
insert into @headers exec('restore headeronly from disk = '''+ @backupFile +'''');
select * from @headers

Vergleich Andronoid / Windows Phone 8.1

Live Tiles sind Platzsparender als Widgets, insgesamt kriegt man eine höhere App Dichte auf den Bildschirm, allerdings sind die Andronoid Icons hübscher

simple power shell sql server invoke sqlcmd test

for example a SQL express Server, using sa to connect:

invoke-sqlcmd  -Query "select * from sys.database_files;" -serverinstance localhost\SQLEXPRESS  -Username sa -Password yourPwd

initialiszing model Database of sql server to minimize fragmentation of new databases / ändern der model Datenbank des SQL Servers um fragmentierung niedriger als mit den default werten zu halten

--init Sql Server
use model;
IF (8192>(select size from sys.database_files where file_id=1)) ALTER DATABASE model MODIFY FILE (NAME = [modeldev], SIZE=64MB);
ALTER DATABASE model MODIFY FILE (NAME = [modeldev], FILEGROWTH = 10%, MAXSIZE = UNLIMITED);
IF (8192>(select size from sys.database_files where file_id=2)) ALTER DATABASE model MODIFY FILE (NAME = [modellog], SIZE=64MB);
ALTER DATABASE model MODIFY FILE (NAME = [modellog], FILEGROWTH = 64MB, MAXSIZE = UNLIMITED);

passing variables from power shell to sql server by using Invoke

power Shell script:
==============

param(
    [Parameter(Mandatory=$true)]
    [string]$PWD,
    [string]$saUser="sa",
    [string]$ServerInstance="localhost\SQLEXPRESS"
)
$winUser=([Environment]::UserDomainName)+"\"+([Environment]::UserName)
Write-Host "Windos User=" $winUser
$sqlVars = "V1 = 'testV1'", "V2 ='$winUser'"
Invoke-Sqlcmd -Query "SELECT `$(V1) AS V1, `$(V2) AS V2;" -Variable $sqlVars -serverinstance $ServerInstance  -Username $saUser -Password $PWD
invoke-sqlcmd   -InputFile "test.sql " -Variable $sqlVars -serverinstance $ServerInstance  -Username $saUser -Password $PWD


SQL Script (test.sql):
================
select $(V1) as v1, $(V2) AS V2;

rename sql server hostname / sql server umbenennen

select * from sys.servers
exec sp_dropserver 'oldname\SQLEXPRESS'
exec sp_addserver 'newname\SQLEXPRESS',local

local is important, beacause without depricated
local ist wichtig ansonst en ist add_server depricated

oracle free sysaux tablespace

select dbms_stats.get_stats_history_retention from dual;

exec dbms_stats.alter_stats_history_retention(10);

--delete old statistics

exec DBMS_STATS.PURGE_STATS(sysdate -10);

further info:

http://www.informatik-aktuell.de/betrieb/datenbanken/der-oracle-sysaux-tablespace-reorganisation-und-verkleinerung.html

SSRS Reportitems to access Textbox Values

SSRS Sql Server Reporting Services:

How to access Values of Textboxes in expressios...
Werte aus Textboxen in Expressions referenzieren

Reportitems!B5_CA204.Value

Sql Server Reporting Services SSRS: LOOKUP Simple Example - einfaches Beispiel

simple Example:
Lookup("searchstring",KeyColumn,ValueColumn,"Datasetname"):

=Lookup("Key1",Fields!DataKey.Value,Fields!DataValue.Value,"AllDataItems")


searchsring can also be expression, but


other expressions:
=IIF(Count(Fields!MA.Value)>1,"Red","No Color")

Windows 10 mehrere / more than one remote desktop

from 

http://www.mysysadmintips.com/windows/clients/545-multiple-rdp-remote-desktop-sessions-in-windows-10

Windows 10 x64 RTM (August 2015)

termsrv.dll file version 10.0.10240.16384.

In termsrv.dll find:

39 81 3C 06 00 00 0F 84 73 42 02 00

and replace it with:

B8 00 01 00 00 89 81 38 06 00 00 90

Patched version can be downloaded from here. Original, untouched version of termsrv.dll v10.0.10240.16384 can be downloaded from here.

Windows 10 x64 Threshold 2 (November 2015)

Windows 10 Fall Update (also called "Threshold Wave 2 Update") updates termsrv.dll to version 10.0.10586.0. To get back concurrent remote desktop connections, make following changes:

Find:

39 81 3C 06 00 00 0F 84 3F 42 02 00

replace with:

B8 00 01 00 00 89 81 38 06 00 00 90

Patched version can be download from here. Original, v10.0.10586.0 file is here.

Sql Server VERY SIMPLE PIVOT EXAMPLE - sehr einfaches PIVOT Beispiel

CREATE TABLE #p
( Name varchar(10),
ErfüllungsgradIst INTEGER,
ErfüllungsgradSoll INTEGER,
Maschine varchar(20) )
GO
INSERT INTO #p VALUES ('Maier', 3, 4, 'Drehbank')
INSERT INTO #p VALUES ('Maier', 2, 3, 'Fräsmaschine')
INSERT INTO #p VALUES ('Maier', 4, 4, 'Bohrmaschine')
INSERT INTO #p VALUES ('Huber', 1, 2, 'Drehbank')
INSERT INTO #p VALUES ('Huber', 2, 3, 'Fräsmaschine')
   
select *
from #p
     
-----------------------------------------

select * from #p
PIVOT
( MAX(ErfüllungsgradIst)
FOR Name IN (Maier,huber)
) as pivotTable

typed Dataset and Joins

if you fill a typed Dataset with a Query containing more then the base table, there might be some troubles with using the generated table adapters (like "cannot update identity column" when not trying to update the id ...)

instead use a normal dataadapter

simple telerik GridViewComboBoxColumn example

if you don't set DataMemberBinding the commbobox will not Display anything !!!!!



GridViewComboBoxColumn cb = new GridViewComboBoxColumn();
cb.Header = "Combo";
cb.ItemsSource = new String[] { "Mobile", "Business", "Fax" };
cb.DataMemberBinding=new Binding("Value");
rgv.Columns.Add(cb);

you have to click one or two times in the column to get the Combobox (it lokks like a ordinary column)

if the valuelist of the combocox Itemsource doesn't match the actual value, nothing is displayed (till you click on it)

oracle shrink tabelspaces

oracle shrink tabelspaces
http://www.informatik-aktuell.de/betrieb/datenbanken/der-oracle-sysaux-tablespace-reorganisation-und-verkleinerung.html

Oracle Table Size

SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',
'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
--AND TABLESPACE_NAME LIKE 'COSTE%'
--AND SEGMENT_NAME LIKE 'P2010201%'
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'TARGET_POC'
--AND ROUND(bytes/(1024*1024),2) > 1000
ORDER BY bytes DESC;

power shell xmlNode Type prüfen

write-host (-Not ($parentNode.NodeType -eq "Comment"))

EF Code First mapping non public properties

in the Contextclass add:

protected override void OnModelCreating(DbModelBuilder modelBuilder)



{

base.OnModelCreating(modelBuilder);

modelBuilder.Conventions.Add(new NonPublicColumnAttributeConvention());



}
and this is the convention




///

Convention to support binding private or protected properties to EF columns.


///

public sealed class NonPublicColumnAttributeConvention : Convention






{

public NonPublicColumnAttributeConvention()



{

Types().Having(NonPublicProperties)

.Configure((config, properties) =>



{

foreach (PropertyInfo prop in properties)



{

config.Property(prop);



}

});

}

private IEnumerable<PropertyInfo> NonPublicProperties(Type type)



{

var matchingProperties = type.GetProperties(BindingFlags.SetProperty | BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance)

.Where(propInfo => propInfo.GetCustomAttributes(typeof(ColumnAttribute), true).Length > 0)

.ToArray();

return matchingProperties.Length == 0 ? null : matchingProperties;



}

}

Sql Server PIVOT Example

very good example from http://www.insidesql.org/blogs/cmu/sql_server/pivot-mit-2-wertefeldern

Set Nocount on
go
CREATE TABLE #p
( Name varchar(10),
ErfüllungsgradIst INTEGER,
ErfüllungsgradSoll INTEGER,
Maschine varchar(20) )
GO
INSERT INTO #p VALUES ('Maier', 3, 4, 'Drehbank')
INSERT INTO #p VALUES ('Maier', 2, 3, 'Fräsmaschine')
INSERT INTO #p VALUES ('Maier', 4, 4, 'Bohrmaschine')
INSERT INTO #p VALUES ('Huber', 1, 2, 'Drehbank')
INSERT INTO #p VALUES ('Huber', 2, 3, 'Fräsmaschine')
    
select *
from #p
      
Select Name, 'ErfüllungsgradSoll' as Erfüllungsgrad, coalesce(Drehbank, 0) as Drehbank,
coalesce(Fräsmaschine, 0) as Fräsmaschine, coalesce(Bohrmaschine, 0) as Bohrmaschine
from
(Select Name, ErfüllungsgradSoll , Maschine from #p) as SourceTable
PIVOT
(
min(ErfüllungsgradSoll)
FOR Maschine IN ( [Drehbank],[Fräsmaschine], [Bohrmaschine])
) as PivotTable
Union ALL
Select Name, 'ErfüllungsgradIst', coalesce(Drehbank,0), coalesce(Fräsmaschine,0), coalesce(Bohrmaschine, 0)
from
(Select Name, ErfüllungsgradIst, Maschine from #p) as SourceTable
PIVOT
(
min(ErfüllungsgradIst)
FOR Maschine IN ( [Drehbank],[Fräsmaschine], [Bohrmaschine])
) as PivotTable
Order By Name, Erfüllungsgrad
     

GO
drop Table #p

LINQ Query / Method Syntax Examples

Entity Framework

for Entity Framework: Load referenced Object first, then query Attribute of Object:
parent.Childs.Select(o=>o.ChildAttribute).FirstOrDefault(x => x.Name == name);

LINQ Query / Method Syntax Examples

Customer[] customers = Service.GetCustomers();
var query = from customer in customers
where customer.Name == "Hans"
from order in customer.Orders
where order.Quantity > 6
select new {order.OrderID, order.ProductID};

Customer[] customers = Service.GetCustomers();
var query = customers
.Where(c => c.Name == "Hans")
.SelectMany(c => c.Orders)
.Where(order => order.Quantity > 6)
.Select(order => new {order.OrderID, order.ProductID});

Entity Framework (EF) Code First Migrations: Seeding

you can use SQL(...) in Up/Down Methods, or Configuration.Seed or check if already exists

Entity Framework (EF) Code First Migration Update

If you want to update an existing database to current Version you have to set the EF Database Initializer:
System.Data.Entity.Database.SetInitializer(new MigrateDatabaseToLatestVersionContext, Migrations.Configuration>());

when you first Access the db, EF checks the current Version and updates it if necesarry. You Need a parameterless contructor in your Context Class. Ist called several times when updating the DB. And you have to call the base constructor (of DBContext) and pass the correct connectionstringname if you want the correct database beiing updated, else the Default database is being updated (db Name=Namespace.classname of ypur EF Context)