Figure 4-5: Data Range report in Visual Studio .NET

Painting QR Code 2d barcode in Visual Studio .NET Figure 4-5: Data Range report

Brought to you by ownSky! 26

The SQL Server database contains two tables for this solution The first is the AccountTransactions table This table contains all the debit and credit entries for the user's account The table is in a one-to-many relationship with the Categories table Each of the transactions goes in a particular category, but each category can be used by many transactions

The other table in the SQL Server database is the Categories table This table contains the list of all the categories that a transaction item can be in

On The CD-ROM AccountTransactionstxt The field specifications for the AccountTransactions table are displayed in Table 4-1

Field Name AccountTransactionID TransactionDate TransDescription CategoryID Amount TransNote Field Type int datetime varchar int money varchar Length = 200 Length = 50 Foreign Key Notes Primary Key, Identity Column

The AccountTransactionID field is the primary key for this table It uniquely identifies each of the records It is an identity column, so it is automatically populated when a new record is added The TransactionDate field stores the date of the transaction The TransDescription field stores a brief name or title for the transaction, which is displayed in the ledger The CategoryID field is a foreign key that links this table with the Categories table The Amount field stores the dollar amount for the debit or credit And the TransNote field allows for a lengthier note regarding the transaction

On The CD-ROM Categoriestxt The field specifications for the Categories table are displayed in Table 4-2

Field Name CategoryID CategoryName Field Type int varchar Notes Primary Key, Identity Column Length = 50

The CategoryID field is the primary key in this table The other field, CategoryName, stores the name of the category

The AccountTransactionsAdd stored procedure provides the mechanism for adding a new record to the AccountTransactions table CREATE PROCEDURE AccountTransactionsAdd @TransactionDate datetime, @TransDescription varchar(50),

Brought to you by ownSky! 27

@CategoryID integer, @Amount money, @TransNote varchar(2000) AS BEGIN Insert Into AccountTransactions (TransactionDate, TransDescription, CategoryID, Amount, TransNote) values (@TransactionDate, @TransDescription, @CategoryID, @Amount, @TransNote) Select @@Identity as TheNewID END GO The procedure requires five input parameters Each of these parameters contains the data for that field to be inserted into the new record: @TransactionDate datetime, @TransDescription varchar(50), @CategoryID integer, @Amount money, @TransNote varchar(2000) You then use a SQL Insert statement to add a new record to the AccountTransactions table: Insert Into AccountTransactions (TransactionDate, TransDescription, CategoryID, Amount, TransNote) values (@TransactionDate, @TransDescription, @CategoryID, @Amount, @TransNote) The procedure returns the AccountTransactionID of the record that was just added: Select @@Identity as TheNewID

The calling application uses the AccountTransactionUpdate stored procedure to edit an existing record CREATE PROCEDURE AccountTransactionsUpdate @AccountTransactionID integer, @TransactionDate datetime, @TransDescription varchar(50), @CategoryID integer, @Amount money, @TransNote varchar(2000) AS BEGIN Update AccountTransactions set TransactionDate = @TransactionDate, TransDescription = @TransDescription, CategoryID = @CategoryID, Amount = @Amount, TransNote = @TransNote Where AccountTransactionID = @AccountTransactionID END GO The first parameter passed into the stored procedure contains the ID of the record to be edited: @AccountTransactionID integer, The other parameters passed in are the new values for the existing record: @TransactionDate datetime,

Brought to you by ownSky! 28

@TransDescription varchar(50), @CategoryID integer, @Amount money, @TransNote varchar(2000) You then use a SQL Update statement to update the desired record: Update AccountTransactions set TransactionDate = @TransactionDate, TransDescription = @TransDescription, CategoryID = @CategoryID, Amount = @Amount, TransNote = @TransNote Where AccountTransactionID = @AccountTransactionID

The AccountTransactionsDelete stored procedure provides the mechanism for removing a transaction record from the AccountTransactions table CREATE PROCEDURE AccountTransactionsDelete @AccountTransactionID integer AS BEGIN Delete from END GO The procedure requires a single parameter, the ID of the record that is to be deleted: @AccountTransactionID integer That record is then removed from the AccountTransactions table: Delete from AccountTransactions Where AccountTransactionID = @AccountTransactionID AccountTransactions Where AccountTransactionID = @AccountTransactionID