Picasso Wiki & Documentation Rss Feed

Updated Wiki: Home

jeremyconnell — Tue, 18 Aug 2009 01:08:08 GMT

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Latest Release: 2.4
Auto-Upgrade: http://www.picasso.net.nz/codegenerator/publish.htm

Introduction:
Speed up your .Net 2.0 development with this powerful, flexible, and transparent Object-Relational data persistance framework.

Compares favourably with mainstream ORM solutions, particulary on its simplicity, transparancy, customisability, portability, and ease-of-regeneration when schema changes.

Configurable template-based code-generator rapidly converts your Relational or Xml schema into a easy-to-use, fully functional, highly customisable set of .Net 2.0 business objects, which extend powerful abstract classes.

Powerful dynamic queries meant you can rapidly develop a major .net application without ever writing SQL. Also supports the older option of using auto-generated (+customised) stored-procedures (if your dba insists on it).

All the usual bells and whistles, plus a few extras including:
- Easy to regenerate code as schema changes, whilst preserving all customisations (uses Partial Classes, Generics)
- Support for multi-column primary keys in the O/R model.
- Handle any type of relations (1-1, 1-n, n-n)
- Aggregates (SUM, AVG, MIN, MAX, COUNT)
- Powerful queries (joins and complex where expressions), plus easy support for stored procedures.
- Generates code (copy & paste) for relationship-mapping properties, allowing easy & efficient navigation around data relationships
- Transactional - Easily supports batches of operations within an ADO.Net Transactions (i.e. doesn't require MSDTC/TransactionScope)
- Paging support, including both sql-based, and in-memory paging
- Easily talk to several database instances simultanously
- Handles circular references without duplication.
- Natural integration with xml data (e.g. xml blobs in db, or xml import/export functions)
- Can co-exist with other frameworks i.e. no constraints on db (other than suggested naming conventions)
- Total Db-Platform Independance and Optimisation. Includes Oracle, MySql, MSAccess, SqlServer, etc, plus three remote ADO.Net drivers including WCF, asmx, and aspx
- Sophisticated in-memory caching option on a table-by-table basis (managed, indexed), greatly improving performance in most circumstances.
- Option of a low-level audit trail to record full details of all inserts, updates and deletes.
- Easy to use code generator app, with high-quality fully-editable templates and logic
- Framework provides powerful abstract classes to minimise schema and application code.

Support options are available, including skype,msn,etc (rates neg.

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Credits: Thanks to http://www.nvinteractive.co.nz/ for their full adoption and support
Sponsors: www.teameffect.com and www.swapster.co.nz

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Sun, 19 Jul 2009 08:08:28 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.3).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CWeb.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). The templates for this feature are obviously team-specific, and will need to be customised to your particular style of web application layout. For example, the new type of a web-projects have a slightly different way of displaying dynamic usercontrols, so the type of project you normally use will also influence template design.The current set of templates demonstrate an approach based around proprietary UI controls.

Additional Notes on UI code generator:
1. A convenient template editor (and import/export feature) has been provided, and users are encouraged to modify the templates to suit, and to export and share their modified template sets around to their team members.
2. If you are using the old-style web projects, you will need to use a simple technique to get visual studio to auto-generate the contents of the *.designer.* files. First include the files in your project. Open the aspx or ascx file (make sure the relevant designer file is NOT opened or it wont work), then insert a whitespace character into any server-side tag e.g. in between 2 attributes, then save and close the file. The designer file will be regenerated, providing there are no major html errors in your template. Templates intended for newer-style projects should have completely empty templates for the designer files, and the code generator will not create those files.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks. Its a good idea to also add a project-level import to bring these classes into scope.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabase" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or app_data folder (e.g. just the filename if its in app_data folder). This logic is implemented in the useful property called Framework.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Select via Stored Procedure, returning a list of that class

    //From within the class, 
    return MakeList("storedProcName"); 
    //... or with parameters
    return this.DataSrcExecuteDataset("storedProcName", new object[]{123, true});

11. Select via Stored Procedure, returning dataset (ad-hoc query)

    //From within the class, 
    return DataSrc.ExecuteDataset("storedProcName", new object[] { 123, true });
    //... or to return a datareader
    return DataSrc.Local.ExecuteReader("storedProcName", new object[] { 123, true });
    //... or to return a datareader or dataset, depending on whether the driver is remote
    return DataSrc.Local.ExecuteQuery("storedProcName", new object[] { 123, true });

12. Bulk Save operation (Implicit Transaction)

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Sun, 19 Jul 2009 08:02:31 GMT

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Select via Stored Procedure
From within the class, returning a list of that class
return MakeList("storedProcName");
... or with parameters...
return MakeList("storedProcName", new object(){123, true}); //Other overloads including named parameters
... or to return an ad-hoc report as a dataset
return this.DataSrcExecuteDataset("storedProcName", new object(){123, true});

11. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 3. Technical Overview

jeremyconnell — Sun, 19 Jul 2009 07:57:04 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Technical Overview:
The design utilizes a code generator to rapidly produce 90-100% of the O/R-mapping classes (Business Objects) for database persistence. It generates lightweight classes in VB.Net or C#, which inherit most of their functionality from abstract framework classes. These provide a structured environment for customization, business logic and reusable code, including standardised region names.

The recommended choice of architecture employs parameterized queries as the underlying technology (as opposed to stored procedures or sql strings), in order to dynamically implement the set of trivial database operations known as CRUD methods i.e. atomic-level select/insert/update/delete. CRUD methods support fully functional O/R classes, and typically constitute between 90-100% of stored procedure requirements.

Benefits:
This design brings 4 key benefits, specifically:
1. Cost - Rapid development & maintenance via code-generation and structure.
2. Quality – High level of code consistency, with minimum scope for human error
3. Concurrency – Dynamic update statements are inherently less likely to produce data conflicts from concurrent user updates.
4. Portability - Keeping the number of stored procedures to a minimum (or nil) greatly improves database portability. When changing platform, tables can usually be imported, but stored procedures/functions/views have to be manually rewritten.

By using the same driver-specific parameters, it maintains equality on 3 other key issues:
1. Performance – The parameters separate the data from the SQL statement, allowing it to be pre-compiled and cached by the database in exactly the same way that a stored procedure is, giving it an identical performance characteristic.
2. Security – This technique is not vulnerable to SQL injection attacks (and errors), because it uses the same driver-specific parameter collections to transmit the data.
3. Transactional – Can perform large batches of CRUD operations within a transaction context to implement a transactional behavior, or just to share an open connection for performance.

Stored procedures:
Sometimes a few additional stored procedures are provided for such things as complex reporting, database-intensive tasks, or rare performance hacks. Theoritically this logic can always be implemented in the .Net layer using CRUD methods, but is occaisonally it is well-suited to a stored procedure implementation.

