Globalne przchwytywanie wyjatków w WCF

using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using System.Collections.ObjectModel;
using System;

namespace Framework.Services
{
    public class ServiceErrorHandlerBehaviorAttribute : Attribute, IServiceBehavior
    {
        private readonly IErrorHandler errorHandler;

        public ServiceErrorHandlerBehaviorAttribute()
        {
            errorHandler = new ServiceErrorHandler();
        }

        public ServiceErrorHandlerBehaviorAttribute(IErrorHandler errorHandler)
        {
            this.errorHandler = errorHandler;
        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        {
            var channelDispatchers = serviceHostBase.ChannelDispatchers;
            var length = channelDispatchers.Count;
            for (var i = 0; i < length; ++i)
            {
                var cd = channelDispatchers[i] as ChannelDispatcher;
                if (cd != null)
                {
                    if (!cd.ErrorHandlers.Contains(errorHandler))
                    {
                        cd.ErrorHandlers.Add(errorHandler);
                    }
                }
            }
        }

        public void AddBindingParameters(
            ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection endpoints, BindingParameterCollection bindingParameters)
        {
        }

        public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        {
        }
    }
}

using System;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Dispatcher;

namespace Framework.Services
{
    public class ServiceErrorHandler : IErrorHandler 
    {        
        private static readonly bool SaveErrorInLog; 
        
        static ServiceErrorHandler()
        {
	  //get value from web.config
            SaveErrorInLog = true;
        } 
        
        public bool HandleError(Exception error)
        {
            if (SaveErrorInLog)
	      HandleErrorLogic(error);
            
            return !(error is FaultException);
        }

        public void ProvideFault(Exception error, MessageVersion version, ref Message fault)
        {
            if (SaveErrorInLog)
	      HandleErrorLogic(error);
            
            if (!(error is FaultException))
            {
                var messageFault = MessageFault.CreateFault(
                    new FaultCode("MessageFault.Code"), new FaultReason(error.Message), error, new NetDataContractSerializer());
                fault = Message.CreateMessage(version, messageFault, null);
            }
        }
        
        private void HandleErrorLogic(Exception error)
        {
            try
            {
                //zapisz błąd w logu
            }
            catch
            {
            }
        }         
    }
}

    [ServiceErrorHandlerBehavior]
    public class MyService : IMyService
    {
          public void DoSomething()
          {
          }
    }

Dynamiczne sortowanie w PostgreSQL

               select 
			mm.userid as UserId, 
			coalesce(mm.firstname,'') || coalesce(' ' || mm.lastname,'') as UserName,
			mm.description as Description, 
			mm.usr as ModifyUser, 
			mm.date as ModifyDateTime
		from ext.rekordy as mm		
		where 
			(_firstname is null or mm.firstname ilike '%' || _firstname || '%') 
			AND (_lastname is null or mm.lastname ilike '%' || _lastname || '%')
			AND (_pesel is null or mm.pesel = _pesel)
			AND (_modifydatetimefrom IS NULL OR mm.date >= _modifydatetimefrom)
			AND (_modifydatetimeto IS NULL OR mm.date <= _modifydatetimeto)
		order by 
			case 
				when _sort_direction = 'DESC' then
					case 
						when _sort_column_name = 'UserId' then cast(mm_log.userid as text)
						when _sort_column_name = 'UserName' then cast(coalesce(mm.firstname,'') || coalesce(' ' || mm.lastname,'') as text)
						when _sort_column_name = 'Description' then cast(mm.description as text)
						when _sort_column_name = 'ModifyUser' then cast(mm.usr as text)
						when _sort_column_name = 'ModifyDateTime' then cast(mm.date as text)
						else cast(mm.userid as text)
					end
			end desc,
			case 
				when _sort_direction = 'ASC' then
					case 
						when _sort_column_name = 'UserId' then cast(mm.userid as text)
						when _sort_column_name = 'UserName' then cast(coalesce(mm.firstname,'') || coalesce(' ' || mm.lastname,'') as text)
						when _sort_column_name = 'Description' then cast(mm.description as text)
						when _sort_column_name = 'ModifyUser' then cast(mm.usr as text)
						when _sort_column_name = 'ModifyDateTime' then cast(mm.date as text)
						else cast(mm.idx_osoby as text)
					end
			end asc			
		offset _skip limit _take

Template SQL z wykorzystaniem transakcji i bloku TRY-CATCH

Template SQL z wykorzystaniem transakcji i bloku TRY-CATCH

USE [db]
GO

-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
BEGIN TRANSACTION
----------------SQL BEGIN------------------

raiserror(N'Jakiś błąd', 16, 1); -- wyrzucenie wyjatku z sekcji try. Zostanie on przechwycony w catch (severity > 10 (np.16))

---------------------SQL END-------------
COMMIT TRANSACTION
PRINT 'The script has been run successfuly'
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The script failed. Rolling back transaction.'
ROLLBACK TRANSACTION;
END

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity,  @ErrorState);
END CATCH

IoC w WCF z wykorzystaniem Microsoft Unity

