表的通用增删改处理
目的：当我们一直为每个表的增删改写都写一个过程，感觉乏味和累赘的时候，就想写个通用的增删改，那就会让编程重新成为一件快乐的事。思路：很简单，先定义一个列定义（就是描述数据库中表的各字段的属性）Attribute，增删改操作都通过反射查找该自定义Attribute的各项内容，从而进行相应的处理。下面给出简单的代码：
1。定义列定义特性：
[AttributeUsage(AttributeTargets.All)]
public sealed class ColumnDefineAttribute : Attribute
{
/// <summary>
/// 数据库中的列名
/// </summary>
public string ColumnName
{
get;
set;
}
/// <summary>
/// 是否为主键
/// </summary>
public bool IsPrimaryKey
{
get;
set;
}
/// <summary>
/// 显示在用户界面上的名称
/// </summary>
public string DisplayName
{
get;
set;
}
/// <summary>
/// 数据类型
/// </summary>
public SqlDbType DbType
{
get;
set;
}
/// <summary>
/// 长度
/// </summary>
public int Length
{
get;
set;
}
/// <summary>
/// 列固定的取值(比如:性别,如果定义为位类型,就0,1）
/// </summary>
public object[] ColumnValues
{
get;
set;
}
/// <summary>
/// 在添加到数据库中时是否使用数据库所在主机的系统的时间（这里涉及到像C、S两端系统时间不一致，应该用S端的系统时间)
/// </summary>
public bool UseSystemDateTimeWhenAdd
{
get;
set;
}
/// <summary>
/// 在修改时是否使用数据库所在主机的系统的时间
/// </summary>
public bool UseSystemDateTimeWhenChange
{
get;
set;
}
/// <summary>
/// 列固定的取值的显示文本（比如：性别，显示为“男”，“女”）
/// </summary>
public object[] DisplayValues
{
get;
set;
}
/// <summary>
/// 是否是删改前的校验位
/// </summary>
public bool IsCheckField
{
get;
set;
}
public override string ToString()
{
return DisplayName;
}
}//end class
2。定义一个实体类接口，所有的实体类都实现它（这样在增删改方法中都可以以该接口作为通用类型）：
/// <summary>
/// 实体类接口
/// </summary>
public inte易做图ce IEntityObject : INotifyPropertyChanged
{
/// <summary>
/// 数据库表名称
/// </summary>
string TableName { get; }
/// <summary>
/// 数据库中字段与实体类中的属性名对应关系
/// </summary>
Dictionary<string, string> ColumnNamePropertys { get; }
}
3。利用ORM思想把数据库中的某个具体表映射成一个EntityObject，并为每个属性应用定义特性：
[DataContract]
public class Customer : IEntityObject
{
public string TableName
{
get { return "Customer"; }
}
[DataMember]
private string _ID = string.Empty;
[ColumnDefine(ColumnName =Customer.ColumnNames.ID, DisplayName = Customer.ColumnNames.ID,
DbType = SqlDbType.VarChar, Length = 50,IsPrimaryKey=true)]
public string ID
{
get
{
return _ID;
}
set
{
if (_ID != value)
{
_ID = value;
NotifyPropertyChanged("ID");
}
}
}
[DataMember]
string _Name = string.Empty;
[ColumnDefine(ColumnName = "Name", DisplayName = "姓名",
DbType = SqlDbType.VarChar, Length = 50)]
public string Name
{
get { return _Name; }
set
{
if (_Name != value)
{
_Name = value;
NotifyPropertyChanged("Name");
}
}
}
[DataMember]
string _Sex = string.Empty;
[ColumnDefine(ColumnName = "Sex", DisplayName = "性别"
, DbType = SqlDbType.VarChar, Length = 50, DisplayValues = new object[] { "男", "女" })]
public string Sex
{
get { return _Sex; }
set
{
if (_Sex!=value)
{
_Sex = value;
NotifyPropertyChanged("Sex");
}
}
}
[DataMember]
string _Tel = string.Empty;
[ColumnDefine(ColumnName = "Tel", DisplayName = "联系电话",
DbType = SqlDbType.VarChar, Length = 50)]
public string Tel
{
get { return _Tel; }
set
{
if (_Tel!=value)
{
_Tel = value;
NotifyPropertyChanged("Tel");
}
}
}
[DataMember]
string _Company = string.Empty;
[ColumnDefine(ColumnName = "Company", DisplayName = "公司名称",
DbType = SqlDbType.VarChar, Length = 50)]
public string Company
{
get { return _Company; }
set
{
if (_Company!=value)
{
_Company = value;
NotifyPropertyChanged("Company");
}
}
}
[DataMember]
string _CompanyTel = string.Empty;
[ColumnDefine(ColumnName = "CompanyTel", DisplayName = "公司电话",
DbType = SqlDbType.VarChar, Length = 50)]
public string CompanyTel
{
get { return _CompanyTel; }
set
{
if (_CompanyTel!=value)
{
_CompanyTel = value;
NotifyPropertyChanged("CompanyTel");
}
}
}
[DataMember]
string _CompanyAdd = string.Empty;
[ColumnDefine(ColumnName = "CompanyAdd", DisplayName = "公司地址",
DbType = SqlDbType.VarChar, Length = 50)]
public string CompanyAdd
{
get { return _CompanyAdd; }
set
{
if (_CompanyAdd!=value)
{
_CompanyAdd = value;
NotifyPropertyChanged("CompanyAdd");
}
}
}
[DataMember]
DateTime _AddDate;// = DateTime.MinValue;
[ColumnDefine(ColumnName = "AddDate", DisplayName = "添加日期",
DbType = SqlDbType.DateTime, Length = 0,UseSystemDateTimeWhenAdd=true)]
public DateTime AddDate
{
get { return _AddDate; }
set
{
if (_AddDate!=value)
{
_AddDate = value;
NotifyPropertyChanged("AddDate");
}
}
}
[DataMember]
int _CheckField;
[ColumnDefine(ColumnName = "CheckField", DisplayName = "校验值",
DbType = SqlDbType.Int, Length = 0,IsCheckField=true)]
public int CheckField
{
get { return _CheckField; }
set
{
if (_CheckField != value)
{
_CheckField = value;
NotifyPropertyChanged("CheckField");
}
}
}
/// <summary>
/// 列名
/// </summary>
public class ColumnNames
{
public const string ID = "ID";
public const string Name = "Name";
public const string Sex = "Sex";
public const string Tel = "Tel";
public const string Company = "Company";
public const string CompanyTel = "CompanyTel";
public const string CompanyAdd = "CompanyAdd";
public const string AddDate = "AddDate";
public const string CheckField = "CheckField";
}//end class
#region INotifyPropertyChanged 成员
public event PropertyChangedEventHandler PropertyChanged;
#endregion
private void NotifyPropertyChanged(string name)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(name));
}
}
#region IEntityObject 成员
[DataMember]
Dictionary<string, string> _ColumnNamePropertys;
public Dictionary<string, string> ColumnNamePropertys
{
get
{
if (_ColumnNamePropertys == null)
{
_ColumnNamePropertys = new Dictionary<string, string>();
_ColumnNamePropertys.Add(Customer.ColumnNames.ID, "ID");
_ColumnNamePropertys.Add(Customer.ColumnNames.Name, "Name");
_ColumnNamePropertys.Add(Customer.ColumnNames.Sex, "Sex");
_ColumnNamePropertys.Add(Customer.ColumnNames.Company, "Company");
_ColumnNamePropertys.Add(Customer.ColumnNames.Tel, "Tel");
_ColumnNamePropertys.Add(Customer.ColumnNames.CompanyTel, "CompanyTel");
_ColumnNamePropertys.Add(Customer.ColumnNames.CompanyAdd, "CompanyAdd");
_ColumnNamePropertys.Add(Customer.ColumnNames.AddDate, "AddDate");
_ColumnNamePropertys.Add(Customer.ColumnNames.CheckField, "CheckField");
}
return _ColumnNamePropertys;
}
}
#endregion
}//end class
其中的[datacontract],[datamember]是应用于.net3.0以上的WCF中，不必管它。
--------------------编程问答--------------------
4。通用的增删改操作，仅以增为例，通过遍历列名与属性对应字典，取得列名，应用反射获得属性相关信息（实例Attribute：即数据库中列定义，属性值等）：
public static bool Add(EntityObject.IEntityObject entity)
{
using (SqlConnection conn = new SqlConnection(Connection._CRM))
{
string sql = "insert into {0}({1}) values ({2})";
string columnString = string.Empty;
string valueString = string.Empty;
foreach (KeyValuePair<string, string> kvp in entity.ColumnNamePropertys)
{
columnString += kvp.Key + ",";
valueString += "@" + kvp.Key + ",";
}
columnString = columnString.Substring(0, columnString.Length - 1);
valueString = valueString.Substring(0, valueString.Length - 1);
sql = string.Format(sql, entity.TableName, columnString, valueString);
SqlCommand cmd = new SqlCommand(sql, conn);
foreach (KeyValuePair<string, string> kvp in entity.ColumnNamePropertys)
{
foreach (Attribute attr in entity.GetType().GetProperty(kvp.Value).GetCustomAttributes(false))
{
if (attr.GetType() == typeof(EntityObject.ColumnDefineAttribute))
{
EntityObject.ColumnDefineAttribute cdattr = (EntityObject.ColumnDefineAttribute)attr;
if (cdattr.UseSystemDateTimeWhenAdd)
{
cmd.Parameters.Add("@" + kvp.Key, cdattr.DbType, cdattr.Length).Value = DateTime.Now;
}
else
{
cmd.Parameters.Add("@" + kvp.Key, cdattr.DbType, cdattr.Length).Value =
entity.GetType().GetProperty(kvp.Value).GetValue(entity, null);
}
}
}
}
conn.Open();
cmd.ExecuteNonQuery();
return true;
}
}
另外：这里并没有使用事务，其实加上事务也很简单。还可以自己做个代码生成工具，就不必再去键入实体类定义代码了。
欢迎拍砖。
--------------------编程问答-------------------- 没有感觉你这有什么。
要直接操作数据库，可以写SQL语句（使用数据库通用类，再结合代码生成器），这样可以高效和灵活；
要想使用ORM，有NHibernate、Linq to SQL、Entity Framework，你觉得你写的跟它们比有效率吗？
作为研究ORM的实现有些积极意义，除此之外，算是重复发明轮子。
--------------------编程问答-------------------- 留印 以后观看 --------------------编程问答-------------------- 楼主这个通用太复杂了 --------------------编程问答-------------------- 有时间看看 --------------------编程问答-------------------- 占个位置慢慢看 --------------------编程问答-------------------- 调用反射，不是很好 --------------------编程问答-------------------- 精神上支持一下的 --------------------编程问答-------------------- mark
补充：.NET技术 , C#