It is very straightforward to implement the stored procedure calls in the business layer on an ad-hoc basis, using very simple helper methods that take the procedure name and an optional list of named parameters (in the form of a Dictionary object). From within the class, you can use MakeList(...) to create business objects of that type, or use DataSrc.ExecuteDataset(...) for ad-hoc queries. See also DataSrc.ExecuteQuery(...) and DataSrc.Local.ExecuteReader(...) overloads.

Alternatively, at the time the classes are generated, one can select an option that causes the classes to be generated using a different pattern, such that stored procedures are generated for all CRUD methods, and the dynamic query capability is absent. There is no advantage to using this approach, other than to fulfill a possible DBA requirement that all database activity must use stored procedures.

DBNull Equivalent values
Whenever data is accessed using the generated business objects, the troublesome issue of null values in the database is taken care of automatically. Specifically, this occurs when the object is loaded from a DataReader or DataRow, and also when a set of parameters are produced for an insert or update operation. A value has been nominated for each basic data-type in .Net to represent null values, and the framework maps these values to and from a database null. Examples include: DateTime.MinValue; Double.NaN, Integer.MinValue, Decimal.MinValue, Guid.Empty. String and binary data use ‘Nothing’ (null) to represent a database Null.

Caching – Default/Optional Behavior
By default, the class generator creates classes that include a useful caching mechanism. This is quite useful for most situations, except where:
1. The table data is currently (or potentially) too large to be stored in application memory
2. The table data is primarily write-only e.g. an error log.
3. A number of independent applications update the same database, with no common layer such as a webservice to co-ordinate or clear the cache e.g. a web-farm deployment scenario.

If a class employs caching for a particular table, then the associated class will expose a static property called “Cache”, returning a custom collection. In such a case, any type of select-where query on that table is best implemented as a custom index on the custom collection returned by the cache. Such an index is always supplied for the primary-key column, (typically called “GetById”) returning either a single object or null, depending on the value(s) supplied. In such a case, if you wanted to look up a single record, use:

	Dim example As CExample = CExample.Cache.GetById(exampleId)
instead of:
	Dim example As New CExample(exampleId)

In order to enforce consistent behavior, only one of the two options described above will be possible. In other words, either the cache property will not exist, or the constructor that takes a primary key value will not exist, depending on whether or not the class employs caching. This is not mandatory, but it helps ensure that the cache will be used if it exists.

At the time the class is generated, you can nominate other columns of interest (such as a foreign key column, status column, or boolean-valued column) that could be used for additional indices, and the class generator will automatically write some code to implement that custom index. These types of index (on a FK) return a list (as opposed to the single instance for a unique/PK index), which may be empty, depending on the value of the foreign key etc supplied. This index can then be used instead of executing a select-where query against a database, and is substantially more efficeint e.g.

	Dim exampleList As CExampleList = CExample.Cache.GetByStatusId(statusId)
instead of:
	Dim exampleList As CExampleList = New CExample().SelectByStatus(statusId)

As with the primary key index, only one of these two options will be provided, depending on whether caching is employed, and only the latter case involves database activity. Both types of index provide an effective space-for-time tradeoff. They both only require a single pass through the entire collection to initialize the index (the first time the index is used), and thereafter provide virtually instantaneous query results by looking up the results from a private hash-table. The “Cache” property stores the main collection, which is created using a single called to the database (select-all statement). Whenever a cache is employed, you will only ever need to do a select-all operation on the actual table, and therefore you typically wont need to implement any indices on that table within the database itself, since the code only ever uses the in-memory, .Net indices.

Note that you can cascade these properties to implement complex combinations of filters e.g.

	CExample.Cache.GetByStatusId(EStatus.Active).GetByLocationId(ELocation.NewZealand)

Generalized Architecture of Framework (.Net Projects & Libraries)

Core Abstract Classes (Framework Level)
The auto-generated classes that make up the business layer (blue) will typically inherit from just one of the 3 main classes shown in yellow below. The lowest one (CBaseSmart) is the default choice for a common framework, but sometimes the stripped down version above it (CBaseDynamic) is used if memory efficiency is an issue. The third choice (CBaseSp) is only employed if stored procedures are a required, i.e. the use of dynamic queries are explicitly prohibited by your database administrator.

