보미네

-- recursive cte example

if object_id ('emp') is not null
drop table emp 
go

create table emp
(empId int
, empName nvarchar(100) 
, managerId int
) 
go

insert into emp (empId, empName, managerId)
select empId, empName, managerId 
from 
	( 
	  values 
	  (1 , 'name1', 3 )    -- level2
	, (2 , 'name2', 3 )    -- level2
	, (3 , 'name3', null ) -- level1
	, (4 , 'name4', 2 )    -- level3  
	, (5 , 'name5', 2 )    -- level3  
	, (6 , 'name6', 2 )    -- level3  
	, (7 , 'name7', 1 )    -- level3  
	, (8 , 'name8', 1 )    -- level3  
	, (9 , 'name9', 1 )    -- level3  
	, (10, 'name10', 9 )   -- level4
	) a (empId, empName, managerId)
go

; with empCte
as 
(
-- recursive anchor query
select empId, empName, managerId, 1 memLevel
from emp 
where empId = 3

union all 

-- recursive target query 
select b.empId, b.empName, b.managerId, a.memLevel + 1 memLevel
from empCte a -- anchor 
	join emp b 
	on b.managerId = a.empId 
)
-- cooking 
select a.empId, a.empName, isnull(b.empName, 'no parent') managerName, a.memLevel
from empCte a
	left join emp b
	on b.empId = a.managerId
order by 1

if object_id ('temp') is not null
drop table temp 
go

create table temp
(id varchar(20)
, time time 
, product varchar(100)
) 
go

insert into temp (id, time, product)
select id, time, product 
from 
	( 
	  values 
	  ('A', '08:10', 'pizza' )
	, ('A', '09:55', 'noodle' )
	, ('B', '12:30', 'pizza' )
	) a (id, time, product)
go

-- Consider the pros and cons of both queries.
-- 1 
; with a
as 
(
select row_number() over (partition by id order by time asc) rn , * 
from temp 
)
select *
from a 
where rn = 1 

-- 2
select b.*
from 
	(
	select distinct id 
	from temp a
	) a
	cross apply 
	(
	select top 1 * 
	from temp 
	where id = a.id
	order by time 
	) b

if object_id ( 'tblx') is not null 
drop table tblx 
go
 
select 
	c1, c2, c3, isnull(sign(c3-lag(c3, 1) over (partition by c1 order by (c2))),0) c4 
into tblx 
from 
	(
	select char(a.idx % 26 + 65) c1
		, cast ('20180101' as datetime) + b.idx c2
		, cast(abs(checksum(newid())) % 100000 as int) c3
	from 
		(
		select top 26 cast(row_number() over (order by (select 1)) as int) -1 idx
		from sys.objects a1
		) a, 
		(
		select top 365 cast(row_number() over (order by (select 1)) as int) -1 idx
		from sys.objects a1
			, sys.objects a2
		) b 
	) a 
where 
   datepart(weekday, c2 ) not in (1,7)

--
select * from tblx 

-- 1 
; with a as
(
select row_number() over ( partition by c1, c order by c2) rn 
	, * 
from tblx 
	, 
	(
	select 0 c union all 
	select 1 union all 
	select 2 union all
	select 3 union all
	select 4 union all
	select 5
	) b
) 
select 
	  max(c1) c1, min(c2) c2
	, max(case when c = 0 then c3 else null end ) d0
	, max(case when c = 1 then c3 else null end ) d1
	, max(case when c = 2 then c3 else null end ) d2 
	, max(case when c = 3 then c3 else null end ) d3
	, max(case when c = 4 then c3 else null end ) d4 
	, max(case when c = 5 then c3 else null end ) d5 
