Generating Database Tables using Attributes

• Download source files - 2.84 Kb

Introduction

We can use Attributes from .NET classes to generate a database schema - a database design including tables, columns, and data types - so that .NET objects can create their own database tables to be persisted into. Then we can extract this schema information to generate the SQL script to create the tables in a database, and to construct in-memory DataTable objects.

If a database structure changes over time then we need to ensure that updates to tables, such as adding new columns, are reflected in the classes that access the database.

Here, we will create subclasses of DataRow that are designed specifically for storing data from a particular database table. In cases where the underlying database schema will not change often, this can provide a very effective way to access databases. If your schema is likely to change frequently, or if you permit users to modify the database structure, it might be better to generate the DataTable objects dynamically by requesting schema information from the database and building the data tables on the fly.

We will demonstrate this by creating two tables, Author and Book, and will work with SQL Server. You could alter the code to work with Oracle or any other database engine.

The DataTable consists largely of boilerplate code, so we will define a base class DataTable object that can serve as a generic container for DataRow objects. The DataRow, however, needs to provide type-safe access to columns within the database, so we will subclass it.

The AuthorRow class will derive from DataRow, and includes properties for each of the columns within the underlying Author table. A DatabaseTable attribute will be added to the row class, and for each property that links to a column in the table, there will be a DatabaseColumn attribute.

DatabaseTable Attribute

The first attribute is used to mark a class, DataRow, with the name of the database table where the DataRow will be saved. Here is the code:

using System;
using System.Reflection;

/// <summary>
/// Attribute to be used on a class to define which database table is used
/// </summary>
[AttributeUsage(AttributeTargets.Class,Inherited=false,AllowMultiple=false )]
public class DatabaseTableAttribute : Attribute
{
  /// <summary>
  /// Construct the attribute
  /// </summary>
  /// <param name="tableName">The name of the database table</param>
  public DatabaseTableAttribute ( string tableName )
  {
    TableName = tableName ;
  }

  /// <summary>
  /// Return the name of the database table
  /// </summary>
  public readonly string TableName ;
}

The constructor accepts the name of the table as a string, and is marked with the Inherited=false and AllowMultiple=false modifiers. There is no method to alter the table name here, so a readonly field will suffice.

DatabaseColumn Attribute

This attribute is designed to be placed on public properties of the DataRow class, and is used to define the name of the column that the property will link to, together with such things as whether the column can contain a null value:

/// <summary>
/// Attribute to be used on all properties exposed as database columns
/// </summary>
[AttributeUsage(AttributeTargets.Property,Inherited=true,AllowMultiple=false)]
public class DatabaseColumnAttribute : Attribute
{
  /// <summary>
  /// Construct a database column attribute
  /// </summary>
  /// <param name="column">The name of the column</param>
  /// <param name="dataType">The datatype of the column</param>
  public DatabaseColumnAttribute ( string column , ColumnDomain dataType )
  {
    ColumnName = column ;
    DataType = dataType ;
    Order = GenerateOrderNumber ( ) ;
  }

  /// <summary>
  /// Return the column name
  /// </summary>
  public readonly string ColumnName ;
  /// <summary>
  /// Return the column domain
  /// </summary>
  public readonly ColumnDomain DataType ;
  /// <summary>
  /// Get/Set the nullable flag. A property might be better
  /// </summary>
  public bool Nullable = false ;
  /// <summary>
  /// Get/Set the Order number. Again a property might be better.
  /// </summary>
  public int Order ;
  /// <summary>
  /// Get/Set the Size of the column (useful for text columns).
  /// </summary>
  public int Size ;

  /// <summary>
  /// Generate an ascending order number for columns
  /// </summary>
  /// <returns></returns>
  public static int GenerateOrderNumber ( )
  {
    return nextOrder++ ;
  }

  /// <summary>
  /// Private value used whilst generating the order number
  /// </summary>
  private static int nextOrder = 100 ;
}

/// <summary>
/// Enumerated list of column data types
/// </summary>
public enum ColumnDomain
{
  /// <summary>
  /// 32 bit
  /// </summary>
  Integer,
  /// <summary>
  /// 64 bit
  /// </summary>
  Long,
  /// <summary>
  /// A string column
  /// </summary>
  String,
  /// <summary>
  /// A date time column
  /// </summary>
  DateTime
}

We have marked this attribute as inheritable so that we can create a class hierarchy for database rows, as it is likely that we will have some similar columns throughout each table within the schema.

The Attribute also has three other properties:

• Nullable, defaulting to false;
• Order, the order number of the column within the table;
• Size, the maximum number of characters allowed in a string type.

To define a Name column, we might use the following code:

[DatabaseColumn("NAME", ColumnDomain.String, Order=10, Size=64)]
public string Name
{
   get { return (string) this["NAME"]; }
   set { this["NAME"] = value; }
}

This defines a field called NAME, and will be generated as a VARCHAR(64) and will not allow null values as it is the default for the Nullable property. The DataRow class has an indexer that takes the name of a field (or ordinal) as the parameter. This returns an object, which is cast to a string before returning it in the get accessor above.

Creating Database Rows

Now, we'll create the two classes, Author and Book, which both derive from a common base class as they share some common fields. We'll also create a generic abstract class, GenericRow, which defines Name and Description properties. It is derived from DataRow, the base class for all database rows in the framework. Then, two classes will derive from GenericRow - one to represent an Author (AuthorRow) and another representing a Book (BookRow). These both contain additional properties, which are linked to fields within the database. Here is the code for it:

/// <summary>
/// Base class row - defines Name and Description columns
/// </summary>
public abstract class GenericRow : DataRow
{
  /// <summary>
  /// Construct the object
  /// </summary>
  /// <param name="builder">Passed in from System.Data</param>
  public GenericRow ( System.Data.DataRowBuilder builder )
    : base ( builder )
  {
  }

  /// <summary>
  /// A column for the record name
  /// </summary>
  [DatabaseColumn("NAME",ColumnDomain.String,Order=10,Size=64)]
  public string Name
  {
    get { return ( string ) this["NAME"] ; }
    set { this["NAME"] = value ; }
  }

  /// <summary>
  /// A column for the description, which may be null
  /// </summary>
  [DatabaseColumn("DESCRIPTION",ColumnDomain.String,Nullable=true,
                                                       Order=11,Size=1000)]
  public string Description
  {
    get { return ( string ) this["DESCRIPTION"] ; }
    set { this["DESCRIPTION"] = value ; }
  }
}

Deriving from DataRow requires that you create a constructor that accepts a single parameter, a DataRowBuilder. This class is internal to the System.Data assembly.

Two properties are defined, Name and Description, each of these is attributed accordingly:

[DatabaseColumn("NAME",ColumnDomain.String,Order=10,Size=64)]

and

[DatabaseColumn("DESCRIPTION",ColumnDomain.String,Nullable=true,
                                                       Order=11,Size=1000)]

The Nullable property is set to true so that we are not forced to define a description column. The order number is set to 11, so that the name and description columns are always kept together in the generated schema.

The Author row is constructed as follows:

/// <summary>
/// Author table, derived from GenericRow
/// </summary>
[DatabaseTable("AUTHOR")]
public class AuthorRow : GenericRow
{
  public AuthorRow ( DataRowBuilder builder )
    : base ( builder )
  {
  }

  /// <summary>
  /// Primary key field
  /// </summary>
  [DatabaseColumn("AUTHOR_ID",ColumnDomain.Long,Order=1)]
  public long AuthorID
  {
    get { return ( long ) this["AUTHOR_ID"] ; }
    set { this["AUTHOR_ID"] = value ; }
  }

  /// <summary>
  /// Date the author was hired
  /// </summary>
  [DatabaseColumn("HIRE_DATE",ColumnDomain.DateTime,Nullable=true)]
  public DateTime HireDate
  {
    get { return ( DateTime ) this["HIRE_DATE"] ; }
    set { this["HIRE_DATE"] = value ; }
  }
}

Here, we have subclassed the GenericRow class, and added in AuthorID and HireDate properties. Note the order number chosen for the AUTHOR_ID column - it is set to 1 to appear as the first column in the emitted table. So the table will be laid out as AUTHOR_ID, NAME, DESCRIPTION, and finally HIRE_DATE.

The BookRow class derives from GenericRow, so as to include the name and description properties. It adds BookID, PublishDate, and ISBN properties:

/// <summary>
/// Table for holding books
/// </summary>
[DatabaseTable("BOOK")]
public class BookRow : GenericRow
{
  public BookRow ( DataRowBuilder builder )
    : base ( builder )
  {
  }

  /// <summary>
  /// Primary key column
  /// </summary>
  [DatabaseColumn("BOOK_ID",ColumnDomain.Long,Order=1)]
  public long BookID
  {
    get { return ( long ) this["BOOK_ID"] ; }
    set { this["BOOK_ID"] = value ; }
  }

  /// <summary>
  /// Author who wrote the book
  /// </summary>
  [DatabaseColumn("AUTHOR_ID",ColumnDomain.Long,Order=2)]
  public long AuthorID
  {
    get { return ( long ) this["AUTHOR_ID"] ; }
    set { this["AUTHOR_ID"] = value ; }
  }

  /// <summary>
  /// Date the book was published
  /// </summary>
  [DatabaseColumn("PUBLISH_DATE",ColumnDomain.DateTime,Nullable=true)]
  public DateTime PublishDate
  {
    get { return ( DateTime ) this["PUBLISH_DATE"] ; }
    set { this["PUBLISH_DATE"] = value ; }
  }

  /// <summary>
  /// ISBN for the book
  /// </summary>
  [DatabaseColumn("ISBN",ColumnDomain.String,Nullable=true,Size=10)]
  public string ISBN
  {
    get { return ( string ) this["ISBN"] ; }
    set { this["ISBN"] = value ; }
  }
}

Generating the SQL

It's time to generate the database schema from these classes. You could pipe the output to a text file by running the built.exe from a command prompt. The following class calls a function, OutputTable() for each type that we wish to create a database table for:

public class DatabaseTest
{
   public static void Main()
   {
      OutputTable(typeof(AuthorRow));
      OutputTable(typeof(BookRow));
   }

   public static void OutputTable(System.Type t)
   {
   ....
   ....
   }
}

We could utilize reflection to loop through each class in the assembly, check if it is derived from GenericRow, and output the classes automatically. To keep things simple, we have hard-coded the names of the tables to be generated: AuthorRow and BookRow.

Here is the OutputTable() method:

/// <summary>
/// Produce SQL Server style SQL for the passed type
/// </summary>
/// <param name="t"></param>
public static void OutputTable ( System.Type t )
{
  // Get the DatabaseTable attribute from the type
  object[]  tableAttributes = t.GetCustomAttributes(
                     typeof(DatabaseTableAttribute),true);

  // Check there is one...
  if ( tableAttributes.Length == 1 )
  {
    // If so output some SQL
    Console.WriteLine ( "CREATE TABLE {0}" ,
     ((DatabaseTableAttribute)tableAttributes[0]).TableName ) ;
    Console.WriteLine ( "(" ) ;
    SortedList columns = new SortedList ( ) ;

    // Roll through each property
    foreach ( PropertyInfo prop in t.GetProperties ( ) )
    {
      // And get any DatabaseColumnAttribute that is defined
      object[] columnAttributes = prop.GetCustomAttributes(
                 typeof (DatabaseColumnAttribute),true);

      // If there is a DatabaseColumnAttribute
      if ( columnAttributes.Length == 1 )
      {
        DatabaseColumnAttribute dca = columnAttributes[0]
                                 as DatabaseColumnAttribute ;

        // Convert the ColumnDomain into a SQL Server data type
        string  dataType = ConvertDataType ( dca ) ;

        // And add this column SQL into the sorted list - I want the
        // columns to come out in ascending order of order number
        columns.Add ( dca.Order, string.Format ( "  {0,-31}{1,-20}{2,8}," ,
          dca.ColumnName ,
          dataType ,
          dca.Nullable ? "NULL" : "NOT NULL" ) ) ;
      }
    }
    // Now loop through the SortedList of columns
    foreach ( DictionaryEntry e in columns )
      // And output the string...
      Console.WriteLine ( e.Value ) ;

    // Then terminate the SQL
    Console.WriteLine ( ")" ) ;
    Console.WriteLine ( "GO" ) ;
    Console.WriteLine ( ) ;
  }
}

This code reflects over the type passed in, and looks for the DatabaseTable attribute. If it is found, it writes a CREATE TABLE clause to the output, including the name of the table from the attribute. We then loop through all properties of the type to find any DatabaseColumn attribute. Any property that has this attribute will become a column in the generated table. The string representation of the column is constructed by calling the ConvertDataType() method, which will be shown shortly. This is stored within a sorted collection so that the columns are generated based on the value of the Order property of the attribute. Then we loop through the sorted list and write each value to the output. Finally, we add the closing bracket and a GO command which will instruct SQL Server to execute the batch of statements and create the table.

The ConvertDataType() function converts value from the ColumnDomain enumeration into a database specific data type. In addition, for string columns, we create the column representation to include the size of the column. Here is the implementation:

/// <summary>
/// Convert a ColumnDomain to a SQL Server data type
/// </summary>
/// <param name="dca">The column attribute</param>
/// <returns>A string representing the data type</returns>
private static string ConvertDataType ( DatabaseColumnAttribute dca )
{
  string dataType = null ;

  switch ( dca.DataType )
  {
    case ColumnDomain.DateTime:
    {
      dataType = "DATETIME";
      break ;
    }
    case ColumnDomain.Integer:
    {
      dataType = "INT";
      break ;
    }
    case ColumnDomain.Long:
    {
      dataType = "BIGINT";
      break ;
    }
    case ColumnDomain.String:
    {
      // Include the size of the string...
      dataType = string.Format ( "VARCHAR({0})" , dca.Size ) ;
      break ;
    }
  }

  return dataType ;
}

For each member of the enumeration, we create a column string appropriate for SQL Server. The SQL emitted for the Author and Book classes from all this code looks like this:

CREATE TABLE AUTHOR
(
    AUTHOR_ID           BIGINT          NOT NULL,
    NAME                VARCHAR(64)     NOT NULL,
    DESCRIPTION         VARCHAR(1000)   NULL,
    HIRE_DATE           DATETIME        NULL
)
GO

CREATE TABLE BOOK
(
    BOOK_ID             BIGINT          NOT NULL,
    AUTHOR_ID           BIGINT          NOT NULL,
    NAME                VARCHAR(64)     NOT NULL,
    DESCRIPTION         VARCHAR(1000)   NULL,
    PUBLISH_DATE        DATETIME        NULL,
    ISBN                VARCHAR(10)     NULL
)
GO

This SQL can be run against an empty or pre-existing SQL Server database to create the tables.