索引
简介
当然,ado.net比起ado和oledb来说要简单多了.我所知道的是打开connection我可以通过dataset和datareader读取数据库.当然你也可以通过你的需要来恢复数据.
我意识到dataset功能强大而且能节省我的时间,但是我还是愿意选择自己的方式了处理数据库程序.我喜欢用一个类来调用方法去更新数据. 因此当用数据库中的一条记录的时候我就会创建一个物件实例,设置它的属性和调用的的更新方法,就是这么简单.
但是这样以来我不得不写老多的代码,业务物件类,数据库更新代码和数据库读取代码.开始的时候,我没有使用存储过程更新数据库,因此我要对没一个业务物件来写sql语句.在我修改数据库或业务物件的时候,这样的工作就显得特别的乏味.
我的方案
我的方案是以创建简单的类开始的,是我写较少的代码来更新物件. 所有我做得就是增加字段的名字,值和类,然后生成sql语句. 当我结束这一切的时候,我一周都沉醉在快乐之中…
可是当我开始使用sqlServer代替access的时候,我的情绪变化了.我不能使用单纯的sql语句来更新我的物件,我不得不用存储过程.不幸又开始了…我不得不创建成打的sql语句来更新物件.枯燥的工作又开始了…
我注意到我能写简单的类来生成参数,正如sql语句生成类一样.尽管这个方案能使我写少一点的代码,但是无论何时我的方案要是有所改动的话,我仍然要去检查我的更新代码.
接着我有了在数据库中如何创建持续化类的想法.使用属性来描述数据库的表,这样属性就可以被映射到表中的字段了.这样我就可以仅仅修改业务物件类了.
为了你更容易读懂,我把这篇文章分成了三部分.第一部分展示使用属性来描述一个业务类.第二部分展示我是如何采集信息,最后我将展示完整的方案.
I - Attributes
attributes是用来描述装配件,类,属性,方法和字段的.在.net 架构里已经有些地方使用了.但是你可以创建自己的属性.
我使用attributes来描述一个类是如何被存储到数据库中的.在一个类中,我将指出那些属性应该被持续化,正如存储过程是如何更新数据库的.为了描述表里的列,我在类的属性里面使用了attributes. 列可以是一个简单的数据字段,唯一的键或外键.
如何创建自己的attribute?
相当容易,你可以创建一个继承于System.Attribute的类.至于命名习惯你可以加上个Attribut后缀.当你创建一个attribute,你要知道该attribute如何被用.是否应该被用在类?属性,或多个定义是允许的?
现在是来看些代码的时候了.这些attributes被用来描述一个业务物件类:
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; }
}
}
}
正如你看到的,每个类的上面有个attributeusage attribute.它是来指示attribute是如何被使用的.
我是如何使用这些 attribute来描述一个类的呢?
假设你有一个应用来保存用户和联系的信息.在OO设计中,我们以一个Person来开始,那么联系信息就是一个person加上地址和信息.用户就是联系信息加上购买信息. 当然用户和别人是有依赖关系的.如果我在别的文章使用这些当然是相当愚蠢的.^_^
这个类的代码如下:
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; }
}
}
}
Person类是我们所有类的一个基类.它是唯一的一个不使用attribute的类,以为他没有必要持续化.只有Contacts,CustomerDependents和customer将会存在数据库中.但是person类已经定义了一些列每个子类必须有的列.对于属性ID来说,它是一个int 关键键字段,在这个例子中来说是一个自动的字段.唯一不同的是CustomerDependent::CustomerId是一个外键.
在sql分析器中为这个类创建如下的脚本:
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
下一步
下篇文章我将展示如何使用反射来帮助我们采集需要的信息来更新数据库.我也会通过在装配件中定义的类创建简单的工具来生成sql脚本来创建表.