Variations on Abstract Classes (by nature of the Primary Keys)
The choice between the 3 main bases classes described above (yellow) establishes the basic framework, but this choice is then multiplied by about 6 independent variations, according to the nature of the primary key, and how many columns it spans (for the specific table that the business object seeks to model. Specifically, the main types are:
1. Single-column primary key, with values (sequence) generated by the database. (This is by far the most common case).
2. Single-column primary key, user supplies the value e.g. 4-letter codes.
3. 2-column primary key, with no other columns to update
4. 2-column primary key, plus some other columns exist in the table
5. 3-column primary key, with no other columns to update
6. 3-column primary key, plus some other columns exist in the table
The first 2 variations inherit directly from the selected base class. They both have constructors that accept a same single-value i.e. the primary key, for selecting and updating a single record. The only difference is that the former has a read-only property to represent the primary key value (since only the database can set its value on insert), whereas the latter has a read-write property (since the user must set it before insert). If the class inherits from CBaseDynamic, there is one extra boolean property that controls whether the PK column is included in the insert statements that are generated.

The other main variations require 2 or 3 parameters be supplied to the constructor in order to be able to select a single record, according to the number of columns that constitute the primary key. These are referred to as many-to-many (M2M) and 3-way tables respectively (also known as Associative tables). In theory there is no limit to the number of columns that a PK can span, but 2 is a minority, 3 is very rare, and 4 has not been encountered to date (can code up another base class if ever required).

Additionally, if these multi-column primary keys are the only columns in the table, then there will never be a need for an update operation (only insert), and there is never a need to select a single record based on the primary key either. This can be modelled with a slightly different base class, having less constructors and a slightly different implementation of the Save method, which only does an insert.

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: Home

jeremyconnell — Sun, 19 Jul 2009 07:51:57 GMT

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Latest Release: 2.3
Auto-Upgrade: http://www.picasso.net.nz/codegenerator/publish.htm

Introduction:
Speed up your .Net 2.0 development with this powerful, flexible, and transparent Object-Relational data persistance framework.

Compares favourably with mainstream ORM solutions, particulary on its simplicity, transparancy, customisability, portability, and ease-of-regeneration when schema changes.

Configurable template-based code-generator rapidly converts your Relational or Xml schema into a easy-to-use, fully functional, highly customisable set of .Net 2.0 business objects, which extend powerful abstract classes.

Powerful dynamic queries meant you can rapidly develop a major .net application without ever writing SQL. Also supports the older option of using auto-generated (+customised) stored-procedures (if your dba insists on it).

All the usual bells and whistles, plus a few extras including:
- Easy to regenerate code as schema changes, whilst preserving all customisations (uses Partial Classes, Generics)
- Support for multi-column primary keys in the O/R model.
- Handle any type of relations (1-1, 1-n, n-n)
- Aggregates (SUM, AVG, MIN, MAX, COUNT)
- Powerful queries (joins and complex where expressions), plus easy support for stored procedures.
- Generates code (copy & paste) for relationship-mapping properties, allowing easy & efficient navigation around data relationships
- Transactional - Easily supports batches of operations within an ADO.Net Transactions (not MSDTC/TransactionScope)
- Paging support, including both sql-based, and in-memory paging
- Easily talk to several database instances simultanously
- Handles circular references without duplication.
- Natural intergration with xml data (e.g. xml blobs in db, or xml import/export functions)
- Can co-exist with other frameworks i.e. no constraints on db (other than suggested naming conventions)
- Total Db-Platform Independance and Optimisation. Includes Oracle, MySql, MSAccess, SqlServer, etc, plus three remote ADO.Net drivers including WCF, asmx, and aspx
- Sophisticated in-memory caching option on a table-by-table basis (managed, indexed), greatly improving performance in most circumstances.
- Option of a low-level audit trail to record full details of all inserts, updates and deletes.
- Easy to use code generator app, with high-quality fully-editable templates and logic
- Framework provides powerful abstract classes to minimise schema and application code.

Support options are available, including skype,msn,etc (rates neg.

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Credits: Thanks to http://www.nvinteractive.co.nz/ for their full adoption and support
Sponsors: www.teameffect.com and www.swapster.co.nz

Updated Wiki: 3. Technical Overview

jeremyconnell — Sun, 19 Jul 2009 07:45:08 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Technical Overview:
The design utilizes a code generator to rapidly produce 90-100% of the O/R-mapping classes (Business Objects) for database persistence. It generates lightweight classes in VB.Net or C#, which inherit most of their functionality from abstract framework classes. These provide a structured environment for customization, business logic and reusable code, including standardised region names.

The recommended choice of architecture employs parameterized queries as the underlying technology (as opposed to stored procedures or sql strings), in order to dynamically implement the set of trivial database operations known as CRUD methods i.e. atomic-level select/insert/update/delete. CRUD methods support fully functional O/R classes, and typically constitute between 90-100% of stored procedure requirements.

Benefits:
This design brings 4 key benefits, specifically:
1. Cost - Rapid development & maintenance via code-generation and structure.
2. Quality – High level of code consistency, with minimum scope for human error
3. Concurrency – Dynamic update statements are inherently less likely to produce data conflicts from concurrent user updates.
4. Portability - Keeping the number of stored procedures to a minimum (or nil) greatly improves database portability. When changing platform, tables can usually be imported, but stored procedures/functions/views have to be manually rewritten.

By using the same driver-specific parameters, it maintains equality on 3 other key issues:
1. Performance – The parameters separate the data from the SQL statement, allowing it to be pre-compiled and cached by the database in exactly the same way that a stored procedure is, giving it an identical performance characteristic.
2. Security – This technique is not vulnerable to SQL injection attacks (and errors), because it uses the same driver-specific parameter collections to transmit the data.
3. Transactional – Can perform large batches of CRUD operations within a transaction context to implement a transactional behavior, or just to share an open connection for performance.

Stored procedures:
Sometimes a few additional stored procedures are provided for such things as complex reporting, database-intensive tasks, or rare performance hacks. Theoritically this logic can always be implemented in the .Net layer using CRUD methods, but is occaisonally it is well-suited to a stored procedure implementation.

It is very straightforward to implement the stored procedure calls in the business layer on an ad-hoc basis, using very simple helper methods that take the procedure name and an optional list of named parameters (in the form of a Dictionary object). From within the class, you can use MakeList(...) to create business objects of that type, or use DataSrc.ExecuteDataset(...) for ad-hoc queries. See also DataSrc.Local.ExecuteReader(...) overloads.

Alternatively, at the time the classes are generated, one can select an option that causes the classes to be generated using a different pattern, such that stored procedures are generated for all CRUD methods, and the dynamic query capability is absent. There is no advantage to using this approach, other than to fulfill a possible DBA requirement that all database activity must use stored procedures.

DBNull Equivalent values
Whenever data is accessed using the generated business objects, the troublesome issue of null values in the database is taken care of automatically. Specifically, this occurs when the object is loaded from a DataReader or DataRow, and also when a set of parameters are produced for an insert or update operation. A value has been nominated for each basic data-type in .Net to represent null values, and the framework maps these values to and from a database null. Examples include: DateTime.MinValue; Double.NaN, Integer.MinValue, Decimal.MinValue, Guid.Empty. String and binary data use ‘Nothing’ (null) to represent a database Null.

Caching – Default/Optional Behavior
By default, the class generator creates classes that include a useful caching mechanism. This is quite useful for most situations, except where:
1. The table data is currently (or potentially) too large to be stored in application memory
2. The table data is primarily write-only e.g. an error log.
3. A number of independent applications update the same database, with no common layer such as a webservice to co-ordinate or clear the cache e.g. a web-farm deployment scenario.

If a class employs caching for a particular table, then the associated class will expose a static property called “Cache”, returning a custom collection. In such a case, any type of select-where query on that table is best implemented as a custom index on the custom collection returned by the cache. Such an index is always supplied for the primary-key column, (typically called “GetById”) returning either a single object or null, depending on the value(s) supplied. In such a case, if you wanted to look up a single record, use:

	Dim example As CExample = CExample.Cache.GetById(exampleId)
instead of:
	Dim example As New CExample(exampleId)

	Dim exampleList As CExampleList = CExample.Cache.GetByStatusId(statusId)
instead of:
	Dim exampleList As CExampleList = New CExample().SelectByStatus(statusId)

	CExample.Cache.GetByStatusId(EStatus.Active).GetByLocationId(ELocation.NewZealand)

Updated Wiki: 3. Technical Overview

jeremyconnell — Sun, 19 Jul 2009 07:43:02 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Technical Overview:
The design utilizes a code generator to rapidly produce 90-100% of the O/R-mapping classes (Business Objects) for database persistence. It generates lightweight classes in VB.Net or C#, which inherit most of their functionality from abstract framework classes. These provide a structured environment for customization, business logic and reusable code, including standardised region names.

The recommended choice of architecture employs parameterized queries as the underlying technology (as opposed to stored procedures or sql strings), in order to dynamically implement the set of trivial database operations known as CRUD methods i.e. atomic-level select/insert/update/delete. CRUD methods support fully functional O/R classes, and typically constitute between 90-100% of stored procedure requirements.

Benefits:
This design brings 4 key benefits, specifically:
1. Cost - Rapid development & maintenance via code-generation and structure.
2. Quality – High level of code consistency, with minimum scope for human error
3. Concurrency – Dynamic update statements are inherently less likely to produce data conflicts from concurrent user updates.
4. Portability - Keeping the number of stored procedures to a minimum (or nil) greatly improves database portability. When changing platform, tables can usually be imported, but stored procedures/functions/views have to be manually rewritten.

By using the same driver-specific parameters, it maintains equality on 3 other key issues:
1. Performance – The parameters separate the data from the SQL statement, allowing it to be pre-compiled and cached by the database in exactly the same way that a stored procedure is, giving it an identical performance characteristic.
2. Security – This technique is not vulnerable to SQL injection attacks (and errors), because it uses the same driver-specific parameter collections to transmit the data.
3. Transactional – Can perform large batches of CRUD operations within a transaction context to implement a transactional behavior, or just to share an open connection for performance.

Stored procedures:
Sometimes a few additional stored procedures are provided for such things as complex reporting, database-intensive tasks, or rare performance hacks. Theoritically this logic can always be implemented in the .Net layer using CRUD methods, but is occaisonally it is well-suited to a stored procedure implementation.

It is very straightforward to implement the stored procedure calls in the business layer on an ad-hoc basis, using very simple helper methods that take the procedure name and an optional list of named parameters (in the form of a Dictionary object). From within the class, you can use MakeList(...) if the columns match to create business objects, or DataSrc.ExecuteDataset(...) for ad-hoc queries.

Alternatively, at the time the classes are generated, one can select an option that causes the classes to be generated using a different pattern, such that stored procedures are generated for all CRUD methods, and the dynamic query capability is absent. There is no advantage to using this approach, other than to fulfill a possible DBA requirement that all database activity must use stored procedures.

DBNull Equivalent values
Whenever data is accessed using the generated business objects, the troublesome issue of null values in the database is taken care of automatically. Specifically, this occurs when the object is loaded from a DataReader or DataRow, and also when a set of parameters are produced for an insert or update operation. A value has been nominated for each basic data-type in .Net to represent null values, and the framework maps these values to and from a database null. Examples include: DateTime.MinValue; Double.NaN, Integer.MinValue, Decimal.MinValue, Guid.Empty. String and binary data use ‘Nothing’ (null) to represent a database Null.

Caching – Default/Optional Behavior
By default, the class generator creates classes that include a useful caching mechanism. This is quite useful for most situations, except where:
1. The table data is currently (or potentially) too large to be stored in application memory
2. The table data is primarily write-only e.g. an error log.
3. A number of independent applications update the same database, with no common layer such as a webservice to co-ordinate or clear the cache e.g. a web-farm deployment scenario.

If a class employs caching for a particular table, then the associated class will expose a static property called “Cache”, returning a custom collection. In such a case, any type of select-where query on that table is best implemented as a custom index on the custom collection returned by the cache. Such an index is always supplied for the primary-key column, (typically called “GetById”) returning either a single object or null, depending on the value(s) supplied. In such a case, if you wanted to look up a single record, use:

	Dim example As CExample = CExample.Cache.GetById(exampleId)
instead of:
	Dim example As New CExample(exampleId)

	Dim exampleList As CExampleList = CExample.Cache.GetByStatusId(statusId)
instead of:
	Dim exampleList As CExampleList = New CExample().SelectByStatus(statusId)

	CExample.Cache.GetByStatusId(EStatus.Active).GetByLocationId(ELocation.NewZealand)

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Sun, 19 Jul 2009 07:38:34 GMT

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: Home

jeremyconnell — Wed, 08 Jul 2009 21:31:04 GMT

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Latest Release: 2.3
Auto-Upgrade: http://www.picasso.net.nz/codegenerator/publish.htm

Introduction:
Speed up your .Net 2.0 development with this powerful, flexible, and transparent Object-Relational data persistance framework.

Compares favourably with mainstream ORM solutions, particulary on its simplicity, transparancy, customisability, portability, and ease-of-regeneration when schema changes.

Configurable template-based code-generator rapidly converts your Relational or Xml schema into a easy-to-use, fully functional, highly customisable set of .Net 2.0 business objects, which extend powerful abstract classes.

Powerful dynamic queries meant you can rapidly develop a major .net application without ever writing SQL. Also supports the older option of using auto-generated (+customised) stored-procedures (if your dba insists on it).

All the usual bells and whistles, plus a few extras including:
- Easy to regenerate code as schema changes, whilst preserving all customisations (uses Partial Classes, Generics)
- Support for multi-column primary keys in the O/R model.
- Handle any type of relations (1-1, 1-n, n-n)
- Aggregates (SUM, AVG, MIN, MAX, COUNT)
- Powerful queries (joins and complex where expressions), plus easy support for stored procedures.
- Generates code (copy & paste) for relationship-mapping properties, allowing easy & efficient navigation around data relationships
- Transactional - Easily supports batches of operations within an ADO.Net Transactions (not MSDTC/TransactionScope)
- Paging support, including both sql-based, and in-memory paging
- Easily talk to several database instances simultanously
- Handles circular references without duplication.
- Natural intergration with xml data (e.g. xml blobs in db, or xml import/export functions)
- Can co-exist with other frameworks i.e. no constraints on db (other than suggested naming conventions)
- Total Db-Platform Independance and Optimisation. Includes Oracle, MySql, MSAccess, SqlServer, etc, plus two custom webservice-based ADO.Net drivers
- Sophisticated in-memory caching option on a table-by-table basis (managed, indexed), greatly improving performance in most circumstances.
- Option of a low-level audit trail to record full details of all inserts, updates and deletes.
- Easy to use code generator app, with high-quality fully-editable templates and logic
- Framework provides powerful abstract classes to minimise schema and application code.

Support options are available, including skype,msn,etc (rates neg.

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Credits: Thanks to http://www.nvinteractive.co.nz/ for their full adoption and support
Sponsors: www.teameffect.com and www.swapster.co.nz

Updated Wiki: Home

jeremyconnell — Wed, 24 Jun 2009 22:10:48 GMT

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Latest Release: 2.3
Auto-Upgrade: http://www.picasso.net.nz/codegenerator/publish.htm
Credits: Thanks to http://www.nvinteractive.co.nz/ for their full adoption and support

Introduction:
Speed up your .Net 2.0 development with this powerful, flexible, and transparent Object-Relational data persistance framework.

Compares favourably with mainstream ORM solutions, particulary on its simplicity, transparancy, customisability, portability, and ease-of-regeneration when schema changes.

Configurable template-based code-generator rapidly converts your Relational or Xml schema into a easy-to-use, fully functional, highly customisable set of .Net 2.0 business objects, which extend powerful abstract classes.

Powerful dynamic queries meant you can rapidly develop a major .net application without ever writing SQL. Also supports the older option of using auto-generated (+customised) stored-procedures (if your dba insists on it).

All the usual bells and whistles, plus a few extras including:
- Easy to regenerate code as schema changes, whilst preserving all customisations (uses Partial Classes, Generics)
- Support for multi-column primary keys in the O/R model.
- Handle any type of relations (1-1, 1-n, n-n)
- Aggregates (SUM, AVG, MIN, MAX, COUNT)
- Powerful queries (joins and complex where expressions), plus easy support for stored procedures.
- Generates code (copy & paste) for relationship-mapping properties, allowing easy & efficient navigation around data relationships
- Transactional - Easily supports batches of operations within an ADO.Net Transactions (not MSDTC/TransactionScope)
- Paging support, including both sql-based, and in-memory paging
- Easily talk to several database instances simultanously
- Handles circular references without duplication.
- Natural intergration with xml data (e.g. xml blobs in db, or xml import/export functions)
- Can co-exist with other frameworks i.e. no constraints on db (other than suggested naming conventions)
- Total Db-Platform Independance and Optimisation. Includes Oracle, MySql, MSAccess, SqlServer, etc, plus two custom webservice-based ADO.Net drivers
- Sophisticated in-memory caching option on a table-by-table basis (managed, indexed), greatly improving performance in most circumstances.
- Option of a low-level audit trail to record full details of all inserts, updates and deletes.
- Easy to use code generator app, with high-quality fully-editable templates and logic
- Framework provides powerful abstract classes to minimise schema and application code.

Support options are available, including skype,msn,etc (rates neg.

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 04 Jun 2009 05:37:05 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.3).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). The templates for this feature are obviously team-specific, and will need to be customised to your particular style of web application layout. For example, the new type of a web-projects have a slightly different way of displaying dynamic usercontrols, so the type of project you normally use will also influence template design.The current set of templates demonstrate an approach based around proprietary UI controls.

Additional Notes on UI code generator:
1. A convenient template editor (and import/export feature) has been provided, and users are encouraged to modify the templates to suit, and to export and share their modified template sets around to their team members.
2. If you are using the old-style web projects, you will need to use a simple technique to get visual studio to auto-generate the contents of the *.designer.* files. First include the files in your project. Open the aspx or ascx file (make sure the relevant designer file is NOT opened or it wont work), then insert a whitespace character into any server-side tag e.g. in between 2 attributes, then save and close the file. The designer file will be regenerated, providing there are no major html errors in your template. Templates intended for newer-style projects should have completely empty templates for the designer files, and the code generator will not create those files.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks. Its a good idea to also add a project-level import to bring these classes into scope.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabase" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or app_data folder (e.g. just the filename if its in app_data folder). This logic is implemented in the useful property called Framework.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 04 Jun 2009 05:24:50 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.3).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout. For example, the new type of a web-projects have a different way of displaying dynamic usercontrols, as demonstrated in the sample web project (code gen templates are based on the older-style web project). Personally I use a different set of templates based around proprietary controls, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export feature) will be provided in the next release, and users are encouraged to modify the templates to suit.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks. Its a good idea to also add a project-level import to bring these classes into scope.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabase" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or app_data folder (e.g. just the filename if its in app_data folder). This logic is implemented in the useful property called Framework.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 2. Useful Tips for Schema projects

jeremyconnell — Thu, 04 Jun 2009 05:21:23 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Relationship-Mapping Properties
Use the Relationship-Mapping tab (described in 1. Quick Start Guide) to generate code to model all the relationships in your schema, taking care to accurately identify the correct foreign keys involved. Note that several different standard patterns are used, depending if one or both entities use caching.

Once you have these relationships implemented, you can build on them to implement more complex properties, depending on your application/business logic requirements. For example, you might need to get data by navigating across several child relationships, or implement recursive behaviour with respect to parent relationships, such as CategoryFullName.

  Public ReadOnly Property CategoryNameFull(Optional ByVal delimiter As String = "::") As String
    Get
      If IsNothing(Parent) Then Return CategoryName
      Return String.Concat(Me.Parent.CategoryNameFull, delimiter, Me.CategoryName)
    End Get
  End Property

Databinding Tweaks
For databinding to datagrids, you might want to add extra properties to account for the fact that asp.net controls do not easily support the indirection (fullstop) operator, e.g. "Category.CategoryName" doesnt work (because it contains a fullstop), but you can add a custom readonly property called "ParentCategoryName" which internally returns the same expression, and which can be used in asp.net late-binding. Alternatively, one can avoid the use of late binding by using dynamic usercontrols instead of late-binding, as this offers other advantages such as performance and compiler checks.

Bulk Database Activity
Generated list classes all include bulk methods such as SaveAll and DeleteAll, which in turn use low-level methods for transactional bulk operations (e.g. CDataSrc.BulkSave). For best performance you should explicitly clear the cache for all tables involved (if those tables use caching), immediately before the bulk save/delete, to avoid having to maintain the cache during the bulk operation. You may wish to inspect the implementation of these bulk methods for examples of how to use ADODB transactions.

Xml Data Columns
If a column stores xml data, make the corresponding property protected, and instead expose an instance of the class that models the root node of that schema, using a private member and public read-only property. The member will only be instantiated when the property is first hit (lazy-loading), and the save method will check if it has been instantiated, and if so, should serialize this back to the xml string and store it in the protected column property, to be included in the update statement as normal. This provides a seamless integration of relational and xml data from the perspective of the application layer.

 'Xml presented using high-level objects (Note: Save method should serialise this object back to the string colum, if property has been hit)
  Private m_sideImages As XmlImages.CImageSet
  Public ReadOnly Property SideImages() As CImageSet
    Get
      If IsNothing(m_sideImages) Then
        m_sideImages = New CImageSet(Me.CategorySideImageName) 'Uses a dom object and custom classes to parse & present the xml
      End If
      Return m_sideImages
    End Get
  End Property

BLOB columns (large objects)
If a column stores a large amount of data, such as a file upload, it is generally more efficient to treat this column separately from the others, by not loading it automatically. Firstly, the SelectColumns property should be overridden to list all columns except the BLOB column (instead of *), so that blob data is not pulled from the database by default. Secondly, the load methods (from datareader/dataset) should be modified by commenting out the line that sets the member variable (also, move these methods from the auto-generated partial class to the customisable one). Finally, add some custom methods to Select and Update just that column, using AbstractionLayer helper classes to assist with the Ado.net.

Encrypted Columns
There are some helpful methods to assist with data encryption, including one pair to very quickly encrypt/data data using an XOR (bitwise) encryption, and another using the triple-des algorithm. Both features obtain their keys from standard configuration settings, and both offer some additional overloads for an alternative key or provider, plus overloads for string, byte-array, and stream-based interfaces. See AbstractionLayer.CBinary.Encrypt and other similar methods. It is recommended that you have a pair of properties that differ only by a suffix, with one being the encrypted/decrypted version of the other, and one exactly reflecting the actual column. If the encryption is one-way (e.g. password hashing the SchemaMembership.CUser), the one property will be readonly and the other will be writeonly.

To use these in the data model, there are 2 general approaches. The first is to decrypt the data when it is read into the business object, and encrypt it at the point where the parameters are assembled for an insert or update. The disadvantage is that decrypted data is stored in memory, which is less secure, so the other (suggested) approach is to have a second read/write property that presents/accepts a decrypted version of the data, and performs the encrypt/decrypt in the get/set before passing it to the original property. For example, if you had a column and corresponding property called “CreditCardNumberEncrypted”, then you would add an additional property (in the customization side of the partial class) called “CreditCardNumberDecrypted”, which does the conversion on-demand.

Audit Trail
A schema project can extend one of the base classes (such as CBaseDynamic), adding a thin layer of extra functionality such as an Audit trail. An example of this is given in SchemaAudit, which introduces a couple of mustoverride methods to load & serialise the objects, and it also overrides the save & delete logic to record a snapshot of the data before it was deleted or modified, along with some identifiying information. The audit trail provided is very easy to use - simply reference SchemaAudit, and when generating classes, don't untick the checkbox called audit-trail (except for logging tables etc).

Inheritance in the data model
Occasionally there is a need to map a table to an abstract class, having several derived classes. Typically, an integer-typed column known as a differentiator is used to control which concrete class should be instantiated when the records are read out of a database.

For example, one website project I had had a table called "tblSpots", consisting of a SpotId (primary key), a SpotType (differentiator), and a SpotText (long-text) columns. The SpotType would determine how to interpret the data in SpotText, which might be: text/html; a filename (image uploader); or xml data from a particular schema. When the records were loaded from the database, different classes were instantiated according to the value of the differentiator, all having the same abstract base class. In each case, the SpotText column was scoped as a protected property, and the data instead exposed using a meaningful alias property of type string, such as "FileName" or "Text", and in the case of xml data this was exposed as a class representing the root node of the schema (auto-generated with the XSD class generator).

Another example I have encountered was a table that implemented a tree-structure with a self-referencing parentId column, and also had a differentiator column (i.e. FundId, FundName, FundType). There were a number of different fund types, with business rules controlling which types could parent which other types. These business rules were too complex to easily enforce with a normal database constraint, and so these constraints were instead implemented by the different classes that modelled each fund type, all of which shared the same common abstact base class, but also modelled the properties that were unique to that fund type, such as various strongly-typed collections of child funds.

To implement inheritance, the following approach is recommended:
1. Auto-generate the class from the table as normal
2. Mark the class as abstract
3. Mark the differentiator column as protected, read-only, and must-override (abstract). Discard the member variable that was used to store this information.
4. Create at least one class to inherit from the abstract base class.
5. Modify the two factory methods in the base class that are responsible for instantiating objects from a datareader and a dataset. These methods now need to "peek" at the datasource to read the value of the differentiator column, and use that information in a switch statement to decide which concrete class to instantiate. The datasource is then supplied to the constructor as normal, and a specific object returned.
6. If required, a collection class can be created for each derived class, e.g. to enforce type-based constraints on parent/child relationships.

Winforms & WebServices Efficiency
When deployed within an intranet environment, a win-forms application can usually be configured to connect directly to the database, and this will typically give better performance that connecting via a web-service. However, for extranet or remote deployments, a web-service interface is the only option. Fortunately this scenario can easily be configured – simply set the driver to be “webservice”, and the connection string to be the URL where the web-service is deployed.

The problem with this scenario is that even on a fast connection, there can be a second or so latency between requests, so if your application is doing a lot of small calls to the database, it can be very slow. To optimize for this environment, it is firstly advised to use the caching option, so data is stored, indexed, and retrieved locally, once the cache is initialized. Secondly, it is advisable to initialize the various data caches simultaneously, using a single call to the web-server, while the application is starting up.

To do this, you need to create a data-transport class to store the data that will be sent i.e. one collection class for each cached table. This is best defined in the schema project, since that project is already present on both the client and the server, allowing it to be serialized/de-serialized correctly. You can then add an application-specific web-service to transfer the binary data. The server side will use a method in the Framework project called CBinary.CompressToBytesAndZip(), which serializes the data and compresses it, returning a byte array to send. The client side will use the companion method CBinary.DecompressFromBytesAndUnzip(), and cast it back to the original type, which is the transport class described above. Finally, the members of this class are used to directly initialize the client-side cache for each applicable table. Note: See also CBinary.Pack and CBinary.Unpack, for encrypted versions of the same thing.

Similarly, for bulk save/delete operations, you should either use the SaveAll/DeleteAll methods generated with the list classes, or use the low-level equivalent methods (CDataSrc.BulkSave and CDataSrc.BulkDelete) if there is a mixture of classes involved.

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Sat, 04 Apr 2009 02:32:19 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.3).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout. For example, the new type of a web-projects have a different way of displaying dynamic usercontrols, as demonstrated in the sample web project (code gen templates are based on the older-style web project). Personally I use a different set of templates based around proprietary controls, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export feature) will be provided in the next release, and users are encouraged to modify the templates to suit.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks. Its a good idea to also add a project-level import to bring these classes into scope.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabasePath" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or appdata folder (e.g. just the filename if its in appdata folder). This logic is implemented in the useful property called AbstractionLayer.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

 
    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

 
    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

 
    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: Home

