Daniel Flower Dot Com Banner

Specifying DBColumns

Shunde is able to automatically populate and save objects to the database because the database column which each field in the object model corresponds to is defined in the object's static constructor.

This is achieved by creating a DBTable object and assigning columns, for example:
 
static Car()
{
    DBTable tbl = new DBTable("Car", new DBColumn[] {
        new DBColumn( "make", typeof(string), 1, 50 ),
        new DBColumn( "year", typeof(int), false, 1900, null ),
        new DBColumn( "owner", typeof(Person), false ),
        new DBColumn( "description", typeof(string), true ),
        new DBColumn( "isConvertible", typeof(bool), false )            
    });
    ObjectInfo.RegisterObjectInfo(typeof(Car), tbl);

}
 

To see the exact data types that Shunde can understand, and how Shunde maps a .NET data type to its SQL equivalent, have a look at this page.

The remainder of this page will look into more detail at how to specify the DBColumns for your objects, and very importantly, how to specify valid values.  First we will look at each data type, and then look at things which apply to all data types.

Strings

You can specify a string in your model as either being a single line with a maximum length, or a multiline string which can contain a lot of data.  In either case, if the string has zero characters, then it is saved as null in the database, and conversly a null value in the database gets converted to an empty string when an object is populated.  This means rather than checking for null, you can always check if the length is 0 for a string, and you won't see annoying null reference exceptions when doing string operations.

Multiline Strings

A multiline string is saved to the database in an ntext column, which means you can save multiline strings, big text documents, etc.  You will want to use these whenever you want people to be able to enter data in a textarea field.

To specify a multiline string, simply declare the column like this:

new DBColumn("columnName", typeof(string), allowNulls)

Where allowNulls is true or false, depending on whether you want to allow zero-length strings.  There is no way to specify a maximum length for multiline strings.

Single-line Strings

For a single line string, you need to specify a minimum length and a maximum length.  For example, to have a string which must be 10 - 100 characters long, you would use:

new DBColumn("columnName", typeof(string), 10, 100)

Or, to allow the field to be optional, you would set the minimum length to be zero:

new DBColumn("columnName", typeof(string), 0, 100)

To specify that a string must have a minimum length, unless it is a zero length string, you need to specify that the column is allowed to have nulls separately: 

DBColumn col = new DBColumn("columnName", typeof(string), 5, 100);
col.AllowNulls = true;

This could be useful if, for example, an email address is optional, but if it is specified it must be at least 5 characters, for example.

Numbers - shorts, ints, longs, floats and doubles - and Dates

The declaration of DBColumns for numbers is always the same: column name, data type, and a bool to specify if nulls are allowed, and optionally a minimum value and a maximum value.  For example, a float that can be null:

new DBColumn("columnName", typeof(float), true)

Or an integer with minimum and maximum values of -10 and 10 respectively, that cannot be null:

new DBColumn("columnName", typeof(int), false, -10, 10)

or dates (for example, to specify a date must be between the 1st of January 1900 and 2999):

new DBColumn("columnName", typeof(DateTime), false, new DateTime(1900,1,1), new DateTime(2999,1,1))

If you have only a minimum value, or only a maximum value, enter the other value as null. For example, a double that must be at least 10.5 but can be null:

new DBColumn("columnName", typeof(double), true, 10.5, null)

When specifying the valid values, you must make sure the minimum and maximum values are of the same type.  It is a common mistake to have, for example, a column of type float, but valid values as doubles, for example:

new DBColumn("columnName", typeof(float), true, 10.5, null)

This is incorrect, because "10.5" will be interpereted as a double! So it should be: 

new DBColumn("columnName", typeof(float), true, 10.5f, null)

A note on nulls

You may be wondering how an int, DateTime, float etc can be null.  Well, in a database they can be - and this is useful (for example, a null DateTime for a date of birth column may mean that it is unknown) - but in .NET they cannot.  We get around this by specifying the value to be a value that, in practice, would probably never be used in code.  For numbers, this is normally the minimum value possible in that number, however a better way is to use defined constants in the DBColumn class. For example, to set an int to be "null":

int myInt = DBColumn.IntegerNullValue;

When saved, this will become null in the database.  You can check whether any value is equivalent to a null by calling:

DBColumn.IsColumnNull(object anyObjectOrNumber); 

Booleans

Booleans are the easiest to specify, because they cannot be null and no validation is possible. So, it is always declared as:

new DBColumn("columnName", typeof(bool))

BinaryData

Binary data is also very easy to specify.  The only difference is that you need to specify whether nulls are possible or not: 

new DBColumn("columnName", typeof(BinaryData), true)

More on BinaryData structures can be found on this page (link coming soon).

DBObjects

You can save a reference to any type of DBObject, and specify whether or not to allow nulls, for example, a link to a Car object (presuming it is a subclass of DBObject) which cannot be null:

new DBColumn("columnName", typeof(Car), false)

Or a link to any kind of DBObject in your database, which can be null: 

new DBColumn("columnName", typeof(DBObject), true)

Linking to "DBObject" is especially useful when you have an object associated with any kind of object. For example, in one project I had a Media object (e.g. an image, video, etc), and set the "owner" to be a DBObject.  I could then associate media with people, projects, places, or anything else, and they could all share the same code.

Unique Columns

You can specify that a column of any type be unique, however this really only makes sense for strings, numbers and dates.  This is very useful for email addresses, or usernames, which must be unique in your database.  In this example, the email column cannot be null, must be between 5 and 100 characters, and must be unique:

DBColumn emailCol = new DBColumn("email", typeof(string), false, 5, 100);
emailCol.IsUnique = true;

In this case, a unique index will exist in the database, and when calling Save() an error will be thrown if this constraint is not honoured.  You can also specify a column as being unique AND allowing nulls.  In SQL Server, this means only one column can be null, however in Shunde, it means "unique, unless null".  In this case no physical index is created in the database; rather Shunde enforces this rule itself.

A note on validation

You may be wondering where the validation options you enter above are used.  They are mainly used in two places: the DBObject.Save() method and in the ObjectEditor (another page will examine that).

When you call Save() on a DBObject, each field is checked to see if it contains valid data.  If not, a Shunde.ValidationException exception is thrown.  A ValidationException's Message property always contains an explanation that is alright for the end-user to see, normally because it is their fault there is an error.  For example, if the minimum length of an email field is 5 characters, and someone has written "a@b", then a ValidationException will be thrown upon Save() being called.  This should be caught, and the message should be shown to the user. In this case, the message would be: "The minimum length for email is 5 characters.  You have written 3 characters." 

 
 
 
Comments for this page
blSbukHFlnB
posted by Angelina on 1/10/2012 11:12:07 a.m. (NZ time)
My dear friend and thaeecr,I can't remember the woman I was when we met just some unformed, wretched, possible human in whom you could see beauty.And because you saw beauty in me, and continue to see it in me, I have become beautiful.I will always be part of your life because of that.I will watch you dance with your red lipstick on your lovely and perfectly proportioned lips WWouldn't miss it!I want to know what you know and I want to study what/where you study.
Add your comment below
 
Your Name:
Comment Title:
Comment: