sql server display all user columns with nvarchar(Max) or normal DateTime

Entity framework (EF) Code First default uses nvarchar(max) for string - thats a performance overhead.


check all columns with nvarchar(max):

select t.name as tablename,c.name as columnname,c.max_length from sys.columns c join sys.tables t on c.object_id=t.object_id Where c.max_length=-1


check all columns with normal datetime (EF better use datetime2):

select t.name as tablename,c.name as columnname,c.max_length from sys.columns c join sys.tables t on c.object_id=t.object_id where system_type_id=61

Visual Studio Einstellungen / Preferences

Tools – Options – Projects and Solutions – Track Active Item in Solution Explorer

Entity Framework Code First Relations one Foreign Key to manyTables

the only possibility to use one foreign key column in Table N for more then one Oneside Table in Code first  is to make a base class (OneSidebase) for the 1:n relation and derive from this class OneDiseBase for all tables (OneSide1 ...m) that Need a 1:n relation to Table N

SqlSkills

source of good tipps:
https://www.sqlskills.com/

powershell files and directory

#is there any *.Bak file ?

if (Get-ChildItem C:\sqlBackup\Stp1\*.Bak) {"exists"} else {"no"}

#create Dir if not exists and don't Display error if exists:
md mydir -Force

sql server: reorg / rebuild fragmented indizies (blows up transaction log)

--0) tables with no primary key - create key first to reorg / rebuild indezis

select s.name, o.name, I.Name

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where I.name is null order by s.name,o.name







--1) reogranize indexes

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Reorganize;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc







--2) rebuild indezies where reorg hasn't succeeded

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Rebuild;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc

power shell: youngest entry in dir

Get-ChildItem | Sort-Object -Property @{Expression={$_.LastWriteTime};Ascending=$false} | Select-Object -First 1

sql server and power shell

#execute tsql script using actual Windows user:


invoke-sqlcmd  -serverinstance "localhost\SQLEXPRESS" -Query "select * from sys.database_files"


invoke-sqlcmd -inputfile "Backup.sql" -serverinstance "localhost\SQLEXPRESS" # -database "mydatabase"




#backup database - since sqlserver 2012 (2008 not working!)
Backup-SqlDatabase -ServerInstance "localhost\SQLEXPRESS" -Database "mydb" -BackupFile "c:\sqlBackup\mydb.Bak"


sometimes ist necesarry to Import sqlSnapIns:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

normale Shell: sqlcmd -S localhost\SQLEXPRESS

EF Code First Migrations Basics

EF (Entity Framework) Migrations are a way to generate versioned Database Scripts from c# Classes. Developpers can specify which Changes are sumerized to a new Version. EF Migrations are enabled by Package Manager Console Command Enable-Migrations. It creates a Migration Folder and a Configuration Class in your Visual Studio Project.
When all changes for a new Version are made to the Entity c# Classes, the Package Manager Console Command "Add Migration (Migrationname)" creates a c# Class deriving from DbMigrations with a Up and a Down Method, which executes the necesarry SQL for Up/Downgrading the DB. The DB itself is upgraded either by the Package Manager Console Command Update-Database (which also lets you create script by -script Parameter) or by using Database Initializer, which are executed when the DBContext first Needs the Database:
//upgrades Production Datebase, maybe first make a backup
Database.SetInitializer(new MigrateDatabaseToLatestVersion());
//creates new Db
Database.SetInitializer(new CreateDatabaseIfNotExists());

So the Software itself can automatically create or upgrade the needed Database.
Summary of Commands and Options: https://coding.abel.nu/2012/03/ef-migrations-command-reference/ or in the Package Manager Console: get-help Add-Migration

most important Package Manager Console Commands:

1. Enable-Migrations
2. Add Migration (Migrationname)
3. Update-Database

Examples:

getting all migrations which have been deployed to a database so far:
get-migrations

adding a migration after changing EF-Relevant Code:
add-migration Mig0025