UnityInstanceProvider.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel.Dispatcher;
using Microsoft.Practices.Unity;
using System.ServiceModel.Channels;
using System.ServiceModel;

namespace Toolkit.Unity
{
    internal class UnityInstanceProvider : IInstanceProvider
    {
        private readonly IUnityContainer container;
        private readonly Type contractType;

        public UnityInstanceProvider(IUnityContainer container, Type contractType)
        {
            this.container = container;
            this.contractType = contractType;

        }

        public object GetInstance(InstanceContext instanceContext, Message message)
        {
            return container.Resolve(contractType);
        }

        public object GetInstance(System.ServiceModel.InstanceContext instanceContext)
        {
            return GetInstance(instanceContext, null);
        }

        public void ReleaseInstance(System.ServiceModel.InstanceContext instanceContext, object instance)
        {
            container.Teardown(instance);
        }
    }
}

UnityServiceBehavior.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel.Description;
using System.ServiceModel;
using Microsoft.Practices.Unity;
using System.ServiceModel.Dispatcher;

namespace Toolkit.Unity
{
    public class UnityServiceBehavior : IServiceBehavior
    {
        private readonly IUnityContainer container;

        public UnityServiceBehavior(IUnityContainer container)
        {
            this.container = container;
        }

        public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, System.Collections.ObjectModel.Collection<ServiceEndpoint> endpoints, System.ServiceModel.Channels.BindingParameterCollection bindingParameters)
        {

        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        {

            foreach (ChannelDispatcher channelDispatcher in serviceHostBase.ChannelDispatchers)
            {
                foreach (EndpointDispatcher endpointDispatcher in channelDispatcher.Endpoints)
                {                    
                    endpointDispatcher.DispatchRuntime.InstanceProvider = new UnityInstanceProvider(container, serviceDescription.ServiceType);                    
                }
            }
        }

        public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        {

        }
    }
}

UnityServiceHost.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using Microsoft.Practices.Unity;

namespace Toolkit.Unity
{
    public class UnityServiceHost : ServiceHost
    {
        private IUnityContainer unityContainer;

        public UnityServiceHost(IUnityContainer unityContainer, Type serviceType, params Uri[] baseAddresses)
            : base(serviceType, baseAddresses)
        {
            this.unityContainer = unityContainer;
        }

        protected override void OnOpening()
        {
            base.OnOpening();

            if (this.Description.Behaviors.Find() == null)
            {
                this.Description.Behaviors.Add(new UnityServiceBehavior(this.unityContainer));
            }
        }
    }
}

UnityServiceHostFactory.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.ServiceModel;
using Microsoft.Practices.Unity;
using Microsoft.Practices.Unity.Configuration;
using System.ServiceModel.Activation;

namespace Toolkit.Unity
{
    public class UnityServiceHostFactory : ServiceHostFactory
    {
        protected override ServiceHost CreateServiceHost(Type serviceType, Uri[] baseAddresses)
        {
            var unityContainer = new UnityContainer();
            var configurationSection = (UnityConfigurationSection)ConfigurationManager.GetSection("unity");
            configurationSection.Configure(unityContainer);

            UnityServiceHost serviceHost = new UnityServiceHost(unityContainer, serviceType, baseAddresses);

            return serviceHost;
        }
    }
}

Definicja Factory dla serwisu WCF

<%@ ServiceHost Language="C#" Debug="true"
    Service="WCFUnityInjection.BookService"
    Factory ="Toolkit.Unity.UnityServiceHostFactory"
    CodeBehind="BookService.svc.cs" %>

web.config dla serwisu WCF

<?xml version="1.0"?>
<configuration>
    <configSections>
        <section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration" />
    </configSections>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
    <system.serviceModel>
        <behaviors>
            <serviceBehaviors>
                <behavior>
                    <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->
                    <serviceMetadata httpGetEnabled="true"/>
                    <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
                    <serviceDebug includeExceptionDetailInFaults="false"/>
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
    </system.serviceModel>
    <system.webServer>
        <modules runAllManagedModulesForAllRequests="true"/>
    </system.webServer>
    <unity>
        <containers>
            <container>                
                <types>
                    <type type="WCFUnityInjection.IInnerBookService, WCFUnityInjection" mapTo="WCFUnityInjection.InnerBookService, WCFUnityInjection"/>
                </types>                    
            </container>
        </containers>
    </unity>
</configuration>

Release SQL template

USE [Database] -- Current database
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------

SQL statement

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
---------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Składnia metody Join

IList<byte> p_statuses = new List<byte>(new byte[] { (byte)Status.Statuses.Accepted, (byte)Status.Statuses.Pending }
return db.CLAIM.Join(db.CLAIM_LINE, c => c.ClaimId, l => l.ClaimId, (c, l) => new { Claim = c, ClaimLine = l })
       .Any(x => p_statuses.Contains(x.ClaimLine.StatusId.Value) && x.ClaimLine.InvoiceLineId == invoiceLineId && x.Claim.InvoiceId == invoiceId);