dbclass  Customer {
  primary key long ID[AutoGenID = true];
  string Name[100];
}

CREATE TABLE [dbo].[Customer](
  [ID] bigint NOT NULL PRIMARY KEY, 
  [Name] nvarchar(100) 
)

CREATE PROCEDURE [dbo].[Customer_ins] (
@P_ID bigint, 
@P_Name nvarchar(100))
AS BEGIN
    INSERT INTO [dbo].[Customer] (
      [ID], 
      [Name]) 
    VALUES (
      @P_ID, 
      @P_Name);
END;

And likewise for the delete, update and selects...

public class Customer {
    public long ID {
        get { return id; }
        set { id = value; }
    }
}
...

Roadblock. You may choose either 1 or 2 from the options below

• Construct unit tests. As SQL is typically tested during runtime, find each and every combination where your data is accessed.


• Let the customer test code. He will typically find several that you missed in step 1 anyway.

Option 1 = 60

Option 2 = 0 + X + Y

query Customers for Customer {
}

CREATE PROCEDURE [dbo].[Customers] 
AS BEGIN
SELECT 
  [dbo].[Customer].[ID], 
  [dbo].[Customer].[Name]
FROM [dbo].[Customer];
END

And a slightly more complex query:

query AddressFor for Address {
  string Name[20] = Customer_lnk.Name;
}

This will be a pain:

CREATE PROCEDURE [dbo].[AddressFor] (@P_Name nvarchar(20)) 
AS BEGIN
SELECT 
  [dbo].[Address].[ID], 
  [dbo].[Address].[SteetAddress1], 
  [dbo].[Address].[SteetAddress2], 
  [dbo].[Address].[City], 
  [dbo].[Address].[CustomerID]    
FROM [dbo].[Address] 
  LEFT OUTER JOIN 
    [dbo].[Customer] ON 
    ([dbo].[Address].[CustomerID] = [dbo].[Customer].[ID])  
    WHERE 
    ( ([dbo].[Customer].[Name] = @P_Name ) ) ;    
END

• Create a new form.
• Drop a query component from toolbox
• Drop a data grid component.
• Set few properties

dbclass Address {
    primary key long 
      ID[AutoGenID = true];   
    string SteetAddress1[50];
    string SteetAddress2[50];
    string City[20];
    ...
    link Customer Customer_lnk: 
        long CustomerID;
}

CREATE TABLE [dbo].[Address](
  [ID] bigint  NOT NULL PRIMARY KEY ,
  [SteetAddress1] nvarchar(50) ,
  [SteetAddress2] nvarchar(50) ,
  [City] nvarchar(20) ,
  [CustomerID] bigint 
)

To access data through a link with Nolics.net you just write the following piece of code

string name = 
address.Customer_lnk.Name

Well, just type away:

SqlCommand cmd = con.CreateCommand();
cmd.CommentText = “Select Name from Custoner where CustomerID = @Customer_ID;”
SqlParameter param = cmd. CreateParameter(“@Customer_ID”, value);
cmd.Parameters.Add(param);
string name = cmd.

And then find and the error in the SQL. Can you spot the error?

CREATE INDEX Address_Customer_lnk_idx 
ON [dbo].[Address]([CustomerID])


CREATE PROCEDURE [dbo].[Address_ins] (
  @P_ID bigint, 
  @P_SteetAddress1 nvarchar(50), 
  @P_SteetAddress2 nvarchar(50), 
  @P_City nvarchar(20), 
  @P_CustomerID bigint)
AS BEGIN
    INSERT INTO [dbo].[Address] (
    [ID], 
    [SteetAddress1], 
    [SteetAddress2], 
    [City], 
    [CustomerID])
     VALUES (
     @P_ID, 
     @P_SteetAddress1, 
     @P_SteetAddress2, 
     @P_City, 
     @P_CustomerID)
;

dbclass Customer {
    ...
    relation Address[] Addresses;
}

SqlCommand cmd = con.CreateCommand();
cmd.CommentText = “Select StreetAddress1, StreetAddress2, City from Address where CustonerID = @Customer_ID;”
SqlParameter param = cmd. CreateParameter(“@Customer_ID”);
IDataReader reader = cmd.ExecuteReader();
While (reader.Read()) {
   // This is getting boring!
   ...
}

Add just one line

dbclass Customer {
    ...
    string VatCode[10];
}

and run the upgrade tool... the whole process takes AT MOST one minute - even if you were to use just one finger

A sad conversation with the pointy haired boss results

Programmer (knowing this will take time): Customer don’t need a new field.
Manager: Yes, he does.
Programmer: It is already late. Tomorrow.
Manager: Tomorrow we don’t have a customer.
Programmer (clearly irritated): OK but this will take hours.

Several days later someone finds out that someone forgot to modify the update procedure. The new field was NOT added to the SQL statement and the customer has been happily creating about a zillion records... with all of them missing the VAT code.

120 + 240 to fix the database or to create a simple interface for the customer to correct the data manually.

And oh yes, one seriously irritated customer

• Create a windows form
• Drop the necessary query components on it (which were, by the way, automatically added to your ToolBox).
• Drop a ReportViewer on the form
• Create new ReportViewer query
• Set the Nolics.net query as the datasource
• Drag some controls on the report
• Rebind the report to the query

dbclass Address {
    ...
    link Customer Customer_lnk[Constraint=Cascade]: 
        long CustomerID;
}

Nolics.net constraints will invoke the object level delete event

ALTER TABLE [dbo].[Address] 
ADD CONSTRAINT 
[Address_Customer_lnk_fk] 
FOREIGN KEY ([CustomerID]) 
REFERENCES [dbo].[Customer]([ID])

• Create an Oracle database.
• Change the connection string
• Run the Upgrade Tool

All done while the salesman was still waiting for an answer whether it was possible or not.

The salesman confesses: "Actually, I already promised the customer that we'll do it".

Gain a lot of respect.

Some spilled coffee, angry words and late night SQL dialect rework. Or then just lose the sale.

• Add 2 new projects from a template. Change 4 lines of code.
• Deploy the Web site.
• Deploy the application for single click deployment.
• Total: 20 minutes

Programmer: Can’t do. We have to rewrite whole application.
Manager: You must.
Programmer: I quit.