windows mobile 10 handy neuer account

Einstellungen -> System - info handy zurücksetzten

ef migrate to specific version per code c#

add this method to your context:

        /// <summary>migrates actual Database to given Migration Version
        /// </summary>
        /// <param name="toMigration">"0" ... Version 0 (remove all), "" is newest Version</param>
        public void Migrate(string toMigration)
        {
            var configuration = new Configuration
            {
                TargetDatabase = new DbConnectionInfo(Database.Connection.ConnectionString, "System.Data.SqlClient")
            };

            var migrator = new DbMigrator(configuration);
            migrator.Update(toMigration);
        }

TSQL BACKUP OPTIONS

                //COPY_ONLY ... doesn't interrupt TransactionLogBackupCycle
                //STATS ... percent after status is updated
                //NOINIT+SKIP: doesnt't delete old Backups in same file
                //TAPE OPTIONS:
                //NOUNLOAD/NOREWIND: doesn't unload / rewind the tape after backup
                //NOFORMAT (=DEFAULT): doesn't delete old Backups in same file

regex Visual Studio search and replace

surround with (), display capture with $1, $2, $n

eg.:

search:  CreateColumnDefinition\(\"(\w*)\",
replace: CreateColumnDefinition("$1", 1,

[ab] only ab
[^ab] everything but ab

 

 

regex Visual Studio search and replace

surround with (), display capture with $1, $2, $n

eg.:

search:  CreateColumnDefinition\(\"(\w*)\",
replace: CreateColumnDefinition("$1", 1,

[ab] only ab
[^ab] everything but ab

Winform jump back to gui thread with invoke

                PLog.LogInfo($"Thread:{Thread.CurrentThread.ManagedThreadId} Gui");
                Task t = new Task(() =>
                {
                    PLog.LogInfo($"Thread:{Thread.CurrentThread.ManagedThreadId} Background");
                    tableAdapter.FillByKundeId(dtBackground, _kundenId);
                    var delegateCallBack = new Action(LoadDataCallBack);
                    this.Invoke(delegateCallBack); //start Callback on Gui Thread
                });
                t.Start();
            }
        }

        private void LoadDataCallBack()
        {
            PLog.LogInfo($"Thread:{Thread.CurrentThread.ManagedThreadId} Callback");
           
        }

c# background thread / hintergrund thread

before .Net 4.0:

            // init backgroundworker
            _worker = new BackgroundWorker();
            _worker.DoWork += new DoWorkEventHandler(_worker_DoWork);
            _worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_worker_RunWorkerCompleted);

in .Net 4.0 TPL:

Task Paralell Library nützt Mehrkern Prozessoren besser.

wichtigste Klassen: Task<TResult> und Paralell

            Task copyMedia = new Task(CopyMedia,TaskCreationOptions.LongRunning);

            copyMedia.Start();

task returning int:
Task<int> t = new Task<int>(() => { return tableAdapter.FillByKundeId(datatable, _kundenId); });

.Net 4.5

IAsyncResult und AsyncCallback ersetzt durch keywords async und await

public Form1()
{
    InitializeComponent();

    StartAsync();
}

private async void StartAsync()
{
    // do some work
    await Task.Run(() => { Thread.Sleep(1000); });

    // start more work
    var moreWork = Task.Run(() => { Thread.Sleep(1000); });

    // update the UI, based on data from “some work”
    textBox1.Text = "From async method";

    // wait until “more work” finishes
    await moreWork;
}

WCF Service Host without app.config (configuration by code) example

using System;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Description;


namespace wcf3
{
    class Program
    {
        static void Main(string[] args)
        {

            ServiceHost serviceHost = new ServiceHost(typeof(TestService), new Uri("http://localhost:80/Test3"));

            // Create Meta Behavior
            ServiceMetadataBehavior behavior = new ServiceMetadataBehavior();
            behavior.HttpGetEnabled = true;

            serviceHost.Description.Behaviors.Add(behavior);

            Binding mexBinding = MetadataExchangeBindings.CreateMexHttpBinding();

            serviceHost.AddServiceEndpoint(typeof(IMetadataExchange), mexBinding, "mex");

            WSHttpBinding httpBinding = new WSHttpBinding(SecurityMode.None);

            serviceHost.AddServiceEndpoint(typeof(ITestService), httpBinding, "rest");

            serviceHost.Open();
            Console.WriteLine("TestService is now running. at: " + serviceHost.BaseAddresses.First());
            Console.WriteLine("Press any key to stop it ...");
            Console.ReadKey();
            serviceHost.Close();
        }
    }

    [ServiceContract]
    public interface ITestService
    {
        [OperationContract]
        string Test(string input);
    }

    public class TestService : ITestService
    {

        public string Test(string input)
        {
            return input + DateTime.Now.ToString();
        }
    }



}

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\WcfTestClient.exe

WcfTestClient.exe 
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE

System.ServiceModel.AddressAccessDeniedException

netsh http add urlacl url=http://+:8080/MyUri user=DOMAIN\user

WCF Client without config file

Example 1: NetTcp

var endPoint = new EndpointAddress("net.tcp://localhost:8080/MyService");
var binding = new NetTcpBinding();
var sbNewClient = new SbNew.ShiftbookServiceClient(binding, endPoint);

Example2: custom http

            CustomBinding cb= new CustomBinding();
            SecurityBindingElement sbe = SecurityBindingElement.CreateUserNameOverTransportBindingElement();
            sbe.MessageSecurityVersion = MessageSecurityVersion.WSSecurity11WSTrustFebruary2005WSSecureConversationFebruary2005WSSecurityPolicy11;
            sbe.SecurityHeaderLayout = SecurityHeaderLayout.Strict;
            sbe.IncludeTimestamp = false;
            sbe.SetKeyDerivation(true);
            sbe.KeyEntropyMode = System.ServiceModel.Security.SecurityKeyEntropyMode.ServerEntropy;
            cb.Elements.Add(sbe);
            cb.Elements.Add(new TextMessageEncodingBindingElement(MessageVersion.Soap11, System.Text.Encoding.UTF8));
            cb.Elements.Add(new HttpsTransportBindingElement());
            EndpointAddress endPoint = new EndpointAddress("net.tcp://localhost:8080/MyService");

            var sbNewClient = new Service1.ServiceClient(cb,endPoint);

WCF BASICS 2

1) ServiceContract & Implementation