from a 
group by c1, rn-c
having max(case when c = 0 then c3 else null end ) is not null
	and sign (max(case when c = 1 then c3 else null end ) - max(case when c = 0 then c3 else null end )) 
	  + sign (max(case when c = 2 then c3 else null end ) - max(case when c = 1 then c3 else null end )) 
	  + sign (max(case when c = 3 then c3 else null end ) - max(case when c = 2 then c3 else null end )) 
	  + sign (max(case when c = 4 then c3 else null end ) - max(case when c = 3 then c3 else null end )) 
	  + sign (max(case when c = 5 then c3 else null end ) - max(case when c = 4 then c3 else null end )) 
	  = 5
order by c1, c2 

-- 2 
; with a 
as
(
select *
	, lag(c3, 1) over (partition by c1 order by (c2)) dm1
	, lag(c3, 2) over (partition by c1 order by (c2)) dm2
	, lag(c3, 3) over (partition by c1 order by (c2)) dm3
	, lag(c3, 4) over (partition by c1 order by (c2)) dm4
	, lag(c3, 5) over (partition by c1 order by (c2)) dm5
from tblx
)
select * 
from a 
where 
	sign(dm4-dm5)
	+ sign(dm3-dm4)
	+ sign(dm2-dm3)
	+ sign(dm1-dm2)
	+ sign(c3-dm1)  = 5

-- 3 
select a.c1, a.c2, a.c3
from 
	(
	select a.* , count(*) over (partition by c1, rn2) as cn
	from 
		(
			select a.*
				, row_number() over (partition by c1 order by c2) 
				  - row_number() over (partition by c1, c4 order by c2) as rn2
			from tblx a
		) a
	where c4 = 1
	) a
where cn >= 5 
order by c1, c2