update Database to a specific Migration
Update-Database -TargetMigration Mig0005 -Verbose -connectionStringName Db1

Add Custom SQL to Migration

use the SQL Methode in the Up / Down Methode:

EF Code First Migrations Basics

EF (Entity Framework) Migrations are a way to generate versioned Database Scripts from c# Classes. Developpers can specify which Changes are sumerized to a new Version. EF Migrations are enabled by Package Manager Console Command Enable-Migrations. It creates a Migration Folder and a Configuration Class in your Visual Studio Project.
When all changes for a new Version are made to the Entity c# Classes, the Package Manager Console Command "Add Migration (Migrationname)" creates a c# Class deriving from DbMigrations with a Up and a Down Method, which executes the necesarry SQL for Up/Downgrading the DB. The DB itself is upgraded either by the Package Manager Console Command Update-Database (which also lets you create script by -script Parameter) or by using Database Initializer, which are executed when the DBContext first Needs the Database:
//upgrades Production Datebase, maybe first make a backup
Database.SetInitializer(new MigrateDatabaseToLatestVersion());
//creates new Db
Database.SetInitializer(new CreateDatabaseIfNotExists());

So the Software itself can automatically create or upgrade the needed Database.
Summary of Commands and Options: https://coding.abel.nu/2012/03/ef-migrations-command-reference/ or in the Package Manager Console: get-help Add-Migration

most important Package Manager Console Commands:

1. Enable-Migrations
2. Add Migration (Migrationname)
3. Update-Database

Examples:

getting all migrations which have been deployed to a database so far:
get-migrations

adding a migration after changing EF-Relevant Code:
add-migration Mig0025

update Database to a specific Migration
Update-Database -TargetMigration Mig0005 -Verbose -connectionStringName Db1

Add Custom SQL to Migration

use the SQL Methode in the Up / Down Methode:

Entity Framework Basics

Entity Framework is a open source OR Mapper from Microsoft. It is installed via NuGet Package Manager to a Visual Studio Project.
There are 3 main ways to use it:
1.Database first: By Adding a new Entity Framework Model you can choose "from existing Database". A Wizard let you select the objects you want to generate Entity Model and Entity Classes for.
2.Model first: By Adding a new Entity Framework Model you can choose "Empty Model". then you can add Entities in the Model Designer - Entity c# Classes are generated when you build the Project, you can deploy it to a database by choosing "Update Database" from the Context Menu in the Model Designer.
3.Code first: You write c# classes and add them as DbSet to a Entity Context Class (derives from System.Data.Entity.DbContext). Then the DBContext creates a Database with Tables for every Class in a DbSet Property and for all related classes. It also creates Relations.

simple wpf entity framework databinding to ms DataGridView and Telerik RadGridView

main Xaml Window: Codebehind: namespace WpfApplication1 { ///

/// Interaction logic for MainWindow.xaml ///

public partial class MainWindow : Window { StpDbEntities1 _stpDbEnt = new StpDbEntities1(); public MainWindow() { InitializeComponent(); } private void Window_Loaded(object sender, RoutedEventArgs e) { MyDataGrid.ItemsSource = _stpDbEnt.tMachine.Local; MyRadGridView.ItemsSource = _stpDbEnt.tMachine.Local; _stpDbEnt.tMachine.Load(); //Window1 w1=new Window1(); //w1.Show(); //Show first Name - not necesarry var machinename = _stpDbEnt.tMachine.First().Name; if (machinename == null) throw new ArgumentNullException("machinename"); MessageBox.Show(machinename); } private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e) { _stpDbEnt.SaveChanges(); } } }

power shell for loop

for ($i=1; $i -le 10; $i++) { Write-Host "Loop:" $i }

Creating some sql server Databases per Tsql script

declare @counter int = 1 declare @sql nvarchar(50) while @counter < 100 begin set @counter = @counter + 1 set @sql = 'Create Database T'+ convert(nvarchar(3),@counter) print ' sql=' + @sql exec (@sql) end