Index
Introduction
Since I started using .NET (after Beta 1 Release) I've been developing small applications that store data either in MS Access or SQL Server. Certainly ADO.NET is much simpler than ADO or OleDB. All I know is that I need an open connection and that I can get database data from a DataSet or a DataReader. You choose the way your going to retrieve database data according to your needs.
I'm aware of the power of Typed DataSet and the time it can save, but I prefer my way of doing database programming. I like to have a class and just call a method to update it. Thus if I want a new row in the database I just create a new object instance, set it properties and call the update method. Simple as that!!
But this way of programming made me type a lot of code. The business object class, the database update code and the database reading code. In the beginning I wasn't using stored procedures to update the database, so to update a table I would write a sql statement for each of my business objects. This is a tedious work that had to be redone whenever I changed the database or the business design.
My Solution
The beginning of my solution was the creation of a simple class (DALQueryBuilder, see source code in the last article) that would make me type less code to update an object. All I would do is to add column name/values and the class would then generate the SQL statement. When I accomplish that I was happy for a week...
My mood changed when I started to use SQL Server instead of Access. I shouldn't use pure SQL statements to update my objects, I had to stick with stored procedures. In the beginning the suffering arrived...I had to create a dozen of sql parameters to update my object. A boring work again...
I notice that I could write a simple class to generate this parameters, just like the sql statement generator class. Although this solution would make me type far less code I'd still have to review the update code whenever my solution changed.
Then I came up with the idea of creating classes that would say how they should be persisted in the database. I would use attributes to say what database table my object should be persisted to, as well as other attributes to say what are the properties that should be mapped to table columns. From now on to update my code after a change in the solution I would only change the business object class.
In order to make your reading easier I decided to divide this article in 3 pieces. The first piece will explain about the attributes used to describe an business class. The second part will explain how I gather this information and on the last one I'm gonna show you the complete solution.
Even tough I still use Access the solution was only tested in SQL Server 7.0. Please tell me if you've tested it with Access and it didn't work.
Part I - The attributes
Attributes are a way of giving descriptive information about assemblies, classes, properties, methods and fields. There are some that already are part of the .NET Framework but you can create your own.
I used attributes to describe how a class had to be stored in a database. In a class I would say what properties should be persisted as well as what Stored Procedure should be used to update the database, if any. To describe the columns of this table I used attributes in the class's properties. The column can be a simple data field, a unique key or a foreign key. To have a better understanding of attributes I recommend reading the .NET help or James T. Johnson's article.
How to create my own attribute?
It's fair simple. You create a class derived from the System.Attribute class. As naming convention your class should have an Attribute suffix. When you create you attribute you use an attribute to inform how it should be used. Should it be used in classes? Properties? Multi definitions are allowed?
Now it's time to see some code. These are the attributes used to describe a business object class:
using System;
using System.Data;
namespace DAL
{
[AttributeUsage(AttributeTargets.Property)]
public class BaseFieldAttribute : Attribute
{
string columnName;
public BaseFieldAttribute(string columnName)
{
this.columnName = columnName;
}
public string ColumnName
{
get { return columnName; }
set { columnName = value; }
}
}
[AttributeUsage(AttributeTargets.Property)]
public class DataFieldAttribute : BaseFieldAttribute
{
DbType dbType = DbType.String;
int size = 0;
public DataFieldAttribute(string columnName) : base(columnName)
{
}
public DbType Type
{
get { return dbType; }
set { dbType = value; }
}
public int Size
{
get { return size; }
set { size = value; }
}
};
[AttributeUsage(AttributeTargets.Property)]
public class KeyFieldAttribute : BaseFieldAttribute
{
public KeyFieldAttribute(string columnName) : base(columnName)
{
}
};
[AttributeUsage(AttributeTargets.Property)]
public class ForeignKeyFieldAttribute : BaseFieldAttribute
{
public ForeignKeyFieldAttribute(string columnName) : base(columnName)
{
}
};
[AttributeUsage(AttributeTargets.Class | AttributeTargets.Struct)]
public class DataTableAttribute : Attribute
{
string tableName;
string updateStoredProcedure = "";
public DataTableAttribute(string tableName)
{
this.tableName = tableName;
}
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
public string UpdateStoredProcedure
{
get { return updateStoredProcedure; }
set { updateStoredProcedure = value; }
}
}
}
As you notice in the top of each class there's an AttributeUsage attribute. It just says how the attribute should be used.
How I'd describe a class using those attribute?
Suppose you have a application that stores customers and contacts information. In OO design we start with a Person class. The contact is a person plus address and information to contact them. The customer is a contact + statistics about it purchases. Also a Customer has it dependents with are Persons. I know it's stupid but I'll used it for the rest of these articles.
The code to this classes is listed below:
using System;
using System.Data;
using DAL;
namespace TestApp
{
public class Person
{
string name = "";
int age = 0;
int id = 0;
[KeyField("id")]
public int Id
{
get { return id; }
set { id = value; }
}
[DataField("name", Size=50)]
public string Name
{
get { return name; }
set { name = value; }
}
[DataField("age")]
public int Age
{
get { return age; }
set { age = value; }
}
public override string ToString()
{
return string.Format("{0}, {1} years old", Name, Age);
}
}
[DataTable("contact", UpdateStoredProcedure="sp_UpdateContact")]
public class Contact : Person
{
string phone = "";
string email = "";
string address = "";
string address2 = "";
string city = "";
string postalCode = "";
string state = "";
string country = "";
[DataField("phone", Size=20)]
public string Phone
{
get { return phone; }
set { phone = value; }
}
[DataField("email", Size=80)]
public string Email
{
get { return email; }
set { email = value; }
}
[DataField("address", Size=80)]
public string Address
{
get { return address; }
set { address = value; }
}
[DataField("address2", Size=80)]
public string Address2
{
get { return address2; }
set { address2 = value; }
}
[DataField("city", Size=50)]
public string City
{
get { return city; }
set { city = value; }
}
[DataField("postalCode", Size=20)]
public string PostalCode
{
get { return postalCode; }
set { postalCode = value; }
}
[DataField("state", Size=4)]
public string State
{
get { return state; }
set { state = value; }
}
[DataField("country", Size=50)]
public string Country
{
get { return country; }
set { country = value; }
}
public override string ToString()
{
return string.Format("<Contact>{0} - {1} from {2}", Id, Name, Country);
}
}
public enum CustomerRelationship { Family, Friend, Other };
[DataTable("customerDependent", UpdateStoredProcedure="sp_UpdateCustomerDependent")]
public class CustomerDependent : Person
{
int customerId = 0;
CustomerRelationship relationship = CustomerRelationship.Family;
protected CustomerDependent()
{
}
public CustomerDependent(int customerId)
{
this.customerId = customerId;
}
[ForeignKeyFieldAttribute("customerId")]
public int CustomerId
{
get { return customerId; }
set { customerId = value; }
}
[DataFieldAttribute("relationship")]
public CustomerRelationship Relationship
{
get { return relationship; }
set { relationship = value; }
}
}
public enum CustomerStatus { Active, Inactive };
[DataTable("customer", UpdateStoredProcedure="sp_UpdateCustomer")]
public class BaseCustomer : Contact
{
CustomerStatus status = CustomerStatus.Active;
Decimal totalPurchased = 0M;
int numberOfPurchases = 0;
DateTime dateRegistered = DateTime.Now;
[DataField("status")]
public CustomerStatus Status
{
get { return status; }
set { status = value; }
}
[DataField("totalPurchased")]
public Decimal TotalPurchased
{
get { return totalPurchased; }
set { totalPurchased = value; }
}
[DataField("numberOfPurchases")]
public int NumberOfPurchases
{
get { return numberOfPurchases; }
set { numberOfPurchases = value; }
}
[DataField("dateRegistered")]
public DateTime DateRegistered
{
get { return dateRegistered; }
set { dateRegistered = value; }
}
public override string ToString()
{
return string.Format("<Customer>{0} - {1} from {2}, registered in {3}."+
" #{4} purchases spending a total of $ {5}",
Id,
Name,
Country,
DateRegistered,
NumberOfPurchases,
TotalPurchased);
}
}
public class Customer : BaseCustomer
{
ArrayList dependents = null;
public ArrayList Dependents
{
get
{
if (dependents == null)
{
DAL dal = new DAL();
dependents = dal.GetCustomerDependents(this);
}
return dependents;
}
}
public CustomerDependent NewDependent()
{
return new CustomerDependent(Id);
}
public Decimal PurchaseMedia
{
get { return TotalPurchased / NumberOfPurchases; }
}
}
}
The Person class is the base class of all of our classes. It's the only one that doesn't have a DataTable attribute because it won't be persisted. Only Contacts, CustomerDependents and Customers will be in our database. But the Person class already defines some columns that every derived class must have. For instance the property Id. It's an int key field, an auto number column in this case. The property Name is a string with size 50, and so on. The only different property is the CustomerDependent::CustomerId which is a foreign key.
To create this classes in the SQL Server just run these SQL scripts in the Query Analyzer tool:
if exists (select * from sysobjects where id = object_id(N'[dbo].[contact]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[contact]
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[customer]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[customer]
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[customerDependent]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[customerDependent]
GO
CREATE TABLE [dbo].[contact] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[age] [int] NOT NULL ,
[address] [varchar] (80) NOT NULL ,
[postalCode] [varchar] (20) NOT NULL ,
[phone] [varchar] (20) NOT NULL ,
[email] [varchar] (80) NOT NULL ,
[address2] [varchar] (80) NOT NULL ,
[city] [varchar] (50) NOT NULL ,
[state] [varchar] (4) NOT NULL ,
[country] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[customer] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[age] [int] NOT NULL ,
[address] [varchar] (80) NOT NULL ,
[postalCode] [varchar] (20) NOT NULL ,
[phone] [varchar] (50) NOT NULL ,
[email] [varchar] (80) NOT NULL ,
[address2] [varchar] (80) NOT NULL ,
[city] [varchar] (50) NOT NULL ,
[state] [varchar] (4) NOT NULL ,
[country] [varchar] (50) NOT NULL ,
[totalPurchased] [money] NOT NULL ,
[numberOfPurchases] [int] NOT NULL ,
[dateRegistered] [datetime] NOT NULL ,
[status] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[customerDependent] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[customerId] [int] NOT NULL ,
[relationship] [int] NOT NULL ,
[age] [int] NOT NULL
) ON [PRIMARY]
GO
Coming Up Next
In the next article I show you how Reflection will help us to gather the information needed to update the database. I'll also create a simple tool application that generates the SQL scripts to create tables according to classes defined in a assembly.
