Tim's Iron Speed Blog

Quick Preview

Tim Titchmarsh — Tue, 07 Apr 2009 17:46:08 GMT

Just a quick detour, whilst we were working on the ecommerce stuff we felt we needed a quicker way of previewing the page as we were losing time waiting for the .Net compiler to build the pages.

We decided to work on a Quick Preview utility that enabled us to view the page in a flash without recourse to the .Net compiler. Our senior programmer Dylan grabbed the nettle and produced a very effective preview utility written in C# to run in Windows.

Now we can see whats going on it's time to press on with the development (and everything else that needs to be done!)

Database Schema

Tim Titchmarsh — Fri, 06 Mar 2009 13:01:37 GMT

For those of you that want to see where we are with the schema I have put together a PDF of the different diagrams. They are work in progress and not complete so may well change as we go forward.

ECommerce Schema - SQL Server 2005

"Work in progress"

Download now

Ecommerce Database Design

Tim Titchmarsh — Fri, 06 Mar 2009 10:38:00 GMT

After getting my feet wet yesterday with V6 I wanted to step back a bit and look at the design for the database. We will be using SQL Server 2005, you should find the project works with SQLExpress as required.

Schema

The database design is very import as far as ISD is concerned. It relies on the table relationships in order to determine how best to provide the quickest and most efficient experience. What I mean by that is that when creating pages with bound data you tend to find on the whole that you want to see data that is related to some other data on the page. For example, when creating a SalesOrder page you will probably want to see related data, the obvious one being SalesOrderDetails. Not so obvious would be the customer this order is for and the status of the order. By linking these in the db, ISD will know how best to create the page layout for you using the wizard, it will use dropdown lists of information that saves you a lot of work. If you see what you consider a FK item that is rendered as a text box rather than a dropdown its a fair bet you do not have a relationship set up in the db. Go back and create the relationship before trying to move on with ISD is my advice.

You can of course create a virtual FK relationship, a relationship that exists only in ISD, no changes are made to your db schema.

SQL Server Good Design Practices

Over the years I have seen many different designs, some I though were awful some I thought Wow "I must use that in my design". This list is my based on my personal preferences and may well differ from yours - it probably does.

Use well defined names for your tables and fields ie SalesOrder, not order
Use the singular ie SalesOrder not SalesOrders
Use Leading capitals for name ie SalesOrder not salesorder
Use words that describe what it is ie SalesOrder
Avoid spaces and other punctuation ie SalesOrder not sales order or tblSales order, avoids having to put [] around the name all the time
Use a single field PK, even though you may have other fields that could compound to give you the PK I find it better not to
Use the name of the table appended with ID as the name of the PK field where possible ie SalesOrderID
Make the first field the PK field
Follow the PK with the FK fields
Use bit fields to store bool yes/no values and start the name with Is ie IsEnabled
For tables that store type information ie a lookuplist append the word Type ie CurrencyType or CountryType
Avoid using table/field names that you know clash with reserved words ie avoid a table called transaction, instead call in SalesTransaction
Add audit fields so you know who created and when and who edited and when. I add these as the last 4 fields.
Add a sort order field so you can order the items, great for when alphanumeric is not good enough
Use the Database diagrams to visualise your schema and makes creating relationships a breeze.
Use Indexes to speed up queries, use the Profile Analyser to make suggestions. Not so good for ISD as the sps's use temp tables which the analyser doesn't seem to like.
Use unique constraints/indexes. Avoids having duplicate data in the db where it shouldn't be.
Use GUID's (uniqueidentifiers) for key fields. Many reasons for it which can be debated at length, I find they work, are globally unique, more secure than ints, avoids errors when all other PK start at 1 and increment by 1, hard to see the wood for the trees when this happends, allows you to set it in code or use sql server default (newid())
Add a DisplayText field that is a computed column that you can define as required, I point them to other fields in the table and then in ISD always use this field as the Display Field As for dropdowns etc. If I need to change it later I just change the formula in the computed field in SQL Server
It is not always possible to delete records, instead use a IsDeleted bit field to mark the field as deleted. Ensure all data is filtered by this.
I also add a couple of extra fields where needed, IsHidden and IsEnabled that allow the row to be filtered by the admin and the user, ie if as an admin I don't want a user to login any more I set the IsEnabled to false, if later they pay the subs I simply flip the flag. The IsHidden can be used to filter items that appear in dropdown lists for example without deleteing it.
Add a autoincrement field but not for use as the PK, can be useful when creating invoices for example and you need a number to increment.
Use nvarchar fields, ensures all languages are catered for
Use varchar(max) when needed, avoid ntext or image unless really needed.
Try to offload any image fields (for documents or images you want to store in the db) in a seperate database or table and just have a pointer to them. That way you can reduce server load as you don't always want the image data coming back when selecting data, only read the image data as and when needed, avoids select * bringing it back. You can also backup the image db separately. (case in point I have an exisitng client that has over 6GB of data in a database, 5.6 GB of that is PDF and word documents. If that was in a separate db downloading the main db would take only 10 minutes not 10 hours)
Use triggers only when needed and insure you really understand them. I have had many head scratching experiences with them, code them carefully and test them well.
Use views, I use them a lot with ISD when I just want to show read only data with multiple joins, ensure you can determine a virtual PK. I also use them to create a dummy table just so I can get ISD to create the page for me, ie a Report template.
Use functions for repeated code, ie I have a suite of functions that return 1st day of month, 1st day of quarter, 1st day of year etc
Keep user permissions to a minimum, its easy to give the user dbo permissions, try not to!
When you have some functionality that requires speed put in in the db. Move it out of the app to a sp, you gain speed but loose the strongly typed environment
Write your stored procs so it can be tested and benchmarked, use SQL profile analyser and multiple users, it's funny how it always works with just 1 user!
Use Red Gate SQL toolbelt for syncing your changes, its money well spent.