jeremyconnell — Fri, 03 Apr 2009 22:41:27 GMT

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Latest Release: 2.3
Auto-Upgrade: http://www.picasso.net.nz/codegenerator/publish.htm
Credits: Thanks to http://www.nvinteractive.co.nz/ for their full adoption and support, and Zainal Shah for help with porting templates to C#

Introduction:
Speed up your .Net 2.0 development with this powerful, flexible, and transparent Object-Relational data persistance framework.

Compares favourably with mainstream ORM solutions, particulary on its simplicity, transparancy, customisability, portability, and ease-of-regeneration when schema changes.

Configurable template-based code-generator rapidly converts your Relational or Xml schema into a easy-to-use, fully functional, highly customisable set of .Net 2.0 business objects, which extend powerful abstract classes.

Powerful dynamic queries meant you can rapidly develop a major .net application without ever writing SQL. Also supports the older option of using auto-generated (+customised) stored-procedures (if your dba insists on it).

All the usual bells and whistles, plus a few extras including:
- Easy to regenerate code as schema changes, whilst preserving all customisations (uses Partial Classes, Generics)
- Support for multi-column primary keys in the O/R model.
- Handle any type of relations (1-1, 1-n, n-n)
- Aggregates (SUM, AVG, MIN, MAX, COUNT)
- Powerful queries (joins and complex where expressions), plus easy support for stored procedures.
- Generates code (copy & paste) for relationship-mapping properties, allowing easy & efficient navigation around data relationships
- Transactional - Easily supports batches of operations within an ADO.Net Transactions (not MSDTC/TransactionScope)
- Paging support, including both sql-based, and in-memory paging
- Easily talk to several database instances simultanously
- Handles circular references without duplication.
- Natural intergration with xml data (e.g. xml blobs in db, or xml import/export functions)
- Can co-exist with other frameworks i.e. no constraints on db (other than suggested naming conventions)
- Total Db-Platform Independance and Optimisation. Includes Oracle, MySql, MSAccess, SqlServer, etc, plus two custom webservice-based ADO.Net drivers
- Sophisticated in-memory caching option on a table-by-table basis (managed, indexed), greatly improving performance in most circumstances.
- Option of a low-level audit trail to record full details of all inserts, updates and deletes.
- Easy to use code generator app, with high-quality fully-editable templates and logic
- Framework provides powerful abstract classes to minimise schema and application code.