you need a contract  (=Interface with     [ServiceContract] Attribute) like

    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        string GetData(int value);

which contains Methods attributed with [OperationContract]

implement the [ServiceContract] Interface:

    public class Service1 : IService1
    {
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

assume its in namespace WcfServiceLib for hosting below

2) Host the ServiceContract

2a) with IIS

you need a .svc file with one single line:

<%@ServiceHost Service="WcfServiceLib.Service1"%>

and configure the service (provide metadata) within the web.config:

    <system.serviceModel>

      <behaviors>

        <serviceBehaviors>

          <behavior name="MyServiceTypeBehaviors">

            <serviceMetadata httpGetEnabled="true" />

            <serviceDebug includeExceptionDetailInFaults="false" />

          </behavior>

        </serviceBehaviors>

      </behaviors>

        <service name="WcfServiceLib.Service1"      behaviorConfiguration="MyServiceTypeBehaviors">

          <endpoint address="" binding="wsHttpBinding"

               contract="WcfServiceLib.IService1"/>

          <endpoint contract="IMetadataExchange"

             binding="mexHttpBinding" address="mex"/>

        </service>

      </services>

    </system.serviceModel>

2b) With .net app:

        static void Main(string[] args)

        {

            Type serviceType = typeof(MyWCFServices.HelloWorldService);

            string httpBaseAddress = ConfigurationManager.AppSettings["HTTPBaseAddress"];

            Uri[] baseAddress = new Uri[] { new Uri(httpBaseAddress) };

            ServiceHost host = new ServiceHost(serviceType, baseAddress);

            host.Open();

            Console.WriteLine("HelloWorldService is now running. at: " +host.BaseAddresses.First());

            Console.WriteLine("Press any key to stop it ...");

            Console.ReadKey();

            host.Close();

        }

