OxyGen Code Generator Overview
Table of Contents
Previous Topic: Introduction
Next Topic: Specifying Table Processing Instructions
Processing Instructions provide additional information on how the tables and columns should be used to produce T-SQL and C# code.
Processing Instructions are applied against the following schema elements
i) Table
ii) Column
iii) View
Table Processing Instructions are the following:
i) Select (Custom Query)
ii) Resolve Many To Many (Join Table Resolution)
iii) Reference Type Marker
iv) Entity Name
v) Cacheable
vi) Get All Order Criteria
vii) Search
viii) Persistent Storage
ix) Updateable Primary Key
View Processing Instructions are the following:
i) Select (Custom Query)
ii) Resolve Many To Many (Join Table Resolution)
iii) Reference Type Marker
iv) Entity Name
v) Cacheable
vi) Get All Order Criteria
vii) Search
viii) Persistent Storage
ix) Updateable Primary Key
x) Virtualization Settings
Column Processing Instructions are the following:
i) Field Name
ii) Identifier Indicator
iii) Persistent Storage
The Select Processing Instruction lets a user specify a Custom Query. Ordinarily, columns that are used as part of a Key (primary key or foreign key) will automatically be used as filter conditions for the built-in queries. To generate queries using columns that are not part of a primary key or a foreign key, use a Select Processing Instruction.
Figure 2: Tables in TechIS.OrderingSystem Customer Table
In Figure 2 above, a set of data retrieval queries (and the associated Stored Procedures and methods) will be generated for the table. This set of queries will use the CustomerID column (the Primary Key).
To generate additional sets of queries, use the Select Processing Instruction. For example, you may want to generate a set of queries that use FirstName and LastName columns as filter conditions. This can be achieved with a few clicks using the built-in query builder. The next chapter of the walkthrough tells you how to do that.
In a situation where there is a join table between two primary tables, using the Resolve Many To Many Processing Instruction will instruct OCG to generate code that resolves or de-normalizes the join table.
Figure 3: The relationship between the 'Customer', 'CustomerService' and 'ServiceInstance' tables
In other words, in the three table system (Figure 3): ‘Customer’, ‘CustomerService’ and ‘ServiceInstance’, the generated Customer class will include methods and properties that will have direct access to the customer’s services. Also the generated ServiceInstance class will include methods and properties that will point to the customer.
When the Reference Type Marker Processing Instruction is specified, additional stored procedures and methods are generated that will make it easier to use reference tables (Static tables).
In the AdventureWorks database that comes with SQL Server 2005, two examples of reference tables are the AddressType and ContactType tables.
In the NorthWind database that comes with SQL Server 2000, the best example of a reference table is the Region table.
Figure 4: The ServiceType table is a reference table
In the TechIS.OrderingSystem database, the sample database that comes with OxyGen Code, the ServiceType table is an example of a reference table.
Use the Entity Name Processing Instruction to assign a name, other than the table name, to the class that will be generated from a particular table. By default, the OCG will use the table name as the class name.
The usage of the Entity Name Processing Instruction can be stylistic or compulsory.
Stylistic reasons to use the Entity Name Processing Instruction
i. Your table names contain prefixes or suffixes
ii. You want your classes to have names more in tune with the application domain
iii. Multiple tables in different schemas have the same name (for example we may have two Customer tables: Sales.Customer and dbo.Customer).
iv. The table name is a C# keyword. For example, if you have a table named ‘Class’, you will have to apply an Entity Name Processing Instruction against that table.
Use the cacheable processing instruction to include the object instance, from generated classes, into the OCG caching system.
Only the instances of classes that are generated from tables marked as Cacheable will be candidates for caching. If the underlying table data is changed, the cached objects are expired from the cache (SQL Server 2005 only).
By default, all tables with the Reference Type Marker Processing Instruction are cached. Caching is only recommended for tables that change infrequently. This means that transactional tables are bad candidates for the Cacheable Processing Instruction.
In the TechIS.OrderingSystem database, all the tables except the ServiceType table are bad candidates for the Cacheable Processing Instruction.
Caching the wrong table will have an adverse effect on the performance of your application and your SQL Server machine.
Use the Field Name Processing Instruction to assign a name, other than the column name, to the Field and Property that will be generated from a particular column.
By default, the OCG will use the column name as the field name. The usage of the Field Name Processing Instruction can be stylistic or compulsory.
Stylistic reasons to use the Field Name Processing Instruction
i. Your column names contain prefixes or suffixes
ii. You want a name that is more in tune with the application domain
iii. The columns were initially poorly named
Compulsory reasons to use the Entity Name Processing Insruction
i. A column name contains a space
ii. The column name is the same as the table name.
iii. The column name is a C# keyword.
Use the Get All Order Criteria Processing Instruction to specify the order of results generated by Select queries while paging of results is turned on. By default, all results returned by paged Select queries will be shown in the descending order according to the primary key columns.
The Search Processing Instruction allows the user to create complex types of queries on a given table or view. Using a clause generator, the user can specify columns, operations between them (Intersection or Union), as well as, define Order By clause for the result set. This Search Processing Instruction will create a Where clause based on the choice of the user and the type of data used in the query. In case of numerical data, the query will include the range comparison (i.e. if a given date is within a specific period of time). In case of text data, the query will include text pattern comparison clause.
In a situation where there are columns or rows in the database which have some restrictions related to database operations (insert, update, delete), using the Persistent Storage Processing Instruction will instruct OCG to generate code that takes into account this kind of restrictions.
Compulsory reasons to use the Persistent Storage Instruction
i. A column of the view or table is not insertable or updateable.
ii. A table or a view is not insertable, updateable or deletable.
By default, for all tables, views and their columns are insertable, updateable and deletable.
Use the Updateable Primary Key Processing Instruction to specify if the primary key of a given table or view is updateable. In some specific situations the user requires to update the primary key and using of this Processing Instruction enables this opportunity.
By default, for all tables and views it is not allowed to update the primary key.
Use the Identifier Indicator Processing Instruction to specify the column which is the identity column of a given table or view.
Use the Name Indicator Processing Instruction to specify the column which is the name column in a given table or view.
The Virtualization Settings Processing Instruction allows the user to specify additional properties of the views. The Virtualized Table is a view with the specified Virtualization Settings Processing Instruction.
In a situation where the user wants to specify the Primary Key of the view, using the Virtualize Primary Key option from Virtualization Settings Processing Instruction will instruct OCG to generate code that virtualizes the Primary Key.
In a situation where the user wants to specify the Foreign Keys of the view, using the Virtualize Foreign Keys option from Virtualization Settings Processing Instruction will instruct OCG to generate code that virtualizes the Foreign Keys.
The Virtualization Settings option called Replace Virtualized Table is used in the situation where the user wants to replace tables by views based on them. In this case tables are hidden from the end user and only virtualized tables are visible.
The Virtualization Settings option called Remove Relationships from Virtualized Tables removes relationships which exist between tables used to create virtualized tables.
OxyGen Code Generator Overview
Table of Contents
Previous Topic: Introduction
Next Topic: Specifying Table Processing Instructions