Convert XmlNode to DataSet

Here is a function that will enable you to pass a valid XML Node and have the system return you a DataSet object that you can use to manipulate further. Good bye nasty XML!

namespace FieldOfSheep
{
public static class NodeXmlExtension
    {
        public static DataSet ConvertToDataSet(this XmlNode node)
        {
            //declaring data set object
            DataSet dataset = null;
            if (node != null)
            {
                XmlTextReader xtr = new XmlTextReader(node.OuterXml, XmlNodeType.Element, null);
                dataset = new DataSet();
                dataset.ReadXml(xtr);
            }

            return dataset;
        }
    }
}

This class above is intended for use as an extension method to be used like so.

using FieldOfSheep;

public static void Main(String[] args)
{
   XmlNode node;

   DataSet ds = node.ConvertToDataSet();

}

There are times when building software that our customer asks us to create items that are out of the box. One such request was to integrate SagePay Tokenization to Nop Commerce 1.80. Having had experience on implementing SagePay for simple websites as well as development for NopCommerce I realised it should not be too difficult to marry the systems. Generally there are 3 components to the system 1) The Payment Class 2) The Notification Class 3) The Database What the system does is that it creates a payment Item that then spins out to SagePay directly, The Notification class is an intermediate website that communicates back and forward to enable SagePay to give us its transaciton information and weather or not a payment passed for failed. After this it then redirects to the appropriate page. It is a really complicated project and I will hopefully write about it later and explain more, However for now feel free to browse my source code directly on SourceForge to gain an overview of what it does.

Remove ASP.NET Membership Database

Have you ever tried to remove the base aspnet_[table] and its associated views and stored procs if you have you probably know that its like a mine field to to remove each file in the correct order of its insertion.
I have realized that after some trial and error that there is a pattern to the table creation so we simply reverse this. To run the drop statements.
*WARNING* - Backup your database, I am in no way responsible for any idiots who incur data loss because of running this script

DROP VIEW vw_aspnet_Applications
DROP VIEW vw_aspnet_MembershipUsers
DROP VIEW vw_aspnet_Profiles
DROP VIEW vw_aspnet_Roles
DROP VIEW vw_aspnet_Users
DROP VIEW vw_aspnet_UsersInRoles
DROP VIEW vw_aspnet_WebPartState_Paths
DROP VIEW vw_aspnet_WebPartState_Shared
DROP VIEW vw_aspnet_WebPartState_User
 