Support options are available, including skype,msn,etc (rates neg.

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 02 Apr 2009 07:37:40 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.2).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout. For example, the new type of a web-projects have a different way of displaying dynamic usercontrols, as demonstrated in the sample web project (code gen templates are based on the older-style web project). Personally I use a different set of templates based around proprietary controls, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export feature) will be provided in the next release, and users are encouraged to modify the templates to suit.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks. Its a good idea to also add a project-level import to bring these classes into scope.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabasePath" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or appdata folder (e.g. just the filename if its in appdata folder). This logic is implemented in the useful property called AbstractionLayer.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

 
    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

 
    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

 
    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 02 Apr 2009 07:09:47 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.2).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout, which may involve your own proprietary controls. I have a different set of templates for this reason, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export) will be provided in the next release.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks. Its a good idea to also add a project-level import to bring these classes into scope.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabasePath" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or appdata folder (e.g. just the filename if its in appdata folder). This logic is implemented in the useful property called AbstractionLayer.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

 
    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

 
    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

 
    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 02 Apr 2009 07:06:40 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Prerequisites
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.2).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout, which may involve your own proprietary controls. I have a different set of templates for this reason, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export) will be provided in the next release.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabasePath" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or appdata folder (e.g. just the filename if its in appdata folder). This logic is implemented in the useful property called AbstractionLayer.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

 
    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

 
    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

 
    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 02 Apr 2009 07:05:39 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Quick Start Guide
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll). Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (e.g. version 2.2).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file. Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button. Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not. Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout, which may involve your own proprietary controls. I have a different set of templates for this reason, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export) will be provided in the next release.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabasePath" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or appdata folder (e.g. just the filename if its in appdata folder). This logic is implemented in the useful property called AbstractionLayer.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

 
    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

 
    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

 
    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Updated Wiki: 1. Quick Start Guide

