Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Developing Next Generation Smart Clients using .NET 2.0 working with Existing .NET 1.1 SOA-based XML Web Services

, 16 Aug 2005
Comprehensive guide to development of .NET 2.0 Smart Clients working with existing Service Oriented Architecture based XML web services, fully utilizing the Enterprise Library
smartclientsoa_src.zip
SmartClient
Client
SMI.App
Controls
Forms
Modules
Properties
app.manifest
Settings.settings
Resources
240.png
Bottom.png
BuilderDialog_delete.bmp
DarkBlue.bmp
download-xp.gif
Earth.png
fldropen.png
iSync.gif
Leonardo_da_Vinci_My_Computer.png
LightBlue.bmp
LoginBox.jpg
LoginButton.jpg
LoginButton1.jpg
LoginButton2.jpg
Logo1.png
LogoPic.jpg
Middle.png
Refresh.bmp
refresh.ico
refresh1.ico
RightArrow.bmp
SuccessComplete.bmp
Task.bmp
Tasks.png
Top.png
TurnOffButton.bmp
wi0062-32.ico
wi0063-32.ico
wi0064-16.ico
wi0064-32.ico
SmartInstitute.App.csproj.user
wi0064-32.ico
SMI.Automation
Commands
Course
Framework
Security
Students
UI
Controls
Documents
MiscDocuments
StudentDocuments
Factories
Forms
Helpers
Misc
Properties
Settings.settings
Resources
BuilderDialog_delete.bmp
LogoSmall.bmp
Misho.jpg
My Pic 7.jpg
table.ico
SmartInstitute.Automation.csproj.user
Web References
SmartInstituteServices.AccountService
Account.datasource
AccountService.disco
AccountService.wsdl
Reference.map
SmartInstituteServices.CourseService
Course.datasource
CourseService.disco
CourseService.wsdl
Reference.map
SmartInstituteServices.SecurityService
Reference.map
SecurityService.disco
SecurityService.wsdl
SmartInstituteServices.StudentService
Reference.map
Student.datasource
StudentService.disco
StudentService.wsdl
SMI.ObjectModel.Enhanced
Base
Models
Properties
Settings.settings
Settings
SmartInstitute.ObjectModel.csproj.user
UMS.ObjectModel.Enhanced.csproj.user
SMI.Objects
SmartInstitute.csproj.user
CodeSmith
Tier
BusinessLogicLayer
Entity.cst
EntityBase.cst
EntityCollection.cst
EntityCollectionBase.cst
Enum.cst
IEntity.cst
DataAccessLayer
App.config.cst
CommonSql.cst
Configuration.cst
DBConcurrencyException.cst
DBException.cst
Factories
EntityRepositoryFactory.cst
IEntityRepository.cst
SqlClient
CVS
Entries
Entries.Extra
Entries.Extra.Old
Entries.Old
Repository
Root
TortoiseCVS.Status
SqlEntityRepository.cst
SqlEntityRepositoryBase.cst
TransactionManager.cst
UnitTests
EntityRepositoryTest.cst
WebServiceClient
WsEntityRepository.cst
WsEntityRepositoryBase.cst
Main.cst
nant.cst
References
GotDotNet.ApplicationBlocks.Data.dll
Microsoft.ApplicationBlocks.Data.dll
nunit.framework.dll
StoredProcedures.cst
vsnet2003.project.cst
vsnet2003.solution.cst
vsnet2005.project.cst
vsnet2005.solution.cst
WebService
Web.config.cst
WebService.cst
Database
SmartInstitute.bak
Library
Microsoft.Practices.EnterpriseLibrary.Caching.dll
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Configuration.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.dll
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.dll
Microsoft.Practices.EnterpriseLibrary.Logging.dll
Microsoft.Practices.EnterpriseLibrary.Security.ActiveDirectory.dll
Microsoft.Practices.EnterpriseLibrary.Security.Cache.CachingStore.dll
Microsoft.Practices.EnterpriseLibrary.Security.Cryptography.dll
Microsoft.Practices.EnterpriseLibrary.Security.Database.Authentication.dll
Microsoft.Practices.EnterpriseLibrary.Security.Database.dll
Microsoft.Practices.EnterpriseLibrary.Security.dll
nunit.core.dll
nunit.framework.dll
Server
BLL
SmartInstitute.csproj.user
DAL
Factories
SmartInstitute.DataAccessLayer.csproj.user
SqlClient
Utilities
GotDotNet.ApplicationBlocks.Data.dll
Microsoft.ApplicationBlocks.Data.dll
nunit.framework.dll
UnitTests
SmartInstitute.build
SmartInstitute.Facade
FacadeTest
SmartInstitute.Facade.csproj.user
SmartInstitute.Test
App.ico
SmartInstitute.Test.csproj.user
SmartInstituteServices
Global.asax
log
SmartInstituteServices.csproj.webinfo
SQL
Setup
SecurityDatabase
SecurityDatabaseConsole
App.ico
SecurityDatabaseConsole.exe.manifest
SR.strings
Tests
Tools.SecurityDatabaseConsole.csproj.user
USE SmartInstitute
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