Visual Studio öffnet Fenster in falscher Tab Gruppe / opens window in wrong Tab Group

Menü / Window / Reset Window Layout

Internals in other assembly

add to AssemblyInfo.cs:
[assembly: InternalsVisibleTo("OtherAssemblyName")]

Sql Server Filetable and Entity Framework

CREATE PROCEDURE SFilesDelete (@fId uniqueidentifier)
AS
BEGIN
SET NOCOUNT ON;
DELETE from SFiles where stream_id = @fId;  
END
GO

ALTER PROCEDURE [dbo].[SFilesInsert]
@fname nvarchar(255)
,@fData varbinary(max)
AS
BEGIN

Declare @fid uniqueidentifier = NEWID();
    INSERT INTO SFiles (stream_id, file_stream, name) VALUES (@fId ,@fdata,@fname);
    SELECT stream_id, file_stream.PathName() as unc_path FROM SFiles where stream_id = @fId
END

http://www.eidias.com/blog/2013/12/4/using-sql-filetable-in-entity-framework

Entity Framework Code First Alter Database for FileTable

if (not exists (select * from sys.filegroups where name ='FStream'))
begin
declare @FilePathAndName nvarchar(4000)= (select physical_name FROM sys.master_files where database_id=db_ID() and file_id=1)
set @FilePathAndName= REPLACE(@FilePathAndName,'.mdf','_FileStream.fs')
--print @FilePathAndName
declare @sqlStr as nvarchar(max) ='alter database ' + db_name() +' ADD FILEGROUP FStream CONTAINS FILESTREAM'+CHAR(13)
set @sqlStr = @sqlStr  + 'ALTER DATABASE ' + db_name() +' ADD FILE ( NAME = N''FStream'', FILENAME = N'''+@FilePathAndName +''' ) TO FILEGROUP fStream'+CHAR(13)
set @sqlStr = @sqlStr  + 'ALTER DATABASE ' + db_name() +' SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'''+db_name()+'FilesDir'')'+CHAR(13)
set @sqlStr = @sqlStr + 'CREATE TABLE dbo.MyFiles AS FILETABLE WITH ( FILETABLE_DIRECTORY = ''MyFileDir'')'+CHAR(13)
print @sqlStr
exec (@SqlStr)
end

Sql Server Filetable (since 2012)

1.) Filestream TSQL

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

ALTER DATABASE testFileTable SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FilesDir')

alter database testFileTable  ADD FILEGROUP fsg CONTAINS FILESTREAM;
ALTER DATABASE testFileTable  ADD FILE ( NAME = N'fsf', FILENAME = N'D:\sqlData\filesteamtest\' ) TO FILEGROUP fsg;
ALTER DATABASE testFileTable  SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT;

C) Prepare TABLE

CREATE TABLE dbo.MyFiles AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'MyFilesDir',
FILETABLE_COLLATE_FILENAME = database_default
)

D) Write Files

just drag them into the filetabledir - get UNC Path of it:

SELECT FileTableRootPath('TestFileTable') AS FileTableRootPath

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].StpFile ([name],[file_stream]) SELECT 'test1.txt', CONVERT(VARBINARY(MAX),'TestFileText') AS FileData

create Dir:
INSERT INTO FileTableTb (name, is_directory)   VALUES ('testFolder', 1)

GO

create file in subdir: have to get pathlocator first:

select GetPathLocator(CONCAT(FileTableRootPath('FileTableTb'), '\testFolder')).GetDescendant(Null,NULL);

INSERT INTO [dbo].FileTableTb  ([name],[file_stream],path_locator) SELECT 'test1.txt', CONVERT(VARBINARY(MAX),'TestFileText'), GetPathLocator(CONCAT(FileTableRootPath('FileTableTb'), '\testFolder')).GetDescendant(Null,NULL) AS FileData

Parent_Path_Locator column is readonly !

E) Read Files

with Explorer - get UNC Path of filetable:
SELECT FileTableRootPath('TestFileTable') AS FileTableRootPath