jeremyconnell — Thu, 02 Apr 2009 07:01:59 GMT

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Quick Start Guide
To begin with, you should have:
- Visual Studio 2005 or 2008 (or equivalent .Net 2.0 environment) installed.
- Code Generator application installed (includes scripts and dlls such as Framework.dll).
Download from http://www.picasso.net.nz/codegenerator/publish.htm, or compile your own from the source code (2.2).
- A solution that includes some kind of application project, such as a website, winforms or console application.
- Either a Relational Database Schema (in the form of tables and relationships) that you want to access, or an Xml schema such as an XSD file that you want to work with (if you have a sample xml file, then Visual Studio can generate an Xsd file). Note - schema should ideally follow strict naming conventions for entities and columns, as they form the basis for a lot of generated code.

Tier-1 Project (Framework Layer):
This free, open source project provides a range of helper methods to allow your application to more easily work with relational databases, xml data, binaray data, asp.web controls, etc.
It is also used in conjunction with the CodeGenerator app to provide powerful abstact base classes for your Tier-2 projects.

Some commonly-used helper methods from this project include:
- CDataSrc.Default - Reads the connection string, presents a ADO.Net wrapper object to easily perform low-level SQL-based commands.
- CDataSrc.ExportToExcel - Many overloads, based on datasets or arrays.
- CBinary.SerialiseToBytesAndZip - Persist and compress in-memory objects into binary data.
- CBinary.Encrypt - Several overloads, defaults to 3DES algorithm using key from config settings.
- CDropdown.GetInt - Reads the selected value from an asp.net and converts it to an integer.
- CTextbox.SetDate - One of a pair of methods to read/write dates to/from an asp.net textbox
- CUtilities.RequestInt - Read a querystring variable and convert it to an integer
- CUtilities.NameAndCount - Builds a string based on the name of an entity, and the number of child records it owns.
- CConfigBase - Abstract class providing methods to easily read and cast config settings, and documenting some system settings.

