Using Microsoft Enterprise Library Data Access Application Block – Part II

��SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblUserMaster](

	[UserID] [int] IDENTITY(1,1) NOT NULL,

	[UserName] [varchar](50) NOT NULL,

	[UserPassword] [varchar](16) NOT NULL,

	[FirstName] [varchar](50) NOT NULL,

	[LastName] [varchar](50) NULL,

	[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_tblUserMaster_IsActive]  DEFAULT ((0)),

	[RegistrationDate] [datetime] NOT NULL CONSTRAINT [DF_tblUserMaster_RegistrationDate]  DEFAULT (getdate()),

 CONSTRAINT [PK_tblUserMaster] PRIMARY KEY CLUSTERED 

(

	[UserID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO

SET ANSI_PADDING OFF



GO





CREATE PROCEDURE [dbo].[sprocSample_GetActiveUsers]

AS 

BEGIN

SET NOCOUNT ON

	SELECT UserID, UserName, FirstName, LastName, UserPassword  FROM tblUserMaster WHERE IsDeleted = 0

END





GO



CREATE PROCEDURE [dbo].[sprocSample_GetUserDetails]

@intUserID INT,

@intErrID int OUT,

@strMessage varchar(255) OUT

AS 

BEGIN

SET NOCOUNT ON

	IF EXISTS(SELECT 1 FROM dbo.tblUserMaster WHERE UserID = @intUserID AND IsDeleted = 0)

		BEGIN

			SELECT UserID, UserName, FirstName, LastName, UserPassword  FROM tblUserMaster WHERE UserID = @intUserID

		END

	ELSE

		BEGIN

			SET @intErrID = -3

			set @strMessage = 'User does not exists'

		END

END