Sample Address Schema

USE [LSITSECommerce]
GO
/****** Object: Table [dbo].[Address]    Script Date: 03/06/2009 11:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Address](
   [AddressID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Table_1_ProductTypeID_1] DEFAULT (newid()),
   [DisplayText] AS ((((((isnull([Address1]+', ','')+isnull([Address2]+', ',''))+isnull([Address3]+', ',''))+isnull([Town]+', ',''))+isnull([Region]+', ',''))+isnull([Postcode]+', ',''))+[dbo].[fnGetCountryName]([CountryTypeID])),
   [Address1] [nvarchar](80) NOT NULL,
   [Address2] [nvarchar](80) NULL,
   [Address3] [nvarchar](80) NULL,
   [Town] [nvarchar](80) NOT NULL,
   [Region] [nvarchar](80) NULL,
   [Postcode] [nvarchar](20) NOT NULL,
   [CountryTypeID] [uniqueidentifier] NOT NULL,
   [Telephone] [nvarchar](20) NULL,
   [Fax] [nvarchar](20) NULL,
   [GeoMapFix] [nvarchar](100) NULL,
   [SortOrder] [smallint] NULL,
   [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_Address_IsDeleted] DEFAULT ((0)),
   [IsHidden] [bit] NOT NULL CONSTRAINT [DF_Address_IsHidden] DEFAULT ((0)),
   [DateCreated] [datetime] NOT NULL CONSTRAINT [DF_Address_DateCreated] DEFAULT (getdate()),
   [CreatedBy] [nvarchar](30) NOT NULL CONSTRAINT [DF_Address_CreatedBy] DEFAULT (suser_sname()),
   [DateModified] [datetime] NULL CONSTRAINT [DF_Address_DateModified] DEFAULT (getdate()),
   [ModifiedBy] [nvarchar](30) NULL CONSTRAINT [DF_Address_ModifiedBy] DEFAULT (suser_sname()),
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
   [AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Jumping straight in

Tim Titchmarsh — Thu, 05 Mar 2009 17:43:05 GMT

I shall be covering the db design shortly, but for now I just wanted to create a few pages in V6 to get a feel for the new features. I would advise against creating to many pages to start with, start small and build up, add pages only when you need them. This will ensure a faster and less painful approach should you need to radically redesign.

I'm starting with a single table, the address table. This will hold address information used in the app as a customer may have many addresses. I wanted to keep it down t a single table at this stage, I can always add more later of course.

The first thing that I noticed when using the wizard to create the pages was the number of pages created off that single table. There must be at least 9 different pages of varying types. If you more or less automatically created you should change the wizard options before you start. that way you get to decide what gets created. Once the pages is created changing the settings won't help you when you regenerate as this page already exists, those new settings apply to new pages.

I deleted the app and restarted again and this time changed the settings before creating the pages. I opened up the AddPageRecordPage to see what the result was. Hmm the fields have been laid out to some kind of formula but I don't know what? You do get some settings that dictate 1, 2, 3 column layout or auto. I could not work out how the fields were laid out, I would expect them to be as per db order by default, the field list in the toolbox is correct but the layout seems random. I can fix this by editing the fields ordering manually. I can also remove fields I don't want to see which I always have to do as ISD creates just about all fields by default. In reality you have to spend time rearranging and getting it how you want. Once thats done the page looks good. I now need to do the same for the edit page... It would be great if you could with a single click copy the fields across from the add to the edit page. Also good would be able to duplicate a page, say the add page and then change it via a setting to edit page, it would save alot of typing and configuration. I must submit that as a suggestion. The guys at ISD do listen and take on board feedback so don't be shy, they won't know if you don't tell them! I can't wait to start adding in the related tab panels and start to nest some of the details pages, all very powerful stuff.

Thats it for now, more soon.

Tim

Change of Direction...

Tim Titchmarsh — Thu, 05 Mar 2009 16:06:40 GMT

Since my last entry things have changed! We have decided to change the topic of the V6 project, instead of the recruitment site we have decided to have a go at putting together an Ecommerce site. We shall start with the back end admin site as this is required to populate the products etc.

We have put together an schema in SQL Server 2005 and are currently checking the relationships and overall design. We won't wait until we think it's 100% complete as we may never start! I would rather get us to 80% and evolve the design as we come across problems that we hadn't (or tried to) anticipate.

V6 has also been launched and I know the guys at Iron Speed have been trying hard to get the message across re the new layout surface. I have to admit at first I was taken aback at the loss of the visual designer but there are some great features that save so much time when adding the more complex layouts that I am hoping I can get by without it. Time will tell and I will be giving you feedback on my experiences.

The live preview is a great new feature in practise, it's a bit slow for instant preview but it is in real time. This project may get a little on the above a small size project so I shall be eager to see what response times I get. I know the guys at Iron Speed are looking at this to see if it can be tuned, the .Net framework is the main cause of the delay, a delay that you would get when running the app in a browser in any case. We may have some news on this shortly...

Project Outline

Tim Titchmarsh — Thu, 19 Feb 2009 14:39:25 GMT

It's a cold morning and time to catch up. The sun is shining, the snow has stopped and the heating has been fixed so I can stop shivering at home (any excuse to stay in the office!).

Whilst we eagerly await the release of Iron Speed V6 we have been knocking around some ideas as to the kind of application we could develop to showcase Iron Speed.

In the end we went for a job seekers site. The concept is as follows:

Light Speed Recruitment Ltd (a fictitious recruitment company) is setting up in business and wants top open a series of recruitment agencies across different cities. Each agency will be autonomous and handle their own clients and job seekers but will report to head office and will file reports to them on a monthly basis.

Each agency will allow companies to sign up for their services for which they charge an annual fee and a fee per vacancy advertised. Ads can be posted on the site by the company as and when they have a job opening.

Jobseekers will be able to sign up to the agency and register their details for which there is no charge. Job seekers will be able to browse for vacancies and apply online. They will be able to upload the CV and add the CV to their job applications.

Projects such as these require a public client facing application that the whole world can see and other more private areas where you need to register and login. In addition a very private area that only the admin can view is required. Our aim is to split the project into 2 applications, the main one being the job seeker application and the other the admin application for the owner Light Speed Recruitment Ltd who wants to configure and view the overall application, sales, payments, agencies etc. We could lump the whole thing into 1 giant application but we feel it’s easier to split the two so they can be worked on separately in parallel and keeping the size of the main application more manageable. Of course they will share the same database.

Some of the concepts we plan to incorporate into the application are:

SQL Server 2005 Database

Good SQL Server database design for use with Iron Speed
Best use of 1 to many, many to 1 and many to many relationship scenarios in Iron Speed
Use of views in Iron Speed
Tips for integrating SQL Server with Iron Speed

Iron Speed Designer

Where to start
Producing 80% of your application in short order
What is takes to get the other 20% complete
Code customisation
Integrating with 3rd part controls
Adding paypal payments
Application role based security
Splitting the project into 2 applications – the main application and the admin application
Adding the bells and whistles
Deployment

Those are the basics, will we now start thinking about the use cases and what each user wants from the system. As these become clear we can start designing the database and getting a feel for the project. More on that soon.

We aim to use a series of iterations to keep the project on an agile footing, we don’t want to sit down and hammer out a detailed spec for 3 months and then start developing, we want to do it bit by bit so we can change course, bring on board new ideas and include any feedback from you guys.

Software required:
Iron Speed V6 (of course…)
SQL Server 2005

Ideally Visual Studio 2008 if you are serious about code development, debugging, etc We shall be doing the code customisations in C# and will also be trying to satisfy the VB crowd and to that end will be porting where possible using readily available tools.

For our own development work we use Microsoft Team Foundation Server to handle the project management and source control aspects.

This project has to fit around our normal development work (we are a small team, 7 last count!) but we will do our best to do a little each day and keep it moving forward. Please comment on the blog as we would love your feedback.

We aim to make the project available as a series of downloads as we progress, more on that later.

That’s it for now, I’m off to mull over some design ideas and hit the whiteboard.

Who are we & what do we do?

Tim Titchmarsh — Thu, 19 Feb 2009 14:29:07 GMT

My name is Tim Titchmarsh. I am an Iron Speed MVP for the MVP Consulting Organization "Light Speed Solutions" based near London in the UK. We provide Iron Speed design, consulting and training services to customers here in the UK, Europe, USA, Australia and beyond.

The aim of this blog is to help new and not so new Iron Speed Users get familiar with ISD 6.0 and demonstrate how application generation is a real alternative to hand-coding. To that end I thought it would be a good idea to try to create a real world application that shows of the best features off ISD 6.0 and at the same time being practical and useful.

Each day I hope to publish some thoughts, concepts and keep you to date on progress and invite you to comment and provide feedback. I hope to start with the basic design for the application (more on that later) and take you through the various stages that my team would follow when creating apps for ourselves and clients. At the end we should have a working application that I hope you will find useful and informative.

Application Generation - a real alternative to hand-coding

Tim Titchmarsh — Thu, 19 Feb 2009 14:19:56 GMT

Let’s be clear from the outset. It is possible to create real .Net web applications that require no hand coding with Iron Speed. My company has created many such web applications to support internal processes for ourselves and our clients. Admittedly the apps err of the simplistic side but are by no means simple. They all hook up to SQL Server databases that provide valuable information in a quick, simple and reliable fashion. And when I say quick I mean 30 minutes from initial generation to live roll out.

From the 30 minute app things start to get more complex as requirements get more demanding and require more thought and more planning. What Iron Speed does for you is to take away the drudgery of hand coding the same old data access, plumbing and user interface code over and over, it does it in an instant and what’s more it’s reliable. The code works time after time and is consistent and predictable.

Of course the database design that Iron Speed takes as its starting point is crucial for an efficient code generation process, getting the database design right will pay dividends in the long run – more on that later.
If you decide you want additional functionality in your design or need to add some custom coding then you are starting from a well known and established platform. Remember Iron speed applications are just .Net web applications at the end of the day using aspx pages, custom controls etc etc so you will be going with the flow not against it.

To save time and money we want to keep hand coding to a minimum. My aim is just that with this project, to make use of the functionality out of the box and only use custom code where needed.