physical path of filetable isn't available, this UNC Path is given by a virtual device
The Filetable stores the files like filestream in a guid structure but offers a filesystemdriver, which respresents those files in a more readable form under a UNC Path, which is available from:
SELECT FileTableRootPath('TestFileTable') AS FileTableRootPath

select stream_id, name, convert(varchar(max), file_stream) as textContent, file_stream.GetFileNamespacePath() as RelPath,
FileTableRootPath('TestFileTable')+file_stream.GetFileNamespacePath() as AbsPath from TestFileTable

Sql Server Filestream using C#

1) WRITE

            using (TransactionScope transactionScope = new TransactionScope())
            {
             
                //1. create Record with Empty Blob (File)
                SqlConnection sqlConnection1 = new SqlConnection(conStr);
                sqlConnection1.Open();

                SqlCommand sqlCommand1 = sqlConnection1.CreateCommand();
                sqlCommand1.CommandText = @"DECLARE @Out TABLE (ID uniqueidentifier)
                                            Insert Into Blob2(Blob) output inserted.ID into @Out values(Cast('' As varbinary(Max)))
                                            Select BLOB.PathName() As Path From BLOB2 Where Id =(SELECT id FROM  @Out)";
                string filePath1 = (string) sqlCommand1.ExecuteScalar();

                //2. Get SqlFileStream to empty File createt in Step 1
                SqlConnection sqlConnection2 = new SqlConnection(conStr);
                sqlConnection2.Open();

                SqlCommand sqlCommand2 = sqlConnection2.CreateCommand();
                sqlCommand2.CommandText = "Select GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext ";
                byte[] transactionContext1 = (byte[]) sqlCommand2.ExecuteScalar();

                //3. Write Data into Stream and Close
                SqlFileStream sqlFileStream1 = new SqlFileStream(filePath1, transactionContext1, FileAccess.Write);
                byte[] fileData = Encoding.ASCII.GetBytes(nameof(AdoFileStreamTest)+" "+DateTime.Now);
                sqlFileStream1.Write(fileData, 0, fileData.Length);
                sqlFileStream1.Close();
                transactionScope.Complete();
            }

2) READ

            using (TransactionScope transactionScope2 = new TransactionScope())

            {

                SqlConnection sqlConnection3 = new SqlConnection(conStr);

                sqlConnection3.Open();

                //1. Get Path & Transaction Scope

                SqlCommand sqlCommand3 = sqlConnection3.CreateCommand();

                sqlCommand3.CommandText = @"Select Top 1 Blob.PathName() As Path,

                                            GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext

                                            From Blob2 Order by Created desc";

                

                SqlDataReader reader = sqlCommand3.ExecuteReader();

                reader.Read();

                string filePath = (string)reader["Path"];

                byte[] transactionContext2 = (byte[])reader["TransactionContext"];

                //2. read file

                SqlFileStream sqlFileStream2 = new SqlFileStream(filePath, transactionContext2, FileAccess.Read);

                byte[] data = new byte[sqlFileStream2.Length];

                sqlFileStream2.Read(data, 0, Convert.ToInt16(sqlFileStream2.Length));

                res = Encoding.ASCII.GetString(data);

                sqlFileStream2.Close();

            }

3) maybe need to config Distributet Transaction Manager:

https://lennilobel.wordpress.com/2011/08/22/using-sqlfilestream-in-c-to-access-sql-server-filestream-data/

down voteaccepted

To enable MSDTC on the business management server that is running on Windows Server 2008 click Start, Run, type dcomcnfg and then click OK to open Component Services. In the console tree, click to expand Component Services, click to expand Computers, click to expand My Computer, and click to expand Distributed Transaction Coordinator. Right click Local DTC, and click Properties to display the Local DTC Properties dialog box. Switch to the Security tab. In the Security Settings section, click Network DTC Access. In the Client and Administration section, select Allow Remote Clients and Allow Remote Administration. In the Transaction Manager Communication section, select Allow Inbound and Allow Outbound. In the Transaction Manager Communication section, select Mutual Authentication Required (if all remote machines are running Windows Server 2003 SP1 or Windows XP SP2 or higher), select Incoming Caller Authentication Required (if running MSDTC in a cluster), or select No Authentication Required if some of the remote machines are pre-Windows Server 2003 SP1 or pre-Windows XP SP2. No Authentication Required is the recommended selection. Select Enable XA Transactions, and then click OK.

