Sunday 21 July 2013

SagePay Server Integration the Easy Way Part (2 of 3)

SagePay Server Integration the Easy Way Part (2 of 3)

Previously I blogged about integration of SagePay in this feature I am going to show and hopefully explain how to integrate my dll.

At a minimal level we need to adjust our web.config file to include the new instructions so the SagePay Dll will work properly.

Step 1.  Modify the Connection String

Firstly we must create a connection to our SQL Database.
  <connectionStrings>
  <clear />
  <add name="LocalSqlServer" connectionString="[YourConnectionString]"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Please note that for the sake of the dll we are using the name 'LocalSqlServer'  you may get an error from your machine saying that this is already used.  This is because 'LocalSqlServer' is held on Machine.Config.  To get past this we add the 'Clear' tag.

Step 2. Modify your App Config

Since we have created the Connection String we also need to add values to the Application Config.  Please update these fields from your SagePay information.
<appSettings>
    <add key="Vendor" value=""/> //This is your vendor code given to you by sagepay
    <add key="TxType" value="PAYMENT"/> //We want payment however we can also set this for Token in which the system would need to be altered just slightly.
    <add key="AccountType" value="C"/>  //We want to support recurring payments so we set our account type to 'C'
    <add key="NotificationUrl" value="http://mysite.com/notificationPage.aspx"/> //This is the place where SagePay posts back updates.
    <add key="FailureUrl" value="http://mysite.com/orderFailed.aspx"/> //This page posts back when an order is failed.
    <add key="SuccessUrl" value="http://mysite.com/orderSuccess.aspx"/> //This page is the page that posts back when the order is successful
    <add key="VPSProtocol" value="2.23"/>
    <add key="SagePayConnectionType" value="SIMULATOR"/>
</appSettings>

Step 3. Create the Database Tables.

For all this system to fall together we need a database.  We will call our table 'Transactions' this is the recommended specification from SagePay however I have added some additional fields to make things easier from my own projects.

CREATE TABLE [dbo].[Transactions](
 [TransactionId] [uniqueidentifier] NULL,
 [ForeignId] [nvarchar](38) NULL,
 [OrderID] [nvarchar](38) NULL,
 [Description] [nvarchar](100) NULL,
 [TransactionType] [nvarchar](100) NULL,
 [Amount] [decimal](18, 2) NULL,
 [Currency] [nvarchar](max) NULL,
 [VPSTxId] [nvarchar](38) NULL,
 [SecurityKey] [nvarchar](10) NULL,
 [TxAuthNo] [bigint] NULL,
 [Status] [nvarchar](15) NULL,
 [StatusMessage] [nvarchar](255) NULL,
 [RelatedVPSTxId] [nvarchar](100) NULL,
 [RelatedVendorTxCode] [nvarchar](40) NULL,
 [RelatedSecurityKey] [nvarchar](10) NULL,
 [RelatedTxAuthNo] [bigint] NULL,
 [AVSCV2] [nvarchar](50) NULL,
 [AddressResult] [nvarchar](20) NULL,
 [PostCodeResult] [nvarchar](20) NULL,
 [CV2Result] [nvarchar](20) NULL,
 [GiftAid] [bit] NULL,
 [ThreeDSecureStatus] [nvarchar](50) NULL,
 [CAVV] [nvarchar](32) NULL,
 [AddressStatus] [nvarchar](20) NULL,
 [PayerStatus] [nvarchar](20) NULL,
 [CardType] [nvarchar](15) NULL,
 [Last4Digits] [nvarchar](4) NULL,
 [Created] [datetime] NULL,
 [InitialPayment] [bit] NULL,
 [TransactionComplete] [bit] NULL,
 [IsReOrder] [bit] NULL,
 [OriginalTransactionId] [uniqueidentifier] NULL,
 [IsRefunded] [bit] NULL,
 [IsVoided] [bit] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_TransactionId]  DEFAULT (newid()) FOR [TransactionId]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_GiftAid]  DEFAULT ((0)) FOR [GiftAid]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_LastUpdate]  DEFAULT (getdate()) FOR [Created]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_FirstPayment]  DEFAULT ((0)) FOR [InitialPayment]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_TransactionComplete]  DEFAULT ((0)) FOR [TransactionComplete]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_IsReOrder]  DEFAULT ((0)) FOR [IsReOrder]
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_IsRefunded]  DEFAULT ((0)) FOR [IsRefunded]


This is everything we need to do to hold everything together.  In the next blog post I will include the DLL and also how to integrate this to your own applications.  Alongside a sample.