[VFP] CursorAdapter 使用 Store Procedure

跟同事討論時突然想到，VFP 都是透過 SPT 來呼叫 SP，所以直覺是 CursorAdapter 無法做到，能呼叫 SP 的話，可以把商業邏輯封在 SP 內，可以解決不少麻煩，查資料發現是可以的，弄個小範例來記錄重點

SQL Server 內相關設定

在 TempDB 內建立 Employee Table

USE [tempdb]
GO

CREATE TABLE [dbo].[Employee](
  [UserID] [int] IDENTITY(1,1) NOT NULL Primary Key,
  [UserName] [char](20) NULL,
  [HireDate] [datetime] NULL,
  [Age] [tinyint] NULL)
GO

INSERT INTO Employee (UserName , HireDate , Age)
  VALUES('張三' , '2019-06-01' , 30)
INSERT INTO Employee (UserName , HireDate , Age)
  VALUES('李四' , '2018-12-11' , 25) 

建立 Insert、Update 和 Delete Store Procedure

-- Insert
CREATE PROCEDURE [dbo].[dmlEmployee_Insert]
(
  @UserName char(10) ,
  @HireDate datetime ,
  @Age int ,
  @UserID int output
)
AS
BEGIN

  SET NOCOUNT ON;

  INSERT INTO Employee (UserName , HireDate , Age)
   VALUES(@UserName , @HireDate , @Age)

  SET @UserID = SCOPE_IDENTITY()
END
GO

-- Update
CREATE PROCEDURE [dbo].[dmlEmployee_Update]
(
  @UserID int ,
  @UserName char(10) ,
  @HireDate datetime ,
  @Age int
)
AS
BEGIN

  SET NOCOUNT ON;

  UPDATE Employee
  SET UserName = @UserName , 
   HireDate = @HireDate , 
   Age = @Age
  WHERE UserID = @UserID
END
GO

-- Delete
CREATE PROCEDURE [dbo].[dmlEmployee_Delete]
(
  @UserID int
)
AS
BEGIN

  SET NOCOUNT ON;

  DELETE FROM Employee WHERE UserID = @UserID
END
GO

CursorAdapter 設定

AutoUpdate Tag => Advanced Button

覆蓋自動產生的 insert、update 和 delete 語法

利用 SQL Profile 來側錄 insert、update 和 delete 語法，確定有使用 SP