Entity Framework no InsertFunction element exists

because it has a DefiningQuery and no InsertFunction element exists in the ModificationFunctionMapping element

most likly: No primary key defined => EF treats Table as view and generates read only code

I had to remove the DefiningQuery Element in the .edmx File and also I removed the store: 

like described here:

http://stackoverflow.com/questions/15322894/because-it-has-a-definingquery-and-no-insertfunction-element-exists-in-the-mo

Ef Entity Framework and Filestream

        public void WriteAndReadFileTest()

        {

            var _ctx = new testFileStreamEntities();

            //Write

            var sw = Stopwatch.StartNew();

            string writeString = "Hallo wie gehts ? "+DateTime.Now;

            var writeBlobRec = new BLOB2();

            writeBlobRec.BLOB = Encoding.ASCII.GetBytes(writeString);

            writeBlobRec.ID = Guid.NewGuid();

            _ctx.BLOB2.Add(writeBlobRec);

            _ctx.SaveChanges();

            sw.Stop();

            Debug.WriteLine($"Writing took {sw.ElapsedMilliseconds}ms");

            //Read

            sw.Restart();

            var blobRec = _ctx.BLOB2.FirstOrDefault(x => x.ID==writeBlobRec.ID);

            string readString = Encoding.ASCII.GetString(blobRec.BLOB);

            sw.Stop();

            Debug.WriteLine($"reading took {sw.ElapsedMilliseconds}ms for {readString}");

        }

Mehr als eine RemoteDesktop Sitzung unter Windows 10 / more than one Remote Desktop (RDP) session on Windows 10

http://woshub.com/how-to-allow-multiple-rdp-sessions-in-windows-10/

buildnr = ver sion = Name Windows 10 Versionen ( Type ver in commandshell to get buildnr)
10.0.10240 = 1507 = first Windows 10 Version (Threshold1)
10.0.10586 = 1511 = November Update
10.0.14393 = 1607 = Aniversary Update

get all resources of an assembly

Assembly a = Assembly.GetExecutingAssembly();

string[] allManifestResourceNamess = a.GetManifestResourceNames();



foreach (string resourceName in allManifestResourceNamess)


{
Trace.WriteLine(resourceName);

if (resourceName.EndsWith(".sql")) //SQL script Files


{
using (TextReader tr = new StreamReader(a.GetManifestResourceStream(resourceName)))


{
string s = tr.ReadToEnd();


}
}
else if (resourceName.EndsWith(".resources")) //real resources with key Value pairs


{
using (ResourceReader reader = new ResourceReader(a.GetManifestResourceStream(resourceName)))


{
IDictionaryEnumerator dict = reader.GetEnumerator();
while (dict.MoveNext())


{
string key = dict.Key as string;
object val = dict.Value;

}
}
string manifest = resourceName.Replace(".resources", string.Empty);
ResourceManager rm = new ResourceManager(manifest, a);



ResourceSet resourceSet = rm.GetResourceSet(CultureInfo.CurrentUICulture, true, true);
foreach (DictionaryEntry entry in resourceSet)


{
string resourceKey = entry.Key.ToString();
object resource = entry.Value;


}
}
}

webserver iis install webdeploy

IIS Server

start net start wmsvc

download installer:
https://technet.microsoft.com/en-us/library/dd569059(v=ws.10).aspx

then search webDeploy 3.6 (right upper Corner search field)



net start wmsvc & net start msdepsvc
the Web Managment Service must be started, Port 8172 opened
check with telnet

check with browser:
https://52.174.50.95:8172/msDeploy.axd




To diagnose installation problems, Web Deploy MSI logs are placed under %programfiles%\IIS\Microsoft Web Deploy v3

https://www.iis.net/learn/publish/troubleshooting-web-deploy/troubleshooting-common-problems-with-web-deploy