-- Drop the dbo.prc_Account_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Get_List
GO

-- Drop the dbo.prc_Account_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_GetPaged
GO

-- Drop the dbo.prc_Account_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Insert
GO

-- Drop the dbo.prc_Account_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Update
GO

-- Drop the dbo.prc_Account_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Delete
GO

-- Drop the dbo.prc_Account_GetByStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_GetByStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_GetByStudentID
GO

-- Drop the dbo.prc_Account_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Account table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_Get_List
AS

SELECT
	[ID],
	[StudentID],
	[TransactionDescription],
	[TransactionDate],
	[DebitAmount],
	[CreditAmount],
	[Balance],
	[ChangeStamp],
	[Type],
	[BankName],
	[CheckNumber],
	[ORNumber]
FROM
	dbo.[Account]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Account table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Account]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.StudentID, O.TransactionDescription, O.TransactionDate, O.DebitAmount, O.CreditAmount, O.Balance, O.ChangeStamp, O.Type, O.BankName, O.CheckNumber, O.ORNumber
FROM
    dbo.[Account] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Account table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_Insert
	@Balance money, 
	@BankName varchar(255), 
	@ChangeStamp smalldatetime, 
	@CheckNumber varchar(255), 
	@CreditAmount money, 
	@DebitAmount money, 
	@ORNumber varchar(255), 
	@StudentID int, 
	@TransactionDate datetime, 
	@TransactionDescription varchar(6000), 
	@Type int 
AS

declare @new_id int


INSERT INTO dbo.[Account] (
	[Balance],
	[BankName],
	[ChangeStamp],
	[CheckNumber],
	[CreditAmount],
	[DebitAmount],
	[ORNumber],
	[StudentID],
	[TransactionDate],
	[TransactionDescription],
	[Type]
) VALUES (
	@Balance,
	@BankName,
	@ChangeStamp,	
	@CheckNumber,
	@CreditAmount,
	@DebitAmount,
	@ORNumber,
	@StudentID,
	@TransactionDate,
	@TransactionDescription,
	@Type
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[StudentID],
	[TransactionDescription],
	[TransactionDate],
	[DebitAmount],
	[CreditAmount],
	[Balance],
	[ChangeStamp],
	[Type],
	[BankName],
	[CheckNumber],
	[ORNumber]
FROM dbo.[Account]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Account table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_Update
	@ID int, 
	@Balance money,
	@BankName varchar(255),
	@ChangeStamp smalldatetime,
	@CheckNumber varchar(255),
	@CreditAmount money,
	@DebitAmount money,
	@ORNumber varchar(255),
	@StudentID int,
	@TransactionDate datetime,
	@TransactionDescription varchar(6000),
	@Type int
	

AS

UPDATE dbo.[Account]
SET	
	[Balance] = @Balance,
	[BankName] = @BankName,
	[ChangeStamp] = @ChangeStamp,
	[CheckNumber] = @CheckNumber,
	[CreditAmount] = @CreditAmount,
	[DebitAmount] = @DebitAmount,
	[ORNumber] = @ORNumber,
	[StudentID] = @StudentID,
	[TransactionDate] = @TransactionDate,
	[TransactionDescription] = @TransactionDescription,
	[Type] = @Type
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[StudentID],
	[TransactionDescription],
	[TransactionDate],
	[DebitAmount],
	[CreditAmount],
	[Balance],
	[ChangeStamp],
	[Type],
	[BankName],
	[CheckNumber],
	[ORNumber]
FROM dbo.[Account]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Account table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[Account]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Account table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_GetByStudentID
	@StudentID int
AS

SELECT
	[ID],
	[StudentID],
	[TransactionDescription],
	[TransactionDate],
	[DebitAmount],
	[CreditAmount],
	[Balance],
	[ChangeStamp],
	[Type],
	[BankName],
	[CheckNumber],
	[ORNumber]
FROM
	dbo.[Account]
WHERE
	[StudentID] = @StudentID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Account table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Account_GetByID
	@ID int
AS

SELECT
	[ID],
	[StudentID],
	[TransactionDescription],
	[TransactionDate],
	[DebitAmount],
	[CreditAmount],
	[Balance],
	[ChangeStamp],
	[Type],
	[BankName],
	[CheckNumber],
	[ORNumber]
FROM
	[dbo].[Account]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_Assessment_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Get_List
GO

-- Drop the dbo.prc_Assessment_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_GetPaged
GO

-- Drop the dbo.prc_Assessment_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Insert
GO

-- Drop the dbo.prc_Assessment_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Update
GO

-- Drop the dbo.prc_Assessment_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Delete
GO

-- Drop the dbo.prc_Assessment_GetByStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_GetByStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_GetByStudentID
GO

-- Drop the dbo.prc_Assessment_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Assessment table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_Get_List
AS

SELECT
	[ID],
	[Serial],
	[StudentID],
	[ExamPermitNumber],
	[AdmissionFee],
	[TuitionFee],
	[ComputerLabFee],
	[ScienceLabFee],
	[ActivityFee],
	[DevelopmentFee],
	[MiscFee],
	[MedicalFee],
	[LibraryFee],
	[IDCardFee],
	[OtherFee1],
	[OtherFee1Desc],
	[OtherFee2],
	[OtherFee2Desc],
	[Discount],
	[Scholarship],
	[OtherDiscount],
	[OtherDiscountDesc],
	[RegistrationDate],
	[IsAdmissionFeePaid],
	[IsAnnualFeePaid],
	[PaymentType],
	[ScholarshipInPercent],
	[DiscountInPercent],
	[Currency],
	[SemesterOrder],
	[PreviousBalance],
	[ChangeStamp]
FROM
	dbo.[Assessment]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Assessment table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Assessment]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.Serial, O.StudentID, O.ExamPermitNumber, O.AdmissionFee, O.TuitionFee, O.ComputerLabFee, O.ScienceLabFee, O.ActivityFee, O.DevelopmentFee, O.MiscFee, O.MedicalFee, O.LibraryFee, O.IDCardFee, O.OtherFee1, O.OtherFee1Desc, O.OtherFee2, O.OtherFee2Desc, O.Discount, O.Scholarship, O.OtherDiscount, O.OtherDiscountDesc, O.RegistrationDate, O.IsAdmissionFeePaid, O.IsAnnualFeePaid, O.PaymentType, O.ScholarshipInPercent, O.DiscountInPercent, O.Currency, O.SemesterOrder, O.PreviousBalance, O.ChangeStamp
