So what is it and how does it work?
Well let's start with a sample object diagram, let us say that you are doing software for a travel company (since this seems to be the standard practice project in swedish universities).
Your object diagram could look somewhat like this:
You got the typical customer with a link to itself and a subclass with a discount, you got a company object that can be either a cruise company, an airline or a charter company. The companies offers travels and one or several persons can write some contracts with some travels. Basic stuff! :)
So now the model is roughly finished and you need to get it into SQL server for persistance. That would mean some few hours of more or less non-creative work, mostly copying properties from c# to SQL Server. Boring, boring job... So (fanfare) enter the CreateSchema extension found here. And then you simply add a reference to the ObjectScriptingExtensions.dll, add a using ObjectScriptingExtensions; to your code and write:
Traveller traveller = new Traveller();
string s = traveller.CreateSchema();
The string returned by the CreateSchema() will be a long SQL statement that looks like this:
/****** Object: Table [dbo].[Traveller] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Traveller]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[FirstName] NVARCHAR(4000) NULL,
[LastName] NVARCHAR(4000) NULL,
[FullAdress] NVARCHAR(4000) NULL,
[Telephone] NVARCHAR(4000) NULL,
[Age] INT NOT NULL,
[FKAssociatedTravellers] INT NOT NULL
)
GO
/****** Object: Table [dbo].[Contract] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contract]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[ContractReference] UNIQUEIDENTIFIER NOT NULL,
[FKItems] INT NOT NULL
)
GO
/****** Object: Table [dbo].[ContractItem] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContractItem]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[Price] DECIMAL(18,2) NOT NULL,
[PurchaseDate] DATETIME NOT NULL,
[Duration] TIME NOT NULL,
[FKPurchasedTravel] INT NOT NULL
)
GO
/****** Object: Table [dbo].[Travel] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Travel]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[Destination] NVARCHAR(4000) NULL,
[BasePrice] DECIMAL(18,2) NOT NULL,
[FKCompany] INT NOT NULL
)
GO
/****** Object: Table [dbo].[Company] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Company]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[Name] NVARCHAR(4000) NULL,
[OrganisationNumber] NVARCHAR(4000) NULL
)
GO
/****** Object: Table [dbo].[CruiseCompany] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CruiseCompany]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[FKCompany] INT NOT NULL FOREIGN KEY REFERENCES [Company]([ID]),
[FKSeasWithOfferedCruises] INT NOT NULL
)
GO
/****** Object: Table [dbo].[CruiseCompany_SeasWithOfferedCruises] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CruiseCompany_SeasWithOfferedCruises]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[Value] NVARCHAR(4000) NULL
)
GO
/****** Object: Table [dbo].[CharterCompany] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CharterCompany]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[FKCompany] INT NOT NULL FOREIGN KEY REFERENCES [Company]([ID]),
[FKTypeOfCharter] INT NOT NULL
)
GO
/****** Object: Table [dbo].[CharterType] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CharterType]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[Name] NVARCHAR(4000) NULL
)
GO
/****** Object: Table [dbo].[AirlineCompany] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AirlineCompany]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[FKCompany] INT NOT NULL FOREIGN KEY REFERENCES [Company]([ID]),
[MajorHub] NVARCHAR(4000) NULL
)
GO
/****** Object: Table [dbo].[VipTraveller] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VipTraveller]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[FKTraveller] INT NOT NULL FOREIGN KEY REFERENCES [Traveller]([ID]),
[Discount] REAL NOT NULL
)
GO
/****** Object: Table [dbo].[Contract_Traveller] Script Date: 2012-04-05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contract_Traveller]
(
[ID] INT IDENTITY NOT NULL PRIMARY KEY,
[FKTraveller_Contract] INT NOT NULL,
[FKContract_Traveller] INT NOT NULL
)
GO
/****** Object: ForeignKey [Contract_Traveller_Contract_Contract_Traveller_FKTraveller_Contract] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[Contract_Traveller] WITH CHECK ADD CONSTRAINT [Contract_Traveller_Contract_Contract_Traveller_FKTraveller_Contract] FOREIGN KEY([FKTraveller_Contract])
REFERENCES [dbo].[Contract] ([ID])
GO
ALTER TABLE[dbo].[Contract_Traveller] CHECK CONSTRAINT [Contract_Traveller_Contract_Contract_Traveller_FKTraveller_Contract]
GO
/****** Object: ForeignKey [Contract_Traveller_Traveller_Contract_Traveller_FKContract_Traveller] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[Contract_Traveller] WITH CHECK ADD CONSTRAINT [Contract_Traveller_Traveller_Contract_Traveller_FKContract_Traveller] FOREIGN KEY([FKContract_Traveller])
REFERENCES [dbo].[Traveller] ([ID])
GO
ALTER TABLE[dbo].[Contract_Traveller] CHECK CONSTRAINT [Contract_Traveller_Traveller_Contract_Traveller_FKContract_Traveller]
GO
/****** Object: ForeignKey [CruiseCompany_CruiseCompany_SeasWithOfferedCruises_CruiseCompany_FKSeasWithOfferedCruises] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[CruiseCompany] WITH CHECK ADD CONSTRAINT [CruiseCompany_CruiseCompany_SeasWithOfferedCruises_CruiseCompany_FKSeasWithOfferedCruises] FOREIGN KEY([FKSeasWithOfferedCruises])
REFERENCES [dbo].[CruiseCompany_SeasWithOfferedCruises] ([ID])
GO
ALTER TABLE[dbo].[CruiseCompany] CHECK CONSTRAINT [CruiseCompany_CruiseCompany_SeasWithOfferedCruises_CruiseCompany_FKSeasWithOfferedCruises]
GO
/****** Object: ForeignKey [CharterCompany_CharterType_CharterCompany_FKTypeOfCharter] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[CharterCompany] WITH CHECK ADD CONSTRAINT [CharterCompany_CharterType_CharterCompany_FKTypeOfCharter] FOREIGN KEY([FKTypeOfCharter])
REFERENCES [dbo].[CharterType] ([ID])
GO
ALTER TABLE[dbo].[CharterCompany] CHECK CONSTRAINT [CharterCompany_CharterType_CharterCompany_FKTypeOfCharter]
GO
/****** Object: ForeignKey [Travel_Company_Travel_FKCompany] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[Travel] WITH CHECK ADD CONSTRAINT [Travel_Company_Travel_FKCompany] FOREIGN KEY([FKCompany])
REFERENCES [dbo].[Company] ([ID])
GO
ALTER TABLE[dbo].[Travel] CHECK CONSTRAINT [Travel_Company_Travel_FKCompany]
GO
/****** Object: ForeignKey [ContractItem_Travel_ContractItem_FKPurchasedTravel] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[ContractItem] WITH CHECK ADD CONSTRAINT [ContractItem_Travel_ContractItem_FKPurchasedTravel] FOREIGN KEY([FKPurchasedTravel])
REFERENCES [dbo].[Travel] ([ID])
GO
ALTER TABLE[dbo].[ContractItem] CHECK CONSTRAINT [ContractItem_Travel_ContractItem_FKPurchasedTravel]
GO
/****** Object: ForeignKey [Contract_ContractItem_Contract_FKItems] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[Contract] WITH CHECK ADD CONSTRAINT [Contract_ContractItem_Contract_FKItems] FOREIGN KEY([FKItems])
REFERENCES [dbo].[ContractItem] ([ID])
GO
ALTER TABLE[dbo].[Contract] CHECK CONSTRAINT [Contract_ContractItem_Contract_FKItems]
GO
/****** Object: ForeignKey [Traveller_Traveller_Traveller_FKAssociatedTravellers] Script Date: 2012-04-05 ******/
ALTER TABLE [dbo].[Traveller] WITH CHECK ADD CONSTRAINT [Traveller_Traveller_Traveller_FKAssociatedTravellers] FOREIGN KEY([FKAssociatedTravellers])
REFERENCES [dbo].[Traveller] ([ID])
GO
ALTER TABLE[dbo].[Traveller] CHECK CONSTRAINT [Traveller_Traveller_Traveller_FKAssociatedTravellers]
GO
/****** Did not script following | Script Date: 2012-04-05
******/
It is a lot of SQL that you dont have to write yourself!
And if you look at the table diagram it will look like this after executing:
Almost correct! But as object relational mapping is a guess work you should modify this to suit your needs (and also there's still some bugs in the generator logic). See it as a way to generate a skeleton of your tables rather than the truth of how you should serialize your objects. Hope you try it out, have fun and maybe even fix the bugs at CodePlex for me! :)
PS. I made it on a big object model just to show that the codes handles it pretty well, start out with a single class first.
See you!
No comments:
Post a Comment