Generating Your DAL

1. Create sample database
   
   create database subSonicExampleDB
   use subSonicExampleDB
   
   CREATE TABLE [codeSnippets] (
     [id] int IDENTITY(1, 1) NOT NULL,
     [languageID] int NOT NULL,
     [name] varchar(255) NOT NULL,
     [body] varchar(8000) NULL,
     PRIMARY KEY CLUSTERED ([id])
   )
   GO
   
   CREATE TABLE [langauges] (
     [id] int IDENTITY NOT NULL,
     [name] varchar(255) NOT NULL,
     [syntaxHighlighterClass] varchar(10) NULL,
     PRIMARY KEY CLUSTERED ([id])
   )
   GO
   
   EXEC sp_grantdbaccess 'test', 'test'
   GO
   
   EXEC sp_addrolemember 'db_owner', 'test'
   GO
   
   insert into dbo.languages(name) values('VB.NET')
   insert into dbo.languages(name) values('C Sharp')
   
   insert
   into dbo.codeSnippets (languageID, name, body)
   select    (select id from languages where name = 'vb.net'),
       'SubSonic Collection',
       'Dim posts As PostCollection = New PostCollection().OrderByDesc("PostedOn").Load()'
      
   insert
   into dbo.codeSnippets (languageID, name, body)
   select    (select id from languages where name = 'c #'),
       'SubSonic Collection with Clauses',
       'Northwind.CustomerCollection customers = new Northwind.CustomerCollection() 
       .Where("Country", "Canada") 
       .Where(Northwind.Customer.Columns.Region, "BC") 
       .OrderByDesc(Northwind.Customer.Columns.PostalCode) 
       .Load();'
   
   insert
   into dbo.codeSnippets (languageID, name, body)
   select    (select id from languages where name = 'vb.net'),
       'SubSonic Entity',
       'If Not String.IsNullOrEmpty(Request.QueryString.Item("id")) Then 
       Dim id As Integer = Int32.Parse(Request.QueryString("id")) 
       post = New MyBlog.Post(id) 
   End If'
   
   insert
   into dbo.codeSnippets (languageID, name, body)
   select    (select id from languages where name = 'c #'),
       'SubSonic Relationships',
       'Northwind.Customer.FetchByID("BOTM") 
       .Orders()[0] 
       .OrderDetails()[0] 
       .Product 
       .ProductName;'
   
   ALTER TABLE [dbo].[codeSnippets]
   ADD CONSTRAINT [codeSnippets_fk] FOREIGN KEY ([languageID])
     REFERENCES [dbo].[languages] ([id])
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
   GO
   
   create proc listAll
   as
   
   select cs.id, cs.languageID,
       cs.name, l.description as language
   from    [dbo].[codeSnippets] as cs
       inner join
       [dbo].[languages] as l
       on
       cs.languageID = l.id
   GO
   
   
2. Setup configuration
   
   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
     <configSections>
       <section name="SubSonicService"
                type="SubSonic.SubSonicSection, SubSonic"
                requirePermission="false" />
     </configSections>
   
     <connectionStrings>
       <clear/>
         <add name="subSonicExampleDB"
            connectionString="Data Source=beehive;Initial Catalog=subSonicExampleDB;User Id=test;Password=test" />
     </connectionStrings>
   
     <SubSonicService defaultProvider="subSonicExampleDBProvider">
       <providers>
         <clear/>
   
         <add name="subSonicExampleDBProvider"
              type="SubSonic.SqlDataProvider, SubSonic"
              connectionStringName="subSonicExampleDB"
              generatedNamespace="dal"
              spClassName="storedProcs"
              />
         <!--fixPluralClassNames="false"-->
         <!--stripSPText="sp"-->
         <!--stripViewText="v"-->
       </providers>
     </SubSonicService>
   </configuration>
   
   