DROP PROCEDURE aspnet_AnyDataInTables
DROP PROCEDURE aspnet_Applications_CreateApplication
DROP PROCEDURE aspnet_CheckSchemaVersion
DROP PROCEDURE aspnet_Membership_ChangePasswordQuestionAndAnswer
DROP PROCEDURE aspnet_Membership_CreateUser
DROP PROCEDURE aspnet_Membership_FindUsersByEmail
DROP PROCEDURE aspnet_Membership_FindUsersByName
DROP PROCEDURE aspnet_Membership_GetAllUsers
DROP PROCEDURE aspnet_Membership_GetNumberOfUsersOnline
DROP PROCEDURE aspnet_Membership_GetPassword
DROP PROCEDURE aspnet_Membership_GetPasswordWithFormat
DROP PROCEDURE aspnet_Membership_GetUserByEmail
DROP PROCEDURE aspnet_Membership_GetUserByName
DROP PROCEDURE aspnet_Membership_GetUserByUserId
DROP PROCEDURE aspnet_Membership_ResetPassword
DROP PROCEDURE aspnet_Membership_SetPassword
DROP PROCEDURE aspnet_Membership_UnlockUser
DROP PROCEDURE aspnet_Membership_UpdateUser
DROP PROCEDURE aspnet_Membership_UpdateUserInfo
DROP PROCEDURE aspnet_Paths_CreatePath
DROP PROCEDURE aspnet_Personalization_GetApplicationId
DROP PROCEDURE aspnet_PersonalizationAdministration_DeleteAllState
DROP PROCEDURE aspnet_PersonalizationAdministration_FindState
DROP PROCEDURE aspnet_PersonalizationAdministration_GetCountOfState
DROP PROCEDURE aspnet_PersonalizationAdministration_ResetSharedState
DROP PROCEDURE aspnet_PersonalizationAdministration_ResetUserState
DROP PROCEDURE aspnet_PersonalizationAllUsers_GetPageSettings
DROP PROCEDURE aspnet_PersonalizationAllUsers_ResetPageSettings
DROP PROCEDURE aspnet_PersonalizationAllUsers_SetPageSettings
DROP PROCEDURE aspnet_PersonalizationPerUser_GetPageSettings
DROP PROCEDURE aspnet_PersonalizationPerUser_ResetPageSettings
DROP PROCEDURE aspnet_PersonalizationPerUser_SetPageSettings
DROP PROCEDURE aspnet_Profile_DeleteInactiveProfiles
DROP PROCEDURE aspnet_Profile_DeleteProfiles
DROP PROCEDURE aspnet_Profile_GetNumberOfInactiveProfiles
DROP PROCEDURE aspnet_Profile_GetProfiles
DROP PROCEDURE aspnet_Profile_GetProperties
DROP PROCEDURE aspnet_Profile_SetProperties
DROP PROCEDURE aspnet_RegisterSchemaVersion
DROP PROCEDURE aspnet_Roles_CreateRole
DROP PROCEDURE aspnet_Roles_DeleteRole
DROP PROCEDURE aspnet_Roles_GetAllRoles
DROP PROCEDURE aspnet_Roles_RoleExists
DROP PROCEDURE aspnet_Setup_RemoveAllRoleMembers
DROP PROCEDURE aspnet_Setup_RestorePermissions
DROP PROCEDURE aspnet_UnRegisterSchemaVersion
DROP PROCEDURE aspnet_Users_CreateUser
DROP PROCEDURE aspnet_Users_DeleteUser
DROP PROCEDURE aspnet_UsersInRoles_AddUsersToRoles
DROP PROCEDURE aspnet_UsersInRoles_FindUsersInRole
DROP PROCEDURE aspnet_UsersInRoles_GetRolesForUser
DROP PROCEDURE aspnet_UsersInRoles_GetUsersInRoles
DROP PROCEDURE aspnet_UsersInRoles_IsUserInRole
DROP PROCEDURE aspnet_UsersInRoles_RemoveUsersFromRoles
DROP PROCEDURE aspnet_WebEvent_LogEvent
 
 
DROP TABLE aspnet_WebEvent_Events
DROP TABLE aspnet_SchemaVersions
DROP TABLE aspnet_Profile
DROP TABLE aspnet_UsersInRoles
DROP TABLE aspnet_Roles
DROP TABLE aspnet_PersonalizationPerUser
DROP TABLE aspnet_PersonalizationAllUsers
DROP TABLE aspnet_Paths
DROP TABLE aspnet_Membership
DROP TABLE aspnet_Users
DROP TABLE aspnet_Applications
 
DROP SCHEMA aspnet_Membership_FullAccess
DROP SCHEMA aspnet_Membership_ReportingAccess
DROP SCHEMA aspnet_Personalization_FullAccess
DROP SCHEMA aspnet_Membership_BasicAccess
DROP SCHEMA aspnet_Personalization_BasicAccess
DROP SCHEMA aspnet_Personalization_ReportingAccess
DROP SCHEMA aspnet_Profile_BasicAccess
DROP SCHEMA aspnet_Profile_FullAccess
DROP SCHEMA aspnet_Profile_ReportingAccess
DROP SCHEMA aspnet_Roles_BasicAccess
DROP SCHEMA aspnet_Roles_FullAccess
DROP SCHEMA aspnet_Roles_ReportingAccess
DROP SCHEMA aspnet_WebEvent_FullAccess
 
 
DROP ROLE aspnet_Membership_FullAccess
DROP ROLE aspnet_Membership_ReportingAccess
DROP ROLE aspnet_Personalization_FullAccess
DROP ROLE aspnet_Membership_BasicAccess
DROP ROLE aspnet_Personalization_BasicAccess
DROP ROLE aspnet_Personalization_ReportingAccess
DROP ROLE aspnet_Profile_FullAccess
DROP ROLE aspnet_Profile_ReportingAccess
DROP ROLE aspnet_Roles_FullAccess
DROP ROLE aspnet_Roles_BasicAccess
DROP ROLE aspnet_Roles_ReportingAccess
DROP ROLE aspnet_WebEvent_FullAccess
DROP ROLE aspnet_Profile_BasicAccess