add USer to IIS Managers !!! and on site level to iis Permissions

if username is wrong or ha sno rights, it could be that error 404 (not found) instead of 401 (no rights) is returned

Visual Studio 2015

Server: Ip Adress or name of server, no http etc

SiteName: Default Web Site/test

Username: localhost\admin

pwd...

DestUrl: http://serverNameOrIp/test

test app has to exist on the server

IIS Webserver Anwendungs/Application Pool Timeout

wenn die Website beim ersten Aufruf lange braucht um geladen zu werden, so kann man die AppPool/erweiterte Eigenschaften / Leerlauftimeout erhöhen, z.b 1440 Minuten = 24h (60*24),
schienbar sind 1700min (28,3h) max

if webapp Needs Long to start,, you can increase appPool/ext.properties/idleTimeout eg. 1440 minutes = 24 hours. it seems that 1700 (=28,3h) is maximum

how to install windows 10 mobile aniversary update version 1607

You can find your Version Number in:
Settings/System/info

1507 orig. Windows 10
1511 November Update
1607 Aniversary Update

1) download Windows 10 upgrade advisor (Aktualisierungsratgeber für Windows 10)

c# .net Action / Func

Action ist ein delegate für eine Methode ohne Rückgabewert. Hier mit Expliziter (statt anaonymer) Methode:

Action<IBeforeChangeEntry<EntityBase>> updateAction = new Action<IBeforeChangeEntry<EntityBase>>(Trigger_Updating);
Triggers<EntityBase>.Updating += updateAction;

        private static void Trigger_Updating(IBeforeChangeEntry<EntityBase> obj)
        {
            obj.Entity.LastChanged = DateTime.Now;
            obj.Entity.Version += 1;
        }


Anonym:
            Triggers<EntityBase>.Updating += x =>
            {
                x.Entity.LastChanged = DateTime.Now;
                x.Entity.Version += 1;
            };

Visual Studio 2015 ASP.NET App Authentication

In VS2015 create new webform project, then a requester ask kind of Authentication and Application Type

Add a Foder LoggedInContent
add there a Web Config like:
<?xml version="1.0"?>
<configuration>
    <system.web>
        <authorization>
            <deny users="?"/>
        </authorization>
    </system.web>
</configuration>

to protect all files in this folder - only authenticated users can access them

add Master Page Forms too the folder

add links from Default Page (available for everyone) to above added MasterPageSitesin LogedInContent

ef framework code first migrations with 2 DbContexts

enable-migrations -ContextTypeName EFTest.Model2 -MigrationsDirectory: Mig2

Add-migration -configuration EFTest.Mig2.Configuration Mig20001

update-database -configuration EFTest.Mig2.Configuration –Verbose

http://www.codeproject.com/Tips/801628/Code-First-Migration-in-Multiple-DbContext

only one context:
enable-migrations
Add-migration Mig0001
Update-Database

scripting triggers of sql server datatbase

right click on db, Tasks, generate scripts, next, advanced, table/view Options: Script Triggers = true

EF Update in Context existing Entities

there is a entity e1 in dbContext1, already loaded from database and its sent by server over WCF, changed byclient, sent back over WCF to server, its' not the same entity e1, its another object e2.
If you say Save to Context nothing is saved.
dbContext1 is the Ef Cache and should always contain the newest state of all objects


1) create a new context, attach the e2 and save it to database, update the dbContext 1 from database
2) update e1 with values (and references9 from e2, save to database with DbContext1
3) Detach e1 (set State to Detached)  and Attach e2, set e2. EnitytState to modified

visual studio 2015 performance analyse

Debug / Start Deiagnostic Tools Without Debugging
Choose CPU Usage

Look at Call with biggest Total CPU % and  TotalCPU (ms)

Sql Server Statistics for query time and so on - für Messung der Dauer einer Abfrage

set statistics io on;
SET STATISTICS TIME on;

log4view example / beispiels pattern

^%date{yyyy-MM-dd HH:mm:ss.ffff} |%level |%logger |%m |%exception |

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)