SQL To ASP Code generator for C# or VB

Overview

Point to a SQL Server database table, choose generation options, set the project location and generate.

Quick Start

Connect to a database, Set your project path( use a new empty folder), Check web.config file, right click the table in the tree view, choose asp.net generation, click generate. Run Visual Studio, File->open website, view your tablename.aspx file in browser. Done! Fine tune with different options to match your coding needs.

Quick Start Videos!!! https://sqltoaspx.codeplex.com/documentation

Requirements

  • Simple generated code
  • Specifically for big databases (multi million row) with many fields.
  • Fast time to deployment
  • Low learning curve
  • Limited 3rd party use

Used to create hundreds of database driven websites.

Summary:

  • An easy starting point for any project
  • Minimum amount of code with Maximum results
  • Verbose naming
  • Websites just work with no additional coding required.
  • Perfect database design, keys, relationships is not necessary.
  • Limited use of dependent libraries
  • Supports database defaults !!!

Output Language:
C# or VB

Comment Level:
None
Basic - Best Practice
Verbose - Deep explanation on each item.

Project Path:
Where you want the code to be generated. It's best to generate to a new folder until you get the hang of the generator.

Grid Coding Style:
  • GridView : Generates the master template using a ASP.Net GridView
    • Useful when find grained detail is not necessary in the master templates.
  • Repeater : Instead of a GridView generates Repeater code with the exact same output as the GridView.
Code Behind.
  • Useful when every other line of the master view needs a custom section, gives the developer a little more fine grained detail than using the GridView.
*CodeBehind: Generates each row and each cell in code for very fine grained master views
  • Useful when a single cell or multiple cells need specific styles, colors or values.

KeyPassing:
How pages know what the previous page was doing.
  • Request: Keys are passed on the request line. Useful for linking to specific detail records, users can see the key to the database on the request line. Should use caution when creating a public website. Or letting users go to any record.
  • Session: Useful for hiding the implementation and users will not be able to see the keys.
  • Inline: Instead of using a Master/Detail design, the Master loads an inline editor for editing details.

Paging:
  • None: No paging. Useful for tables with 0 to 20 items and you want all the items on the same page.
  • Basic: Default out of the box grid paging, useful for small result sets.
  • High Speed: Paging is done on the SQL Server through highly optimized sql, useful for multi-million row database tables and queries.

Connection String Style:
Where do you store the connection string information?
  • Web.config Connection Strings section
    • If you don't know which one to use this is the best choice. The connection string information is loaded from the web.config connection strings section.
  • Web.config app settings section
    • Appsettings useful for desktop applications that were converted to web applications, or if you want to keep all your settings in one place.
  • Global Class Variable
    • Useful for existing projects where non-asp.net libraries are used such as heavy use of the code section.
  • Session Variable
    • Useful if the database connections are different for each user.
  • Application Variable
    • Useful for legacy and template design applications.

Connection String Name:
What the connection string is named?
For Config, App Settings, Session, Application is the key used to identify the item.
For Global Class Variable it is expecting typically a static variable such as ClassName.VariableName.

Sorting:
Whether sorting is enabled or not. ( only available for gridview generations)

Source Code Files:
When checked it will generate the master/detail code and aspx files.
When unchecked it will generate any additional/support files needed such as css, javascript

Web.config file:
If you want a web.config file generated, most of the time you don't want to have your web.config file overwritten.

Demo Mode:
Will generate every iteration of every option(80+), used to test each and every scenario when doing coding.

After a table is selected on the tree view, and "New ASP Generation" selected

This lists all the column field names fields of the table, the datatype, the Master View options, the detail view options, Search Type, Identity Column, Data Format(for dates), Lookup Table Selector, Lookup Table Selected Table, Lookup Table Key, and Lookup Table Value.

!!Column Name
This is the column that was found in the in the table that was selected. You cannot change this field.

Type

Data type the datatype of the column selected. Supported data types: Identity Integer, nchar, nvarchar, text, char, varchar, bit, bigint, int, smallint, tinyint, float, real, numerical, decimal, money, smallmoney, datetime, timestamp, smalldatetime.

Master View Options

What fields are displayed on the Master view.
Typically you want to show enough fields to fill the screen, but not too many where the user will have to scroll.
Common fields that are not displayed, auto number fields, internal fields, insignificant fields and dates.

Master Detail Options

What fields are generated when reads/writes/view/data capture.
Many times fields you don't want to set or included on the ui are fields such as fields that are created by database defaults, or triggers, fields that are not editable such as default date, identity columns, internal fields.

Search Type

What type of searching will be generated.
Essentially you are deciding here whether searching is done with an Equals or a Like both, or Like Left or Like Right.

Data Format

How to handle dates.
Sometimes you want to see dates ( MM/dd/yyyy) other times you want to have (MM/dd/yyyy hh:mm:ss).
Leave blank - The default is language default.
date - generates jquery datepickers and formats with a basic date format.
datetime - generates a jquery datetimepicker and formats with a datetime format.

Lookup table selector

Choose table where a column is an database foreign key, or a application enforced foreign key.
For example if you have a CustomerOrder table with a CustomerID column, you would choose the Customer table.

Lookup table key selector

Choose the table key of the lookup table that you want to use.
For example if you have a CustomerOrder table with a CustomerID column, you would choose the CustomerID in the Customer table.

Lookup table Description

Choose the table Description column for use when data is shown.
This will generate dropdown boxes if the search area, display the description column if the master view, and dropdown boxes in the details/edit view.

Database design strategies.

Keep it simple.

It's best to keep your project and database as simple as possible to allow for maximum versatility.
The Code gen works best with a main table, with lookup tables for relationship tables.
You don't always have a choice with the data you must work with data out of your control
Use views to join 2 or more tables together to model the Master/Detail/Lookup table workflow.
  • for example
    • AnonymousCustomer table has First Name/Last Name/ACustomerID has a 1 to 1 with KnownCustomer with email address column. To use the generator make a view called vKnownCustomers that joins the two tables, and generate from the view.

Looking for a MVC Version

Try the MVCGenerator addin project.
http://MVCGenerator.codeplex.com

Last edited Jul 29, 2013 at 4:34 AM by DanielHarrod, version 8