GeoLocation - Get Country of IP Address

Sometimes in projects we build we need to add a level of localization. E.g. Some customers access sites from the UK and others from Ireland. Each site offers a different selection of products and the only way to truly ensure a customer can see the correct products for their location is to create a user account.

Although the Mechanism for creating a user account is simple for browsing a website - Customers do find this confusing as one minute the products are there and the next they are not.

So In this case we need to identify the Country by IP, There are many reasons why this could potentially fail however on the whole it catches most potential customers.

First I create a link to http://freegeoip.net/json/ and I append the IP Address onto the end of this.

private static readonly string GeolocationUrl = "http://freegeoip.net/json/212.58.244.66";

From this we get a JSON String that gives us the information pertaining to this IP Address.

{"ip":"212.58.244.66","country_code":"GB","country_name":"United Kingdom","region_code":"N7","region_name":"Surrey","city":"Tadworth","zipcode":"","latitude":51.2833,"longitude":-0.2333,"metro_code":"","area_code":""}

This indeed gives us a Country Code and Country Name from which we can parse and use for our application. The following method below is sufficient to return us an object called 'IpInformationLocation' The 'IpInformationLocation' object is rather simple, Its an object that we can use to build our data.

[Serializable]
public class IpLocationInformation
{
    [DefaultValue("")]
    public string IpAddress { get; set; }

    [DefaultValue("")]
    public string CountryCode { get; set; }

    [DefaultValue("")]
    public string CountryName { get; set; }

    [DefaultValue("")]
    public string RegionCode { get; set; }

        [DefaultValue("")]
        public string RegionName { get; set; }

        [DefaultValue("")]
        public string City { get; set; }

        [DefaultValue("")]
        public string ZipCode { get; set; }

        [DefaultValue("")]
        public string Latitude { get; set; }

        [DefaultValue("")]
        public string Longitude { get; set; }

        public IpLocationInformation() { }

        public IpLocationInformation(string json)
        {
            JObject jObject = JObject.Parse(json);

            //JToken jDataBlock = jObject[""];

            IpAddress = jObject["ip"].ToString();

            CountryCode = jObject["country_code"].ToString();
            CountryName = jObject["country_name"].ToString();
            RegionCode = jObject["region_code"].ToString();
            RegionName = jObject["region_name"].ToString();
            City = jObject["city"].ToString();
            ZipCode = jObject["zipcode"].ToString();
            Latitude = jObject["latitude"].ToString();
            Longitude = jObject["longitude"].ToString();
        }
        public override string ToString()
        {
            string theReturn = string.Format("@\n\n" +
                                             "IP Address: {0}\n" +
                                             "Country Code: {1}\n" +
                                             "Country Name: {2}\n" +
                                             "RegionCode: {3}\n" +
                                             "City: {4}\n" +
                                             "ZipCode: {5}\n" +
                                             "Latitude: {6}\n" +
                                             "Longitude: {7}\n\n", new object[] {IpAddress, CountryCode, CountryName, RegionCode, City, ZipCode,Latitude,Longitude});
            return theReturn;
        }
    }

We then create a method that uses the website above, Adds and IP Address then generates an IpLocationInformation object. We do this like so.

public IpLocationInformation GetLocationByIp(string ipAddress)

        {

            try

            {

                string formattedUri = string.Format(CultureInfo.InvariantCulture, GeolocationUrl, ipAddress);



                var webRequest = GetWebRequest(formattedUri);

                var response = (HttpWebResponse)webRequest.GetResponse();



                var jsonResponse = string.Empty;



                var stream = response.GetResponseStream();

                if (stream != null)

                {

                    var reader = new StreamReader(stream);



                    jsonResponse = reader.ReadToEnd();

                }



                var ipLocation = new IpLocationInformation(jsonResponse);



                return ipLocation;

            }

            catch (Exception)

            {

                throw new WebException("There was a problem processing this Request.  Please Check the Information which you are passing.");

            }

        }

After this we are able to use the method to deduce the location of the calling IP Address which can then give the user a customized version of the site.

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

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.

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.