Schema Tool

The Schema Tool is the engine which provides the most interesting workings of the LymeTools application. The Schema Tool analyzes data in order to automatically generate code to support CRUD, search and reporting UI and API endpoints that communicate with a database.

It was originally developed to serve a completely different purpose by helping to determine what SQL Server data types mapped to .NET C# data types for the development of Entity Framework models. It started off by studying the output of the columns sourced from a Stored Procedure query and would output a list of the columns and their respective C# data type mapping. With that list of columns, we now had a schema available to use in generating UI code and C# WebApi code to support those schemas.

A schema item is similar to what you would see in a SQL Server designer, except it is more generic and can be manually defined or use a different type of data store to define schema (e.g. an Excel Spreadsheet (currently supported) or a different database format (coming soon).

The schema tool generates the following code snippets from a list of Schema Items.

Schema Item

Schema Helper

The Schema Helper is a C# class library that is responsible for analyzing the schema from various data sources and take inventory of the columns / properties of the input data source and return a list of schema items that are used by the Code Generator, described below this section. At the time of this writing, the Schema Helper can interpret the schema for SQL Server query output and Excel Spreadsheets, but will be expanded to include CSV, JSON, and C# class definitions.

FUTURE: Tools will also be built to manually build schema item lists. These tools will be employed by the Scaffold Manual Feature tool.

Code Generator

The Code Generator is another C# class library that is responsible for generating code from a list of schema items. The code generator has one main method that is called to generate various App UI and API code for common CRUD and Reporting scenarios. At risk of becoming too technical, below is the main method that is called to generate code and it should be readable enough to interpret what the various outputs are from the code generator. We will expand more on this in the Examples section of this document.

public CodeGeneratorResults GenerateCodeFromSchema(SchemaToolOptions options, IList<SchemaItem> schema)
{
    _options = options;
    var retVal = new CodeGeneratorResults
    {
        Schema = schema,
        TableHtml = GenerateHtmlTableFromSchema(schema),
        FormHtml = GenerateFormCodeFromSchema(schema, false),
        SearchFormHtml = GenerateFormCodeFromSchema(schema, false, true),
        TableFormHtml = GenerateFormCodeFromSchema(schema, true),
        CsharpEntityClass = GenerateCsharpClass(schema),
        CsharpSearchClass = GenerateCsharpClass(schema, CsharpClassType.SearchForm),
        ViewHtml = GenerateViewHtmlFromSchema(schema, false),
        CsharpSearchQuery = GenerateCsharpSearchQuery(schema),
        CsharpSearchResultClass = GenerateCsharpClass(schema, CsharpClassType.SearchResult),
        ReportAngularCode = GenerateReportCode(schema)
    };
    return retVal;
}

Conventions

As of the writing of this document, the following conventions are assumed. As this tool matures, configuration options will be introduced to override these conventions.

• Singular named data tables.
• Id is the Primary Key field on all entity tables.
• Foreign keys and EF Navigation properties match the Entity Name. The foreign key for an entity should be named [Tablename]Id. Example: Two entities exist: Product and ProductType. ProductType should be referenced by naming it's foreign key field in the Property table as: PropertyTypeId.

Using the Schema Tool

When first navigating to the Schema Tool, a user is presented with a few basic options. Below is a screenshot.

1. Configuration ID - As you use the schema tool, session information gets stored to a local database. This will allow for a user to save a session and come back to it later if they need to move onto something else.
2. Session Name - The name of the saved session.
3. Data Connection - Specify the SQL Server Instance information for the Schema Tool to use. Either select an existing connection or add a new connection
4. SQL - Specify the SQL Query that will produce output for the SchemaHelper to analyze. This can be either a SELECT query or a Stored Procedure call.
5. Overriding Class Name - By default, the entities generated by the CodeGenerator will match the name of the table or stored procedure that is executed. Many times the default table names or stored proc names can be awkward, so this field allows a user to override the class name in that entity related output.

Schema List Output

For this documentation, we will use a simple schema as an example. Consider the following SQL Server table created by the following hypothetical sample table in SQL Server called "Contact":

I tried to include a diverse set of properties with lots of special business / real-world use cases. For example, a phone number should be formatted as (xxx) xxx-xxxx ... or a bit field could be formatted as "Yes" or "No". Another example is that IncomeAnnual is a money datatype and should be formatted as such. Also note that some of these fields allow for NULL values (no input required).

This table can be created in SQL Server if you want to follow along with the example by executing this CREATE SQL Statement:

CREATE TABLE dbo.Contact
(
    Id int NOT NULL IDENTITY (1, 1),
    Name varchar(255) NOT NULL,
    Phone varchar(50) NULL,
    EmailAddress varchar(50) NOT NULL,
    DateOfBirth date NOT NULL,
    LastUpdated datetime NULL,
    IsUsCitizen bit NOT NULL,
    IncomeAnnually money NULL,
    HeightInInches int NULL,
    WeightInPounds decimal(18, 2) NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Contact ADD CONSTRAINT
PK_Contact PRIMARY KEY CLUSTERED 
(
    Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Now that this table is created, I return to the Schema Tool feature in LymeTools and enter the following SQL Query: SELECT * FROM Contact

Pressing the "Submit & Save" button will save the state of your data entry and retrieve from the API results from the SchemaHelper and Code Generator via a tabbed interface. A user can navigate each tab and use the "Copy to Clipboard" button to copy and paste the result code manually into their code editors.

For more complicated use-cases and a more practical example on how to use the UI embodied by the "Configure Fields" button, please see the Scaffold a Generated Feature documentation.

Below are the outputs from the Code Generator for our Sample Schema.

Sample Output from Schema Tool

See this page to see sample output from the Schema Tool.