Generating a data layer using SubSonic

Ashutosh Phoujdar

Rate me:

2.20/5 (5 votes)

20 May 2008Ms-PL5 min read

44K

571

This article will demonstrate how to generate a data access layer using SubSonic.

subsonic.zip
- SQL
  - CreateDatabase.sql
  - CreateTables.sql
- SubSonic
  - a.txt
  - AllStructs.cs
  - App.config
  - AssemblyInfo.cs
  - bin
    - Debug
      - EntLib.dll
      - Microsoft.Practices.EnterpriseLibrary.Common.dll
      - Microsoft.Practices.EnterpriseLibrary.Common.xml
      - Microsoft.Practices.EnterpriseLibrary.Data.dll
      - Microsoft.Practices.EnterpriseLibrary.Data.xml
      - Microsoft.Practices.EnterpriseLibrary.Validation.dll
      - Microsoft.Practices.EnterpriseLibrary.Validation.xml
      - Microsoft.Practices.ObjectBuilder.dll
      - MySql.Data.dll
      - Northwind.dll
      - SubSonic.dll
  - Credit.cs
  - CreditController.cs
  - Customer.cs
  - CustomerController.cs
  - Debit.cs
  - DebitController.cs
  - EntLib.csproj
  - EntLib.sln
  - Order.cs
  - OrderController.cs
  - Product.cs
  - ProductController.cs
  - StoredProcedures.cs
  - TestApp
    - App.config
    - AssemblyInfo.cs
    - bin
      - Debug
        
        EntLib.dll
        
        Microsoft.Practices.EnterpriseLibrary.Common.dll
        
        Microsoft.Practices.EnterpriseLibrary.Common.xml
        
        Microsoft.Practices.EnterpriseLibrary.Data.dll
        
        Microsoft.Practices.EnterpriseLibrary.Data.xml
        
        Microsoft.Practices.EnterpriseLibrary.Validation.dll
        
        Microsoft.Practices.EnterpriseLibrary.Validation.Integration.WinForms.dll
        
        Microsoft.Practices.EnterpriseLibrary.Validation.Integration.WinForms.xml
        
        Microsoft.Practices.EnterpriseLibrary.Validation.xml
        
        Microsoft.Practices.ObjectBuilder.dll
        
        MySql.Data.dll
        
        SubSonic.dll
        
        TestApp.exe
        
        TestApp.exe.config
    - FrmOrders.cs
    - FrmOrders.Designer.cs
    - FrmOrders.resx
    - MainForm.cs
    - MainForm.Designer.cs
    - MainForm.resx
    - TestApp.csproj

��USE [EntLibQuickStarts]

GO

/****** Object:  Table [dbo].[Credits]    Script Date: 05/19/2008 22:08:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Credits](

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

	[AccountID] [nchar](20) NOT NULL,

	[Amount] [money] NOT NULL,

 CONSTRAINT [PK_Credits] PRIMARY KEY CLUSTERED 

(

	[CreditID] 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

/****** Object:  Table [dbo].[Customers]    Script Date: 05/19/2008 22:08:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Customers](

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

	[Name] [nvarchar](30) NULL,

	[Address] [nvarchar](60) NULL,

	[City] [nvarchar](15) NULL,

	[Country] [nvarchar](15) NULL,

	[PostalCode] [nvarchar](10) NULL,

 CONSTRAINT [PKCustomerID] PRIMARY KEY CLUSTERED 

(

	[CustomerID] 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

/****** Object:  Table [dbo].[Debits]    Script Date: 05/19/2008 22:08:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Debits](

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

	[AccountID] [nchar](20) NOT NULL,

	[Amount] [money] NOT NULL,

 CONSTRAINT [PK_Debits] PRIMARY KEY CLUSTERED 

(

	[DebitID] 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

/****** Object:  Table [dbo].[Orders]    Script Date: 05/19/2008 22:08:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Orders](

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

	[CustomerID] [int] NOT NULL,

	[ProductID] [int] NOT NULL,

	[Description] [nchar](50) NULL,

	[Unit] [int] NOT NULL,

 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 

(

	[OrderID] 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

/****** Object:  Table [dbo].[Products]    Script Date: 05/19/2008 22:08:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Products](

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

	[ProductName] [nvarchar](50) NOT NULL,

	[CategoryID] [int] NULL,

	[UnitPrice] [money] NOT NULL,

	[LastUpdate] [datetime] NULL,

 CONSTRAINT [PKProductID] PRIMARY KEY CLUSTERED 

(

	[ProductID] 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

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Orders] FOREIGN KEY([CustomerID])

REFERENCES [dbo].[Customers] ([CustomerID])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Customer_Orders]

GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Products_Orders] FOREIGN KEY([ProductID])

REFERENCES [dbo].[Products] ([ProductID])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Products_Orders]

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, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Written By

Ashutosh Phoujdar

Architect

Canada

Ashutosh is an avid programmer, who “lives to code”. One can always find him busy seeking challenging programming assignments on various complex problems ranging from Data management, Classification, Optimization, Security, Network analysis, Distributed computing.

He started his programming stint with “C”; he has also worked on C++, Visual Basic, JAVA, Perl, FoxPro, PASCAL, Shell Scripting, and Perl. Currently, he is proficient and working on C#.

His area of interest includes Distributed Computing, Analytic and Business Intelligence, Large Enterprise System Architectures, Enterprise Content Management.

He is an advocate of Open source and likes to share solutions with open source communities like
1.Stack Overflow
2. nHibernate.

Award's :
Prize winner in Competition "Best article of May 2008"

Articles :
Click to see my CodeProject Articles

Blog :
Share a solution | Explore the .NET world

Link'd :

Ashutosh Phoujdar

His Favorite(s) :
nHibernate - The best ORM.
nHibernate Contributed Tools

Generating a data layer using SubSonic

License

Comments and Discussions