Here's the field mappings for the two sql tables that ASP.NET storefront requires and the SQL stored procedure to accomplish the task.
The two SQL tables that require mapping are: aspdnsf_tcustomer and aspdnsf_address
The stored procedure is:
create procedure usp_DR_ASPDOTNETSTOREFRONT_InsertSQL
(@UserID int,@PortalID int,@URLReferrer nvarchar(1000),@IPAddress nvarchar(20),
@Username nvarchar(200),@Firstname nvarchar(50),@Lastname nvarchar(50),
@Email nvarchar(200),@Howdid nvarchar(50),@Phone nvarchar(20),@OKto nvarchar(5),
@FirstnameBill nvarchar(50),@LastnameBill nvarchar(50),@PhoneBill nvarchar(20),
@CompanyBill nvarchar(200),@AddressTypeBill nvarchar(50),@Address1Bill nvarchar(200),
@Address2Bill nvarchar(200),@SuiteBill nvarchar(100),@CityBill nvarchar(100),
@CountryBill nvarchar(100),@ZipBill nvarchar(15),@StateBill nvarchar(50),
@Sameas nvarchar(5), @FirstnameShip nvarchar(50),@LastnameShip nvarchar(50),
@PhoneShip nvarchar(20),@CompanyShip nvarchar(200),@AddressTypeShip nvarchar(50),
@Address1Ship nvarchar(200),@Address2Ship nvarchar(200),@SuiteShip nvarchar(100),
@CityShip nvarchar(100),@CountryShip nvarchar(100),@ZipShip nvarchar(15),@StateShip nvarchar(50))
as
begin
declare @OkayToEmail int
declare @BillingEqualsShipping int
declare @FullnameBill nvarchar(200)
declare @FullnameShip nvarchar(200)
declare @BillingAddressID int
declare @ShippingAddressID int
declare @CustomerID int
set @FullnameBill = @FirstnameBill + ' ' + @LastnameBill
set @FullnameShip = @FirstnameShip + ' ' + @LastnameShip
if (@OKto = 'Yes') set @OkayToEmail = 1 else set @OkayToEmail = 0
if (@Sameas = 'Yes') set @BillingEqualsShipping = 1 else set @BillingEqualsShipping = 0
-- check if customer already exists based on UserID, if not insert new record
if not exists (select userid from aspdnsf_tcustomer where userid = @UserID)
begin
-- insert into ASPDOTNETSTOREFRONT aspdnsf_tcustomer SQL table
insert into aspdnsf_tcustomer(CustomerGUID,CustomerLevelID,RegisterDate,Phone,AffiliateID,Referrer,OkToEmail,
BillingEqualsShipping,LastIPAddress,LocaleSetting,RecurringShippingMethodID,BillingAddressID,ShippingAddressID,
GiftRegistryGUID,GiftRegistryIsAnonymous,GiftRegistryAllowSearchByOthers,GiftRegistryHideShippingAddresses,
CODCompanyCheckAllowed,CODNet30Allowed,Deleted,CreatedOn,Over13Checked,VATSetting,StoreCCInDB,IsRegistered,
AdminCanViewCC,Active,UserID,PortalID)
values(newid(),0,getdate(),@Phone,0,@URLReferrer,@OkayToEmail,@BillingEqualsShipping,@IPAddress,'en-US',1,@BillingAddressID,@ShippingAddressID,
newid(),1,1,1,0,0,0,getdate(),0,0,1,1,1,1,@UserID,@PortalID)
set @CustomerID = (select scope_identity())
-- insert into ASPDOTNETSTOREFRONT aspdnsf_address SQL table for Billing
insert into aspdnsf_address(AddressGUID,CustomerID,FirstName,LastName,Company,Address1,Address2,Suite,City,
State,Zip,Country,ResidenceType,Phone,Email,CardName,eCheckBankAccountName,Deleted,CreatedOn,Crypt,PortalID)
values(newid(),@CustomerID,@FirstnameBill,@LastnameBill,@CompanyBill,@Address1Bill,@Address2Bill,@SuiteBill,@CityBill,
@StateBill,@ZipBill,@CountryBill,@AddressTypeBill,@PhoneBill,@Email,@FullnameBill,@FullnameBill,0,getdate(),0,@PortalID)
set @BillingAddressID = (select scope_identity())
-- insert into ASPDOTNETSTOREFRONT aspdnsf_address SQL table for Shipping
insert into aspdnsf_address(AddressGUID,CustomerID,FirstName,LastName,Company,Address1,Address2,Suite,City,
State,Zip,Country,ResidenceType,Phone,Email,CardName,eCheckBankAccountName,Deleted,CreatedOn,Crypt,PortalID)
values(newid(),@CustomerID,@FirstnameShip,@LastnameShip,@CompanyShip,@Address1Ship,@Address2Ship,@SuiteShip,@CityShip,
@StateShip,@ZipShip,@CountryShip,@AddressTypeShip,@PhoneShip,@Email,@FullnameShip,@FullnameShip,0,getdate(),0,@PortalID)
set @ShippingAddressID = (select scope_identity())
-- now update aspdnsf_tcustomer table with the corresponding BillingAddressID / ShippingAddressID
update aspdnsf_tcustomer set BillingAddressID = @BillingAddressID, ShippingAddressID = @ShippingAddressID
where CustomerID = @CustomerID
end
-- update record instead
else if exists (select userid from aspdnsf_tcustomer where userid = @UserID)
begin
declare @UpdateBillingAddressID int
declare @UpdateShippingAddressID int
declare @UpdateCustomerID int
set @UpdateBillingAddressID = (select BillingAddressID from aspdnsf_tcustomer where userid = @UserID)
set @UpdateShippingAddressID = (select ShippingAddressID from aspdnsf_tcustomer where userid = @UserID)
set @UpdateCustomerID = (select CustomerID from aspdnsf_tcustomer where userid = @UserID)
-- update tcustomer table
update aspdnsf_tcustomer set Phone = @Phone, Referrer = @URLReferrer,OkToEmail = @OkayToEmail,
LastIPAddress = @IPAddress, BillingEqualsShipping = @BillingEqualsShipping where userid = @UserID
-- update billing record
update aspdnsf_address set FirstName = @FirstnameBill, LastName = @LastnameBill,Company = @CompanyBill,
Address1 = @Address1Bill, Address2 = @Address2Bill, Suite = @SuiteBill, City = @CityBill,
State = @StateBill, Zip = @ZipBill, Country = @CountryBill, ResidenceType = @AddressTypeBill,
Phone = @PhoneBill, Email = @Email, CardName = @FullnameBill, eCheckBankAccountName = @FullnameBill,
PortalID= @PortalID where AddressID = @UpdateBillingAddressID and CustomerID = @UpdateCustomerID
-- update shipping record
update aspdnsf_address set FirstName = @FirstnameShip, LastName = @LastnameShip,Company = @CompanyShip,
Address1 = @Address1Ship, Address2 = @Address2Ship, Suite = @SuiteShip, City = @CityShip,
State = @StateShip, Zip = @ZipShip, Country = @CountryShip, ResidenceType = @AddressTypeShip,
Phone = @PhoneShip, Email = @Email, CardName = @FullnameShip, eCheckBankAccountName = @FullnameShip,
PortalID= @PortalID where AddressID = @UpdateShippingAddressID and CustomerID = @UpdateCustomerID
end
end
-- David