Real Test Driven Development – Thinking of Money 5

 

We now have a library that supply’s all the required functionality to  add, subtract and display a balance, also display the history of each transaction.

we now need to decide how to save this data, I could simply serialize the collection and balance to an xml file, but I want to use this data in the future, on the next project where I can budget using known data from the past.

ok so it seams that a sql database would be in order. a simple table should do for the minute. along with the usual select, insert, update and delete stored procedures

 

USE [MoneyEngine]
GO
/****** Object:  Table [dbo].[tTransactions]    Script Date: 01/01/2007 20:28:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tTransactions](
    [TransID] [bigint] IDENTITY(1,1) NOT NULL,
    [TransType] [int] NOT NULL,
    [TransDate] [datetime] NOT NULL,
    [TransCategory] [nvarchar](max) NULL,
    [TransDescription] [nvarchar](max) NULL,
    [TransAmount] [money] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

.csharpcode, .csharpcode pre {font-size:small;color:black;font-family:consolas, "Courier New", courier, monospace;background-color:#ffffff;} .csharpcode pre {margin:0em;} .csharpcode .rem {color:#008000;} .csharpcode .kwrd {color:#0000ff;} .csharpcode .str {color:#006080;} .csharpcode .op {color:#0000c0;} .csharpcode .preproc {color:#cc6633;} .csharpcode .asp {background-color:#ffff00;} .csharpcode .html {color:#800000;} .csharpcode .attr {color:#ff0000;} .csharpcode .alt {background-color:#f4f4f4;width:100%;margin:0em;} .csharpcode .lnum {color:#606060;}

 

USE [MoneyEngine]
GO
/****** Object:  StoredProcedure [dbo].[uspSelectTransactionsDateOrder]    Script Date: 01/01/2007 20:29:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Duncan
-- Create date: 1/1/2007
-- Description:    Select all transactions
-- =============================================
CREATE PROCEDURE [dbo].[uspSelectTransactionsDateOrder] 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT TransID, TransType, TransDate, TransCategory, TransDescription, TransAmount

    FROM tTransactions

    ORDER BY TransDate
END

.csharpcode, .csharpcode pre {font-size:small;color:black;font-family:consolas, "Courier New", courier, monospace;background-color:#ffffff;} .csharpcode pre {margin:0em;} .csharpcode .rem {color:#008000;} .csharpcode .kwrd {color:#0000ff;} .csharpcode .str {color:#006080;} .csharpcode .op {color:#0000c0;} .csharpcode .preproc {color:#cc6633;} .csharpcode .asp {background-color:#ffff00;} .csharpcode .html {color:#800000;} .csharpcode .attr {color:#ff0000;} .csharpcode .alt {background-color:#f4f4f4;width:100%;margin:0em;} .csharpcode .lnum {color:#606060;}

 

USE [MoneyEngine]
GO
/****** Object:  StoredProcedure [dbo].[uspInsertTransaction]    Script Date: 01/01/2007 20:39:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Duncan
-- Create date: 1/1/2007
-- Description:    insert a new transaction
-- =============================================
CREATE PROCEDURE [dbo].[uspInsertTransaction] 
    -- Add the parameters for the stored procedure here
    @TransType            INT,
    @TransDate            DATETIME,
    @TransCategory        NVARCHAR(MAX),
    @TransDescription    NVARCHAR(MAX),
    @TransAmount        MONEY,
    @TransID            BIGINT OUTPUT

AS
BEGIN
    INSERT INTO tTransactions(TransType, TransDate, TransCategory, TransDescription, TransAmount)
    VALUES(@TransType, @TransDate, @TransCategory, @TransDescription, @TransAmount)

    SET @TransID = @@IDENTITY
END
USE [MoneyEngine]
GO
/****** Object:  StoredProcedure [dbo].[uspUpdateTransaction]    Script Date: 01/01/2007 20:41:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Duncan
-- Create date: 1/1/2007
-- Description:    Update a transaction
-- =============================================
CREATE PROCEDURE [dbo].[uspUpdateTransaction] 
    -- Add the parameters for the stored procedure here
    @TransID            BIGINT,
    @TransType            INT,
    @TransDate            DATETIME,
    @TransCategory        NVARCHAR(MAX),
    @TransDescription    NVARCHAR(MAX),
    @TransAmount        MONEY

AS
BEGIN

    UPDATE tTransactions

    SET TransType = @TransType,
        TransDate = @TransDate,
        TransCategory = @TransCategory,
        TransDescription = @TransDescription,
        TransAmount = @TransAmount

    WHERE TransID = @TransID
        
END
USE [MoneyEngine]
GO
/****** Object:  StoredProcedure [dbo].[uspDeleteTransaction]    Script Date: 01/01/2007 20:53:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Duncan
-- Create date: 1/1/2007
-- Description:    Delete a transaction
-- =============================================
CREATE PROCEDURE [dbo].[uspDeleteTransaction] 
    -- Add the parameters for the stored procedure here
    @TransID INT 

AS
BEGIN
    DELETE 

    FROM tTransactions
    
    WHERE TransID = @TransID
END
Of cause each of these stored procedures will need to be given permission to execute, and then its back to the 
code to write the data access layer.
 
Advertisements

About Duncan Butler

Trying to be a very agile software developer, working in C# with Specflow, Nunit and Machine Specifications, and in the evening having fun with Ruby and Rails
This entry was posted in Projects. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s