FROM
    dbo.[Assessment] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Assessment table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_Insert
	@ActivityFee money, 
	@AdmissionFee money, 
	@ChangeStamp smalldatetime, 
	@ComputerLabFee money, 
	@Currency int, 
	@DevelopmentFee money, 
	@Discount money, 
	@DiscountInPercent int, 
	@ExamPermitNumber varchar(10), 
	@IDCardFee money, 
	@IsAdmissionFeePaid bit, 
	@IsAnnualFeePaid bit, 
	@LibraryFee money, 
	@MedicalFee money, 
	@MiscFee money, 
	@OtherDiscount money, 
	@OtherDiscountDesc varchar(255), 
	@OtherFee1 money, 
	@OtherFee1Desc varchar(255), 
	@OtherFee2 money, 
	@OtherFee2Desc varchar(255), 
	@PaymentType int, 
	@PreviousBalance money, 
	@RegistrationDate datetime, 
	@Scholarship money, 
	@ScholarshipInPercent int, 
	@ScienceLabFee money, 
	@SemesterOrder int, 
	@Serial int, 
	@StudentID int, 
	@TuitionFee money 
AS

declare @new_id int


INSERT INTO dbo.[Assessment] (
	[ActivityFee],
	[AdmissionFee],
	[ChangeStamp],
	[ComputerLabFee],
	[Currency],
	[DevelopmentFee],
	[Discount],
	[DiscountInPercent],
	[ExamPermitNumber],
	[IDCardFee],
	[IsAdmissionFeePaid],
	[IsAnnualFeePaid],
	[LibraryFee],
	[MedicalFee],
	[MiscFee],
	[OtherDiscount],
	[OtherDiscountDesc],
	[OtherFee1],
	[OtherFee1Desc],
	[OtherFee2],
	[OtherFee2Desc],
	[PaymentType],
	[PreviousBalance],
	[RegistrationDate],
	[Scholarship],
	[ScholarshipInPercent],
	[ScienceLabFee],
	[SemesterOrder],
	[Serial],
	[StudentID],
	[TuitionFee]
) VALUES (
	@ActivityFee,
	@AdmissionFee,
	@ChangeStamp,	
	@ComputerLabFee,
	@Currency,
	@DevelopmentFee,
	@Discount,
	@DiscountInPercent,
	@ExamPermitNumber,
	@IDCardFee,
	@IsAdmissionFeePaid,
	@IsAnnualFeePaid,
	@LibraryFee,
	@MedicalFee,
	@MiscFee,
	@OtherDiscount,
	@OtherDiscountDesc,
	@OtherFee1,
	@OtherFee1Desc,
	@OtherFee2,
	@OtherFee2Desc,
	@PaymentType,
	@PreviousBalance,
	@RegistrationDate,
	@Scholarship,
	@ScholarshipInPercent,
	@ScienceLabFee,
	@SemesterOrder,
	@Serial,
	@StudentID,
	@TuitionFee
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[Serial],
	[StudentID],
	[ExamPermitNumber],
	[AdmissionFee],
	[TuitionFee],
	[ComputerLabFee],
	[ScienceLabFee],
	[ActivityFee],
	[DevelopmentFee],
	[MiscFee],
	[MedicalFee],
	[LibraryFee],
	[IDCardFee],
	[OtherFee1],
	[OtherFee1Desc],
	[OtherFee2],
	[OtherFee2Desc],
	[Discount],
	[Scholarship],
	[OtherDiscount],
	[OtherDiscountDesc],
	[RegistrationDate],
	[IsAdmissionFeePaid],
	[IsAnnualFeePaid],
	[PaymentType],
	[ScholarshipInPercent],
	[DiscountInPercent],
	[Currency],
	[SemesterOrder],
	[PreviousBalance],
	[ChangeStamp]
FROM dbo.[Assessment]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Assessment table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_Update
	@ID int, 
	@ActivityFee money,
	@AdmissionFee money,
	@ChangeStamp smalldatetime,
	@ComputerLabFee money,
	@Currency int,
	@DevelopmentFee money,
	@Discount money,
	@DiscountInPercent int,
	@ExamPermitNumber varchar(10),
	@IDCardFee money,
	@IsAdmissionFeePaid bit,
	@IsAnnualFeePaid bit,
	@LibraryFee money,
	@MedicalFee money,
	@MiscFee money,
	@OtherDiscount money,
	@OtherDiscountDesc varchar(255),
	@OtherFee1 money,
	@OtherFee1Desc varchar(255),
	@OtherFee2 money,
	@OtherFee2Desc varchar(255),
	@PaymentType int,
	@PreviousBalance money,
	@RegistrationDate datetime,
	@Scholarship money,
	@ScholarshipInPercent int,
	@ScienceLabFee money,
	@SemesterOrder int,
	@Serial int,
	@StudentID int,
	@TuitionFee money
	

AS

UPDATE dbo.[Assessment]
SET	
	[ActivityFee] = @ActivityFee,
	[AdmissionFee] = @AdmissionFee,
	[ChangeStamp] = @ChangeStamp,
	[ComputerLabFee] = @ComputerLabFee,
	[Currency] = @Currency,
	[DevelopmentFee] = @DevelopmentFee,
	[Discount] = @Discount,
	[DiscountInPercent] = @DiscountInPercent,
	[ExamPermitNumber] = @ExamPermitNumber,
	[IDCardFee] = @IDCardFee,
	[IsAdmissionFeePaid] = @IsAdmissionFeePaid,
	[IsAnnualFeePaid] = @IsAnnualFeePaid,
	[LibraryFee] = @LibraryFee,
	[MedicalFee] = @MedicalFee,
	[MiscFee] = @MiscFee,
	[OtherDiscount] = @OtherDiscount,
	[OtherDiscountDesc] = @OtherDiscountDesc,
	[OtherFee1] = @OtherFee1,
	[OtherFee1Desc] = @OtherFee1Desc,
	[OtherFee2] = @OtherFee2,
	[OtherFee2Desc] = @OtherFee2Desc,
	[PaymentType] = @PaymentType,
	[PreviousBalance] = @PreviousBalance,
	[RegistrationDate] = @RegistrationDate,
	[Scholarship] = @Scholarship,
	[ScholarshipInPercent] = @ScholarshipInPercent,
	[ScienceLabFee] = @ScienceLabFee,
	[SemesterOrder] = @SemesterOrder,
	[Serial] = @Serial,
	[StudentID] = @StudentID,
	[TuitionFee] = @TuitionFee
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[Serial],
	[StudentID],
	[ExamPermitNumber],
	[AdmissionFee],
	[TuitionFee],
	[ComputerLabFee],
	[ScienceLabFee],
	[ActivityFee],
	[DevelopmentFee],
	[MiscFee],
	[MedicalFee],
	[LibraryFee],
	[IDCardFee],
	[OtherFee1],
	[OtherFee1Desc],
	[OtherFee2],
	[OtherFee2Desc],
	[Discount],
	[Scholarship],
	[OtherDiscount],
	[OtherDiscountDesc],
	[RegistrationDate],
	[IsAdmissionFeePaid],
	[IsAnnualFeePaid],
	[PaymentType],
	[ScholarshipInPercent],
	[DiscountInPercent],
	[Currency],
	[SemesterOrder],
	[PreviousBalance],
	[ChangeStamp]
FROM dbo.[Assessment]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Assessment table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[Assessment]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Assessment table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_GetByStudentID
	@StudentID int
AS

SELECT
	[ID],
	[Serial],
	[StudentID],
	[ExamPermitNumber],
	[AdmissionFee],
	[TuitionFee],
	[ComputerLabFee],
	[ScienceLabFee],
	[ActivityFee],
	[DevelopmentFee],
	[MiscFee],
	[MedicalFee],
	[LibraryFee],
	[IDCardFee],
	[OtherFee1],
	[OtherFee1Desc],
	[OtherFee2],
	[OtherFee2Desc],
	[Discount],
	[Scholarship],
	[OtherDiscount],
	[OtherDiscountDesc],
	[RegistrationDate],
	[IsAdmissionFeePaid],
	[IsAnnualFeePaid],
	[PaymentType],
	[ScholarshipInPercent],
	[DiscountInPercent],
	[Currency],
	[SemesterOrder],
	[PreviousBalance],
	[ChangeStamp]
FROM
	dbo.[Assessment]
WHERE
	[StudentID] = @StudentID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Assessment table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Assessment_GetByID
	@ID int
AS

SELECT
	[ID],
	[Serial],
	[StudentID],
	[ExamPermitNumber],
	[AdmissionFee],
	[TuitionFee],
	[ComputerLabFee],
	[ScienceLabFee],
	[ActivityFee],
	[DevelopmentFee],
	[MiscFee],
	[MedicalFee],
	[LibraryFee],
	[IDCardFee],
	[OtherFee1],
	[OtherFee1Desc],
	[OtherFee2],
	[OtherFee2Desc],
	[Discount],
	[Scholarship],
	[OtherDiscount],
	[OtherDiscountDesc],
	[RegistrationDate],
	[IsAdmissionFeePaid],
	[IsAnnualFeePaid],
	[PaymentType],
	[ScholarshipInPercent],
	[DiscountInPercent],
	[Currency],
	[SemesterOrder],
	[PreviousBalance],
	[ChangeStamp]
FROM
	[dbo].[Assessment]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_Course_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Get_List
GO

-- Drop the dbo.prc_Course_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_GetPaged
GO

-- Drop the dbo.prc_Course_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Insert
GO

-- Drop the dbo.prc_Course_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Update
GO

-- Drop the dbo.prc_Course_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Delete
GO

-- Drop the dbo.prc_Course_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Course table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Course_Get_List
AS

SELECT
	[ID],
	[CourseCode],
	[Title],
	[SciCredit],
	[CompCredit],
	[LecCredit],
	[ChangeStamp]
FROM
	dbo.[Course]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Course table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Course_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Course]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.CourseCode, O.Title, O.SciCredit, O.CompCredit, O.LecCredit, O.ChangeStamp