function SqlQuery 
{
    param (
        [parameter(Mandatory=$true)]  [string] $server, 
        [parameter(Mandatory=$true)]  [string] $port,
        [parameter(Mandatory=$true)]  [string] $database, 
        [parameter(Mandatory=$true)]  [string] $id, 
        [parameter(Mandatory=$true)]  [string] $pass, 
        [parameter(Mandatory=$true)]  [string] $query,
        [parameter(Mandatory=$false)] [switch] $isRead = $false , 
        [parameter(Mandatory=$false)] [int]    $queryTimeout = 0 ,
        [parameter(Mandatory=$false)] [int]    $connTimeout = 5 ,

        [parameter(Mandatory=$false)] [string] $appName = "PowerShell"  
    ) 
    try
    {
        $substringLen = 30
        if ($query.Length -lt $substringLen)
        {
            $substringLen = $query.Length
        }
        $querySubstring = $query.substring(0, $substringLen)
        
        $conn = New-Object System.Data.SQLClient.SQLConnection
        $conn.ConnectionString = "server=$($server),$($port);database=$($database);User Id=$($id);Password=$($pass);Connect Timeout=$($connTimeout);Application Name=$($appName)"
        $conn.Open()
        $cmd = New-Object System.Data.SQLClient.SQLCommand
        $cmd.Connection = $conn
        $cmd.CommandText = $query
        $cmd.CommandTimeout=$queryTimeout
        if($isRead)
        {
            $ds=New-Object system.Data.DataSet
            $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
            $da.fill($ds) | Out-Null
            Write-Host ($ds.Tables | Format-Table | Out-String)
        }
        else
        {
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $conn.Close()

        Write-Host "SUCCESS, SERVER : $($server), QUERY : $($querySubstring) ..."
    }
    catch 
    {
        Write-Host "FAILED, SERVER : $($server), QUERY : $($querySubstring) ..."
        throw "FAILED SERVER : $($server), ERROR MESSAGE : $($_)"
    }
}



[string] $port = "1433"
[string] $id = "id"
[string] $pass = "pass"
[string] $query = 
@"
select @@version
"@

SqlQuery -server "ip" -port "1433" -database "master" -id  $id -pass $pass -query $query -isRead:$true

using System;
using System.Collections.Generic;
using System.IO.Ports;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace TaskTest2
{
    // Parallel execution can be performed as much as the specified number.
    // Job can be canceled.
    // You can set the task timeout.
    // When a task exception occurs, all workers can be stopped.

    internal class Program
    {
        static readonly CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();

        public static void Main(string[] args)
        {

            // Simulation of job cancellation time (external factor)
            DateTime endTime = DateTime.Now.Add(new TimeSpan(0, 0, 1000));

            Manager manager = new Manager(cancellationTokenSource);
            var task = manager.Run(maxDegreeOfParallelism: 10, workerCnt: 10, dummyWorkerIterationCnt: 10, errorPosition: 1);
            try
            {
                // Work cancellation The internal work must be completed within this time.
                task = AsyncExtensions.TimeoutAfter(task, TimeSpan.FromSeconds(100), cancellationTokenSource);
                while (!task.Wait(50))
                {
                    Console.Write(".");
                    if (DateTime.Now > endTime)
                        manager.Stop();
                }
            }
            catch (AggregateException aex)
            {
                foreach (var ex in aex.InnerExceptions)
                {
                    Console.WriteLine($@"main inner exception {ex.Message}");
                }
            }
            catch (TimeoutException ex)
            {
                Console.WriteLine($@"main timeout {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($@"main exception ex {ex.Message}");
            }
            Console.WriteLine("main program end");
        }
    }

    internal class Manager
    {
        CancellationTokenSource cancellationTokenSource;

        public Manager(CancellationTokenSource cancellationTokenSource)
        {
            this.cancellationTokenSource = cancellationTokenSource;
        }

        public void Stop()
        {
            cancellationTokenSource.Cancel();
            Console.WriteLine("cancel requested");
        }

        public async Task Run(int maxDegreeOfParallelism, int workerCnt, int dummyWorkerIterationCnt, int? errorPosition)
        {

            CancellationToken token = cancellationTokenSource.Token;
            List<Func<Task>> actionList = new List<Func<Task>>();

            for (int i = 0; i < workerCnt; i++)
            {
                int j = i;
                actionList.Add(() => new DummyWorker(token).DoWork(iteration: dummyWorkerIterationCnt, errorPosition: errorPosition));
                if (token.IsCancellationRequested)
                {
                    Console.WriteLine($@"run token cancel requested");
                    break;
                }
            }



            try
            {
                await AsyncExtensions.InvokeAsync(actionList, maxDegreeOfParallelism: maxDegreeOfParallelism, token);
            }
            catch (Exception ex)
            {
                Console.WriteLine($@"run method exception {ex.Message}");
            }
            Console.WriteLine("end");

        }
    }

    internal class DummyWorker
    {
        CancellationToken token;

        public DummyWorker(CancellationToken token)
        {
            this.token = token;
        }

        public Task DoWork(int iteration, int? errorPosition)
        {
            var task = Task.Run(async () =>
            {
                for (int i = 0; i < iteration; i++)
                {
                    if (token.IsCancellationRequested)
                    {
                        Console.WriteLine($@"{GetType()} cancel requested");
                        break;
                    }
                    if (i == errorPosition)
                        throw new Exception("simulation exception : fire!");

                    // working...
                    await Task.Delay(500);

                    Console.WriteLine($@"working...iteration {i}");
                }
            }, token);
            return task;
        }
    }

    public static class AsyncExtensions
    {
        public static async Task<T> TimeoutAfter<T>(this Task<T> task, TimeSpan timeout, CancellationTokenSource cancellationTokenSource = null)
        {
            if (task == await Task.WhenAny(task, Task.Delay(timeout)))
                return await task;
            else
            {
                if (cancellationTokenSource != null)
                    cancellationTokenSource.Cancel();

                throw new TimeoutException();
            }
        }
        public static async Task TimeoutAfter(this Task task, TimeSpan timeout, CancellationTokenSource cancellationTokenSource = null)
        {
            if (task == await Task.WhenAny(task, Task.Delay(timeout)))
                await task;
            else
            {
                if (cancellationTokenSource != null)
                    cancellationTokenSource.Cancel();

                throw new TimeoutException();
            }
        }
        public static async Task InvokeAsync(IEnumerable<Func<Task>> taskFactories, int maxDegreeOfParallelism, CancellationToken token)
        {
            if (taskFactories == null) throw new ArgumentNullException(nameof(taskFactories));
            if (maxDegreeOfParallelism <= 0) throw new ArgumentException(nameof(maxDegreeOfParallelism));

            // Defensive copy. Similar to what Task.WhenAll/WhenAny does.
            Func<Task>[] queue = taskFactories.ToArray();

            if (queue.Length == 0)
            {
                return;
            }

            List<Task> tasksInFlight = new List<Task>(maxDegreeOfParallelism);
            int index = 0;

            do
            {
                while (tasksInFlight.Count < maxDegreeOfParallelism && index < queue.Length)
                {
                    if (token.IsCancellationRequested)
                        return;

                    Func<Task> taskFactory = queue[index++];

                    tasksInFlight.Add(taskFactory());
                }

                if (token.IsCancellationRequested)
                    return;

                Task completedTask = await Task.WhenAny(tasksInFlight).ConfigureAwait(false);

                try
                {
                    await completedTask.ConfigureAwait(false); // Check result.
                }
                catch
                {
                    throw;
                }

                tasksInFlight.Remove(completedTask);
            }
            while (index < queue.Length || tasksInFlight.Count != 0);
        }
    }
}

if object_id ('tblx') is not null
drop table tblx, tbly, tblz
go
 
create table tblx (id int, c int)
go
 
create table tbly (id int, c int)
go
 
create table tblz (idx bigint identity(1,1),etime datetime, emessage nvarchar(max), )
go
 
create unique nonclustered index nc_tbly_01 on tbly (id)
go
 
if object_id ('usp_a') is null
exec ('create proc usp_a as select 1')
go
 
alter proc usp_a
(
@id int,
@c int
)
as
set implicit_transactions on ; -- 암시적 트랜잭션
begin try -- try catch 로 transaction 처리 할 부분 전체를 감싼다.
insert into tblx (id, c) values (@id, @c)
insert into tbly (id, c) values (@id, @c)
commit tran ;
end try
begin catch
if @@trancount > 0
begin
rollback tran;
set implicit_transactions off ;
-- 추가적인 로깅을 하던지
DECLARE @ErrorMessage NVARCHAR(max) = ERROR_MESSAGE()
insert into tblz (etime, emessage) values (getdate(), @ErrorMessage)
end
end catch
go
 
-- 아래
-- 테스트 시작
select @@trancount
select * from tblx
select * from tbly
select * from tblz
go
-- 오류없는 테스트
exec usp_a 1,1
exec usp_a 2,2
go
-- 데이터 확인
select * from tblx
select * from tbly
select * from tblz
go
-- 에러상황 테스트
-- tbly 는 unique index 가 있어서 중복값이 들어가지 못하는 상황이다.
-- rollback 했을때 전체가 rollback 되어야 하므로 tblx 에도 1,1 이 중복해 들어가지 않기를 바란다.
exec usp_a 1,1
select * from tblz
go
-- 데이터 확인
-- tbly 에서 에러가 생겨 데이터가 들어가지 않았다.
select * from tblx
select * from tbly
select * from tblz
go
-- 따로는 데이터가 잘 들어간다.
insert into tblx (id, c) values (1,1)
go
-- 데이터 확인
select * from tblx
select * from tbly
select * from tblz
go

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

namespace Helpers
{
    class ThreadedHelper<I,O>
    {
        public I input { get; set; }

        public ThreadedHelper(Func<I,O> doWork, Action<O> callback, Action<I, Exception> errorCallback)
        {
            this.doWork = doWork;
            this.callback = callback;
            this.errorCallback = errorCallback;
            t = new Thread(Process);
        }

        public void Start()
        {
            t.Start();
        }

        private void Process()
        {
            try
            {
                O retun = doWork(input);
                callback(retun);
            }
            catch (Exception ex)
            {
                errorCallback(input, ex);
                t.Abort();
            }
        }

        private Func<I,O> doWork;
        private Action<O> callback;
        private Action<I, Exception> errorCallback;

        private Thread t;


        // example
        // using System.Threading;
        // using Helpers;
        //class Program
        //{
        //    class Input
        //    {
        //        public int a { get; set; }
        //        public int b { get; set; }
        //    }

        //    class Output
        //    {
        //        public Input input { get; set; }
        //        public int result { get; set; }
        //    }

        //    static void Main(string[] args)
        //    {
        //        var p = new Program();

        //        new ThreadedHelper<Input, Output>(p.DoWork, p.Callback, p.ErrorCallback)
        //        {
        //            input = new Input { a = 4, b = 0 }
        //        }.Start();

        //        for (int i = 0; i < 30; i++)
        //        {
        //            Console.WriteLine("wait");
        //            Thread.Sleep(100);
        //        }

        //        Console.WriteLine("end");
        //        Console.ReadKey();
        //    }

        //    Output DoWork(Input input)
        //    {
        //        try
        //        {
        //            Thread.Sleep(2000);
        //            int s = input.a / input.b;
        //            return new Output { input = input, result = s };
        //        }
        //        catch (Exception ex)
        //        {
        //            throw ex;
        //        }
        //    }

        //    void Callback(Output output)
        //    {
        //        Console.WriteLine($@"output : {output.input.a},{output.input.b},{output.result}");
        //    }

        //    void ErrorCallback(Input i, Exception ex)
        //    {
        //        Console.WriteLine($@"output error : {i.a}, {i.b}, {ex.Message}, {ex.StackTrace}");
        //    }

        //}

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

namespace Helpers
{
    class ThreadedHelper<I,O>
    {
        public delegate O DoWork(I inValue);
        public delegate void CallBack(O outValue);
        public delegate void ErrorCallBack(I input, Exception message);
        public I input { get; set; }

        public ThreadedHelper(DoWork work, CallBack callback, ErrorCallBack errorCallback)
        {
            this.work = work;
            this.callback = callback;
            this.errorCallback = errorCallback; 
            t = new Thread(Process);
        }

        public void Start()
        {
            t.Start();
        }

        private void Process()
        {
            try
            {
                O retun = work(input);
                callback(retun);
            }
            catch (Exception ex)
            {
                errorCallback(input, ex);
                t.Abort();
            }
        }

        private DoWork work;
        private CallBack callback;
        private ErrorCallBack errorCallback;
        private Thread t;


        // example
        // using System.Threading;
        // using Helpers;
        //class Program
        //{
        //    class Input
        //    {
        //        public int a { get; set; }
        //        public int b { get; set; }
        //    }

        //    class Output
        //    {
        //        public Input input { get; set; }
        //        public int result { get; set; }
        //    }

        //    static void Main(string[] args)
        //    {
        //        var p = new Program();

        //        new ThreadedHelper<Input, Output>(p.DoWork, p.Callback, p.ErrorCallback)
        //        {
        //            input = new Input { a = 4, b = 0 }
        //        }.Start();

        //        for (int i = 0; i < 30; i++)
        //        {
        //            Console.WriteLine("wait");
        //            Thread.Sleep(100);
        //        }

        //        Console.WriteLine("end");
        //        Console.ReadKey();
        //    }

        //    Output DoWork(Input input)
        //    {
        //        try
        //        {
        //            Thread.Sleep(2000);
        //            int s = input.a / input.b;
        //            return new Output { input = input, result = s };
        //        }
        //        catch (Exception ex)
        //        {
        //            throw ex;
        //        }
        //    }

        //    void Callback(Output output)
        //    {
        //        Console.WriteLine($@"output : {output.input.a},{output.input.b},{output.result}");
        //    }

        //    void ErrorCallback(Input i, Exception ex)
        //    {
        //        Console.WriteLine($@"output error : {i.a}, {i.b}, {ex.Message}, {ex.StackTrace}");
        //    }

        //}

    }
}

타이머로 테스트 

https://github.com/yck1509/ConfuserEx/releases 무료 난독화 
JetBrain DotPeeker 로 잘 안보임 

* 모두삭제
del hostname.*

* private key 와 인증서 및 인증서 암호를 입력한다. 
makecert -r -len 2048 -pe -n "CN=HOSTNAME" -eku 1.3.6.1.5.5.7.3.1,1.3.6.1.5.5.7.3.2,1.3.6.1.5.5.7.3.3,1.3.6.1.5.5.7.3.4 -sky exchange -sv HOSTNAME.pvk HOSTNAME.cer -b 01/01/2005 -e 01/01/2035

* 암호입력
1234

* 포터블 파일로 변경한다. 
pvk2pfx -pvk HOSTNAME.pvk -spc HOSTNAME.cer -pfx HOSTNAME.pfx -pi 1234 -po 1234 -f

* pfx my, localhost 에 등록
* cer Root, localhost 에 등록 
* certlm.mmc 에서 Personal + Trusted Root Certi 저장소에 HOSTNAME pfx 와 cert 파일이 있는지 확인한다. 
* 자동으로 등록하는 클래스 Certification.cs 확인 

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using CsLib; 

namespace WcfHttpsServer
{
    class Certification
    {

        public bool Bind(string path, string pfx, string pfxpassword, string cert, string port)
        {
            try
            {
                string pfxFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, path, pfx);
                string certFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, path, cert);

                DeleteCertification(pfxFileName
                    , StoreName.My
                    , StoreLocation.LocalMachine
                    , pfxpassword
                    , X509KeyStorageFlags.MachineKeySet | X509KeyStorageFlags.PersistKeySet);

                DeleteCertification(certFileName
                    , StoreName.Root
                    , StoreLocation.LocalMachine);

                InstallCertification(pfxFileName
                    , StoreName.My
                    , StoreLocation.LocalMachine
                    , pfxpassword
                    , X509KeyStorageFlags.MachineKeySet | X509KeyStorageFlags.PersistKeySet);

                InstallCertification(certFileName
                    , StoreName.Root
                    , StoreLocation.LocalMachine);

                CertificationNetBind(certFileName, port);
            }
            catch (Exception)
            {
                throw;
            }
            return true;
        }

        private void InstallCertification(string certFileName, StoreName storeName, StoreLocation storeLocaion, string password, X509KeyStorageFlags x509KeyStorageFlags)
        {
            X509Store store = new X509Store(storeName, storeLocaion);
            try
            {
                bool Exists = false;
                X509Certificate2 cert = new X509Certificate2(certFileName, TranString.convertToSecureString(password), x509KeyStorageFlags);
                store.Open(OpenFlags.ReadWrite);
                foreach (X509Certificate2 storeCert in store.Certificates)
                {
                    if (storeCert.Thumbprint.Equals(cert.Thumbprint, StringComparison.OrdinalIgnoreCase))
                    {
                        Exists = true;
                        break;
                    }
                }
                if (!Exists)
                {
                    store.Add(cert);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                store.Close();
            }
        }

        private void InstallCertification(string certFileName, StoreName storeName, StoreLocation storeLocaion)
        {
            X509Store store = new X509Store(storeName, storeLocaion);
            try
            {
                bool Exists = false;
                X509Certificate2 cert = new X509Certificate2(certFileName);
                store.Open(OpenFlags.ReadWrite);
                foreach (X509Certificate2 storeCert in store.Certificates)
                {
                    if (storeCert.Thumbprint.Equals(cert.Thumbprint, StringComparison.OrdinalIgnoreCase))
                    {
                        Exists = true;
                        break;
                    }
                }
                if (!Exists)
                {
                    store.Add(cert);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                store.Close();
            }
        }

        private void DeleteCertification(string certFileName, StoreName storeName, StoreLocation storeLocaion, string password, X509KeyStorageFlags x509KeyStorageFlags)
        {
            X509Store store = new X509Store(storeName, storeLocaion);
            try
            {
                X509Certificate2 cert = new X509Certificate2(certFileName, TranString.convertToSecureString(password), x509KeyStorageFlags);
                store.Open(OpenFlags.ReadWrite);
                foreach (X509Certificate2 storeCert in store.Certificates)
                {
                    if (storeCert.Thumbprint.Equals(cert.Thumbprint, StringComparison.OrdinalIgnoreCase))
                    {
                        store.Remove(cert);
                        break;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                store.Close();
            }
        }

        private void DeleteCertification(string certFile, StoreName storeName, StoreLocation storeLocaion)
        {
            X509Store store = new X509Store(storeName, storeLocaion);
            try
            {
                X509Certificate2 cert = new X509Certificate2(certFile);
                store.Open(OpenFlags.ReadWrite);
                foreach (X509Certificate2 storeCert in store.Certificates)
                {
                    if (storeCert.Thumbprint.Equals(cert.Thumbprint, StringComparison.OrdinalIgnoreCase))
                    {
                        store.Remove(cert);
                        break;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                store.Close();
            }
        }



        private string GetCertHash(string certFileName)
        {
            using (X509Certificate2 cert = new X509Certificate2(certFileName))
                return cert.Thumbprint.ToLower();
        }

        private string GetAssemblyGuid()
        {
            var assembly = typeof(Program).Assembly;
            var attribute = (GuidAttribute)assembly.GetCustomAttributes(typeof(GuidAttribute), true)[0];
            var id = attribute.Value;
            return id.ToLower();
        }

        private bool CertificationNetBind(string certFileName, string port)
        {
            StringBuilder sbPsiResults = new StringBuilder();
            ProcessStartInfo psi = new ProcessStartInfo() { CreateNoWindow = true, UseShellExecute = false, RedirectStandardOutput = true };
            bool isBounded = false;
                       
            // SHOW
            psi.FileName = "netsh";
            psi.Arguments = $"http show sslcert ipport=0.0.0.0:{port}";
            sbPsiResults.Clear();
            Process procHttpShow = Process.Start(psi);
            while (procHttpShow != null && !procHttpShow.StandardOutput.EndOfStream)
                sbPsiResults.Append(procHttpShow.StandardOutput.ReadLine());
            procHttpShow?.WaitForExit(2000);

            if (sbPsiResults.ToString().ToLower().Contains(GetCertHash(certFileName)) && sbPsiResults.ToString().ToLower().Contains(GetAssemblyGuid()))
            {
                // DELETE
                psi.FileName = "netsh";
                psi.Arguments = $"http delete sslcert ipport=0.0.0.0:{port}";
                sbPsiResults.Clear();
                Process procHttpDelete = Process.Start(psi);
                while (procHttpDelete != null && !procHttpDelete.StandardOutput.EndOfStream)
                    sbPsiResults.Append(procHttpDelete.StandardOutput.ReadLine());
                procHttpDelete?.WaitForExit(2000);

                if (sbPsiResults.ToString().ToLower().Contains("success"))
                    isBounded = false;
                else
                    throw new Exception($"http delete sslcert error : {sbPsiResults.ToString()}");
            }
            else
                isBounded = false;
            
            if (!isBounded)
            {
                // ADD
                psi.FileName = "netsh";
                psi.Arguments = $"http add sslcert ipport=0.0.0.0:{port} certhash={GetCertHash(certFileName).ToLower()} appid={{{GetAssemblyGuid()}}}";
                sbPsiResults.Clear();
                Process procHttpAdd = Process.Start(psi);
                while (procHttpAdd != null && !procHttpAdd.StandardOutput.EndOfStream)
                    sbPsiResults.Append(procHttpAdd.StandardOutput.ReadLine());
                procHttpAdd?.WaitForExit(2000);
                if (sbPsiResults.ToString().ToLower().Contains("error"))
                {
                    isBounded = false;
                    throw new Exception($"http add sslcert error : {sbPsiResults.ToString()}");
                }
                else
                    isBounded = true;
            }
            return isBounded;
        }
    }
}