Tier-2 Projects (O-O Models of your Schema):
For each relational database schema (or xml schema) that you have, create a new class library to represent that schema in the 2.0 Framework, and add a reference the Tier-1 project called Framework.dll. You may wish to remove references to 3.5 components to avoid confusion with LINQ.

Xml Schema: (XSD)
1. Always store a copy of the .xsd file in the root of your schema project for reference, along with a sample xml file if you have one. Note that Visual studio can automatically create an XSD file from a sample xml file.
2. Open the CodeGenerator application, and use the tab called "Xml Schema" to browse for a schema file.
Screenshot: CodeGen_BrowseXmlSchema.PNG
3. Check that the output path is pointing to the root of your schema project (should be the case if step 1 was done), and generate classes.
4. Refresh your solution explorer and include the new files.
5. Customise the *.customisation.* partial classes as required, and regenerate the *.regenerated.* using the tool whenever the schema changes. Note that the xml class generator only handles relatively simple data structures, but more complex data structures (e.g. abstract entities) can be manually coded, and the code-generator updated to your preferred design pattern if necessary.

Relational Database Schema:
1. If the schema is potentially reusable (e.g. SchemaMembership), then store a copy of the sql scripts in the project, with separate scripts for schema then data. 2. If you want to use the AuditTrail feature (see the checkbox on the class generator) in your application, then your schema project should reference the SchemaAudit project (or dll). You will also need to run the script to create the tables, if they are not already present in your database.
3. Open the CodeGenerator application, and use the first tab to connect to the database using the "Test Connection" button.
Screenshot:CodeGen_BrowseDatabaseConnection.PNG
4. From the second main tab, browse to the root folder of the schema project to identify any existing classes, and create new ones.
5. Generate classes for each table, one table at a time, taking care with details such as: the decision to use caching or not; the identify and nature of the primary key(s); the list of foreign keys; the default sort order; and any special columns such as controlled ordering. There is more information on this in section below called "More details on generating classes from tables" (including a screenshot)
6. Refresh the SolutionExplorer (visual studio) and include the new files in your project.
7. Now use the next subtabs called "Relationship-Mapping" to assist with generating code to model the relationships between tables. These are very important properties that will shorten your UI code and business logic considerably. Be sure to have a database diagram in front of you before you start this task, and check that constraints have been defined in the database. At least 2 properties are generated for each relationship (one for each entity). There is also a link-button provided to toggle the perspective between the two entities involved in a relationship. Note that different patterns are produced, depending on whether the parent, child, or both entities are cached or not.
Screenshots: CodeGen_RelationshipsSimple.PNG and CodeGen_RelationshipsAssociative.PNG
8. Customise the *.customisation.* partial classes with business logic (and common presentation logic) as required, and regenerate the *.regenerated.* files using the tool whenever the schema changes.
9. Occasionally you may require a different sort order (other than the detail order specified when the class was generated. For this, there is another subtab called "Alternative Sorting" generating a small code snippet to to this.
10. Finally, the is another sub-tab called "Asp.Net", which is a very powerful UI code generator, designed to automatically generate search/list and add/edit pages (and associated usercontrols). Note that there is a simple technique to get visual studio to auto-generate the contents of the *.designer.* files - the instructions are in the empty generated designer file. The templates for this feature are obviously application-specific, and will need to be customised to your particular style of web application layout, which may involve your own proprietary controls. I have a different set of templates for this reason, so the open-source templates supplied have not been heavily tested. A convenient template editor (and import/export) will be provided in the next release.

More details on generating classes from tables

Screenshot: CodeGen_GenerateClassesForEachTable.PNG

Once you have identified a connection string, and the location of your schema project (output folder), you need to configure the following:
1. Select a language (C# or Vb.net).
2. Select the architecture (i.e. an abstract class) from the 3 options, using the default first option if unsure, or the last option if stored procedures are compulsory (advanced option, currently disabled for simplicity).
3. Decide whether to use the Audit trail feature (requires a script and an extra project reference)
4. If all your tables have a common prefix then enter that prefix (a common prefix is recommended for all tables in a schema, to separate those tables from other schema in the same database).

Then for each table in the schema, you need to:
1. Define the identity and nature of the primary key. Normally this is just a single column, first in the list, having an auto-increment behaviour, and the code genererator will usually guess this correctly/
2. Decide if caching is appropriate for the data in this table (uncheck if not), and whether the audit trail feature will be used, and then hit the “Generate Classes” button, which launches a folder-browse dialog.
3. Enter a default Order-By clause (if applicable), which will be used for select queries and array sort methods.
4. If you have a special column whose sole purpose is to provide ordering, then nominate this column to generate moveup/movedown methods in the business layer (and up/down arrows in the UI generator).
5. A view optionally can be nominated for select statements, to bring across some extra read-only properties or apply database-level business logic, but this approach has now been depreciated in favor of migrating business logic out of the database, and using the in-memory cache for resolving relationships such as id->name lookups.
6. Identify any columns that might be used as a search filter, such as foreign key columns or Boolean fields (if not already identified automatically based on naming conventions). Simple queries will be provided for each of these columns.
7. Check the class name (code generator will attempt to guess the singular version of the table name, after its prefix has been removed, then hit the "Generate Classes" button.

Notes on circular references
Your relational schema projects can reference one of your xml schema projects, e.g. if a database column contains xml data. Alternatively, if your xml data contains keys that refer to a database, you can reference in the other direction e.g. to look up names for the keys. However, in general visual studio does not allow 2 projects to reference each other (referred to as circular references), so you should design your project structure so the precidence is clear (or alternatively, combine the projects that need to reference each other into a single project)

Tier-3 Projects (Your Application Layer):
1. Your application project(s) must reference the project (or dll) called Framework.dll in order for your Tier-2 projects to function. This also provides a range of helper methods and abstract classes, e.g. to provide short syntax for common tasks.
2. Your application project(s) should also reference whichever of your schema projects it needs, depending on the type of data it needs to access and manage. Its also a good idea to add a project-level import for the main schema project(s), especially if one main schema will likely be used on most pages/forms.
3. If you reference a relational database schema project, then you also need configure a default connection string (and driver) in the web.config (or app.config) file (see next paragraph for details. Some applications may use their own system of config settings (e.g. to manage a range of different connection strings), and can provide those to the schema classes whenever they are used. Also, some schema projects may provide a hard-coded connection string automatically, in which case config settings are not required.
4. Refer to step #10 in the previous section, regarding auto-generation capabilities for UI-level code (e.g. asp.net or .

More Details on Connection Strings
For storing database connection strings, the default option is a pair of configuration settings: “Driver” and “ConnectionString”, where driver can be “oledb”, “sqlserver”, “oracle”, etc (you can easily add more). There is also two shorthand versions for MSAccess or SqlExpress databases, using a single config setting called "AccessDatabasePath" or "SqlExpress". These options support absolute paths, or paths relative to the website root, or appdata folder (e.g. just the filename if its in appdata folder). This logic is implemented in the useful property called AbstractionLayer.CDataSrc.Default.

A second option is to provide a similar custom property in your Tier-2 schema project (e.g. CConfig.DifferentDataSrc), which reads your own system of configuration settings, and uses them to create a single static/shared instance of a CDataSrc (such as CMySql). This data source will become the default for that schema if you adjust a protected method called DefaultDataSource in each of the table-mapping classes. This feature can either be used to customise the connection-string storage format, or to connect to several databases simultanously.

The third option is to implement this property at the application level, and pass it to the constructor of the business objects when they are instantiated. For example, the application may need to talk to several database instances with the same schema, and control this by supplying different data sources to the object constructors at different times. If this is used with caching, you need to implement the caching at the application layer.

Common Tasks - Using your Schema project (Relational) from your Application project
1. Insert a new record

With New CSample()
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

2. Insert a new record into a different database

With New CSample(CConfig.My2ndDataSrc) 'Static property returning an instance of a CDataSrc, such as CMySql
    .SampleName = "Test"
    .Save()
    MsgBox.Show(.SampleID)
End With

3. Update an existing record (Non-cached version) having ID=44

With New CSample(44)
    .SampleName = "Test2"
    .Save()
End With

3. Update an existing record (Cached version) having ID=44

With CSample.Cache.GetById(44)
    .SampleName = "Test2"
    .Save()
End With

4. Delete an existing record (Non-cached version)

With New CSample(44)
    .Delete()
End With

5. Delete an existing record (Cached version)

With CSample.Cache.GetById(44)
    .Delete()
End With

6. Select-All (Cached version)

 
    CSample.Cache     'Returns a customisable CSampleList, which inherits List(Of CSample)

7. Select-All (Non-Cached version)

 
    With New CSample()     'Returns a customisable CSampleList, which inherits List(Of CSample)
        .SelectAll()
    End With

8. Select-Where (Cached version)

 
    CSample.Cache.GetByParentId(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)

..where GetByParentId is used to access a custom index in the collection class, typically generated automatically

9. Select-Where (Non-Cached)

    With New CSample()
        .SelectByParentID(55)  'Returns a customisable CSampleList, which inherits List(Of CSample)
    End With

..where the implementation of SelectByParentId might look like...

   Dim criteria as new CCriteriaList()
   criteria.Add("ParentId", 44)
   return mybase.SelectWhere(criteria) 'Many overloads for this method, including single-line syntax, OrderBy, etc

10. Bulk Save operation

    Dim newItemsList As New List(Of CSample)
    for (...)
   {
      Dim sample As New CSample()
      sample.SampleName = "test"
      newItemsListAdd(sample)
   }
   CDataSrc.Default.BulkSave(newItemsList) 'See also BulkDelete & BulkSaveDelete - their implementations are a good example of using transactions

Home
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview