Monday 4 November 2013

Create MSSQL Table Function the Easy Way

Some times when you use Microsoft Products you find that they never quite get to the point and we require just a simple example, Nothing is more painful than trying to make head nor tail of the 'Create Table Valued Function' This is the garbage it provides.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
CREATE FUNCTION  
( 
 -- Add the parameters for the function here
 <@param1, sysname, @p1> , 
 <@param2, sysname, @p2> 
)
RETURNS TABLE 
AS
RETURN 
(
 -- Add the SELECT statement with parameter references here
 SELECT 0
)
GO

If I want to return a table this makes very little sense. At at time looks rather unclear. Do I use Angle Brakets when declaring variables '[id] '? Here's a short working example of the above.
CREATE FUNCTION [dbo].[SampleFunction]()
RETURNS @rtnTable TABLE
(
 [ID] uniqueidentifier null
)
AS
BEGIN


declare @sampleTable Table
(
 [ID] uniqueidentifier
)

insert into @sampleTable
select [stuffintosampletable]

insert into @rtnTable
select * from @sampleTable

Return

End

Sunday 13 October 2013

SQL Get Start of Day

SQL Get Start of Day

As many of you know SQL can be fun when you are dealing with records in the database.  One common thing that I have always been required to do is to get Records where their date is greater than the start of the day.

Imagine this Scenario.

DateLogged = '2012-10-08 00:00:00.000'

If I run the following command

Select GetDate()

This Returns: '2012-10-08 12:59:11.887' 

So how can I go about building a query that will get me this date?

Simple!, We basically need to tell SQL to get us the first second of the day or earlier.

select dateadd(second,0,dateadd(day,datediff(day,0,getdate()),0)

This Returns: '2012-10-08 00:00:00.000'

Start of Day x Number of Days Ago Excluding Weekends

Now the next problem I have had is getting a record that was added say 3 days ago but I want SQL to Ignore the weekend. 

Imagine this scenario

DateLogged = '2012-10-05 00.00.00:000'

To get this date I can achieve this by running the following command

select dateadd(weekday, -3, GetDate())

This Returns: '2012-10-05 12:59:11.887'

However this is not what I want, so if we adapt the sql above.

select dateadd(second,0,dateadd(weekday,-3,dateadd(day,datediff(day,0,getdate()),0)))

This Returns: '2012-10-05 00:00:00.000'

Obviously I do not believe this is the most efficient way of doing this but it is rather dynamic.

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.

Sunday 9 June 2013

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

SagePay Integration the Easy Way

I was recently asked to integrate a billing platform for a product that my company was selling to a client.  After evaluating several different payment gateways such as PayPal, Google Cart and RealEx Payments I decided to settle on SagePay.

PayPal and Google Cart offer brilliant integration scripts with a vast knowledge base for help.
RealEx was a rather poor system in that it offered virtually no support to developers.  I had even found flaws in several parts of their documentation that took 3 days for them to confirm.  Many developers can agree this is a waste of time when projects have deadlines.

So I decided to integrate SagePay.

SagePay for those of you who do not know acts as a simple payment Gateway for processing transactions.  It does not store shopping carts it also has a good title as it is linked in part to the Sage Software Company.

SagePay has 3 Types of Integration
  • Forms
  • Sever
  • Direct
  • Token
After viewing their samples for each of these I decided that Server would offer the best integration with most functions however it would also remove the onus on the eCommerce company to need numerous security checks.

From experimenting from their examples I found that most code was repetitive however it was heavily engrained in each of their sample forms with no actual Code Library to Use.

Over the next 3 posts throughout the week I will show how to integrate with SagePay through a small .dll library I have created.  As well as storing the information about these transactions to the database.

Keep Tuned.