Scripts for Settings Admin

 USE [SettingAdmin]

GO

CREATE TABLE [dbo].[App](

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

[Name] [nvarchar](100) NOT NULL,

[ModifiedBy] [nvarchar](80) NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

[IsDeleted] [bit] NOT NULL,

 CONSTRAINT [PK_App] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)

)

GO

CREATE TABLE [dbo].[AppSetting](

[AppId] [int] NOT NULL,

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

[Name] [nvarchar](100) NOT NULL,

[Value] [nvarchar](4000) NOT NULL,

[SettingTypeId] [int] NOT NULL,

[Description] [nvarchar](250) NOT NULL,

[ModifiedBy] [nvarchar](80) NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

[IsDeleted] [bit] NOT NULL,

 CONSTRAINT [PK_AppSetting] PRIMARY KEY CLUSTERED 

(

[AppId] ASC,

[Id] ASC

)

)

GO

CREATE TABLE [dbo].[SettingType](

[Id] [int] NOT NULL,

[Name] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_SettingType] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)

)

GO

GO

GO

ALTER TABLE [dbo].[AppSetting]  WITH CHECK ADD  CONSTRAINT [FK_AppSetting_App] FOREIGN KEY([AppId])

REFERENCES [dbo].[App] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[AppSetting] CHECK CONSTRAINT [FK_AppSetting_App]

GO

ALTER TABLE [dbo].[AppSetting]  WITH CHECK ADD  CONSTRAINT [FK_AppSetting_SettingType] FOREIGN KEY([SettingTypeId])

REFERENCES [dbo].[SettingType] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[AppSetting] CHECK CONSTRAINT [FK_AppSetting_SettingType]

GO

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (1, N'Plain Text')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (2, N'Boolean')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (3, N'DateTime')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (4, N'Decimal')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (5, N'Double')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (6, N'Int32')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (7, N'Ado.Net connection string')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (8, N'Port Number')

INSERT [dbo].[SettingType] ([Id], [Name]) VALUES (9, N'Password', N'')

GO

CREATE PROCEDURE [dbo].[spGetSettings]

@AppName nvarchar(100)

AS

BEGIN    

        SELECT 

            AC.Name,

            AC.Value

        FROM

            App A INNER JOIN

            AppSetting AC ON A.Id = AC.AppId

        WHERE

            A.Name = @AppName AND

            A.IsDeleted = 0 AND

            AC.IsDeleted = 0

END

END