FROM
    dbo.[Course] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Course table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Course_Insert
	@ChangeStamp smalldatetime, 
	@CompCredit int, 
	@CourseCode varchar(255), 
	@LecCredit int, 
	@SciCredit int, 
	@Title varchar(255) 
AS

declare @new_id int


INSERT INTO dbo.[Course] (
	[ChangeStamp],
	[CompCredit],
	[CourseCode],
	[LecCredit],
	[SciCredit],
	[Title]
) VALUES (
	@ChangeStamp,	
	@CompCredit,
	@CourseCode,
	@LecCredit,
	@SciCredit,
	@Title
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[CourseCode],
	[Title],
	[SciCredit],
	[CompCredit],
	[LecCredit],
	[ChangeStamp]
FROM dbo.[Course]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Course table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Course_Update
	@ID int, 
	@ChangeStamp smalldatetime,
	@CompCredit int,
	@CourseCode varchar(255),
	@LecCredit int,
	@SciCredit int,
	@Title varchar(255)
	

AS

UPDATE dbo.[Course]
SET	
	[ChangeStamp] = @ChangeStamp,
	[CompCredit] = @CompCredit,
	[CourseCode] = @CourseCode,
	[LecCredit] = @LecCredit,
	[SciCredit] = @SciCredit,
	[Title] = @Title
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[CourseCode],
	[Title],
	[SciCredit],
	[CompCredit],
	[LecCredit],
	[ChangeStamp]
FROM dbo.[Course]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Course table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Course_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[Course]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Course table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Course_GetByID
	@ID int
AS

SELECT
	[ID],
	[CourseCode],
	[Title],
	[SciCredit],
	[CompCredit],
	[LecCredit],
	[ChangeStamp]
FROM
	[dbo].[Course]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_CourseSection_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Get_List
GO

-- Drop the dbo.prc_CourseSection_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetPaged
GO

-- Drop the dbo.prc_CourseSection_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Insert
GO

-- Drop the dbo.prc_CourseSection_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Update
GO

-- Drop the dbo.prc_CourseSection_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Delete
GO

-- Drop the dbo.prc_CourseSection_GetByCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetByCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetByCourseID
GO

-- Drop the dbo.prc_CourseSection_GetByCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetByCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetByCourseID
GO

-- Drop the dbo.prc_CourseSection_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the CourseSection table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_Get_List
AS

SELECT
	[ID],
	[Title],
	[Description],
	[Capacity],
	[CourseID],
	[ClassID],
	[Status],
	[Type],
	[ChangeStamp]
FROM
	dbo.[CourseSection]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the CourseSection table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[CourseSection]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.Title, O.Description, O.Capacity, O.CourseID, O.ClassID, O.Status, O.Type, O.ChangeStamp
FROM
    dbo.[CourseSection] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the CourseSection table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_Insert
	@Capacity int, 
	@ChangeStamp smalldatetime, 
	@ClassID varchar(50), 
	@CourseID int, 
	@Description varchar(255), 
	@Status int, 
	@Title varchar(10), 
	@Type int 
AS

declare @new_id int


INSERT INTO dbo.[CourseSection] (
	[Capacity],
	[ChangeStamp],
	[ClassID],
	[CourseID],
	[Description],
	[Status],
	[Title],
	[Type]
) VALUES (
	@Capacity,
	@ChangeStamp,	
	@ClassID,
	@CourseID,
	@Description,
	@Status,
	@Title,
	@Type
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[Title],
	[Description],
	[Capacity],
	[CourseID],
	[ClassID],
	[Status],
	[Type],
	[ChangeStamp]
FROM dbo.[CourseSection]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the CourseSection table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_Update
	@ID int, 
	@Capacity int,
	@ChangeStamp smalldatetime,
	@ClassID varchar(50),
	@CourseID int,
	@Description varchar(255),
	@Status int,
	@Title varchar(10),
	@Type int
	

AS

UPDATE dbo.[CourseSection]
SET	
	[Capacity] = @Capacity,
	[ChangeStamp] = @ChangeStamp,
	[ClassID] = @ClassID,
	[CourseID] = @CourseID,
	[Description] = @Description,
	[Status] = @Status,
	[Title] = @Title,
	[Type] = @Type
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[Title],
	[Description],
	[Capacity],
	[CourseID],
	[ClassID],
	[Status],
	[Type],
	[ChangeStamp]
FROM dbo.[CourseSection]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the CourseSection table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[CourseSection]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseSection table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_GetByCourseID
	@CourseID int
AS

SELECT
	[ID],
	[Title],
	[Description],
	[Capacity],
	[CourseID],
	[ClassID],
	[Status],
	[Type],
	[ChangeStamp]
FROM
	dbo.[CourseSection]
WHERE
	[CourseID] = @CourseID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseSection table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseSection_GetByID
	@ID int
AS

SELECT
	[ID],
	[Title],
	[Description],
	[Capacity],
	[CourseID],
	[ClassID],
	[Status],
	[Type],
	[ChangeStamp]
FROM
	[dbo].[CourseSection]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_CourseTakenByStudent_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Get_List
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetPaged
GO

-- Drop the dbo.prc_CourseTakenByStudent_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Insert
GO

-- Drop the dbo.prc_CourseTakenByStudent_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Update
GO

-- Drop the dbo.prc_CourseTakenByStudent_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Delete
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetByCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByCourseID
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetBySectionID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetBySectionID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionID
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetByStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentID
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByID
GO

-- Drop the dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the CourseTakenByStudent table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Get_List
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	dbo.[CourseTakenByStudent]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the CourseTakenByStudent table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[CourseTakenByStudent]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.StudentID, O.CourseID, O.SectionID, O.Status, O.NonCredit, O.ChangeStamp
FROM
    dbo.[CourseTakenByStudent] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the CourseTakenByStudent table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Insert
	@ChangeStamp smalldatetime, 
	@CourseID int, 
	@NonCredit bit, 
	@SectionID int, 
	@Status int, 
	@StudentID int 
AS

declare @new_id int


INSERT INTO dbo.[CourseTakenByStudent] (
	[ChangeStamp],
	[CourseID],
	[NonCredit],
	[SectionID],
	[Status],
	[StudentID]
) VALUES (
	@ChangeStamp,	
	@CourseID,
	@NonCredit,
	@SectionID,
	@Status,
	@StudentID
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM dbo.[CourseTakenByStudent]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the CourseTakenByStudent table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Update
	@ID int, 
	@ChangeStamp smalldatetime,
	@CourseID int,
	@NonCredit bit,
	@SectionID int,
	@Status int,
	@StudentID int
	

AS

UPDATE dbo.[CourseTakenByStudent]
SET	
	[ChangeStamp] = @ChangeStamp,
	[CourseID] = @CourseID,
	[NonCredit] = @NonCredit,
	[SectionID] = @SectionID,
	[Status] = @Status,
	[StudentID] = @StudentID
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM dbo.[CourseTakenByStudent]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the CourseTakenByStudent table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[CourseTakenByStudent]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByCourseID
	@CourseID int
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	dbo.[CourseTakenByStudent]
WHERE
	[CourseID] = @CourseID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionID
	@SectionID int
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	dbo.[CourseTakenByStudent]
WHERE
	[SectionID] = @SectionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentID
	@StudentID int
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	dbo.[CourseTakenByStudent]
WHERE
	[StudentID] = @StudentID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID
	@SectionID int,
	@StudentID int
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	[dbo].[CourseTakenByStudent]
WHERE
	[SectionID] = @SectionID
	AND [StudentID] = @StudentID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByID
	@ID int
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	[dbo].[CourseTakenByStudent]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID
	@StudentID int,
	@CourseID int
AS

SELECT
	[ID],
	[StudentID],
	[CourseID],
	[SectionID],
	[Status],
	[NonCredit],
	[ChangeStamp]
FROM
	[dbo].[CourseTakenByStudent]
WHERE
	[StudentID] = @StudentID
	AND [CourseID] = @CourseID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_Permission_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Get_List
GO

-- Drop the dbo.prc_Permission_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_GetPaged
GO

-- Drop the dbo.prc_Permission_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Insert
GO

-- Drop the dbo.prc_Permission_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Update
GO

-- Drop the dbo.prc_Permission_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Delete
GO

-- Drop the dbo.prc_Permission_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Permission table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Permission_Get_List
AS

SELECT
	[ID],
	[Title],
	[Description],
	[ChangeStamp]
FROM
	dbo.[Permission]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Permission table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Permission_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Permission]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.Title, O.Description, O.ChangeStamp
FROM
    dbo.[Permission] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Permission table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Permission_Insert
	@ChangeStamp smalldatetime, 
	@Description varchar(255), 
	@ID int, 
	@Title varchar(50) 
AS

INSERT INTO dbo.[Permission](
	[ChangeStamp],
	[Description],
	[ID],
	[Title]
) VALUES (
	@ChangeStamp,	
	@Description,
	@ID,
	@Title
)

SELECT
	[ID],
	[Title],
	[Description],
	[ChangeStamp]
FROM dbo.[Permission]
WHERE
	([ID] = @ID)

GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Permission table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Permission_Update
		@ID int , 
		@ChangeStamp smalldatetime, 
		@Description varchar(255), 
		@Title varchar(50), 

	@OriginalID int	
AS

UPDATE dbo.[Permission]
SET	
		[ChangeStamp] = @ChangeStamp, 
		[Description] = @Description, 
		[Title] = @Title, 
		[ID] = @OriginalID	
WHERE
	[ID] = @OriginalID
	AND ChangeStamp = @ChangeStamp

SELECT
	[ID],
	[Title],
	[Description],
	[ChangeStamp]
FROM dbo.[Permission]
WHERE
	[ID] = @OriginalID
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Permission table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Permission_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[Permission]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Permission table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Permission_GetByID
	@ID int
AS

SELECT
	[ID],
	[Title],
	[Description],
	[ChangeStamp]
FROM
	[dbo].[Permission]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_SectionRoutine_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Get_List
GO

-- Drop the dbo.prc_SectionRoutine_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetPaged
GO

-- Drop the dbo.prc_SectionRoutine_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Insert
GO

-- Drop the dbo.prc_SectionRoutine_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Update
GO

-- Drop the dbo.prc_SectionRoutine_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Delete
GO

-- Drop the dbo.prc_SectionRoutine_GetBySectionID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetBySectionID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetBySectionID
GO

-- Drop the dbo.prc_SectionRoutine_GetBySectionID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetBySectionID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetBySectionID
GO

-- Drop the dbo.prc_SectionRoutine_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the SectionRoutine table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_Get_List
AS

SELECT
	[ID],
	[SectionID],
	[StartTime],
	[EndTime],
	[ClassType],
	[ChangeStamp]
FROM
	dbo.[SectionRoutine]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the SectionRoutine table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[SectionRoutine]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.SectionID, O.StartTime, O.EndTime, O.ClassType, O.ChangeStamp
FROM
    dbo.[SectionRoutine] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the SectionRoutine table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_Insert
	@ChangeStamp smalldatetime, 
	@ClassType int, 
	@EndTime int, 
	@SectionID int, 
	@StartTime int 
AS

declare @new_id int


INSERT INTO dbo.[SectionRoutine] (
	[ChangeStamp],
	[ClassType],
	[EndTime],
	[SectionID],
	[StartTime]
) VALUES (
	@ChangeStamp,	
	@ClassType,
	@EndTime,
	@SectionID,
	@StartTime
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[SectionID],
	[StartTime],
	[EndTime],
	[ClassType],
	[ChangeStamp]
FROM dbo.[SectionRoutine]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the SectionRoutine table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_Update
	@ID int, 
	@ChangeStamp smalldatetime,
	@ClassType int,
	@EndTime int,
	@SectionID int,
	@StartTime int
	

AS

UPDATE dbo.[SectionRoutine]
SET	
	[ChangeStamp] = @ChangeStamp,
	[ClassType] = @ClassType,
	[EndTime] = @EndTime,
	[SectionID] = @SectionID,
	[StartTime] = @StartTime
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[SectionID],
	[StartTime],
	[EndTime],
	[ClassType],
	[ChangeStamp]
FROM dbo.[SectionRoutine]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the SectionRoutine table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[SectionRoutine]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the SectionRoutine table through a foreign key
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_GetBySectionID
	@SectionID int
AS

SELECT
	[ID],
	[SectionID],
	[StartTime],
	[EndTime],
	[ClassType],
	[ChangeStamp]
FROM
	dbo.[SectionRoutine]
WHERE
	[SectionID] = @SectionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the SectionRoutine table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_SectionRoutine_GetByID
	@ID int
AS

SELECT
	[ID],
	[SectionID],
	[StartTime],
	[EndTime],
	[ClassType],
	[ChangeStamp]
FROM
	[dbo].[SectionRoutine]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




-- Drop the dbo.prc_Student_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Get_List
GO

-- Drop the dbo.prc_Student_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_GetPaged
GO

-- Drop the dbo.prc_Student_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Insert
GO

-- Drop the dbo.prc_Student_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Update
GO

-- Drop the dbo.prc_Student_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Delete
GO

-- Drop the dbo.prc_Student_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_GetByID
GO
		

		
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Student table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Student_Get_List
AS

SELECT
	[ID],
	[FirstName],
	[MiddleName],
	[LastName],
	[PresentAddress1],
	[PresentAddress2],
	[PermanentAddress1],
	[PermanentAddress2],
	[StudentID],
	[Status],
	[CGPA],
	[CreditCompleted],
	[DateAdmitted],
	[DateGraduated],
	[ChangeStamp]
FROM
	dbo.[Student]

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Student table passing page index and page count parameters
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Student_GetPaged
(
    @WhereClause varchar(2000) = NULL,
    @OrderBy varchar(2000) = NULL,
    @PageIndex int,
    @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ID] int
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Student]'
IF @WhereClause IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
 
IF @OrderBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.ID, O.FirstName, O.MiddleName, O.LastName, O.PresentAddress1, O.PresentAddress2, O.PermanentAddress1, O.PermanentAddress2, O.StudentID, O.Status, O.CGPA, O.CreditCompleted, O.DateAdmitted, O.DateGraduated, O.ChangeStamp
FROM
    dbo.[Student] O,
    #PageIndex PageIndex
WHERE
    O.ID = PageIndex.ID AND
    PageIndex.IndexID > @PageLowerBound AND
    PageIndex.IndexID < @PageUpperBound
ORDER BY
    PageIndex.IndexID

END
GO
	

----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Student table and returns the new PK
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Student_Insert
	@CGPA real, 
	@ChangeStamp smalldatetime, 
	@CreditCompleted int, 
	@DateAdmitted datetime, 
	@DateGraduated datetime, 
	@FirstName varchar(50), 
	@LastName varchar(50), 
	@MiddleName varchar(50), 
	@PermanentAddress1 varchar(255), 
	@PermanentAddress2 varchar(255), 
	@PresentAddress1 varchar(255), 
	@PresentAddress2 varchar(255), 
	@Status int, 
	@StudentID varchar(50) 
AS

declare @new_id int


INSERT INTO dbo.[Student] (
	[CGPA],
	[ChangeStamp],
	[CreditCompleted],
	[DateAdmitted],
	[DateGraduated],
	[FirstName],
	[LastName],
	[MiddleName],
	[PermanentAddress1],
	[PermanentAddress2],
	[PresentAddress1],
	[PresentAddress2],
	[Status],
	[StudentID]
) VALUES (
	@CGPA,
	@ChangeStamp,	
	@CreditCompleted,
	@DateAdmitted,
	@DateGraduated,
	@FirstName,
	@LastName,
	@MiddleName,
	@PermanentAddress1,
	@PermanentAddress2,
	@PresentAddress1,
	@PresentAddress2,
	@Status,
	@StudentID
)


set @new_id = SCOPE_IDENTITY()

SELECT
	[ID],
	[FirstName],
	[MiddleName],
	[LastName],
	[PresentAddress1],
	[PresentAddress2],
	[PermanentAddress1],
	[PermanentAddress2],
	[StudentID],
	[Status],
	[CGPA],
	[CreditCompleted],
	[DateAdmitted],
	[DateGraduated],
	[ChangeStamp]
FROM dbo.[Student]
WHERE
	[ID] = @new_id
GO

----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Student table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Student_Update
	@ID int, 
	@CGPA real,
	@ChangeStamp smalldatetime,
	@CreditCompleted int,
	@DateAdmitted datetime,
	@DateGraduated datetime,
	@FirstName varchar(50),
	@LastName varchar(50),
	@MiddleName varchar(50),
	@PermanentAddress1 varchar(255),
	@PermanentAddress2 varchar(255),
	@PresentAddress1 varchar(255),
	@PresentAddress2 varchar(255),
	@Status int,
	@StudentID varchar(50)
	

AS

UPDATE dbo.[Student]
SET	
	[CGPA] = @CGPA,
	[ChangeStamp] = @ChangeStamp,
	[CreditCompleted] = @CreditCompleted,
	[DateAdmitted] = @DateAdmitted,
	[DateGraduated] = @DateGraduated,
	[FirstName] = @FirstName,
	[LastName] = @LastName,
	[MiddleName] = @MiddleName,
	[PermanentAddress1] = @PermanentAddress1,
	[PermanentAddress2] = @PermanentAddress2,
	[PresentAddress1] = @PresentAddress1,
	[PresentAddress2] = @PresentAddress2,
	[Status] = @Status,
	[StudentID] = @StudentID
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

SELECT
	[ID],
	[FirstName],
	[MiddleName],
	[LastName],
	[PresentAddress1],
	[PresentAddress2],
	[PermanentAddress1],
	[PermanentAddress2],
	[StudentID],
	[Status],
	[CGPA],
	[CreditCompleted],
	[DateAdmitted],
	[DateGraduated],
	[ChangeStamp]
FROM dbo.[Student]
WHERE
	[ID] = @ID
GO


----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Student table
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Student_Delete
@ID int

,
@ChangeStamp smalldatetime
AS

DELETE FROM dbo.[Student]
WHERE
	[ID] = @ID
	AND [ChangeStamp] = @ChangeStamp

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


	


	
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.prc_Student_GetByID
	@ID int
AS

SELECT
	[ID],
	[FirstName],
	[MiddleName],
	[LastName],
	[PresentAddress1],
	[PresentAddress2],
	[PermanentAddress1],
	[PermanentAddress2],
	[StudentID],
	[Status],
	[CGPA],
	[CreditCompleted],
	[DateAdmitted],
	[DateGraduated],
	[ChangeStamp]
FROM
	[dbo].[Student]
WHERE
	[ID] = @ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Omar Al Zabir
Architect BT, UK (ex British Telecom)
United Kingdom United Kingdom

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411022.1 | Last Updated 17 Aug 2005
Article Copyright 2005 by Omar Al Zabir
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid