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.13).
- 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 (schema-driven) projects, and some helper methods for Tier-3 (application) 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.DataSrc.ExecuteDataset("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)
   'Generic list:
    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

  'Or with a typed list:
  Dim typedList as new CSampleList
  ...
  typedList.SaveAll()


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

Last edited Nov 15, 2010 at 12:26 PM by jeremyconnell, version 62

Comments

No comments yet.