if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Resources]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Resources]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ResourcesLocale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ResourcesLocale]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Images]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImagesLocale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ImagesLocale]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Culture]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Culture]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadResources]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LoadResources]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadImages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LoadImages]
GO
CREATE TABLE Resources
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY (1,1),
[Name] VARCHAR(64) NOT NULL
)
CREATE TABLE ResourcesLocale
(
[LocaleId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[ResourceId] INT NOT NULL,
[CultureId] INT NOT NULL,
[Value] VARCHAR(1024) NOT NULL
)
CREATE TABLE ImagesLocale
(
[LocaleId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[ResourceId] INT NOT NULL,
[CultureId] INT NOT NULL,
[Width] INT NOT NULL,
[Height] INT NOT NULL,
[Alt] VARCHAR(1024) NOT NULL
)
CREATE TABLE Images
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY (1,1),
[Name] VARCHAR(64) NOT NULL
)
CREATE TABLE Culture
(
[CultureId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Culture] CHAR(5) NOT NULL,
[DisplayName] VARCHAR(64) NOT NULL
)
GO
CREATE PROCEDURE LoadResources
(
@DefaultCulture CHAR(5),
@CurrentCulture CHAR(5)
)
AS
SET NOCOUNT ON
SELECT R.[Name], RL.[Value]
FROM Resources R
INNER JOIN ResourcesLocale RL ON R.Id = RL.ResourceId
INNER JOIN Culture C ON RL.CultureId = C.CultureId
WHERE C.Culture = @CurrentCulture
UNION ALL
SELECT R.[Name], RL.[Value]
FROM Resources R
INNER JOIN ResourcesLocale RL ON R.Id = RL.ResourceId
INNER JOIN Culture C ON RL.CultureId = C.CultureId
WHERE C.Culture = @DefaultCulture
AND R.[Name] NOT IN (
SELECT [Name] FROM Resources R2
INNER JOIN ResourcesLocale RL2 ON R2.Id = RL2.ResourceId
INNER JOIN Culture C2 ON RL2.CultureId = C2.CultureId
WHERE C2.Culture = @CurrentCulture
)
SET NOCOUNT OFF
GO
CREATE PROCEDURE LoadImages
(
@DefaultCulture CHAR(5),
@CurrentCulture CHAR(5)
)
AS
SET NOCOUNT ON
SELECT I.[Name], IL.[Width], IL.[Height], IL.[Alt]
FROM Images I
INNER JOIN ImagesLocale IL ON I.Id = IL.ResourceId
INNER JOIN Culture C ON IL.CultureId = C.CultureId
WHERE C.Culture = @CurrentCulture
UNION ALL
SELECT I.[Name], IL.[Width], IL.[Height], IL.[Alt]
FROM Images I
INNER JOIN ImagesLocale IL ON I.Id = IL.ResourceId
INNER JOIN Culture C ON IL.CultureId = C.CultureId
WHERE C.Culture = @DefaultCulture
AND I.[Name] NOT IN (
SELECT [Name] FROM Images I2
INNER JOIN ImagesLocale IL2 ON I2.Id = IL2.ResourceId
INNER JOIN Culture C2 ON IL2.CultureId = C2.CultureId
WHERE C2.Culture = @CurrentCulture
)
SET NOCOUNT OFF
GO
--SAMPLE DATA
DECLARE @EnglishCultureId INT,
@FrenchCultureId INT,
@ResourceId INT
INSERT INTO Culture (Culture, DisplayName) VALUES ('en-CA', 'English')
SET @EnglishCultureId = SCOPE_IDENTITY()
INSERT INTO Culture (Culture, DisplayName) VALUES ('fr-CA', 'Fran�ais')
SET @FrenchCultureId = SCOPE_IDENTITY()
INSERT INTO Resources ([Name]) VALUES ('colon')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, ':')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, ' :')
INSERT INTO Resources ([Name]) VALUES ('username')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Username')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Nom d''utilisateur')
INSERT INTO Resources ([Name]) VALUES ('password')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Password')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Mots de passe')
INSERT INTO Resources ([Name]) VALUES ('login')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Login')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Soumettre')
INSERT INTO Resources ([Name]) VALUES ('rememberMe')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Remember Me?')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Soumettre')
INSERT INTO Resources ([Name]) VALUES ('usernameRequired')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Username required')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Nom d''utilisateur requis')
INSERT INTO Resources ([Name]) VALUES ('passwordRequired')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Password required')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Mots de passe requis')
INSERT INTO Resources ([Name]) VALUES ('invalidLoginInformation')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Your username/password could not be found')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Nom d''utilisateur/mot de passe invalide')
INSERT INTO Resources ([Name]) VALUES ('forgottenPassword')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Forgotten Password?')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Oubliez votre mots de passe?')
INSERT INTO Resources ([Name]) VALUES ('email')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Email')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Courriel')
INSERT INTO Resources ([Name]) VALUES ('send')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Send')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Envoyer')
INSERT INTO Resources ([Name]) VALUES ('emailRequired')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Please enter your email')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Courriel requis')
INSERT INTO Resources ([Name]) VALUES ('ClickToSeeBinding')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, '<a href="{URL}">Click here</a> to see a binding/placeholder example')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, '<a href="{URL}">Clicker i�i</a> pour voir une example de placeholder')
INSERT INTO Resources ([Name]) VALUES ('Welcome')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'Welcome to {SITENAME}')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Bienvenue a notre site {SITENAME}')
INSERT INTO Resources ([Name]) VALUES ('LoginAudit')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, '{USERNAME}, also known as {NAME} last logged in on {DATE}')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'La derni�re date d''ouverture pour {USERNAME}, aussi connue par {NAME}, � �t� le {DATE}')
INSERT INTO Resources ([Name]) VALUES ('EnteredUsername')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'You have entered "{0}" as the username')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Le nom d''utilisateur est "{0}"')
INSERT INTO Resources ([Name]) VALUES ('EnteredPassword')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @EnglishCultureId, 'You have entered "{0}" as the password.')
INSERT INTO ResourcesLocale (ResourceId, CultureId, [Value]) VALUES (@ResourceId, @FrenchCultureId, 'Le mots de pass est "{0}"')
INSERT INTO Images ([Name]) VALUES ('Title')
SET @ResourceId = SCOPE_IDENTITY()
INSERT INTO ImagesLocale (ResourceId, CultureId, [Width], [Height], [Alt])
VALUES (@ResourceId, @EnglishCultureId, 252, 30, 'Multilingual Sample' )
INSERT INTO ImagesLocale (ResourceId, CultureId, [Width], [Height], [Alt])
VALUES (@ResourceId, @FrenchCultureId, 258, 31, 'Exemple Multilingue' )
Submit an article or tip