3. Generate DAL
   
   
1. With NAnt
   
   <project name="subsonicExample" default="build" basedir="."
            xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
   
     <!-- The relative path to the directory where SubSonic.dll is found -->
     <property name="subSonicRelativePath" value="..\SubSonic 2.1 Beta 2\SubSonic" />
   
     <!-- The relative path to the SubSonic commander -->
     <property name="sonicCommanderRelativePath" value="..\SubSonic 2.1 Beta 2\SubCommander\sonic.exe" />
   
     <!-- The command line arguments for the SubSonic commander -->
     <property name="sonicCommanderArguments" value="generate" />
   
     <!-- The root namespace for the project -->
     <property name="build.rootNamespace" value="dal" />
   
     <!-- The build type (release or debug) for the project -->
     <property name="build.config" value="release" />
   
     <!-- The relative path to the project directory (from the location of the build file) -->
       <property name="targetDirectoryRelativePath" value="." overwrite="false" />
   
       <!-- The relative path to directory where SubSonic will generated the files (from the project target directory) -->
       <property name="subsonicGeneratedFilesRelativePath" value="generated" />
   
     <property name="targetDirectoryFullPath" value="${path::get-full-path(targetDirectoryRelativePath)}" overwrite="false" />
     <property name="subsonicGeneratedFilesFullPath" value="${path::combine(targetDirectoryFullPath, 'generated')}" />
       <property name="binDirectory" value="${path::combine(targetDirectoryFullPath, 'bin')}" overwrite="false" />
     <echo message="${subsonicGeneratedFilesFullPath}"/>
     <property name="buildDirectoryFullPath" value="${path::combine(binDirectory, build.config)}" overwrite="false" />
     <property name="buildOutput" value="dal.dll" />
   
     <property name="subSonicFullPath" value="${path::get-full-path(subSonicRelativePath)}" />
     <!-- The full path to the SubSonic commander -->
     <property name="sonicCommanderFullPath" value="${path::get-full-path(sonicCommanderRelativePath)}" />
   
     <!-- Default build target -->
       <target name="build">
         <delete>
               <fileset>
                   <!-- Delete any visual studio related files -->
                   <include name="${subsonicGeneratedFilesFullPath}/*.*" />
               </fileset>
           </delete>
   
         <!-- Execute the SubSonic commander to generated the files in the defined "sonicCommanderFullPath" directory -->
         <exec
           basedir="${targetDirectoryFullPath}"
           program="${sonicCommanderFullPath}"
           commandline="${sonicCommanderArguments} /out &quot;${subsonicGeneratedFilesFullPath}&quot;"
           workingdir="${targetDirectoryFullPath}"
           failonerror="true" />
   
       <!-- Execute the vb compiler to compile the SubSonic generated files -->
       <csc target="library" output="${path::combine(buildDirectoryFullPath, buildOutput)}">
           <sources>
             <!-- Include all the SubSonic generated files -->
             <include name="${subsonicGeneratedFilesFullPath}\*.cs" />
           </sources>
   
           <!-- Include a reference to the SubSonic dll -->
           <references basedir="${subSonicFullPath}">
                   <include name="SubSonic.dll" />
               </references>
        </csc>   
       </target>
   </project>
   
   
2. With SubCommander as external VS tool
1. Go to Tools/External tools
2. Add a new tool:
1. Title: NAnt
2. Command: "path\to\SubCommander\sonic.exe"
3. Arguments: generate /out generated
4. Initial Directory: $(ProjectDir)
5. Check "Use Output Window"
   
   
3. With a build provider in ASP.NET
1. Reference SubSonic.dll
2. Add provider setting
   
   <buildProviders>
       <add extension=".abp" type="SubSonic.BuildProvider, SubSonic"/> 
   </buildProviders>
   
   
3. Create empty .apb file in "App_Code"
   
   
4. Rest Access to your data
   
   http://localhost/subSonicWeb/codesnippets/list.xml
   http://localhost/subSonicWeb/codesnippets/list.json
   
   http://localhost/subSonicWeb/codesnippets/show.xml?id=1
   http://localhost/subSonicWeb/codesnippets/show.json?id=1